Excellent!!!!!
A reader, June 12, 2003 - 2:47 pm UTC
Cursor details not known
Yogesh, June 12, 2003 - 3:06 pm UTC
It was very useful reply ... one more subquestion
If I don't know what type of ref cursor is going to be returned .. i.e. I don't know in which package it is defined ...(so I can't define variable type as ...%TYPE) Is it still possible to to refer it in other schema?
June 12, 2003 - 3:52 pm UTC
if you don't know the type of cursor being returned, you cannot possible pass it onto something else.
You need to get your code "under control". How many weak ref cursor types do you need in your system? one.
In 9i, you would just use SYS_REFCURSOR, until then, you just have to standardize your code.
Thanks a ton
Yogesh, June 12, 2003 - 4:16 pm UTC
Thanks for valuable reply. But I have 8.1.7 in production, so need to go with "Package Control" Thanks once again
June 12, 2003 - 4:25 pm UTC
hold on, backup the bus. looks like ref cursors (weak ones at least) are
"special", different from other variable types! They are the exception to the
rule (and proof that yes, we all learn new stuff every day, i did not expect
this myself).
This example uses 3 separate types, no worries. If you use a STRONGLY typed
cursor:
type rc is ref cursor return dual%rowtype;
it will NOT work, but weak ref cursors do just fine:
ops$tkyte@ORA817DEV> create user x identified by x;
User created.
ops$tkyte@ORA817DEV> create user y identified by y;
User created.
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> grant create session, create procedure to x;
Grant succeeded.
ops$tkyte@ORA817DEV> grant create session, create procedure to y;
Grant succeeded.
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> @connect x/x
ops$tkyte@ORA817DEV> set termout off
x@ORA817DEV> REM GET afiedt.buf NOLIST
x@ORA817DEV> set termout on
x@ORA817DEV>
x@ORA817DEV> create or replace package foo
2 as
3 type rc is ref cursor;
4 procedure p ( p_cursor out rc );
5 end;
6 /
Package created.
x@ORA817DEV> create or replace package body foo
2 as
3 procedure p ( p_cursor out rc )
4 is
5 begin
6 open p_cursor for select * from dual;
7 end;
8 end;
9 /
Package body created.
x@ORA817DEV> grant execute on foo to public;
Grant succeeded.
x@ORA817DEV>
x@ORA817DEV> @connect y/y
x@ORA817DEV> set termout off
y@ORA817DEV> REM GET afiedt.buf NOLIST
y@ORA817DEV> set termout on
y@ORA817DEV>
y@ORA817DEV> create or replace package bar
2 as
3 type rc is ref cursor;
4 procedure p ( p_cursor in rc );
5 end;
6 /
Package created.
y@ORA817DEV> create or replace package body bar
2 as
3 procedure p ( p_cursor in rc )
4 is
5 l_rec dual%rowtype;
6 begin
7 fetch p_cursor into l_rec;
8 dbms_output.put_line( l_rec.dummy );
9 close p_cursor;
10 end;
11 end;
12 /
Package body created.
y@ORA817DEV> grant execute on bar to public;
Grant succeeded.
y@ORA817DEV>
y@ORA817DEV> @connect /
y@ORA817DEV> set termout off
ops$tkyte@ORA817DEV> REM GET afiedt.buf NOLIST
ops$tkyte@ORA817DEV> set termout on
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> declare
2 type rc is ref cursor;
3 l_cursor rc;
4 begin
5 x.foo.p( l_cursor );
6 y.bar.p( l_cursor );
7 end;
8 /
X
PL/SQL procedure successfully completed.
Amazing
Yogesh, June 13, 2003 - 11:18 am UTC
Thats really Amazing .... so one don't need to worry about the incoming ref cursor type .... so no worry ... thanks once again ....
Another problem with cursor
Yogesh, June 19, 2003 - 4:46 pm UTC
I have written following code .. it is incomplete .. but I'm stuck at one point
CREATE OR REPLACE PROCEDURE PopVirtualTab
(
p_Id VARCHAR2,
p_SchemaName VARCHAR2,
p_VirtualTab VARCHAR2,
p_LocSeqId Locationref.Locseqid%TYPE,
p_StatusCode OUT NUMBER,
p_StatusMessage OUT VARCHAR2
)
IS
v_ErrorFlag NUMBER :=0;
v_Termtable Termrecloc.Termtable%TYPE;
v_Termcolnm Termrecloc.Termcolnm%TYPE;
v_Id VARCHAR2(8);
v_SelectString VARCHAR2(1000);
v_InsertString VARCHAR2(1000);
TYPE REF_CURSOR IS REF CURSOR;
v_PopCursor REF_CURSOR;
v_AccessTabCursor REF_CURSOR;
CURSOR v_ColumnCursor(v_TabName VARCHAR2) IS
SELECT Column_Name FROM User_Tab_Columns WHERE
Table_name = v_TabName;
BEGIN
v_Id := '00'||p_Id;
v_InsertString := 'INSERT INTO '|| p_VirtualTab ||
'(p_Tab,p_Col,p_Val) VALUES (:1,:2,:3)';
OPEN v_AccessTabCursor FOR
SELECT Termtable,Termcolnm FROM Termrecloc
WHERE Locseqid = p_LocSeqId;
v_ErrorFlag :=1;
LOOP
FETCH v_AccessTabCursor INTO v_Termtable,v_Termcolnm;
EXIT WHEN v_AccessTabCursor%NOTFOUND;
v_SelectString:='SELECT * FROM '||p_SchemaName||'.'||v_Termtable ||
' WHERE '||v_Termcolnm||' = '||v_Id;
v_ErrorFlag :=2;
OPEN v_PopCursor FOR v_SelectString;
LOOP
FETCH v_PopCursor INTO ......;
EXIT WHEN v_PopCursor%NOTFOUND;
v_ErrorFlag :=3;
FOR v_ColumnRec IN v_ColumnCursor (v_TableName)
LOOP
EXECUTE IMMEDIATE v_InsertString USING v_TableName,v_ColumnRec,...
END LOOP;
v_ErrorFlag :=4;
END LOOP;
CLOSE v_PopCursor;
END LOOP;
CLOSE v_AccessTabCursor;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_StatusCode := -1;
p_StatusMessage := 'Error in PopVirtualTab At :'||v_ErrorFlag || SUBSTR(SQLERRM,1,1000);
WHEN OTHERS THEN
p_StatusCode := -2;
p_StatusMessage := 'Error in PopVirtualTab At :'||v_ErrorFlag || SUBSTR(SQLERRM,1,1000);
END;
Here in this statement
v_SelectString:='SELECT * FROM '||p_SchemaName||'.'||v_Termtable ||
' WHERE '||v_Termcolnm||' = '||v_Id;
It returns me the actual name of the columns and its values ... I want to open this cursor using cursor for loop as the table might contain upto 200 columns ... I don't want to .. rather I can't define so many variables ... This is for one schema .. there are multiple schemas where table sturctures of same table(name) is different ... is it possible to use for loop in this case .. if not what could be the option in this case ?
or in other way .. is there any way where we manually open cursor and then use it in for cursor loop .. I could not do this .. I tried ..
or
can I use for loop like this
for x in (some dynamic sql)
loop
end loop
I can't define ROWTYPEs as there are man yschemas and many tables in it ..
Please give me some solution
June 20, 2003 - 4:08 pm UTC
ugh, no bind variables -- how horrible is that.\
so, tell me why this isn't just "insert into .... select ... from "???? why do you have any procedural processing row by row in there at ALL?
Table Type
Yogesh, July 10, 2003 - 1:51 pm UTC
On the parallel lines what you explained for ref cursor ... can we even access table types from one package which is in one database .. in other database in some package ?
July 10, 2003 - 3:07 pm UTC
object types are not "distributed" in 8i, in 9i you can do some things distributed with them.
Whats the way out
Yogesh, July 10, 2003 - 3:33 pm UTC
Thanks for the reply ... what is the way out then in 8i ? do I have to get control of package ? can you please give a small piece of code .. or some link where I can find that ..
July 10, 2003 - 5:21 pm UTC
way out of what exactly?
Way out
yogesh, July 10, 2003 - 5:43 pm UTC
If I have a package in Database db1 ...which creates a table type .. now I want to refer it in another package which is in another database .. can I do it ?
July 11, 2003 - 7:27 am UTC
if the type is a plsql type:
create package foo
as
type array is table of varchar2(25) index by binary_integer;
end;
/
then the remote database can:
create synonym foo for foo@remote;
create package bar
as
g_data foo.array;
end;
/
you could use the dblink in the package itself, but this is one place where I like to use synonyms to hide that dblink.
Thanks a lot
Yogesh, July 11, 2003 - 10:45 am UTC
Thanks a lot once again ... I was not sure if I could do that ... you cleared by doubt
Collection and dblink
Yogesh, July 15, 2003 - 2:15 pm UTC
Suppose I have defined a collection
Create or replace TYPE DummyT AS OBJECT
(
Feature VARCHAR2(50),
FeatureVal VARCHAR2(50)
);
and a table of collection
create type TblType as table of DummyT;
my first question is can we define this inside a package ? I get PLS-00540: object not supported in this context error .. am I defining something wrong ?
Secondly if I define above types in database A and now I want to refer it in database B can I define my procedure parameter as
v_param IN TblType@remote ?
if not whats the alternative ?
July 15, 2003 - 3:07 pm UTC
you would create a plsql table type of records - not objects - in plsql
then, we are right back to where we were before right above..
Need more help
Yogesh, July 15, 2003 - 3:12 pm UTC
I want to create a composit type
Feature VARCHAR2(50),
FeatureVal VARCHAR2(50) how would I Do that ?
and How I'll be able to handle that in my code ? as there will be two elements per row ?
Object Scope
Yogesh, July 15, 2003 - 3:15 pm UTC
If I create an composit object type in DB1 added 10 records.. Can I pass it to other database using dblink ?
July 15, 2003 - 3:18 pm UTC
don't you have a pair of machines to test some ideas on ?
read above, i think i answered this
Yogesh, July 15, 2003 - 3:26 pm UTC
I don't have access to any of the boxes .... right now .. so can't experiment much ....
July 15, 2003 - 4:11 pm UTC
use your local machine with a loopback link
Yogesh, July 15, 2003 - 3:53 pm UTC
First
CREATE TYPE DummyT AS OBJECT
(
project_no NUMBER(2),
title VARCHAR2(35),
cost NUMBER(7,2)
);
and then
create type TblType as table of DummyT;
which I can't define in Pl / Sql
Secondly
TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
emp_tab EmpTabTyp;
So in this case .. I need to create a table of two columns and
then define a tab of that type ......
Third
TYPE AnEntry IS RECORD
(
term VARCHAR2(20),
meaning VARCHAR2(200)
);
TYPE Glossary IS VARRAY(250) OF AnEntry;
No If I want to use any one of these on DBlink in a package
which once should I use
July 15, 2003 - 4:11 pm UTC
*the last* -- really, that is what I said above -- you have to use a plsql record. You have to use a plsql table type. then you can use a dblink.
Thanks once again
yogesh, July 15, 2003 - 4:20 pm UTC
Thanks for clearing my doubts and your valuable time ... are there any performance issues which may crop up due to this ? as this procedure may be called frequently .. may be 1-5 times in 1 min ... and with a resultset of 100 rows everytime ..
July 15, 2003 - 4:31 pm UTC
time to BENCHMARK
1 to 5 times in a minute is pretty "small"
OPEN and FETCH
Arun Gupta, July 16, 2003 - 10:06 pm UTC
Tom
When does the query associated with a ref cursor actually executes?
I created an example as:
SQL> ed
Wrote file afiedt.buf
1 create or replace package pkg_test as
2 type rc is ref cursor;
3 procedure usp_test(ref_cur out rc);
4* end;
5 /
Package created.
SQL> ed
Wrote file afiedt.buf
1 create or replace package body pkg_test as
2 procedure usp_test (ref_cur out rc) is
3 begin
4 open ref_cur for <a bad SQL which takes 5 minutes to return results>;
5 end;
6* end;
7 /
Package body created.
SQL> variable r_cur refcursor;
SQL> execute pkg_test.usp_test(:r_cur);
PL/SQL procedure successfully completed.
This step completed in a fraction of second. If the ref was executed, this step should have taken 5 minutes. However, the next step, which prints the
July 17, 2003 - 10:17 am UTC
in general queries are not "executed" until you ask for the first row back, they are just setup to be executed. so parsing is "fast", fetching can be "slow"
Cursor Farm
Neil, October 08, 2003 - 8:22 am UTC
I was pondering whether to separate all my cursors from my business logic by putting them into packages.
The benefits are fairly obvious, but what sort of trouble could I expect?
October 08, 2003 - 10:40 am UTC
well, the benefits are not obvious to me.
what benefits do you see?
I like implicit cursors in a for loop first.
A locally declared cursor second (in the local stack of the procedure/function)
A globally declared cursor in the PACKAGE BODY third (don't really like it at all tho)
Putting them in a specification however, that wouldn't be my approach AT ALL.
Here is a preview of next months column -
My personal preference is, has been, and will always be to use implicit cursors in most cases; that is, do not explicitly define a cursor at all! Like this for example:
is
...
begin
....
for x in ( select * from emp )
loop
This technique works exceptionally well for result sets of under say 50-100 rows. I prefer this technique over explicit cursors for these reasons:
o It is marginally more efficient CPU-wise than using an explicit cursor.
o I can glance at the code and easily see what data I'm dealing with. The query is staring me in the face.
o It makes me use views when queries get big. I still see what data I'm querying in the view, but the complexity of the view is hidden. Rather then hide it in a cursor, I "hide it" in a view.
Now, there are times you have to use an explicitly defined cursor; the most common one being when you have larger result sets to process and you need to use BULK COLLECT during the FETCH phase for performance. When I have to use an explicitly defined cursor, I would opt for a local one, defined inside the procedure itself (not even in the package body, but right inside of the procedure in the body). Why? Well,
o It is marginally more efficient CPU-wise than a global cursor (which is defined in the spec).
o I can still glance at the code and easily see what I'm dealing with.
o It makes me use views since the query is in my procedure and I don't want it to obscure the rest of the code.
o It is clear who the cursor belongs to.
Do you see a reason pattern repeating here? Also, a cursor with a local scope (in a procedures STACK) automagically cleans up after itself. There is no if cursor%isopen junk littering my code. (None of my code ever has used that 'feature' of isopen.) I don't have to worry that "oh, you know procedure P1 uses cursor_x, and I use cursor_x, and since they are the same cursor_x, will we stomp on each other?" So I don't have that problem people always have with global variables. I view cursors in the package specification or body (not defined in a procedure, but having global scope) with the same level of distrust I would have of a global variable; the possibility of side effects from multiple procedures accessing these global variables is too high. I use global variables only when no other choice exists.
So, to wrap up, in order of preference:
a) no cursors (select into, for x in ( select..... )
b) locally declared cursors, when forced into it for whatever reason, such as
- bulk binds that need to use the LIMIT clause
- ref cursors
I recommend that you avoid globally declared cursors, in a package spec, for these reasons:
o You lose the nice features of encapsulation. The cursors are globally accessible and exposed to anyone that can access the package.
o You experience marginally slower performance (I stress marginally, and this is not the main point).
o You reduce the readability of the package body.
o They are globals, and globals are, in general, a bad coding practice to be avoided whenever possible.
That'll be a "NO", then!
Neil, October 09, 2003 - 3:07 am UTC
Thanks, Tom - informative as ever! I'm shortly off on a new build and was mulling over the possibilities of exercising some sort of control over the cursors in the system. Having them all in one place seemed an attractive proposition. It would appear that the pros are outweighed by the cons, though...
Rgds,
Neil
Ref Cursor parameter
Jamil Shaibani, October 09, 2003 - 4:30 am UTC
Dear Sir
I am getting this error message when I try
to use this code ,and also always if I create a user
I could not connect even if I give grant connect to to the user like this
SQL> CREATE USER X IDENTIFIED BY X;
User created.
SQL> CREATE USER Y IDENTIFIED BY Y;
User created.
SQL> GRANT CREATE SESSION,CREATE PROCEDURE TO X;
Grant succeeded.
SQL> GRANT CREATE SESSION,CREATE PROCEDURE TO Y;
Grant succeeded.
SQL> @connect x/x
unable to open file "connect.sql"
SQL> GRANT CONNECT TO X
2 /
Grant succeeded.
SQL> connect x/x
ERROR:
ORA-04045: errors during recompilation/revalidation of TEST.LOGON_TRIGGER
ORA-01031: insufficient privileges
Warning: You are no longer connected to ORACLE.
moste of the time if I create the user I am getting error to connect to the new created user al
so I will not be able to connect to the new user if I am not giving
GRANT DBA TO THE NEW USER
Best Regards
Jamil
October 09, 2003 - 4:30 pm UTC
umm, that has nothing to do with ref cursors as far as I can see???
someone has put an on logon trigger.
this trigger is called LOGON_TRIGGER
it is owned by the schema TEST
It is invalid
It cannot be recompiled due to this.
Your DBA account you are using to create the users is not affected by this (DBA accounts are not affected by the failure of a login trigger) but your normal user X is.
Nothing to do with ref cursors.
Everything to do with an object in the TEST schema. Fix it.
this sentences can not work in oracle,why?
peter.liu, October 09, 2003 - 4:41 am UTC
SQL> variable r_cur refcursor;
SQL> execute pkg_test.usp_test(:r_cur);
October 09, 2003 - 4:32 pm UTC
why can't they indeed? they work for me -- assuming of course I have a package named pkg_test (or schema named pkg_test) with a procedure usp_test in it that has an out parameter of a ref cursor type.
you'll have to be a little more "clear" as to why it is not working for you (eg: a test case like i supply all of the time with everything needed to reproduce the issue)
REFCURSOR not supported
peter.liu, October 09, 2003 - 4:45 am UTC
when i run this two command,it appear this error:REFCURSOR not supported,why? i have created the package and package body,and compile successfully.
SQL> variable r_cur refcursor;
SQL> execute pkg_test.usp_test(:r_cur);
October 09, 2003 - 4:32 pm UTC
you'll need to give the FULL example and CUT AND PASTE the exact error message.
Re: That'll be a "NO" then!
Neil, October 09, 2003 - 6:18 am UTC
Hold on a sec there, Tom! I've just come back to this thread, and re-read your answer. You're absolutley right, of course - but this is a thread about ref_cursors. Would it still be a no-brainer to encapsulate the cursors in a few packages and have them exposed via functions returning a ref_cursor? You could still do bulk operations and use the "for x in" construct, couldn't you?
October 09, 2003 - 4:57 pm UTC
it is not just about for x in
it is about encapsulation and hiding of variables.
A cursor is just like a variable -- putting it into a spec would make it a global. In my world, globals are to be avoided at all costs.
I never said you could not do any of the above
- return refcursor
- bulk operations
- for x in
on packaged cursors -- it was all about dependencies and cascading invalidations (tell me what happens when that package spec that you have with 50 queries in it used by 40 packages gets ONE query added or ONE query fixed??? cascading invalidations all over the place)
What happens when function Foo called by function Bar decides to reuse cursor X -- which Bar is already using?
ops$tkyte@ORA920> create or replace package demo_pkg
2 as
3 cursor c is select * from dual;
4 end;
5 /
Package created.
ops$tkyte@ORA920>
ops$tkyte@ORA920> create or replace procedure bar
2 as
3 begin
4 for x in demo_pkg.C
5 loop
6 null;
7 end loop;
8 end;
9 /
Procedure created.
ops$tkyte@ORA920> create or replace procedure foo
2 as
3 begin
4 for x in demo_pkg.C
5 loop
6 bar;
7 end loop;
8 end;
9 /
Procedure created.
ops$tkyte@ORA920>
ops$tkyte@ORA920> exec foo
BEGIN foo; END;
*
ERROR at line 1:
ORA-06511: PL/SQL: cursor already open
ORA-06512: at "OPS$TKYTE.DEMO_PKG", line 3
ORA-06512: at "OPS$TKYTE.BAR", line 4
ORA-06512: at "OPS$TKYTE.FOO", line 6
ORA-06512: at line 1
A global is a global -- and stinks as bad -- by any other name...
Hi, this is a full example,pls help me.
peter.liu, October 09, 2003 - 10:14 pm UTC
-------------package------
create or replace package pkg_test
as
type cur_test is ref cursor;
procedure p_test ( v_cur out pkg_test.cur_test ) ;
end pkg_test;
-------------package body------
create or replace package body pkg_test is
procedure p_test ( v_cur out pkg_test.cur_test )
as
v_sql varchar2(100);
begin
v_sql := 'select * from dept';
OPEN v_cur FOR v_sql;
end p_test;
end;
--then i input command-----
SQL> variable r_cur refcursor;
REFCURSOR not supported
----end----
pls help me thx!
October 10, 2003 - 7:57 am UTC
what tool are you using???
is that sqlplus?
Re: That'll be a "NO" then!
Neil, October 10, 2003 - 5:34 am UTC
You must have been wondering what on earth I was taking about! What I had in mind was something like this:
lRefCur := pk_cursor_farm.f_get_certain_info();
for x in lRefCur
loop
p_process(x.column);
end loop;
But it's not possible! You have to pass a pointer that is already associated with the data using the "OPEN FOR" statement. I tried it. I should have tried it BEFORE I posted my question, but...
October 10, 2003 - 8:13 am UTC
correct - and due to the way ref cursors MUST be treated (parsed during each open, instead of once per session like all other static SQL in plsql) I recomend using a ref cursor ONLY WHEN YOU MUST return a result set, rather then as a rule. avoid them until you actually *need* them.
It's only not possible due to the FOR loop though, isn't it?
A reader, October 10, 2003 - 10:36 am UTC
Hi Tom,
Not that i'm extolling the virtues of this approach, of course, but the problem with not being able to do this cursor farm approach is down to the FOR looping isn't it? You can still FETCH and CLOSE the REF CURSOR from a FUNCTION based approach
SQL> CREATE OR REPLACE PACKAGE farm
2 AS
3 TYPE l_ref IS REF CURSOR;
4 FUNCTION get_cursor RETURN l_ref;
5 END farm;
6 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY farm
2 AS
3 FUNCTION get_cursor RETURN l_ref
4 AS
5 refc l_ref;
6 BEGIN
7 OPEN refc FOR SELECT dummy FROM dual;
8 RETURN refc;
9 END get_cursor;
10 END farm;
11 /
Package body created.
SQL> DECLARE
2 refc farm.l_ref;
3 l_dummy VARCHAR2(1);
4 BEGIN
5 refc := farm.get_cursor;
6 FETCH refc INTO l_dummy;
7 CLOSE refc;
8 DBMS_OUTPUT.PUT_LINE(l_dummy);
9 END;
10 /
X
PL/SQL procedure successfully completed.
Regards
October 10, 2003 - 11:07 am UTC
absolutely -- that works but... you don't want to build a system that way as you pointed out.
passing a refcursor to a stored procedure from a stored procedure
dwl, February 11, 2004 - 12:07 pm UTC
Tom
i would like to pass a resultset from a stored procedure to a calling procedure. Are refcursors the best way? I tried to code it like this:
CREATE OR REPLACE TYPE rec_Table1 IS OBJECT ( Org_ID int,
ST_Count int);
/
CREATE OR REPLACE TYPE rec_Table1_TABLE IS TABLE OF rec_Table1 INDEX BY BINARY_INTEGER;
/
CREATE OR REPLACE PACKAGE PKG_SBE515 AS
TYPE C_Return_Cursor IS REF CURSOR;
PROCEDURE P_SBETable_1a ( P_Cur IN OUT C_Return_Cursor,
V_Start_Date IN DATE,
V_End_Date IN DATE );
PROCEDURE P_SBETable_1;
END PKG_SBE515;
/
CREATE OR REPLACE PACKAGE BODY PKG_SBE515 AS
PROCEDURE P_SBETable_1a ( P_Cur IN OUT C_Return_Cursor,
V_Start_Date IN DATE,
V_End_Date IN DATE )
IS
BEGIN
OPEN P_Cur FOR
'SELECT ORG_ID,
ST_COUNT
FROM (
SELECT ORG_ID,
NVL(SUM(THECOUNT), 0) AS ST_COUNT
FROM (
SELECT
PAYMENT_ORG_ID,
COUNT(*) AS THECOUNT
FROM PAID_CLAIM
WHERE PAYMENT_ORG_ID IN (SELECT Org_ID FROM Organisation WHERE Type_Id IN (5, 7, 10))
AND PAYMENT_PERIOD_TO >= ''1/Oct/2003'' AND PAYMENT_PERIOD_TO < ''1/Apr/2004''
AND CLAIM_TYPE=''GOS1''
GROUP BY PAYMENT_ORG_ID
UNION ALL
SELECT PAYMENT_ORG_ID,
SUM(ST_TOTAL_NUMBER) AS THECOUNT
FROM PAID_NHS_SIGHT_TESTS
WHERE PAYMENT_ORG_ID IN (SELECT Org_ID FROM Organisation WHERE Type_Id IN (5, 7, 10))
AND PAYMENT_PERIOD_TO >= ''1/Oct/2003'' AND PAYMENT_PERIOD_TO < ''1/Apr/2004''
GROUP BY PAYMENT_ORG_ID
UNION ALL
SELECT PAYMENT_ORG_ID,
SUM(NVL(ST_BEFORE_NUMBER, 0) + NVL(ST_AFTER_NUMBER, 0)) AS THECOUNT
FROM PAID_OMPS_STATEMENTS
WHERE PAYMENT_ORG_ID IN (SELECT Org_ID FROM Organisation WHERE Type_Id IN (5, 7, 10))
AND PAYMENT_PERIOD_TO >= ''1/Oct/2003'' AND PAYMENT_PERIOD_TO < ''1/Apr/2004''
GROUP BY PAYMENT_ORG_ID
),
(SELECT Org_ID FROM Organisation WHERE Type_Id IN (5, 7, 10))
WHERE PAYMENT_ORG_ID(+) = ORG_ID
GROUP BY ORG_ID
)';
END P_SBETable_1a;
PROCEDURE P_SBETable_1
IS
l1_data rec_Table1_TABLE;
t1 C_Return_Cursor;
BEGIN
P_SBETable_1a ( t1,
'1/Oct/2003',
'1/Apr/2004');
loop
l1_data := rec_Table1_TABLE();
fetch t1 bulk collect into l1_data;
dbms_output.put_line( 'Fetched ' || l1_data.count || ' rows' );
exit when t1%notfound;
end loop;
close t1;
insert into tble1
select * from TABLE( cast (l1_data as rec_Table1_TABLE) );
end P_SBETable_1 ;
END PKG_SBE515;
/
This compiles but when run i get:
BEGIN PKG_SBE515.P_SBETable_1; END;
*
ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at "OPT1.PKG_SBE515", line 122
ORA-06512: at line 1
What am i doing wrong?
If this isn't the best way, then how else should i do it, pass arrays of objects, pl/sql tables??
I want to fetch the cursor into an array of a complex object type so i can cast the table and do a bulk insert into final table.
I am using 8.1.7.4.
Thanks
February 11, 2004 - 2:41 pm UTC
simple -- small -- concise, yet 100% complete. that is what examples should be (and runnable by anyone as well!)
anyway.... the problem is in 8i (not so in 9i) you cannot array fetch from the dynamically opened ref cursor:
ops$tkyte@ORA817DEV> create or replace package demo_pkg
2 as
3 type rc is ref cursor;
4
5 procedure p1;
6 procedure p2;
7 procedure p3( p_cur in out rc );
8 end;
9 /
Package created.
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> create or replace package body demo_pkg
2 as
3
4 procedure p1
5 is
6 l_cursor rc;
7 l_dummy dual.dummy%type;
8 begin
9 p3( l_cursor );
10 loop
11 fetch l_cursor into l_dummy;
12 exit when l_cursor%notfound;
13 end loop;
14 close l_cursor;
15 end;
16
17
18 procedure p2
19 is
20 l_cursor rc;
21 type tabType is table of dual.dummy%type index by binary_integer;
22 l_dummy tabType;
23 begin
24 p3( l_cursor );
25 fetch l_cursor BULK COLLECT into l_dummy;
26 close l_cursor;
27 end;
28
29 procedure p3( p_cur in out rc )
30 is
31 begin
32 open p_cur for 'select * from dual';
33 end;
34
35 end;
36 /
Package body created.
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> exec demo_pkg.p1
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV> exec demo_pkg.p2
BEGIN demo_pkg.p2; END;
*
ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at "OPS$TKYTE.DEMO_PKG", line 25
ORA-06512: at line 1
Only difference between p1 and p2 is the "bulk collect".
if you use static sql:
29 procedure p3( p_cur in out rc )
30 is
31 begin
32 open p_cur for select * from dual;
33 end;
34
35 end;
36 /
Package body created.
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> exec demo_pkg.p1
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV> exec demo_pkg.p2
PL/SQL procedure successfully completed.
no such issues.
AND you have to use binds (please please please) not those literals in your dynamic sql!!!!!!
cant quite get iti to work yet!
dwl, February 12, 2004 - 6:01 am UTC
Tom
Thanks for the information regarding bulk collect into arrays.
However i am still having trouble with this my new procedure now looks like:
PROCEDURE P_SBETable_1
IS
t1 C_Return_Cursor;
--l1_data t1%TYPE;
l1_data_element rec_Table1;
l1_data rec_Table1_TABLE;
i int := 0;
BEGIN
null;
P_SBETable_1a ( t1,
'1/Oct/2003',
'1/Apr/2004');
loop
i := i + 1;
fetch t1 into l1_data_element;
l1_data(i) := l1_data_element;
exit when t1%notfound;
end loop;
close t1;
insert into tble1
select org_id from TABLE( cast (l1_data as rec_Table1_TABLE) );
end P_SBETable_1 ;
10:48:30 OPT1@OP5>exec PKG_SBE515.P_SBETable_1;
BEGIN PKG_SBE515.P_SBETable_1; END;
*
ERROR at line 1:
ORA-00932: inconsistent datatypes
ORA-06512: at "OPT1.PKG_SBE515", line 131
ORA-06512: at line 1
I still can't fetch into the object type. What am i doing wrong??
my object and table types are:
CREATE OR REPLACE TYPE rec_Table1 IS OBJECT ( Org_ID number(38),
ST_Count number(38));
/
CREATE OR REPLACE TYPE rec_Table1_TABLE IS TABLE OF rec_Table1;
/
I couldn't use INDEX BY BINARY_INTEGER plsql table as it wouldn't compile. What is the best way to get my cursor resultset into my oracle object type table??
I've also played around with using different datatypes in the object eg int.
Nothing works though, what am i doing wrong??
Seeing as i cannot BULK COLLECT anymore then am i using the best method for this? would passing arrays of objects instead of refcursors to the calling procedure be faster??
(By the way when you asked about literals in the data instead of binds were you referring to the dates 01/Apr/2003 ?? If so i was planning on using variables in the final procedure!)
thanks again
February 12, 2004 - 8:53 am UTC
can you give me a
concise
yet 100% complete
small
yet everything there
test case to work with. You see, the error is reported on line 131, no idea what line 131 is. I cannot run your code, I do not have your tables, your stuff.
set up a small, tiny example using emp and dept or something -- provide everything one would need to reproduce.
I find that 999 times out of 1,000 in the generation of such a test case -- I discover whats wrong (and you might too)
this is what I mean -- i took your example and tried to reproduce your issue -- but I cannot. But see how the test case is sort of "self contained" and line numbers and all are available:
ops$tkyte@ORA9IR2> CREATE OR REPLACE TYPE rec_Table1 as OBJECT
2 ( Org_ID number(38),
3 ST_Count number(38)
4 );
5 /
Type created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> CREATE OR REPLACE TYPE rec_Table1_TABLE as TABLE OF rec_Table1;
2 /
Type created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table tble1( x int );
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace PROCEDURE P
2 as
3 type c_return_cursor is ref cursor;
4 t1 C_Return_Cursor;
5 l1_data_element rec_Table1;
6 l1_data rec_Table1_TABLE := rec_table1_table();
7 BEGIN
8 open t1 for select rec_table1( 55, 100 ) data from dual;
9 loop
10 fetch t1 into l1_data_element;
11 exit when t1%notfound;
12 l1_data.extend;
13 l1_data(l1_data.count) := l1_data_element;
14 end loop;
15 close t1;
16
17 insert into tble1
18 select org_id
19 from TABLE( cast (l1_data as rec_Table1_TABLE) );
20
21 end P;
22 /
Procedure created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec p
PL/SQL procedure successfully completed.
Now, a comment on your code -- it cannot run.
l1_data(i) := l1_data_element;
that would fail with
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at "OPS$TKYTE.P", line 13
Your loop:
loop
i := i + 1;
fetch t1 into l1_data_element;
l1_data(i) := l1_data_element;
exit when t1%notfound;
end loop;
if it worked would end up putting the last row in the table TWICE. the exit needs to be right after the fetch else you'll move that last row in there two times.
"i" as a variable should not be used, you'll be using .extend and l1_data.count to see how big your array is, no need for "i"
so, without a FULL, self contained test case from you -- something simple I can run -- we won't get anywhere.
sorry!
dwl, February 12, 2004 - 9:44 am UTC
Here is the test case you require (this should compile)
drop type rec_test ;
drop type rec_test_TABLE ;
CREATE OR REPLACE TYPE rec_test IS OBJECT ( empno number(4),
mgr number(4));
/
CREATE OR REPLACE TYPE rec_test_TABLE IS TABLE OF rec_test;
/
CREATE OR REPLACE PACKAGE package1 AS
TYPE C_Return_Cursor IS REF CURSOR;
PROCEDURE proc1 ( P_Cur IN OUT C_Return_Cursor );
PROCEDURE proc2;
END package1;
/
CREATE OR REPLACE PACKAGE BODY package1 AS
PROCEDURE proc1 ( P_Cur IN OUT C_Return_Cursor )
IS
BEGIN
OPEN P_Cur FOR
'SELECT empno,
mgr
from emp';
END proc1;
PROCEDURE proc2
IS
l1_data_element rec_test;
l1_data rec_test_Table;
t1 C_Return_Cursor;
BEGIN
proc1 ( t1);
-- open t1 for select rec_test( empno, mgr ) data from dual;
loop
fetch t1 into l1_data_element;
exit when t1%notfound;
l1_data.extend;
l1_data(l1_data.count) := l1_data_element;
end loop;
close t1;
-- insert into tble1
-- select * from TABLE( cast (l1_data as rec_test_TABLE) );
end proc2;
END package1;
/
exec package1.proc2;
BEGIN package1.proc2; END;
*
ERROR at line 1:
ORA-00932: inconsistent datatypes
ORA-06512: at "OPT1.PACKAGE1", line 41
ORA-06512: at line 1
That is what i get.
The line it doesn't like is
fetch t1 into l1_data_element;
And i can kinda see why, it is trying to fetch the results of the cursor into my object type which doesn't get "cast" directly as the table record??
So how can i do this correctly. Your example shows it working when the cursor is opened in the same procedure and using the data element to select from dual which works, but i specifically want to use the cursor returned from another procedure.
I am still questioning with this is the best thing to do anyway. I mean maybe i should populate an array in proc1 using bulk collect and then pass that back to proc2 instead of the refcursor, would this be more efficient??
Maybe it would help if i gave you some more context.
Basically once i have the resultset in proc 2 with the data in an array then i need to select from that array and process each element into a new larger array. I will be doing this lots of times with several other different sized arrays, all loading into one parent array which will do an insert into the final table.
I will effectively be flattening lots of resultsets into one big flat array. I guess this can only be done procedurally stepping through each array element in turn.
I know this is not ideal and i should probably be using nested tables to store this type of data structure?? But the legacy application expects the data in the flat table.
How could i select from the nested table to populate a flat table though?? it would have to iterate through each nested table wouldn't it?
Hope this helps see what i am trying to do!
Many thanks
February 12, 2004 - 11:58 am UTC
it is easy to see now. I was fetching an object type from dual:
8 open t1 for select rec_table1( 55, 100 ) data from dual;
You are not:
OPEN P_Cur FOR
'SELECT empno,
mgr
from emp';
you are fetching SCALARS, not object types. You have an obvious type mismatch here.
You are selecting two columns -- you are fetching one.
anyway, you can code this:
13 PROCEDURE proc2
14 IS
15 l1_data_element rec_test;
16 l1_data rec_test_Table := rec_test_table( null, null );
17 t1 C_Return_Cursor;
18 BEGIN
19 proc1 ( t1);
20
21 loop
22 fetch t1 into l1_data_element.empno, l1_data_element.mgr;
23 exit when t1%notfound;
24 l1_data.extend;
25 l1_data(l1_data.count) := l1_data_element;
26 end loop;
27 close t1;
28
29 end proc2;
and now you are fetching scalars into scalars -- and the scalars you fetch into are just attributes of the UDT.
What about forms or reports as front end
eric givler, February 12, 2004 - 8:56 pm UTC
Suppose you had a form and you wanted to base a block on a stored procedure. The options are to base it on table, a view, a from-clause query, and then a stored procedure that either returns a refcursor or a pl/sql table. I would think if you wanted to REMOVE all the front-end logic for building the where clause on the client, you could simply pass the prms to the package which would return a ref cursor. Either that, or base it on a view.
If it is based on a view or a stored proc and the join is mucked up, you could avoid opening the front end and just fix the query. Also, you could interrogate the function and examine the resultset in sql*plus. Also, views aren't always the easiest to query against - based on the inputs, I could open up different cursors that perform the best for what was requested.
I can't say:
select *
from (view with 8 way-join unioned to another 8 way join)
where employee_id in (select employee_id from persons where phone_num=:user_phone)
However, I could send the phone to a stored procedure and write a query that performs more efficiently than the previous note. (this example is somewhat contrived, but we do have forms with blocks based on views this complex)
I would think where you have 3 reports that all use similar queries, that you could consolidate them more easily with a report based on a package returning a refcursor, than building the report on the client-side static sql interspersed with lexicals to modify the query for the report conditions, right?
That is, it would seem prudent to shy away from hardcoding all these queries in the front-end, in that if they were all defined in a few re-usable components, then you wouldn't keep seeing the same code cut&paste everywhere, and could just re-use these "resultsets" and change your underlying structures as needed with no front-end change at all.
February 13, 2004 - 9:32 am UTC
"prms"? what is "prms"
"join is mucked up". how could a join be "mucked up"
why cannot you say "select * from ( view with .... ) where employee_id in ( ...)"?
if it were simple joins, sure you could.
It depends on what toolset you are using as to which approach you would most likely take. A 4gl like forms and reports -- you'll find it "hard not to use sql", "easiest to use sql". A 3gl like java, vb, c -- you'll find it "easiest to use plsql", "harder to do straight sql" (not just from a programming perspective but from tuning and such as well)
Thanks it works now!
dwl, February 13, 2004 - 6:53 am UTC
Tom
Thanks very much i have it working now.
Two more things though:
1) To get it to compile i had to initialise the scalartype and the arraytype.
This worked except that the collection has initially got 2 empty null rows in it. As you can see from the results below:
My new proc2 is:
PROCEDURE proc2
IS
l1_data_element rec_test;
l1_data rec_test_Table := rec_test_TABLE(null, null);
t1 C_Return_Cursor;
BEGIN
-- l1_data.delete;
proc1 ( t1);
l1_data_element := rec_test(null, null);
loop
fetch t1 into l1_data_element.empno, l1_data_element.mgr;
exit when t1%notfound;
dbms_output.put_line('l1_data.count = '||l1_data.count);
l1_data.extend;
l1_data(l1_data.count) := l1_data_element;
end loop;
close t1;
-- insert into tble1
-- select * from TABLE( cast (l1_data as rec_test_TABLE) );
end proc2;
11:47:30 OPT1@OP5>exec package1.proc2;
l1_data.count = 2
l1_data.count = 3
l1_data.count = 4
l1_data.count = 5
l1_data.count = 6
l1_data.count = 7
l1_data.count = 8
l1_data.count = 9
l1_data.count = 10
l1_data.count = 11
l1_data.count = 12
l1_data.count = 13
l1_data.count = 14
l1_data.count = 15
What i have to do is uncomment the line
l1_data.delete;
To remove the initial rows, then it works ok:
11:47:58 OPT1@OP5>exec package1.proc2;
l1_data.count = 0
l1_data.count = 1
l1_data.count = 2
l1_data.count = 3
l1_data.count = 4
l1_data.count = 5
l1_data.count = 6
l1_data.count = 7
l1_data.count = 8
l1_data.count = 9
l1_data.count = 10
l1_data.count = 11
l1_data.count = 12
l1_data.count = 13
Why is this? Is it to do with initialising the objects to null first?
2) Now i have this working i will write the whole procedure like this. I will also write it without using refcursors but build arrays or objects to pass between procedures instead. Then see which is quicker. Which one do you think will perform better?
February 13, 2004 - 10:14 am UTC
1) you initialized wrong.
l1_data_element rec_test := rec_test();
thats all the initialization you need.
2) what would be fastest and most correct? not your approach. it would be:
insert into tbl1
select ......;
period, NO procedural code whatsoever. the routine that is returning a ref cursor, should not. it should just execute the insert into statement.
strange
dwl, February 13, 2004 - 10:45 am UTC
This is not exactly what i have found:
First if i only initialise the l1_data_element i get:
15:34:12 OPT1@OP5>drop type rec_test_TABLE ;
Type dropped.
Elapsed: 00:00:00.00
15:34:26 OPT1@OP5>drop type rec_test ;
Type dropped.
Elapsed: 00:00:00.00
15:34:26 OPT1@OP5>
15:34:26 OPT1@OP5>
15:34:27 OPT1@OP5>CREATE OR REPLACE TYPE rec_test IS OBJECT ( empno number(4),
15:34:27 2 mgr number(4));
15:34:27 3 /
Type created.
Elapsed: 00:00:00.00
15:34:27 OPT1@OP5>
15:34:27 OPT1@OP5>
15:34:27 OPT1@OP5>
15:34:27 OPT1@OP5>CREATE OR REPLACE TYPE rec_test_TABLE IS TABLE OF rec_test;
15:34:27 2 /
Type created.
Elapsed: 00:00:00.00
15:34:27 OPT1@OP5>
15:34:27 OPT1@OP5>
15:34:27 OPT1@OP5>
15:34:27 OPT1@OP5>
15:34:27 OPT1@OP5>CREATE OR REPLACE PACKAGE package1 AS
15:34:27 2
15:34:27 3 TYPE C_Return_Cursor IS REF CURSOR;
15:34:27 4
15:34:27 5 PROCEDURE proc1 ( P_Cur IN OUT C_Return_Cursor );
15:34:27 6 PROCEDURE proc2;
15:34:27 7
15:34:27 8 END package1;
15:34:27 9 /
Package created.
Elapsed: 00:00:00.00
15:34:27 OPT1@OP5>
15:34:27 OPT1@OP5>
15:34:27 OPT1@OP5>CREATE OR REPLACE PACKAGE BODY package1 AS
15:34:27 2
15:34:27 3
15:34:27 4
15:34:27 5
15:34:27 6 PROCEDURE proc1 ( P_Cur IN OUT C_Return_Cursor )
15:34:27 7 IS
15:34:27 8
15:34:27 9 BEGIN
15:34:27 10
15:34:27 11
15:34:27 12 OPEN P_Cur FOR
15:34:27 13
15:34:27 14 'SELECT empno,
15:34:27 15 mgr
15:34:28 16 from emp';
15:34:28 17
15:34:28 18 END proc1;
15:34:28 19
15:34:28 20
15:34:28 21
15:34:28 22
15:34:28 23 PROCEDURE proc2
15:34:28 24
15:34:28 25 IS
15:34:28 26
15:34:28 27 l1_data_element rec_test := rec_test(null, null);
15:34:28 28 --l1_data_element rec_test;
15:34:28 29 --l1_data rec_test_Table := rec_test_TABLE(null, null);
15:34:28 30 l1_data rec_test_Table;
15:34:28 31 t1 C_Return_Cursor;
15:34:28 32
15:34:28 33 BEGIN
15:34:28 34
15:34:28 35 -- l1_data.delete;
15:34:28 36 proc1 ( t1);
15:34:28 37
15:34:28 38 -- l1_data_element := rec_test(null, null);
15:34:28 39
15:34:28 40 loop
15:34:28 41 fetch t1 into l1_data_element.empno, l1_data_element.mgr;
15:34:28 42 exit when t1%notfound;
15:34:28 43
15:34:28 44 dbms_output.put_line('l1_data.count = '||l1_data.count);
15:34:28 45
15:34:28 46 l1_data.extend;
15:34:28 47 l1_data(l1_data.count) := l1_data_element;
15:34:28 48 end loop;
15:34:28 49 close t1;
15:34:28 50
15:34:28 51
15:34:28 52 -- insert into tble1
15:34:28 53 -- select * from TABLE( cast (l1_data as rec_test_TABLE) );
15:34:28 54
15:34:28 55
15:34:28 56
15:34:28 57 end proc2;
15:34:28 58
15:34:28 59
15:34:29 60 END package1;
15:34:29 61 /
Package body created.
Elapsed: 00:00:00.01
15:34:29 OPT1@OP5>
15:34:29 OPT1@OP5>exec package1.proc2;
BEGIN package1.proc2; END;
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at "OPT1.PACKAGE1", line 44
ORA-06512: at line 1
So this time i initialise the l1_data too:
Elapsed: 00:00:00.01
15:34:29 OPT1@OP5>drop type rec_test_TABLE ;
Type dropped.
Elapsed: 00:00:00.00
15:34:55 OPT1@OP5>drop type rec_test ;
Type dropped.
Elapsed: 00:00:00.00
15:34:55 OPT1@OP5>
15:34:56 OPT1@OP5>
15:34:56 OPT1@OP5>CREATE OR REPLACE TYPE rec_test IS OBJECT ( empno number(4),
15:34:56 2 mgr number(4));
15:34:56 3 /
Type created.
Elapsed: 00:00:00.00
15:34:56 OPT1@OP5>
15:34:56 OPT1@OP5>
15:34:56 OPT1@OP5>
15:34:56 OPT1@OP5>CREATE OR REPLACE TYPE rec_test_TABLE IS TABLE OF rec_test;
15:34:56 2 /
Type created.
Elapsed: 00:00:00.00
15:34:56 OPT1@OP5>
15:34:56 OPT1@OP5>
15:34:56 OPT1@OP5>
15:34:56 OPT1@OP5>
15:34:56 OPT1@OP5>CREATE OR REPLACE PACKAGE package1 AS
15:34:56 2
15:34:56 3 TYPE C_Return_Cursor IS REF CURSOR;
15:34:56 4
15:34:56 5 PROCEDURE proc1 ( P_Cur IN OUT C_Return_Cursor );
15:34:56 6 PROCEDURE proc2;
15:34:56 7
15:34:56 8 END package1;
15:34:56 9 /
Package created.
Elapsed: 00:00:00.00
15:34:56 OPT1@OP5>
15:34:56 OPT1@OP5>
15:34:56 OPT1@OP5>CREATE OR REPLACE PACKAGE BODY package1 AS
15:34:56 2
15:34:56 3
15:34:56 4
15:34:56 5
15:34:56 6 PROCEDURE proc1 ( P_Cur IN OUT C_Return_Cursor )
15:34:56 7 IS
15:34:56 8
15:34:56 9 BEGIN
15:34:56 10
15:34:56 11
15:34:56 12 OPEN P_Cur FOR
15:34:56 13
15:34:56 14 'SELECT empno,
15:34:57 15 mgr
15:34:57 16 from emp';
15:34:57 17
15:34:57 18 END proc1;
15:34:57 19
15:34:57 20
15:34:57 21
15:34:57 22
15:34:57 23 PROCEDURE proc2
15:34:57 24
15:34:57 25 IS
15:34:57 26
15:34:57 27 l1_data_element rec_test := rec_test(null, null);
15:34:57 28 --l1_data_element rec_test;
15:34:57 29 l1_data rec_test_Table := rec_test_TABLE(null, null); <-- INITIALISE THIS ASWELL THIS TIME
15:34:57 30 --l1_data rec_test_Table;
15:34:57 31 t1 C_Return_Cursor;
15:34:57 32
15:34:57 33 BEGIN
15:34:57 34
15:34:57 35 -- l1_data.delete;
15:34:57 36 proc1 ( t1);
15:34:57 37
15:34:57 38 -- l1_data_element := rec_test(null, null);
15:34:57 39
15:34:57 40 loop
15:34:57 41 fetch t1 into l1_data_element.empno, l1_data_element.mgr;
15:34:57 42 exit when t1%notfound;
15:34:57 43
15:34:57 44 dbms_output.put_line('l1_data.count = '||l1_data.count);
15:34:57 45
15:34:57 46 l1_data.extend;
15:34:57 47 l1_data(l1_data.count) := l1_data_element;
15:34:57 48 end loop;
15:34:57 49 close t1;
15:34:57 50
15:34:57 51
15:34:57 52 -- insert into tble1
15:34:57 53 -- select * from TABLE( cast (l1_data as rec_test_TABLE) );
15:34:57 54
15:34:57 55
15:34:57 56
15:34:58 57 end proc2;
15:34:58 58
15:34:58 59
15:34:58 60 END package1;
15:34:58 61 /
Package body created.
Elapsed: 00:00:00.01
15:34:58 OPT1@OP5>
15:34:58 OPT1@OP5>exec package1.proc2;
l1_data.count = 2
l1_data.count = 3
l1_data.count = 4
l1_data.count = 5
l1_data.count = 6
l1_data.count = 7
l1_data.count = 8
l1_data.count = 9
l1_data.count = 10
l1_data.count = 11
l1_data.count = 12
l1_data.count = 13
l1_data.count = 14
l1_data.count = 15
PL/SQL procedure successfully completed.
ie it starts at element number 2.
It was necessary for me to use
l1_data_element := rec_test(null, null);
instead of just
l1_data_element := rec_test();
otherwise i get this:
Elapsed: 00:00:00.02
15:34:58 OPT1@OP5>drop type rec_test_TABLE ;
Type dropped.
Elapsed: 00:00:00.00
15:35:34 OPT1@OP5>drop type rec_test ;
Type dropped.
Elapsed: 00:00:00.00
15:35:34 OPT1@OP5>
15:35:34 OPT1@OP5>
15:35:34 OPT1@OP5>CREATE OR REPLACE TYPE rec_test IS OBJECT ( empno number(4),
15:35:34 2 mgr number(4));
15:35:34 3 /
Type created.
Elapsed: 00:00:00.00
15:35:35 OPT1@OP5>
15:35:35 OPT1@OP5>
15:35:35 OPT1@OP5>
15:35:35 OPT1@OP5>CREATE OR REPLACE TYPE rec_test_TABLE IS TABLE OF rec_test;
15:35:35 2 /
Type created.
Elapsed: 00:00:00.00
15:35:35 OPT1@OP5>
15:35:35 OPT1@OP5>
15:35:35 OPT1@OP5>
15:35:35 OPT1@OP5>
15:35:35 OPT1@OP5>CREATE OR REPLACE PACKAGE package1 AS
15:35:35 2
15:35:35 3 TYPE C_Return_Cursor IS REF CURSOR;
15:35:35 4
15:35:35 5 PROCEDURE proc1 ( P_Cur IN OUT C_Return_Cursor );
15:35:35 6 PROCEDURE proc2;
15:35:35 7
15:35:35 8 END package1;
15:35:35 9 /
Package created.
Elapsed: 00:00:00.00
15:35:35 OPT1@OP5>
15:35:35 OPT1@OP5>
15:35:35 OPT1@OP5>CREATE OR REPLACE PACKAGE BODY package1 AS
15:35:35 2
15:35:35 3
15:35:35 4
15:35:35 5
15:35:35 6 PROCEDURE proc1 ( P_Cur IN OUT C_Return_Cursor )
15:35:35 7 IS
15:35:35 8
15:35:35 9 BEGIN
15:35:35 10
15:35:35 11
15:35:35 12 OPEN P_Cur FOR
15:35:35 13
15:35:35 14 'SELECT empno,
15:35:35 15 mgr
15:35:35 16 from emp';
15:35:35 17
15:35:35 18 END proc1;
15:35:35 19
15:35:35 20
15:35:35 21
15:35:35 22
15:35:35 23 PROCEDURE proc2
15:35:35 24
15:35:35 25 IS
15:35:35 26
15:35:36 27 --l1_data_element rec_test := rec_test(null, null);
15:35:36 28 l1_data_element rec_test;
15:35:36 29 l1_data rec_test_Table := rec_test_TABLE(null, null);
15:35:36 30 --l1_data rec_test_Table;
15:35:36 31 t1 C_Return_Cursor;
15:35:36 32
15:35:36 33 BEGIN
15:35:36 34
15:35:36 35 -- l1_data.delete;
15:35:36 36 proc1 ( t1);
15:35:36 37
15:35:36 38 -- l1_data_element := rec_test(null, null);
15:35:36 39
15:35:36 40 loop
15:35:36 41 fetch t1 into l1_data_element.empno, l1_data_element.mgr;
15:35:36 42 exit when t1%notfound;
15:35:36 43
15:35:36 44 dbms_output.put_line('l1_data.count = '||l1_data.count);
15:35:36 45
15:35:36 46 l1_data.extend;
15:35:36 47 l1_data(l1_data.count) := l1_data_element;
15:35:36 48 end loop;
15:35:36 49 close t1;
15:35:36 50
15:35:36 51
15:35:36 52 -- insert into tble1
15:35:36 53 -- select * from TABLE( cast (l1_data as rec_test_TABLE) );
15:35:36 54
15:35:36 55
15:35:36 56
15:35:36 57 end proc2;
15:35:36 58
15:35:36 59
15:35:36 60 END package1;
15:35:36 61 /
Package body created.
Elapsed: 00:00:00.01
15:35:37 OPT1@OP5>
15:35:37 OPT1@OP5>exec package1.proc2;
BEGIN package1.proc2; END;
*
ERROR at line 1:
ORA-06530: Reference to uninitialized composite
ORA-06512: at "OPT1.PACKAGE1", line 41
ORA-06512: at line 1
Elapsed: 00:00:00.01
15:35:37 OPT1@OP5>
Can you recreate this behaviour?
Why can i only get it to compile when i initialise both the scalar and the array, and then when it does compile i get element 2 as the first array preceded by null arrays??
February 13, 2004 - 11:02 am UTC
just do this please (it is what i stated above)
11 PROCEDURE proc2
12 IS
13
14 l1_data_element rec_test := rec_test(null, null);
15 l1_data rec_test_Table := rec_test_table();
16 t1 C_Return_Cursor;
17 BEGIN
18
19 proc1 ( t1);
20 loop
21 fetch t1 into l1_data_element.empno, l1_data_element.mgr;
22 exit when t1%notfound;
23
24 dbms_output.put_line('l1_data.count = '||l1_data.count);
25
26 l1_data.extend;
27 l1_data(l1_data.count) := l1_data_element;
28 end loop;
29 close t1;
30 end proc2;
31 END;
32 /
Package body created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec package1.proc2
l1_data.count = 0
l1_data.count = 1
l1_data.count = 2
l1_data.count = 3
l1_data.count = 4
l1_data.count = 5
l1_data.count = 6
l1_data.count = 7
l1_data.count = 8
l1_data.count = 9
l1_data.count = 10
l1_data.count = 11
l1_data.count = 12
l1_data.count = 13
PL/SQL procedure successfully completed.
You want an empty object:
14 l1_data_element rec_test := rec_test(null, null);
and an empty array:
15 l1_data rec_test_Table := rec_test_table();
that is all.
follow-up to your comments
eric givler, February 18, 2004 - 6:17 pm UTC
prms = parameters. All that other typing, and I didn't type parameters.
"Mucked up" means that the join was not written correctly for the view. For example, in our system, a person can have one or more addresses of an address type. Each address is stored in an addresses table for a person along with a date of effectivity. When an address is changed, the stop_date is now filled in, and a new entry is created with a NULL stop_date column. If the join to this table does not reference stop_Date is null, then you will see duplicate entries on screens based on this invalid join. Additionally, suppose the person has NO address at all. In this case, the join would be an outer join, but maybe it was miscoded as:
persons.person_eid=addresses.person_eid(+)
and 'HOME' = ADDRESSES.ADDRESS_TYPE_CODE
AND ADDRESSES.STOP_DATE IS NULL
In this case, if no home address is on file, or there is one but it has a stop date, the person cannot be retrieved properly.
Maybe this is more of a design issue but it's nice for me to simply say in plus:
variable x refcursor
exec :x := pkg.refcur(prm1,prm2,prm3);
print x
Then, I examine the results. If they are ok, I base the report or forms block on these. Then, if I eventually drop forms from the front end, I have all the query logic self-contained in the backend as opposed to trying to dig it back out of the front end.
I would think that there are only a limited number of ways in our application that data is queried, but when I look at the application it is riddled with hundreds and hundreds of sql statements pasted in every form and report. It makes maintenance a nightmare.
How do you tackle something like this? I've tried to move all hardcoded sql out of the front end,making packages and functions for everything (as much as possible). SAme with business logic. Blocks based on resultsets (refcursors) seemed like the natural thing to do. Did I go the wrong route?
February 19, 2004 - 7:22 am UTC
by mucked up you mean "we have a bug in the developed code".
I would (do) use views.
You can use refcursors -- but didn't you find it "awkward" in some respects?
ref cursor + db link
A reader, February 23, 2004 - 2:05 pm UTC
How can i Or Can I ? Pass the ref cursor over a dblink ?
February 23, 2004 - 4:55 pm UTC
you cannot, documented restriction.
Please have a look
Ram, March 17, 2004 - 7:06 am UTC
Dear Sir,
Please have a look at the following code
1)
SQL> create or replace procedure p(x in emp.ename%type,y out emp%rowtype) as
2 c emp%rowtype;
3 begin
4 select * into c from emp where ename = x;
5 y := c;
6 end;
7 /
Procedure created.
SQL> VAR A REFCURSOR
SQL> SET AUTOP ON
SQL> exec p('WARD',:A)<< --- Can't a refcursor be used here
BEGIN p('WARD',:A); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'P'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
ERROR:
ORA-24338: statement handle not executed
SP2-0625: Error printing variable "a"
2)
SQL> create synonym op for dbms_output
2 /
Synonym created.
SQL> exec op.put_line('Hello')<<-- Is it not possible to
<<create a syn. for
<<dbms_output?
BEGIN op.put_line('Hello'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'OP.PUT_LINE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Sorry for disturbing you.May I seek your prompt reply?
Thanks in advance.
Bye!
March 17, 2004 - 8:28 am UTC
a ref cursor is not a record, emp%rowtype is a record, so no, a ref cursor cannot be used as you have it coded.
Now, if you code:
create or replace procedure p(x in emp.ename%type,y out sys_refcursor) as
begin
open y for select * into c from emp where ename = x;
end;
/
it'll work great.
In the second case, you created a synonym to a synonym -- not good. create the synynom to the "real thing"
ops$tkyte@ORA9IR2> create or replace synonym op for <b>SYS.</b>dbms_output;
Synonym created.
ops$tkyte@ORA9IR2> exec op.put_line('hello');
hello
PL/SQL procedure successfully completed.
Passing variable number of Parameters to REF cursors
Anand, April 26, 2004 - 6:36 am UTC
Hi Tom,
Sorry for posting this query here...
I have a requirement to open a REF cursor, but the problem is that the WHERE condition would vary based on some condition. I have 5 columns based on which i would make my WHERE condition.But any of my 5 columns might be null. So while I am building my WERE clause.. i would want to skip the columns which are NULL and build the WHERE clause based on the remaining not null fields... and I have a set of data for comparing it with the WHERE clause...
Eg :
Normal query:
------------
Select a, b, c, d, e from <table_name>
where ABC = l_abc
and bcd = l_bcd
and cde = l_cde
and def = l_def
and efg = l_efg;
in case if the value of CDE is null then i would code create the REF cursor with this query:
Select a, b, c, d, e from <table_name>
where ABC = l_abc
and bcd = l_bcd
and def = l_def
and efg = l_efg;
But as my query WHERE condtion changes, i cannot use a normal cursor.
Please help me doing it in a REF cursor.
Anand
April 26, 2004 - 6:42 am UTC
Related to RefCursor
Kiranmayee, June 03, 2004 - 8:43 am UTC
I want to Know the way in which we can pass ref cursor as a parameter, when it is an OUT Parameter?
I was asked the same in an interview
Thanks
Kiran
June 03, 2004 - 1:13 pm UTC
trick question, it would not be an OUT, it could be IN or IN OUT, but to *pass it* you would use IN
ops$tkyte@ORA9IR2> create or replace package demo_pkg
2 as
3 procedure p;
4 procedure p2( x in sys_refcursor );
5 end;
6 /
Package created.
ops$tkyte@ORA9IR2> create or replace package body demo_pkg
2 as
3
4 procedure p
5 is
6 l_cursor sys_refcursor;
7 begin
8 open l_cursor for select * from dual;
9 p2(l_cursor);
10 end;
11
12
13 procedure p2( x in sys_refcursor )
14 is
15 l_dummy dual.dummy%type;
16 begin
17 fetch x into l_dummy;
18 close x;
19 dbms_output.put_line( 'dummy = ' || l_dummy );
20 end;
21
22 end;
23 /
Package body created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec demo_pkg.p
dummy = X
PL/SQL procedure successfully completed.
I want create a view based on ref cursor type, please help
sean chang, June 07, 2004 - 10:37 am UTC
SQL> create table abc( a char,b char, c char);
SQL> insert into abc values('a', 'b', 'c');
SQL> CREATE OR REPLACE PACKAGE farm
2 AS
3 TYPE l_ref IS REF CURSOR;
4 FUNCTION get_cursor RETURN l_ref;
5 END farm;
6 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY farm
2 AS
3 FUNCTION get_cursor RETURN l_ref
4 AS
5 refc l_ref;
6 BEGIN
7 OPEN refc FOR SELECT dummy FROM abc;
8 RETURN refc;
9 END get_cursor;
10 END farm;
11 /
Package body created.
SQL>select farm.get_cursor from dual;
Cursor statement : 1
Cursor statement : 1
A B C
a b c
1 row selected
SQL> create view test_view as select farm.get_cursor as F
from dual ;
cannot create a view with column based on CURSOR type
SQL> create view test_view as select farm.get_cursor.a
as a from dual;
ORA-22806:not an object or REF
Sir, How do I create a view of some columns based on
returned Ref cursor type? thank you.
June 07, 2004 - 1:09 pm UTC
you cannot.
it is not a supported construct. "ref cursor" is a plsql thing -- not a SQL thing. SQL doesn't understand "ref cursors"
Ref cursor and dynamic sql
Ted, June 18, 2004 - 2:39 pm UTC
Oracle version: Oracle8i EE 8.1.7.4.1 - Production
Hi Tom,
How can I return a record set using a dynamic sql? If I try to use dynamic sql with ref cursor, the version does not support it. The user wants the procedure to return set of rows returned based on combination of 5-6 input parameters.
Please help!!!
Variables based on identifiers
A reader, June 20, 2004 - 3:13 pm UTC
Maybe this is not appropriate for this thread, but it sort of is, so here goes.
Many places in my code I need to create variables that are going to contain Oracle table/column/object names. I know these have to be varchar2(30) since that is the max length for identifiers. Instead of hardcoding varchar2(30) all over the place, can I use the %type or a user-defined type? i.e. define all my types in a TYPES schema and do types.identifier%type or something? How can I do this?
Thanks
June 20, 2004 - 4:25 pm UTC
foobar all_objects.object_name%type;
you can create your own subtype as well.
ops$tkyte@ORA9IR2> create or replace package demo_pkg
2 as
3 subtype ident_type is varchar2(30);
4 end;
5 /
Package created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
2 x demo_pkg.ident_type;
3 begin
4 null;
5 end;
6 /
PL/SQL procedure successfully completed.
SUBTYPE
A reader, June 20, 2004 - 5:02 pm UTC
The subtype feature appeals to me. But is there a way to create a stand-alone subtype, not as part of a package? Sort of like the 'CREATE TYPE'? This way I can
foobar types.identifier;
instead of
foobar types.some_pkg.identifier;
Small nit, I know!
June 21, 2004 - 8:05 am UTC
how about
create package our_types
as
....
end;
/
create public_synonym our_types for our_types
/
and now it is just
our_types.identifier;
(no, you cannot create a plsql subtype in SQL, only sql types and that would make it an object type -- create type identifier as object ( id varchar2(30) );
and while you could then:
foobar identifier;
you would have to address:
foobar.id := 'hello';
in the code.
Function returning ref cursor
Aggarwal, July 09, 2004 - 4:26 pm UTC
Tom
I am new to ref cursors. I wrote a packaged function that returns ref cursor. How can i use this function to get the values in some other pl/sql code:
My function is:
Create or Replace package vn_methods is
Type rc is ref cursor;
Function get_group_names(set_type In Varchar2,
my_date In Date,
my_environment In Varchar2) return rc;
end vn_methods;
Create Or Replace Package Body vn_methods is
Function get_group_names(set_type In Varchar2,
my_date In Date,
my_environment In Varchar2) return rc as
my_group_names rc;
Begin
OPEN my_group_names for Select name
From vn_pmv_group
Where environment = my_environment
And type = set_type
And asof_date in (Select max(asof_date)
From vn_pmv_group
Where environment = my_environment
And type = set_type
And trunc(asof_date) <= to_date(my_date, 'MM/DD/YYYY'));
Return my_group_names;
End get_group_names;
End vn_methods;
July 09, 2004 - 6:30 pm UTC
just fetch from it?
is
l_cursor vn_methods.rc;
l_name varchar2(...);
begin
l_cursor := get_group_names( ... );
loop
fetch l_cursor into l_name;
exit when l_cursor%notfound;
process row....
end loop
close l_cursor;
....
btw:
trunc(asof_date) <= to_date(my_date,'mm/dd/yyyy')
will return the same rows as:
asof_date <= to_date( .... )
and can make better use of indexes....
Also, select max() without a group by returns at least and as most one row -- so use
and asof_date = ( select max()....)
and in fact, if the goal is to get A SINGLE record --
select name
from (select name
from vn_pmv_group
where environment = ...
and type = ...
order by environment DESC, type DESC, asof_date DESC )
where rownum = 1;
*might* be a better approach.
Aggarwal, July 09, 2004 - 4:36 pm UTC
I figured it, do I need to select from dual, like this:
select vn_methods.get_group_names('Standard-7.1.1',to_date('07/07/2004','MM/DD/YYYY'), 'vndbacpt')
from dual;
July 09, 2004 - 6:31 pm UTC
nah, you have a cursor -- do it like above.
Aggarwal, July 12, 2004 - 2:30 pm UTC
Thanks so much Tom!! Also for the extra suggestions that you made.
I have one question: I wld like to know what is the harm in Selecting from dual ?
Because at some places I need to do this:
Select a, b, c
From t1, t2
Where name in (select vn_methods.get_group_names('Standard-7.1.1',to_date('07/07/2004','MM/DD/YYYY'), 'vndbacpt') from dual) ;
This is the whole purpose of writing this funtion.
July 12, 2004 - 9:02 pm UTC
there is nothing wrong with using dual when appropriate? did i say otherwise?
Shalu, July 30, 2004 - 2:00 pm UTC
Tom
I get this error:
ORA-00932: inconsistent datatypes: expected - got CURSER
when I am doing:
Select *
From tab
Where name in (Select vn_methods.get_group_names('Standard',to_date('07/29/2004','MM/DD/YYYY'), 'vndbacpt') from dual);
Where "vn_methods.get_group_names" returns a ref cursor (of names, which are Varchars).
What am I doing wrong here ?
This is my package:
package vn_methods is
Type rc is ref cursor;
Function get_group_names(set_type In Varchar2,
my_date In Date,
my_environment In Varchar2) return rc;
end vn_methods;
Package Body vn_methods is
Function get_group_names(set_type In Varchar2,
my_date In Date,
my_environment In Varchar2) return rc as
my_group_names rc;
Begin
Open my_group_names for Select name
From vn_pmv_group
Where environment = my_environment
And type = set_type
And asof_date = (Select max(asof_date)
From vn_pmv_group
Where environment = my_environment
And type = set_type
And asof_date <= my_date);
Return my_group_names;
End get_group_names;
End vn_methods;
July 30, 2004 - 5:45 pm UTC
how can a "name" be in a "ref cursor" -- a ref cursor is a pointer to a cursor, a name is a string, a string isn't the same as a ref cursor?
you cannot use ref cursors as "sets" in queries like that.
Shalu, July 30, 2004 - 3:17 pm UTC
Do I need to do CASTing here ? I tried it, but didn't work.
IN OUT mode
Push, August 24, 2004 - 9:49 am UTC
Tom,
From the Oracle documentation...
"When you declare a cursor variable as the formal parameter of a subprogram that opens the cursor variable, you must specify the IN OUT mode. That way, the subprogram can pass an open cursor back to the caller".
The Java Code is having problem calling the procedure
which has REF CURSOR as the IN OUT parameter.
The Java programmer wants this REF CURSOR declared just
as a OUT parameter and not as IN OUT parameter.
CREATE PACKAGE emp_data AS
...
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp);
END emp_data;
CREATE PACKAGE BODY emp_data AS
...
PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp) IS
BEGIN
OPEN emp_cv FOR SELECT * FROM emp;
END open_emp_cv;
END emp_data;
But the Java programmers do not like the above method
and instead they want (emp_cv OUT EmpCurTyp). With just OUT mode, it works fine from the Java.
Please advice if this is correct.
Also please tell us know if there is any impact if we do not declare REF CURSOR parameter as IN OUT mode.
Thanks
Pushparaj
August 24, 2004 - 10:27 am UTC
why do they care? are they getting an error if it is "in out"?!?
it is the only way I define them, what is it that is preventing them from continuing on, why do they care?
push, August 24, 2004 - 2:04 pm UTC
Tom,
When it is declared as IN OUT the following error
message is raised in Java.
java.sql.SQLException: ORA-06550: line 1, column 13
PLS-00306: wrong number or types of arguments in call to 'GET_USER_INFO'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
...
...
The description of the stored function within a package:
FUNCTION get_user_info(
SesN IN NUMBER,
cGenCursor IN OUT p_gen.CursorTyp,
CnyNam OUT VARCHAR2,
BidShp OUT VARCHAR2,
AEN OUT VARCHAR2,
AETyp OUT VARCHAR2,
AENam OUT VARCHAR2,
AEDes OUT VARCHAR2,
ASMN OUT VARCHAR2,
ASMN OUT VARCHAR2,
CICSMsg OUT VARCHAR2
) RETURN INTEGER;
The Java code:
public void getBidInfoData(BidInfoDTO bidInfoDTO) throws DAOException {
int returnCode = 0;
try{
//con = getConnection(bidInfoDTO.getCountryCode());
con = getConnection(1);
cs = con.prepareCall("begin ?:= pkg_all_info.get_user_info_data(?,?,?,?,?,?,?,?,?,?); end;");
cs.registerOutParameter(1, OracleTypes.NUMBER);
cs.setInt(2, 4063);
//cs.setInt(2, bidInfoDTO.getSesNum());
cs.registerOutParameter(3, OracleTypes.CURSOR);
cs.registerOutParameter(4, OracleTypes.VARCHAR);
cs.registerOutParameter(5, OracleTypes.VARCHAR);
cs.registerOutParameter(6, OracleTypes.VARCHAR);
cs.registerOutParameter(7, OracleTypes.VARCHAR);
cs.registerOutParameter(8, OracleTypes.VARCHAR);
cs.registerOutParameter(9, OracleTypes.VARCHAR);
cs.registerOutParameter(10, OracleTypes.VARCHAR);
cs.registerOutParameter(11, OracleTypes.VARCHAR);
System.out.println(" Test1 ");
cs.execute();
System.out.println(" Test2 ");
returnCode = cs.getInt(1);
if(returnCode == 0) {
System.out.println("Successfully called p_iwa_bid_info.f_get_bid_info_data");
rs = (ResultSet)cs.getObject(3);
while(rs.next()) {
bidInfoDTO.setSesNum(rs.getInt(1));
bidInfoDTO.setPrcInd(rs.getString(2));
bidInfoDTO.setBidInfoUpdInd(rs.getString(3));
bidInfoDTO.setCustUpdInd(rs.getString(4));
bidInfoDTO.setReasonUpdInd(rs.getString(5));
bidInfoDTO.setBidExeUpdInd(rs.getString(6));
bidInfoDTO.setBidName(rs.getString(7));
bidInfoDTO.setBidStartDate(rs.getString(8));
bidInfoDTO.setBidEndDate(rs.getString(9));
bidInfoDTO.setPaNum(rs.getString(10));
bidInfoDTO.setPaName(rs.getString(11));
bidInfoDTO.setBidRegion(rs.getString(12));
bidInfoDTO.setBidDistrict(rs.getString(13));
bidInfoDTO.setBidComplianceRegion(rs.getString(14));
bidInfoDTO.setBidComplianceDistrict(rs.getString(15));
bidInfoDTO.setContactName(rs.getString(16));
bidInfoDTO.setAddress1(rs.getString(17));
bidInfoDTO.setAddress2(rs.getString(18));
bidInfoDTO.setCity(rs.getString(19));
bidInfoDTO.setCounty(rs.getString(20));
bidInfoDTO.setState(rs.getString(21));
bidInfoDTO.setPostalCode(rs.getString(22));
bidInfoDTO.setJobTitle(rs.getString(23));
bidInfoDTO.setPhoneNum(rs.getString(24));
bidInfoDTO.setFax(rs.getString(25));
bidInfoDTO.setCountry(rs.getString(26));
bidInfoDTO.setBidReasonCode(rs.getString(27));
bidInfoDTO.setScenarioBidInd(rs.getString(28));
bidInfoDTO.setFormalBidInd(rs.getString(29));
bidInfoDTO.setDebugBidInd(rs.getString(30));
bidInfoDTO.setAssocBidInd(rs.getString(31));
bidInfoDTO.setMultiCountry(rs.getString(32));
bidInfoDTO.setPgrNum(rs.getString(33));
bidInfoDTO.setRateType(rs.getString(34));
bidInfoDTO.setCommitmentType(rs.getString(35));
bidInfoDTO.setReviewDate(rs.getString(36));
bidInfoDTO.setBidStartDate(rs.getString(37));
bidInfoDTO.setEmail(rs.getString(38));
}
rs.close();
bidInfoDTO.setLeadShipper(cs.getString(4));
//bidInfoDTO.setAeNum(cs.getString(5));
bidInfoDTO.setAeType(cs.getString(6));
bidInfoDTO.setAeName(cs.getString(7));
//bidInfoDTO.setAeTxt(cs.getString(8));
//bidInfoDTO.setAsmNum(cs.getString(9));
//bidInfoDTO.setAsmName(cs.getString(10));
bidInfoDTO.setCICSMsgNum(cs.getString(11));
}
else
System.out.println("Error calling p_iwa_bid_info.f_get_bid_info_data");
}catch(Exception e) {
e.printStackTrace();
}finally {
closeAll(con,cs);
}
}
Please advice.
Thanks
Push..
August 24, 2004 - 3:37 pm UTC
beg to differ, run this:
create or replace package demo_pkg
as
type refcur is ref cursor;
procedure get_cur( x in out refcur );
end;
/
create or replace package body demo_pkg
as
procedure get_cur( x in out refcur )
is
begin
open x for select * from emp;
end;
end;
/
import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;
class curvar
{
public static void main (String args [])
throws SQLException, ClassNotFoundException
{
String query =
"begin demo_pkg.get_cur( :1 ); end;";
DriverManager.registerDriver
(new oracle.jdbc.driver.OracleDriver());
Connection conn=
DriverManager.getConnection
("jdbc:oracle:oci8:@ora9ir2",
"scott", "tiger");
CallableStatement cstmt = conn.prepareCall(query);
cstmt.registerOutParameter(1,OracleTypes.CURSOR);
cstmt.execute();
ResultSet rset = (ResultSet)cstmt.getObject(1);
for(int i = 0; rset.next(); i++ )
System.out.println( rset.getString(1) );
cstmt.close();
}
}
print_table
A reader, September 07, 2004 - 4:27 pm UTC
Hi Tom
Is it possible to modify the print_table routine
of yours to do the exact same thing as it does
but with a ref cursor parameter?
September 07, 2004 - 4:33 pm UTC
no, you cannot "describe" a ref cursor, you cannot procedurally process a ref cursor using an API.
ok - thank you!
A reader, September 07, 2004 - 4:41 pm UTC
alt, May 20, 2005 - 4:58 am UTC
HI Tom
how to pass a arguments to a cursor which is inside the procedure(can we use bind variables here )
May 20, 2005 - 8:00 am UTC
just reference plsql variables in the sql, it just happens.
Ref Cursor - passing as parameter works, but locally declared not.
Andrey, August 23, 2005 - 7:49 am UTC
Hi Tom,
i have a problem with a dynamically opened ref cursor.
The scenario is:
create or replace package pkg is
type trefcur is ref cursor;
procedure p1;
procedure p2(p_cur in out trefcur);
end;
/
create or replace package body pkg is
procedure p1
is
l_cur trefcur;
begin
p2(l_cur);
fetch l_cur into ... --NOT A BULK COLLECT! fetched into a variable of object type
...
end;
procedure p2(p_cur in out trefcur) is
begin
...
open p_cur for '...query...'; -- opens dynamically
end;
end;
/
begin
pkg.p1;
end;
/
This ends up with "ORA-01001 invalid cursor" at the fetch line in p1.
However, if i change
create or replace package pkg is
type trefcur is ref cursor;
procedure p1(p_cur in out trefcur);
procedure p2(p_cur in out trefcur);
end;
/
create or replace package body pkg is
procedure p1(p_cur in out trefcur)
is
--l_cur trefcur; --Cursor is not locally declared anymore
begin
p2(p_cur);
fetch p_cur into ...
...
end;
procedure p2(p_cur in out trefcur) is
begin
...
open p_cur for '...query...'; -- opens dynamically
end;
end;
/
I can then run in SQL*Plus:
variable cur refcursor
exec pkg.p1(:cur)
and that works well, but(!)
declare
cur pkg.trefcur;
begin
pkg.p1(cur);
end;
/
ends oncemore with
declare
*
ERROR at line 1:
ORA-01001: invalid cursor
It seems to be, that only ref cursors, declared at the client-side and provided
as parameters works there, but not cursors, that are declared at the server-side.
My problem is that i must call p1 over a DB-Link, so i can't provide a ref cursor as paramter for p1.
I have also to mention that this seems to be only the issue with that particular query in p2.
This works fine with some other queries. That's why i haven't provided the complete example,
because the real example contains 1000-2000 lines and some demo-example is just working.
Are there any issues known?
Thanks
August 24, 2005 - 4:13 am UTC
do you have a complete, small testcase I can run.
(i'm not sure where p1 and p2 are supposed to "be" here)
Barry, August 24, 2005 - 10:50 am UTC
Tom,
I have a procedure with 2 cursors. The first procedure calls a function
that fills an array the feeds into the second cursor. If I call them in
the same procedure, the second cursor always returns no rows, even though
the source array is filled when the cursor is fetched. I see in a prior
response, you indicated:
in general queries are not "executed" until you ask for the first row back
This is on 8.1.7
SQL>
SQL> CREATE OR REPLACE TYPE t_numarray AS TABLE OF NUMBER
2 /
Type created.
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE testme IS
2
3 TYPE t_cursor IS REF CURSOR;
4 v_numarray t_numarray;
5
6 PROCEDURE test_refcursor(
7 p_out_curs1 OUT t_cursor,
8 p_out_curs2 OUT t_cursor);
9
10 PROCEDURE test_refcursor2(
11 p_out_curs2 OUT t_cursor);
12
13 FUNCTION filltable(p_in_number IN NUMBER) RETURN NUMBER;
14
15 END testme;
16 /
Package created.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY testme IS
2
3 PROCEDURE test_refcursor(
4 p_out_curs1 OUT t_cursor,
5 p_out_curs2 OUT t_cursor) IS
6 BEGIN
7 v_numarray := t_numarray();
8 OPEN p_out_curs1 FOR
9 SELECT filltable(rownum) FROM all_tables WHERE rownum <= 10;
10 OPEN p_out_curs2 FOR
11 SELECT column_value
12 FROM TABLE(CAST(testme.v_numarray AS t_numarray));
13 END test_refcursor;
14
15 PROCEDURE test_refcursor2(
16 p_out_curs2 OUT t_cursor) IS
17 BEGIN
18 OPEN p_out_curs2 FOR
19 SELECT column_value
20 FROM TABLE(CAST(testme.v_numarray AS t_numarray));
21 END test_refcursor2;
22
23 FUNCTION filltable(p_in_number IN NUMBER) RETURN NUMBER IS
24 BEGIN
25 v_numarray.EXTEND;
26 v_numarray(v_numarray.COUNT) := p_in_number;
27 RETURN(p_in_number*2);
28 END filltable;
29
30 END testme;
31 /
Package body created.
SQL>
SQL> var p_out_curs1 refcursor
SQL> var p_out_curs2 refcursor
SQL> BEGIN
2 testme.test_refcursor(:p_out_curs1,:p_out_curs2);
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> print p_out_curs1
TESTME.FILLTABLE(ROWNUM)
------------------------
2
4
6
8
10
12
14
16
18
20
10 rows selected.
SQL> -- why is this empty?
SQL> print p_out_curs2
no rows selected
SQL>
SQL> var p_out_curs2 refcursor
SQL> BEGIN
2 testme.test_refcursor2(:p_out_curs2);
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> -- now the cursor is full
SQL> print p_out_curs2
COLUMN_VALUE
------------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
SQL>
My questions are
1. Why does the cursor return no rows when within test_refcursor, yet
is filled when I call test_refcursor2
2. My workaround was to fetch the results of the first cursor into an
array, and output the array as a ref cursor which seems to work.
(not the ideal solution) Do you see any issue with this?
3. Is there a better workaround?
August 24, 2005 - 5:46 pm UTC
when you open the second cursor the first time, there is "nothing" in the array yet, you haven't filled in the array as yet and it would appear it has made a copy of the array when you opened the cursor -- hence, when you fetch the data and fill it in, it doesn't "count" since the array was already referenced (when empty) and used "empty"
if you modify the code to be:
7 BEGIN
8 v_numarray := t_numarray();
9 OPEN p_out_curs1 FOR
10 SELECT filltable(rownum) FROM all_tables WHERE rownum <= 10;
11 loop
12 fetch p_out_curs1 into l_num;
13 exit when p_out_curs1%notfound;
14 dbms_output.put_line( l_num );
15 end loop;
16 OPEN p_out_curs2 FOR
17 SELECT column_value
18 FROM TABLE(CAST(testme.v_numarray AS t_numarray));
that is, fill the array BEFORE opening the cursor, you get what you seem to want.
I cannot give you a better solution since I'm not really sure what the goal here is, you simply need to have the array populated before you open the cursor that references it.
Ref Cursor - passing as parameter works, but locally declared not
Andrey, August 26, 2005 - 4:14 pm UTC
A have found the reason, while trying to produce a test case! That happens, if the cursor is opened in a subprocedure (p_inner):
SQL>create or replace package pkg is
2 type trefcur is ref cursor;
3
4 procedure p1;
5
6 procedure p1_par(p_cur in out trefcur);
7
8
9 procedure p2(p_cur in out trefcur);
10
11 end;
12 /
Package created.
SQL>create or replace package body pkg is
2
3 procedure p1
4 is
5 l_cur trefcur;
6 a number(2);
7 begin
8 p2(l_cur);
9 fetch l_cur into a;
10 close l_cur;
11 end;
12
13
14 procedure p1_par(p_cur in out trefcur)
15 is
16 a number(2);
17 begin
18 p2(p_cur);
19 fetch p_cur into a;
20 --close p_cur;
21 end;
22
23
24
25 procedure p2(p_cur in out trefcur) is
26 procedure p_inner is
27 begin
28 open p_cur for 'select count(*) from dual'; -- opens dynamically
29 end;
30
31 begin
32 p_inner;
33 end;
34 end;
35 /
Package body created.
SQL>exec pkg.p1
begin pkg.p1; end;
*
ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at "WEB_DEV.PKG", line 9
ORA-06512: at line 1
SQL>var pcur refcursor
SQL>exec pkg.p1_par(:pcur)
PL/SQL procedure successfully completed.
no rows selected
SQL>
As for me, this seems to be a bug.
August 27, 2005 - 4:41 am UTC
Yes, does seem to be a scoping problem -- if you write the inner procedure this way:
25 procedure p2(p_cur in out trefcur) is
26 procedure p_inner( pp_cur in out trefcur ) is
27 begin
28 open pp_cur for 'select count(*) from dual'; -- opens dynamically
29 end;
30
31 begin
32 p_inner(p_cur);
33 end;
It does not occur. Since you have such a small test case now, would you be able to forward it onto support to get a bug created?
sheebs, September 06, 2005 - 8:24 am UTC
In this program, how I will access the G_COND.
I am running this script as Concurrent Program.
Tom, Please help if you have time...
TYPE x_item_type IS REF CURSOR;
x_cur_item x_item_type;
TYPE x_rec_type IS RECORD
( inventory_item_id mtl_system_items_b.inventory_item_id%TYPE
,item mtl_system_items_b.segment1%TYPE
,organization_id org_organization_definitions.organization_id%TYPE
,organization_code org_organization_definitions.organization_code%TYPE
,category mtl_categories.segment1%TYPE);
rec_item x_rec_type;
/*After this code,
getting the condition in G_COND global variable
The G_COND will return this value
ood.organization_code IN ('V1','M1')
*/
OPEN x_cur_item FOR SELECT msi.inventory_item_id
,msi.segment1 Item
,ood.organization_id
,ood.organization_code
,mc.segment1 "Category"
FROM mtl_category_sets mcs
,mtl_categories mc
,org_organization_definitions ood
,mtl_system_items_b msi
,mtl_item_categories mic
WHERE mcs.category_set_name = 'Purchasing'
AND mc.segment1 = NVL(x_pur_cat,mc.segment1)
AND :G_COND
ORDER BY msi.segment1,ood.organization_code;
ELSE
OPEN x_cur_item FOR SELECT msi.inventory_item_id
,msi.segment1 Item
,ood.organization_id
,ood.organization_code
,mc.segment1 "Category"
FROM mtl_category_sets mcs
,mtl_categories mc
,org_organization_definitions ood
,mtl_system_items_b msi
,mtl_item_categories mic
WHERE mcs.category_set_name = 'Purchasing'
AND mc.segment1 = NVL(x_pur_cat,mc.segment1)
AND ood.organization_code BETWEEN x_from_org AND x_to_org
AND MSI.inventory_item_status_code ='Active'
ORDER BY msi.segment1,ood.organization_code;
END IF;
FETCH x_cur_item INTO rec_item;
EXIT WHEN x_cur_item%NOTFOUND;
---------------------------------------------------------------
---------------------------------------------------------------
CLOSE x_cur_item;
September 06, 2005 - 8:47 am UTC
you would have to use dynamic sql to dynamically change the queries predicate at runtime.
search this site for str2tbl and read those articles as well so you can see ways to bind an in-list.
Passing explicit cursor as a REF CURSOR
Darin, November 16, 2005 - 4:35 pm UTC
Hi Tom,
A question in response to the above post:
Related to RefCursor June 03, 2004
Reviewer: Kiranmayee from Hyderabad, India
I have a similar situation to to example you gave in your response, but I would like to use an explicit cursor, instead of defining it in-line as you did.
following is my test case, which compiles to the following error:
PLS-00306: wrong number or types of arguments in call to 'BAR'
drop table footab;
drop table bartab;
create table footab (x number);
create table bartab as select * from footab where 1=0;
begin
for i in 1..10 loop
insert into footab values(i);
end loop;
end;
/
select * from footab;
create or replace package foobar as
PROCEDURE foo;
END foobar;
/
create or replace package body foobar as
type rc is REF CURSOR return footab%rowtype;
PROCEDURE bar(p rc);
PROCEDURE foo is
cursor c is select * from dual;
BEGIN
open c;
bar(c);
close c;
END foo;
PROCEDURE bar(p rc) is
type t is table of p%rowtype;
v t;
BEGIN
loop
FETCH p BULK COLLECT into v LIMIT 3;
exit when v.count = 0;
FORALL i in v.first..v.last
insert into bartab values v(i);
commit;
exit when p%NOTFOUND;
end loop;
END bar;
END foobar;
/
So the root questoin: Is there a way to pass an explicit cursor into a procedure expecting a REF CURSOR type? <let's assume for discussion's sake that there is a valid reason I want to *pre-define* the cursor (i.e. explicit cursor), such as I need to call it once in the calling procedure (i.e foo), alter some stuff, then call again in the called procedure (i.e. bar)>.
Thanks as always
FYI:
SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for HPUX: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production
November 16, 2005 - 6:22 pm UTC
has to be a ref cursor.
Re:Passing explicit cursor as a REF CURSOR
Darin, November 16, 2005 - 4:46 pm UTC
One other question:
I changed my package to use the in-line def for the ref cursor, and it worked fine, then I changed the ref cursor type to be weakly defined, as follows:
create or replace package body foobar as
-- type rc is REF CURSOR return footab%rowtype;
type rc is REF CURSOR;
PROCEDURE bar(p rc);
PROCEDURE foo is
-- cursor c is select * from dual;
c rc;
BEGIN
open c for select * from dual;
bar(c);
close c;
END foo;
PROCEDURE bar(p rc) is
type t is table of p%rowtype;
v t;
BEGIN
loop
FETCH p BULK COLLECT into v LIMIT 3;
exit when v.count = 0;
FORALL i in v.first..v.last
insert into bartab values v(i);
commit;
exit when p%NOTFOUND;
end loop;
END bar;
END foobar;
/
I get the following compile errors:
16/5 PL/SQL: Item ignored
16/24 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
20/7 PL/SQL: SQL Statement ignored
20/33 PLS-00597: expression 'V' in the INTO list is of wrong type
23/9 PL/SQL: SQL Statement ignored
23/35 PL/SQL: ORA-00904: : invalid identifier
For defining type T, and declaring variable v in procedure bar.
Can you advise why I cannot extract the record type from a weakly defined ref cursor? I thought I had seen this done elsewhere.
Thanks again.
November 16, 2005 - 6:25 pm UTC
P%rowtype cannot make sense here, the rowtype of p isn't known until run time... hence it cannot compile.
A reader, November 30, 2005 - 11:44 am UTC
Hi Tom,
I have the following requirement
I have a table like :
create table p1
(
n1 varchar2(20),
d1 varchar2(200),
pr1 varchar2(50)
);
insert into p1 values ('K1', 'Description 1', 'Proc 1');
insert into p1 values ('K2', 'Description 2', 'Proc 2');
where value in column pr1 indicates a procedure call.
Now there is one procedure which will return the rows of table p1 to the front end. Hyperlinks will be provided for each row and when a user clicks on 1 then the frontend will pass 'Proc 1' as an input parameter and I have to execute 'Proc 1'. If the user clicks on 2 then the frontend will pass 'Proc 2' as an input parameter and I have to execute 'Proc 2'. What is the best way to achieve this?
Please help.
Thanks.
November 30, 2005 - 3:06 pm UTC
depends on your client programming language entirely.
you'll be doing dynamic sql. use binds, that is all i can say!
A reader, November 30, 2005 - 3:34 pm UTC
Can you direct me to a useful link to learn about dynamic SQL and bind parameters so that I can see some examples?
Thanks.
November 30, 2005 - 8:52 pm UTC
REF CURSORS in diferent DB'S
Jorge, March 21, 2006 - 7:54 am UTC
Hi TOM,
I have a problem with ref cursors.I'll try to explain it(sorry if my english is not very good).
I have 2 databases and i want to return values from one DBto the other.
In the DB that i want to recieve the data i have the call(with a procedure) and i create a variable
wich the type is REF CURSOR from the second DB. In example:
--the variable
vResultCursor user_DB2.pk_k1.vSqlCursorD@DB2;
--where pk1 is a package in which i declare the REF CURSOR variable
..
--The call
user_DB2.pk_k1.P_1@DB2(vResultCursor);
--where P1 is the procedure in wich i open the cursor and
after that i want to work with this cursor
loop
--vx is varchar2
FETCH vResultCursor INTO vx;
EXIT WHEN vResultCursor%NOTFOUND;
insert into tbl_probe values (sysdate,'vx',vx);
commit;
end loop;
close vResultCursor;
In the first DB i have in PK_K1 the declaration of the ref cursor, and the procedure wich open the dinamic
cursor:
CREATE OR REPLACE PACKAGE PK_K1 IS
TYPE vSqlCursorD IS REF CURSOR;
PROCEDURE P_RESOLVECURSOR (vSQLCURSOR OUT vSqlCursorD);
END PK_K1;
CREATE OR REPLACE PACKAGE BODY PK_K1 IS
PROCEDURE P_RESOLVECURSOR (vSQLCURSOR OUT vSqlCursorD) IS
vSqlCursortxt VARCHAR2(4096);
BEGIN
vSqlCursortxt:= 'SELECT * FROM DUAL';
OPEN vSQLCURSOR FOR vSqlCursortxt;
EXCEPTION
WHEN OTHERS THEN
IF (vSQLCURSOR%ISOPEN) THEN
CLOSE vSQLCURSOR;
END IF;
END;
END PK_K1;
The problem that i have is, that if i make a procedure in the package PK_K1 and i call the procedure P_RESOLVECURSOR
it works, but when i call from the other DB it doesnt work. The error is ERROR ORA-01001 when whe make the FETCH
I gave the EXECUTE grant from one DB to the OTHER
GRANT EXECUTE ON PK_K1 TO USERDB1;
could u help me?
Thanks
March 22, 2006 - 2:10 pm UTC
ref cursors cannot be used over a dblink like that.
</code>
http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14261/sqloperations.htm#sthref1448 <code>
....
Note:
* Using a REF CURSOR variable in a server-to-server RPC results in an error. However, a REF CURSOR variable is permitted in a server-to-server RPC if the remote database is a non-Oracle database accessed through a Procedural Gateway. * LOB parameters are not permitted in a server-to-server RPC.
.....
Passing a cursor from one DB to the other
Jorge, March 23, 2006 - 6:15 am UTC
Thank for the explanation, we solve the problem opening and closing the cursor in one DB and passig the data to the other server in an TABLE Object by means of a function.
Thanks a lot
20yr Oracle Veteran in Awe
Duane Dieterich, December 03, 2006 - 9:58 pm UTC
Tom, I have found AskTom to be an extremely useful information source. I am in awe of your expertise, patience, and willingness to look at problems from different angles. I have learned a lot! Keep up the good work. Duane
RPC with Commit and OUT param
Sandeep, January 29, 2007 - 2:06 pm UTC
Hi Tom,
I ran into a problem for which i will need your valuable solution.
BOX A(10g)
PROC a
BOX B(9i)
PROC b (no commits or OUT parameters)
PROC c (REF CURSOR OUT parameter and commits)
"A" calls "b" using a DBLINK works fine.
"A" calls "b" and "b" calls "c" does not work(ORA-02064)
Other than removing the COMMIT statements in proc C do you see any other workaround for this problem.
Thanks a lot!!
Sandeep.
January 31, 2007 - 1:22 pm UTC
not really - commits should be done at the top level anyway if you ask me (eg: if I could, I would remove the ability to commit and rollback in plsql all together!)
ORA-02064
Vikas Khanna, April 07, 2007 - 11:55 am UTC
Hi Tom,
This query is giving me ora-02064 :"distributed operation not supported" but I am not able to make out what is wrong with this statement.
This updates the remote table for the dum_imp_flag for which it discovers that there are more than one click_tag
Please help in solving this problem SQL statement for remote databases, since it is working fine on a single box
MERGE /*+ Driving_site(dn) Parallel(dn 16) Parallel(mrg 16) */ INTO imp_clk_invrep_denorm@wadm.amazon dn
USING
(
SELECT /*+ Driving_site(dn1) parallel(a 16) parallel(dn1 16) */
dn1.impression_tag,dn1.click_tag
FROM
(
SELECT /*+ parallel(a 8) */ impression_tag,MAX(click_tag) max_click_tag
FROM
(
SELECT /*+ parallel(c 8) */
impression_tag,
click_tag,
COUNT(click_tag) OVER(PARTITION BY impression_tag) cnt
FROM
click_data c
WHERE
c.insert_date > to_date('05/04/2007 07:59:59','dd/mm/yyyy hh24:mi:ss')
AND c.insert_date <= to_date('06/04/2007 07:59:59','dd/mm/yyyy hh24:mi:ss')
GROUP BY
impression_tag,click_tag
)
WHERE
cnt > 1
GROUP BY
impression_tag
) a,
imp_clk_invrep_denorm@wadm.amazon dn1
WHERE
a.impression_tag=dn1.impression_tag
AND dn1.click_tag<a.max_click_tag
AND dn1.created_date=dn1.updated_date
AND dn1.dup_imp_flag IS NULL
) mrg
ON (dn.impression_tag=mrg.impression_tag AND dn.click_tag=mrg.click_tag)
WHEN MATCHED THEN
UPDATE SET
dn.dup_imp_flag=1;
April 09, 2007 - 10:18 am UTC
well, direct path parallel operations are a non-starter with dblinks - and the driving site on the modification isn't going to work either.
Passing ref cursors between procedures
VMN, October 06, 2007 - 9:44 pm UTC
This is somewhat of a followup to your exchagne with Neil in this thread (see
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10369303150185#12907615620876 and
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10369303150185#12958094892048 ).
This is more of a design question. A query is to be used by several procedures. Each procedure using the query evaluates the query parameters and processes the results in a diffrent way. What I want to avoid is to have the query on each procedure because of code maintenance.
The options that I can think are:
* Have a function that returns a ref cursor to each calling procedure.
* Have a function that BULK COLLECTS into a nested table and returns the table to each calling procedure.
* Create a view with the query's logic, and have each procedure query the view.
In your excange with Neil, you recommend using ref cursors only when necessary. Would you consider this kind of scenario to be such a case? I think using a view can be a problem if the cartesian product of the related tables is large (my particular case), and using the nested table can be a problem if query results has many rows (isn't passing a ref cursor more efficient?) Or maybe, using an implicit cursor (coding the query several times) on each procedure is still a better approach? I am not a big fan of FETCH/%notfound/CLOSE loops.
What are the pros and cons of each approach?
I'd like to know your opinion on this. Example is below.
/*** settings ***/
SET serveroutput ON;
/*** tables ***/
DROP TABLE tst_players
/
CREATE TABLE tst_players
(
league VARCHAR2( 240 ) ,
name VARCHAR2( 240 ) ,
chara VARCHAR2( 240 ) ,
wins NUMBER ,
losses NUMBER
)
/
INSERT INTO tst_players VALUES ( 'Newbies' , 'p1' , 'Ciel' , 14 , 16 );
INSERT INTO tst_players VALUES ( 'Pros' , 'p2' , 'Hisui' , 26 , 4 );
INSERT INTO tst_players VALUES ( 'Pros' ,'p3' , 'Aoko' , 20 , 10 );
INSERT INTO tst_players VALUES ( 'Newbies' , 'p4' , 'Hisui' , 15 , 15 );
INSERT INTO tst_players VALUES ( 'Newbies' , 'p5' , 'Sion' , 15 , 15 );
/*** package ***/
CREATE OR REPLACE PACKAGE tst_playerProcessing AS
PROCEDURE main1;
END tst_playerProcessing;
/
CREATE OR REPLACE PACKAGE BODY tst_playerProcessing AS
FUNCTION getPlayers( p_league VARCHAR2 ) RETURN sys_refcursor
IS
l_refCursor sys_refcursor;
BEGIN
OPEN l_refCursor FOR
SELECT *
FROM tst_players player
WHERE player.league = p_league
;
RETURN l_refCursor;
END getPlayers;
PROCEDURE main1
IS
l_league VARCHAR( 240 );
l_playersRC sys_refcursor;
l_playersRec tst_players%rowtype;
BEGIN
l_league := 'Newbies';
l_playersRC := getPlayers( l_league );
LOOP
FETCH l_playersRC INTO l_playersRec;
EXIT WHEN l_playersRC%notfound;
dbms_output.put_line( l_playersRec.name );
END LOOP;
CLOSE l_playersRC;
END main1;
-- main2, main3 ...
END tst_playerProcessing;
/
/*** sqlplus call ***/
EXEC tst_playerProcessing.main1;
/*** cleanup ***/
DROP TABLE tst_players
/
DROP PACKAGE tst_playerProcessing
/
how can I do this using ref cursor
Umesh Kasturi, April 13, 2008 - 6:55 am UTC
Tom,
I am trying some thing as stated below but failing Please help
create or replace procedure p1 ( out_rec sys_refcursor)
as
r1 sys_refcursor;
v1 varchar2(200):='a1234';
stmt varchar2(200);
begin
stmt:= 'select ename,:v1 from emp';
open r1 for stmt using v1;
end;
I have a framed value based on some logic for variable v1 and want it to appear in addition to other column
I am unable to print the rfcursor
Thanks in advance
April 13, 2008 - 8:51 am UTC
well, um, you open R1 - a local variable, but you have an out parameter out_rec you never assign to??!?!?!
ops$tkyte%ORA10GR2> create table emp as select ename, empno from scott.emp
2 /
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace procedure p1 ( l_cursor in out sys_refcursor)
2 as
3 v1 varchar2(200):='a1234';
4 stmt varchar2(200);
5 begin
6 stmt:= 'select ename,:v1 v1 from emp';
7 open L_CURSOR for stmt using v1;
8 end;
9 /
Procedure created.
ops$tkyte%ORA10GR2> column v1 format a10
ops$tkyte%ORA10GR2> variable x refcursor
ops$tkyte%ORA10GR2> exec p1(:x)
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> print x
ENAME V1
---------- ----------
SMITH a1234
ALLEN a1234
WARD a1234
JONES a1234
MARTIN a1234
BLAKE a1234
CLARK a1234
KING a1234
TURNER a1234
ADAMS a1234
JAMES a1234
FORD a1234
MILLER a1234
SCOTT a1234
14 rows selected.
Thanks
Umesh Kasturi, April 13, 2008 - 10:47 pm UTC
Tom
Thanks, for the above reply
You know what, I stay in job with your tips
Thanks
USK
having a function to return number of rows from ref cursor
shahram samardar, December 12, 2008 - 1:46 pm UTC
Hi Tom
From your last example , instead of procedure p, If we want to have a function P that returns number of rows in ref cursor,
what do we have to change in function body to do that?
By doing fetch within function from refcursor, it seems it moves pointer of refcursor that effects consumers of function.
Thanks
December 12, 2008 - 4:16 pm UTC
there is no way to know "How many licks does it take to get to the Tootsie Roll center of a Tootsie Pop?" unless and until you do it.
http://en.wikipedia.org/wiki/Tootsie_Pop ref cursors are just like tootsie pops, unless and until you actually fetch the last row - even we do not know how many rows it will return.
declare
cursor c is select * from ten_billion_row_table;
rec c%rowtype;
begin
open c; <<<=== completes INSTANTLY, we do not actually copy the data or anything
fetch c into rec; <<<== completes INSTANTLY, we do not get the last row to get the first row
Passing Ref Cursor as a Parameter
Lawrance, July 02, 2011 - 1:39 am UTC
Hi Tom,
I have 3 tables, Owner, Participant, Gap_data_Participant. owner table have some the missing information is available in Gap_data_Participant.
Owner Table:
Owner_ID Number; (Primary Key)
Owner_Name varchar2(25);
Participant Table:
PT_ID Number; (Primary Key)
owner_ID Number; (Foreign Key reference On Owner)
PT_Name varchar2(25);
Gap_data_Participant Table:
PT_ID Number; (Foreign Key reference On Participant)
Salary Number;
Address Varchar2(25);
select * from owner;
14 XXX
15 yyy
select * from Participant;
82,14,qqqq
83,14,wwww
select * from Gap_data_Participant;
82,15000,pppp
83,21000,llll
CREATE OR REPLACE PROCEDURE Searching (
inownerId IN NUMBER,
icSearchVar IN VARCHAR2,
inIndex IN NUMBER,
ocurresult OUT SYS_REFCURSOR
)
AS
CURSOR curER(inownerId NUMBER) IS
SELECT PT_id
FROM participant WHERE owner_id = inownerId;
BEGIN
FOR recER IN curER(inownerId) LOOP
OPEN ocurresult FOR
SELECT
Salary,address
FROM (SELECT tEmpR.*, rownum r
FROM (SELECT *
FROM Gap_data_Participant
WHERE PT_id=recER.PT_ID AND Gap_data_Participant.address like icSearchVar||'%' order by PT_ID) tEmpR
WHERE rownum <= (inIndex * 10))
WHERE r >= inIndex;
END LOOP;
END;
/
I'm passing ownerId,search address and index. If i'm passing owner id as 14. It contain two rows in participant table and 2 rows in Gap_data_Participant. Now I need to pass the Gap_data_Participant two rows to the front end using ref cursor. It was not working.
I thinking because of cursor is open in for loop, First time its open then while coming second time already cursor is open so problem is occurring.
If it is possible to send record in ref cursor otherwise there is any other way to pass the record to front end. Can you help in this for me.
July 05, 2011 - 7:43 am UTC
you only have one refcursor to open here - how could you open it twice? Think of a ref cursor like a file, would this work to open two files???
Why is there more than one sql statement here anyway? Why are you not just using "IN" (instead of pt_id = recer.tp_id, use pt_id in (select ....))
Clarification on your response
Boris, June 26, 2014 - 2:46 am UTC
I am looking at your response for one of the reader's question (reviewer: Kiranmayee)
Question
I want to Know the way in which we can pass ref cursor as a parameter, when it is an OUT Parameter?
Your response was
"trick question, it would not be an OUT, it could be IN or IN OUT, but to *pass it* you would use IN "
But I could able to pass the ref cursor as OUT parameter. Here is the script
create or replace package demo_pkg
as
procedure p;
procedure p2( x OUT sys_refcursor );
end;
create or replace package body demo_pkg
as
procedure p
is
l_cursor sys_refcursor;
begin
open l_cursor for select * from dual;
p2(l_cursor);
end;
procedure p2( x OUT sys_refcursor )
is
l_dummy dual.dummy%type;
begin
fetch x into l_dummy;
close x;
dbms_output.put_line( 'dummy = ' || l_dummy );
end;
end;
/
exec demo_pkg.p
SQL> exec demo_pkg.p
dummy = X
PL/SQL procedure successfully completed.
Am I Missing something ?? My oracle database version 11g r2. Please advise.
Thanks & Regards,
Boris