PL/SQL :: Using Collection Variable With OUT Parameter?
Aug 8, 2012
The following procedure stores the retrieved data in o_ref_primary_dept collection variable,suppose I want to display the data from that variable while executing what code I have to write.
CREATE OR REPLACE PROCEDURE sp_ost(
o_ref_primary_dept OUT PRIMARY_DEPT)
IS
l_primary_dept LONG;
BEGIN
l_primary_dept :=
'SELECT emp_obj(empno,ename)'
|| ' FROM emp';
EXECUTE IMMEDIATE l_primary_dept BULK COLLECT INTO o_ref_primary_dept;
END;
how to use collection variables as OUT parameters in procedure.
The following procedure stores the retrieved data in o_ref_primary_dept collection variable, suppose I want to display the data from that variable while executing what code I have to write.
CREATE OR REPLACE PROCEDURE sp_ost( o_ref_primary_dept OUT PRIMARY_DEPT) IS l_primary_dept LONG; [code].....
how to use collection variables as OUT parameters in procedure.
Is there any table except (global temp table and permanent table) which can be used to store data and be used in inner, left and right join for a session.
Declare Cursor c1...; Cursor c2...; begin open c1 ; fecth c1 bulk collect into v1; close c1;
[Code]...
Is there any way by which if condition gets true then v1 gets appended rather than being overwritten?
declare type lst_deptno is table of dept.deptno%type index by binary_integer; type lst_deptno_emp is table of emp.deptno%type index by binary_integer; v_deptno lst_deptno; v_deptno_emp lst_deptno_emp; cursor c1 is select deptno from dept;
Is it possible to bind collection while opening a ref cursor. Find below the code that I am trying. My goal is to open cursor once using collection variable. Can it be done using DBMS_SQL ?
DECLARE TYPE typ_emp_rec_in IS RECORD ( deptno NUMBER, sal NUMBER [code]......
While reading data from collection variable using ref cursor . I am getting the below two errors.
PLS-00382:Expression is of wrong type ORA-22905 Cannot access rows from a non-nested table item.
CREATE OR REPLACE PACKAGE APPS_GLOBAL.GIIOMEGAORDERLIST AS TYPE BU_LIST_TYPE IS TABLE OF VARCHAR(50); TYPE OFFER_DETAIL IS RECORD ( GII_BU VARCHAR(50), GII_OFFER NUMBER, [code]........
I am reading in a selection of parameters. I have created a new variable which I want to set according to the value of one of the input parameters.
I am doing this straight after declaring the variable, but before the cursors and BEGIN statement It is throwing an error when I do this - but I have to do it before the cursors.the variable I am setting is: v_fptransType you can see the IF statement towards the end of the code.
the error I am getting is:Error(28,3): PLS-00103: Encountered the symbol "IF" when expecting one of the following: begin function package pragma procedure subtype type use <an identifier> <a double-quoted delimited-identifier> form current cursor The symbol "begin" was substituted for "IF" to continue.
beginning of the
create or replace PROCEDURE "P_GLPOST" (i_entity IN varchar2, i_transType IN varchar2, i_startDate IN VARCHAR2, i_endDate IN VARCHAR2, i_accountPeriod IN VARCHAR2, i_includeInternals IN NUMBER, i_chargeable IN NUMBER, i_trialPost IN NUMBER, i_postingReport IN NUMBER, TESTER IN VARCHAR2) is --set serveroutput on size 1000000;
There is a nested table with in a nested table type and i want to print the value and again assign a new value to the next subscript and i have tried a lot but couldn't find any solution.
declare type type_name is table of varchar2(10); type type_name1 is table of type_name; names type_name1:=type_name1(type_name('hello')); begin -----HOW TO PRINT A VALUE-------- -----HOW TO ASSIGN A NEW VALUE TO NEW SUBSCRIPT null; end;
1) need to print the values of names(1) 2)Assign a value to names(2)
I have been doing some code in collection for testing. I have been doing the below but getting the error.
SQL> desc t_mine; Name Null? Type ----------------------------------------------------- -------- OWNER NOT NULL VARCHAR2(30) OBJECT_NAME NOT NULL VARCHAR2(30)
SQL> ed Wrote file afiedt.buf
1 declare 2 v_start_time PLS_INTEGER := DBMS_UTILITY.GET_TIME; 3 v_elapsed PLS_INTEGER; 4 type allobjects_record is record 5 (owner varchar2(1000) [code].......
ERROR at line 15:
ORA-06550: line 15, column 15: PL/SQL: ORA-00947: not enough values ORA-06550: line 15, column 3: PL/SQL: SQL Statement ignored
remove duplicates from my collection(table type).Or an alternate solution for my issue is get clean data into my collection which i am not able to get to either.
Object creation
create table testingtype_table(ordernumber number,org_id number , company_name varchar2(10)) insert into testingtype_table values (1124,2424,'cbaaa'); insert into testingtype_table values (1124,2424,'cbaaa');
create or replace type testingtype_obj as object (ordernumber number,org_id number , company_name varchar2(10)); create or replace type testingtype_tab as table of testingtype_obj;
Code Block declare l_testingtype_tab testingtype_tab := testingtype_tab(); begin select distinct testingtype_obj(ordernumber ,org_id ,company_name) bulk collect into l_testingtype_tab from testingtype_table; end;
If only i can get a way to bulk collect only distinct values into the table type that will just do great but when i try the above (with distinct highlighted in red) it throws an error
ORA-22950: cannot ORDER objects without MAP or ORDER method
CREATE OR REPLACE PACKAGE BODY pkg_das_stag_to_master AS PROCEDURE sp_load_dasstage_security AS cursor cur_stag is select Asset_id, ID_ISIN, ID_SEDOL1, ID_CUSIP, [code].........
error:
66/35 PLS-00302: component 'ASSET_ID' must be declared 66/35 PLS-00302: component 'ASSET_ID' must be declared 66/18 PL/SQL: ORA-00904: "TYP_SECURITY_VAR"."ASSET_ID": invalid identifier 63/1 PL/SQL: SQL Statement ignored
I have an existing Procedure that uses a ref cursor as a parameter. The procedure currently simply selects data into the ref cursor using an OPEN FOR and then exits.
I have a need to extend the processing of the procedure. This will mean retrieving extra columns in the select and also removing some of the retrieved records.
I have the constraint that the definition of the REF CURSOR (which is based on a record type as follows) cannot be modified.
TYPE t_charge IS RECORD ( id number, date_from invoice_detail.from_date%TYPE, date_to invoice_detail.to_date%TYPE, description invoice_detail.desc_text%TYPE, amount invoice_detail.amount%TYPE ); TYPE t_charge_cursor IS REF CURSOR RETURN t_charge;
The procedure does this
PROCEDURE get_bill_lines ( p_bill_id IN NUMBER, bill_lines_list OUT t_charge_cursor ) IS OPEN bill_lines_list FOR SELECT id, from_date, to_date, desc_text, amount from invoice_table where id = p_bill_id;
I am thinking instead of selecting directly into the bill_lines_list parameter I will use a local REF CURSOR to select into a COLLECTION. I can then do some work on the collection, similar to below:
PROCEDURE get_bill_lines ( p_bill_id IN NUMBER, bill_lines_list OUT t_charge_cursor ) TYPE t_local_charge IS RECORD (
[code].....
This all works fine, but the question is, how do I then put the collection into the bill_lines_list REF CURSOR where the '*** HERE ***' comment is so I can send the existing REF CURSOR structure back? (Remember I cannot change any definition that is referenced externally)
I need to check if a small collection contains a specific value. I know I can use the .exist method to let me know if the nth element exists. But is there a similar syntax for checking if an actual value exists?
So the below collection has 6 values (300,301,310,320,400,410) after the cursor values are fetched. I want to use something similar to the below exists syntax in order to search the collection for a value (rather than position) within an IF statement.
The below code shows the kind of thing I want to do. Currently, unless my test value (310) is in the range 1 to 6 the test will always return FALSE. But is there a similar syntax for testing against the value?
The example I have given is a simplification of what I need. In reality there will be more than one test value... returned by another cursor.
DECLARE CURSOR c_type_id_usg IS
[Code]....
-- get the list of sms type id usg values OPEN c_type_id_usg; FETCH c_type_id_usg bulk collect into l_type_ids; CLOSE c_type_id_usg;
-- the above returns value 300,301,310,320,400,410
IF l_type_ids.exists(310) then dbms_output.put_line('I have found an entry '); else dbms_output.put_line('I have NOT found an entry ' ); end if; END;
I'm working on a plsql program and i'm using collections. I loop the collection and delete rows of it depending on the edits of my program. Here is the question.
if my collection holds rows [1]value [2]value [3]value
i can simply do something like FOR indx in invoice.first..invoice.lasthowever if i delete row 2 of my collection i get an error. no data found. ive been researching this site
[URL].......
rows [1]value [3]value [4]value
is there a way to tell plsql i just want it to loop the collection from top to bottom regardless of the index values?
In PL/Sql, I create a new collection using TYPE. Then populate it with BULK COLLECT.The next step I need is to load data from the collection to pass as WHERE condition to a ref cursor.How I can do this?Every test I did fais. It seems to me that I cannot use some kind of SELECT to retrieve all data from the collection, but I might use a cursor to read row by row. (I'm on Oracle 11gR2 DB )
while doing stats collection weather system takes the backup of current statistics. i think we can specify stattab. but weather it takes stats backup before over writing? I got this requirement as a part of upgrade, i have already gone through export_schema_stats and import_schema stats already. Just trying all other possible options only.
I have the following questions you can create a bulk collect in which he has 3 fields and one of them is a type collect failing index or other bulk collect?.If so, how would the procedure for when to insert the first record sub fill the bulk collect. being for example something like this:
----------------------------------------------------------------- index | codigo | nombre | telefono | | |----------------------- | index | telefono ----------------------------------------------------------------- | | | |
I am getting the below output when I tried to take the "Payables" RDA from command-line (Application RDA is failing to collect) I am getting the below output.
appldev@devpi:/home/appldev>cd $IZU_TOP/bin appldev@devpi:/d02/appldev/apps/apps_st/appl/izu/12.0.0/bin>./rda.sh -vdCRP -e APPL_SHORT='SQLAP' ACT Collecting diagnostic data ... No module requiring data collection Generating the reports ... No pending report
[code]....
why it is saying that "No module requiring data collection"?
I have following query: type EMP_REC is record ( id number, name varchar2(20), dept number );
type EMP_TBL IS TABLE OF EMP_REC INDEX BY BINARY_INTIGER; EMP_TABL1 EMP_TBL;
select * BULK COLLECT into EMP_TBL1 FROM emp;
How to remove duplicate records from EMP_TBL1 collection if exists.I don't want to remove duplicate records from main table. But actually want to remove duplicate records from EMP_TBL1 collection if exists.
we had an issue with our 10.2.0.4.0 two node RAC Database where when running stats collection caused the database performance to go down and in AWR I noticed the following:
Top 5 Timed Events Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class gc buffer busy 95,077 73,786 776 76.0 Cluster cursor: pin S wait on X 3,524,808 54,467 15 56.1 Concurrency library cache lock 30,223 13,660 452 14.1 Concurrency gc cr request 2,876 3,594 1,250 3.7 Cluster library cache pin 1,740 1,800 1,035 1.9 Concurrency
when looked under Library Cache Activity section in the AWR report, I see the following:
Namespace Get Requests Pct Miss Pin Requests Pct Miss Reloads Invali- dations BODY 59 3.39 3,056 0.13 1 0 CLUSTER 70 0.00 73 0.00 0 0 INDEX 178 0.00 925 0.00 0 0 SQL AREA 423,064 50.25 2,465,209 2.37 182 119,671 TABLE/PROCEDURE 1,848 21.27 22,638 0.62 70 0 TRIGGER 14 0.00 1,801 0.06 1 0
does this mean, we are having memory crunch for this instance on node 1? we have sga_target=26gb and sga_max_size=32gb set for this instance.
I have been testing some functionalist, I have been trying to get the updated dept no values into an array and then print the count. But i am getting the following error.
I have implemented, the whole example is to know about 'dynamic sql returning clause into collection'
s@ORCL> select * from t_dept;
DEPTNO DNAME LOC ---------- -------------- ------------- 10 comp NEW YORK 20 Compt DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 60 Comp Ahaaa 80 data ab 80 data ab 80 data ab 80 data ab
9 rows selected.
s@ORCL> ed Wrote file afiedt.buf
1 declare 2 type tp_dept is table of number; 3 arr_dept tp_dept; 4 begin 5 execute immediate q'['update t_dept set dname = 'Pointers' where deptno = 80 returning deptno into :out]' 6 returning into arr_dept; 7 dbms_output.put_line('The count is '||arr_dept.count); 8* end; s@ORCL> / returning into arr_dept; * ERROR at line 6: ORA-06550: line 6, column 19: PLS-00597: expression 'ARR_DEPT' in the INTO list is of wrong type ORA-06550: line 5, column 4: PL/SQL: Statement ignored
I'm trying to sort a collection in a nested table in PL/SQL so these value can be used later for a display and for export to Excel. The sort is failing.
-- PLS-00642: local collection not allowed in SQL statements
-- PL/SQl: ORA-00902: invalid datatype
The error message are also noted below in the code on the line that fails.
A quick overview of this code- Using a nested table MyNestedTable the values from several select queries are combined into MyRecordsetZero using CURSOR, and MULTISET UNION. I'm trying to either sort MyRecordsetZero or populate MyRecordsetSorted with the sorted values for futher use.
IMPORTANT: The code is running in an enviroment that does not have permission to create.
I have a collection of objects built in a PL/SQL program.
I want to keep only the distinct elements of this collection for the rest of the program.
I failed to find the correct way to do it
For instance (the actual object is far more complex containing object attributes itself), with the following types:
drop type t1b / drop type t1a / create or replace type t1a as object ( v1 integer, v2 integer ) / create or replace type t1b as table of t1a / I have the following variable:
v t1b := t1b(t1a(1,2),t1a(1,2));
And I want to get only one "t1a(1,2)" in my collection.
My first idea (actually the second one but it does not matter) was to use DISTINCT:
SQL> declare
2 v t1b := t1b(t1a(1,2),t1a(1,2)); 3 begin 4 select distinct t1a(v1,v2) bulk collect into v from table(v); 5 dbms_output.put_line(v.count); 6 end; 7 / declare * ERROR at line 1:
ORA-22950: cannot ORDER objects without MAP or ORDER method ORA-06512: at line 4
As I said the object is far more complex and builting a MAP function is quite tedious (but I will do it if there is no other way).
The next idea was to use multiset operators:
SQL> declare 2 v t1b := t1b(t1a(1,2),t1a(1,2)); 3 begin 4 select v multiset intersect distinct v into v from dual; 5 dbms_output.put_line(v.count); 6 end; 7 / 1
PL/SQL procedure successfully completed.
This works well but I suspect this is not the correct way and there is one to do it in PL/SQL but currently failed to find it.
