I've come across an example of doing this on the net which also fails on my DB (10g) but here's my version. ... TYPE prog_rec_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER; prog_rec_list prog_rec_type; begin ... EXECUTE IMMEDIATE 'SELECT PROGRESS_RECID FROM ' || v_table_name || v_where BULK COLLECT INTO prog_rec_list; --ERROR FOUND...
I am getting the following error when trying to execute a stored procedure in Oracle that contains two input parameters: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'P_GET_NEXT_AVAILABLE_RUN' ORA-06550: line 1, column 7: PL/SQL: Statement ignored Both input parameters require values...
I have this trigger: create or replace trigger t_calctotal after insert or update on item_fornecimento REFERENCING NEW AS NEW OLD AS OLD for each row begin if inserting then dbms_output.put_line(' On Insert'); update fornecimento f set f.total_enc_fornec = f.total_enc_fornec +:NEW.prec_total_if where f.id_fornecimento = :NEW.id...
Hi Gurus, We currently are in a debate within our IT group over what the best practice is for handling the majority of all of our processes. Our IT director is pushing for everything to be event driven. His reasoning is that is will save on resources and it is the "best method" for sending ship notifications, order validations, order p...
I have this procedure: create or replace PROCEDURE CONVERTE IS CURSOR oldemployees IS SELECT * FROM emp1 WHERE data_saida= NULL; new_ndep emp1.num_dep%type; bi_inexistente EXCEPTION; dep_inexistente EXCEPTION; employeeNr emp1.num_empregado%type; BEGIN FOR old_emp IN oldemployees LOO...
I have many cursors that all return rows with the same fields: a numeric ID field and an XMLType field. Every time I access one of these cursors (each cursor has now got its own function for access), I go through the same pattern: --query behind cursor is designed to no more than one row. for rec in c_someCursor(in_searchKey => local_se...
I have a little DB, for academic purpose only, and I have object tables at most. I've created a entity-relationship model (ERM) in Power Designer and the program, by default, creates index for the serial id's for each table. I want to know how do I use a index like that on a query.Say I would want to find a product by its id, but using...
Hi there, I have this big code where I want 3 things in my search: 1- look for all the orders (delivered and not) that match the search: 2- look for all the pendent orders that match the search; 3- look for all the delivered orders that match the search; create or replace function search_order(search IN VARCHAR2, a_option NUMBER) R...
How can I return from float/decimal value with the following: SELECT 210 FROM DUAL ...to get: 210.00 ...instead of 210? Using pl/sql oracle 10g ...
I have a table for orders and it has 2 column with DATE types: delivery_date and order_date. In my sqldeveloper, all the dates I've inserted via Java or by hand are in the format 10.01.25 for the 25th Jan of this year. When I try to see the the total_price for orders between one date and another, I force the format like this: create...
I need to create a table every morning based on overnight generated data from a massive table. This will then be accessed by a handful of users form Excel. My initial approach was to use a materilazed view and when this was rejected (for political reasons) to used Managed XLL but this was rejected for other reasons. I don't want to get ...
I have table which holds a company hierarchy. It was decided to use this flat table as there is no defined number of levels in the company. The table workes great, and if you were to use cascading lists on a client it's perfect. However, I need to see a "section", and all other "sections" owned by it. Hopefully the information below will...
I have the following tables: Employees with level (rank or wtv you may call it): income_value,id, etc. INCOME that stores the rank vs. the income value (very tinny and static) REGIST_INCOME table with the following columns: ID_REG; ID_EMPLOYEE; MONTH_and_Year_OF_PAYMENT DATE (I want to format to yy.mm); DATE_OF_PAYMENT DATE ...
I have a hierarchical query in Oracle 10 SQL that used to work. However, I removed the materialized view it was based on, and now I can't get it to come out properly, even leaving that view out altogether. The original query looked like this: select oh.name, oh.description , sys_connect_by_path(groupname, ':') "Groups" , (select c...
I am failry new to PL/SQL and need a quick help with this.The whole cursor deal is unnerving but I am getting used to it. Please focus only on cursors 1) cur_cheese_proprietary and 2)cur_cheese_mms.I am trying to retrieve those rows from cur_cheese_mms which have the same cheeseNumber as the cheeseNumber in current row of cursor cur_ch...
I am catching errors from a bulk insert operation like this: begin --bulk insert forall i in v_data.first .. v_data.last save exceptions insert into my_filter_table values v_data (i); commit; exception -- catch and print the saved-up DML errors. when X_DML_ERRORS then declare v_iteration...
I have this table: ALLITEMS --------------- ItemId | Areas --------------- 1 | EAST 2 | EAST 3 | SOUTH 4 | WEST The DDL: drop table allitems; Create Table Allitems(ItemId Int,areas Varchar2(20)); Insert Into Allitems(Itemid,Areas) Values(1,'east'); Insert Into Allitems(ItemId,areas) Values(2,'east'); insert ...
Hi, Im trying to execute a refresh on a materialized view, but I cant get the script to compile. CREATE OR REPLACE PROCEDURE REFRESH_MV AS BEGIN exec DBMS_MVIEW.REFRESH('my_mat_view_mv','C'); END REFRESH_MV; I get the message: ORA-06550: line 3, column 9: PLS-00103: Encountered the symbol "DBMS_MVIEW" when expecting one o...
hi , I'm using an existing stored procedure in my C code. The stored procedure in question has been compiled and is proven to work without any errors. However, when I use the same in my C code, its failing with the above error. The Store procedure definition looks like : CREATE OR REPLACE FUNCTION SP( srq_id integer , ...
I have the following (I can't change it, it is provided for me to use): TYPE Person_Rec IS RECORD( ID NUMBER(10), Name VARCHAR2(30), Age Number(10)); PROCEDURE Modify_Person(rec IN Person_rec, option IN NUMBER) IS BEGIN ... END; How would I call Modify_Person externally, using some SQL statement - from an SQL console or C++, etc? How...