pls explain Internal Cause
Vikas Sharma, June 18, 2002 - 11:36 am UTC
hi tom,
thanks for your answer. can you please tell what happens internally.why it does not work with weak ref cursors. please elaborate more.
June 18, 2002 - 12:10 pm UTC
it just wasn't supported until Oracle9i release 1 and up. It just "didn't work" until then.
do we have any performance gain in strongly typed ref cursor
john, July 11, 2002 - 2:27 pm UTC
tom,
is there performance gain using strongly typed ref cursor over weekly typed or both are one and same.
July 12, 2002 - 7:53 am UTC
using the trivial test harness:
http://asktom.oracle.com/~tkyte/runstats.html
anyone could run this (hint hint hint)
ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table emp;
Table dropped.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table emp as select * from scott.emp;
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
2 type rc1 is ref cursor;
3 type rc2 is ref cursor return emp%rowtype;
4
5 l_cursor1 rc1;
6 l_cursor2 rc2;
7
8 l_rec emp%rowtype;
9
10 l_start number;
11 begin
12 insert into run_stats select 'before', stats.* from stats;
13
14 l_start := dbms_utility.get_time;
15 for i in 1 .. 1000
16 loop
17 open l_cursor1 for select * from emp;
18 loop
19 fetch l_cursor1 into l_rec;
20 exit when l_cursor1%notfound;
21 end loop;
22 close l_cursor1;
23 end loop;
24 dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
25
26
27 insert into run_stats select 'after 1', stats.* from stats;
28 l_start := dbms_utility.get_time;
29 for i in 1 .. 1000
30 loop
31 open l_cursor2 for select * from emp;
32 loop
33 fetch l_cursor2 into l_rec;
34 exit when l_cursor2%notfound;
35 end loop;
36 close l_cursor2;
37 end loop;
38 dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
39
40 insert into run_stats select 'after 2', stats.* from stats;
41 end;
42 /
193 hsecs
186 hsecs
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.name, b.value-a.value run1, c.value-b.value run2,
2 ( (c.value-b.value)-(b.value-a.value)) diff
3 from run_stats a, run_stats b, run_stats c
4 where a.name = b.name
5 and b.name = c.name
6 and a.runid = 'before'
7 and b.runid = 'after 1'
8 and c.runid = 'after 2'
9 and (c.value-a.value) > 0
10 and (c.value-b.value) <> (b.value-a.value)
11 order by abs( (c.value-b.value)-(b.value-a.value))
12 /
NAME RUN1 RUN2 DIFF
------------------------------ ---------- ---------- ----------
LATCH.active checkpoint queue 0 1 1
latch
LATCH.done queue latch 0 1 1
LATCH.ksfv messages 2 3 1
LATCH.ncodef allocation latch 1 0 -1
LATCH.undo global data 4001 4000 -1
STAT...consistent gets 18011 18012 1
STAT...enqueue releases 3 2 -1
STAT...cursor authentications 2 1 -1
STAT...parse count (hard) 2 1 -1
STAT...physical reads 1 0 -1
STAT...messages sent 0 1 1
STAT...calls to get snapshot s 5006 5005 -1
cn: kcmgss
LATCH.transaction branch alloc 1 0 -1
ation
LATCH.session switching 1 0 -1
LATCH.job_queue_processes para 1 0 -1
meter latch
STAT...enqueue requests 4 2 -2
STAT...free buffer requested 4 2 -2
LATCH.cache buffers lru chain 3 6 3
STAT...redo entries 16 13 -3
LATCH.messages 4 7 3
LATCH.checkpoint queue latch 6 10 4
STAT...parse time cpu 4 8 4
STAT...db block changes 27 22 -5
LATCH.row cache objects 76 70 -6
STAT...session logical reads 30035 30029 -6
STAT...db block gets 12024 12017 -7
LATCH.redo allocation 14 25 11
STAT...recursive cpu usage 140 124 -16
LATCH.shared pool 2151 2127 -24
LATCH.library cache 10325 10292 -33
LATCH.cache buffers chains 60095 60132 37
STAT...redo size 21440 21392 -48
32 rows selected.
I would say the differences are neglible AT BEST.
strongly typed ref cursor usage
Muhammad Riaz Shahid, January 26, 2008 - 2:52 pm UTC
Tom,
Could you please tell us the use of strongly typed ref cursors? For weakly typed ref cursors, it is clear that whenever we want to build query dynamically we shall use them. But about strongly typed ref cursors I am not so much sure as when to use them.
Thanks in Advance
January 29, 2008 - 2:20 am UTC
when you know at compile time what the structure of the query looks like - if you have a function that always expects a ref cursor that selects three columns - a number, a date and a string - in that order - you might use a strongly typed ref cursor in order to enforce that
Manipulating the ref cursor
Warrick, October 02, 2009 - 1:28 am UTC
Is it possible then to manipulate the contents of a ref cursor (v10.2) prior to passing it back to the calling application? i.e. can I call the ref cursor in a package, inspect it's content as with a normal cursor, and then modify one of the cursor's columns prior to returning it to the calling application?
It seems that although the ref cursor is particularly useful for bulk collects, it's structure prevents further manipulation within the calling procedure.
October 07, 2009 - 3:01 pm UTC
once fetched - it is fetched. If you fetch from it, and then return it, the person that gets it will NOT fetch what you saw.
Sounds like you want a pipelined function. The invoker would select from your routine. Your routine would open the cursor, fetch the data, do something about it, and then pipe it to the invoker
Any idea?
rama, November 25, 2009 - 5:45 am UTC
Hi Tom,
I have a problem while opening a ref cursor for a sql select statement.
Open refc for dql1||dsql2 where dsql1 and dsql2 are of clob datatypes.
Again dsql1 is dsel||dfrm||dwhere
dsel is of varchar2(3000) data type
dfrom is of varchar2(200)
dwhere is of long data type
The same is the case for dsql2
Based on the input parameters, checks are done and appropriate tables are referred and join
conditions arrived at.
When I do an open ref cursor,
I am getting an exception as PLS-00382 :expression is of wrong type
What could be the reason behind this?
November 25, 2009 - 3:12 pm UTC
clob types are not supported with native dynamic sql until version 11g
are you on 11g?
http://docs.oracle.com/docs/cd/B28359_01/appdev.111/b28370/whatsnew.htm#CHDGIGFJ therefore, anything that exceeds 32k will fail - and you have the ability to exceed 32k with what you show.
You would have to use dbms_sql and the parse interface that takes an array of smaller strings to break the 32k barrier in pre-11g.
but since you give no example, no version, no context - that is all anyone could say.
On another note, It looks like someone has once again invented the "we'll store SQL in a table outside of the application and do everything by dynamic magic stuff" again... That is the only reason I could see for your current approach. sigh.
Any idea?
rama, November 25, 2009 - 5:46 am UTC
Hi Tom,
I have a problem while opening a ref cursor for a sql select statement.
Open refc for dql1||dsql2 where dsql1 and dsql2 are of clob datatypes.
Again dsql1 is dsel||dfrm||dwhere
dsel is of varchar2(3000) data type
dfrom is of varchar2(200)
dwhere is of long data type
The same is the case for dsql2
Based on the input parameters, checks are done and appropriate tables are referred and join
conditions arrived at.
When I do an open ref cursor,
I am getting an exception as PLS-00382 :expression is of wrong type
What could be the reason behind this?
OK
A reader, November 30, 2009 - 9:50 am UTC
Thanks Tom.
we use Oracle 9i.
we decided to use a temp. table for that lengthy select
statement instead of using ** OPEN refc for select **.
But I have a problem here with "insert into select "
when I use execute immediate it still will exceed statement length of 32K.
How can I convert this into a static SQL instead of going for a dynamic sql. I would like to convert the dsql1||dsql2
into a normal one so that I can use
insert into tmp_tab
select * from .. using bv1
Could you please suggest a valid approach on this?
Thanks for your time.
December 01, 2009 - 3:13 am UTC
... we decided to use a temp. table for that lengthy select
statement instead of using ** OPEN refc for select **.
...
why?
... when I use execute immediate it still will exceed statement length of 32K.
...
huh? where did that come from
... How can I convert this into a static SQL instead of going for a dynamic sql. I ...
to do static sql, you sort of need to know the sql AT COMPILE TIME - do you?
short of that, re-read my previous response to you, it actually contains everything you need to know in order to parse dynamically a sql statement exceeding 32k in any release of Oracle.
OK
rama, December 01, 2009 - 7:01 am UTC
Hi Tom,
Sorry for that blunder.
Actually the queries lengths are given as below
Query length of ME Benefits query:15494
Query Length of APAC Benefits query:16661
Query length of US Benefits query:27273
Query length of UK Benefits query:14455
I need to execute the below insert using dbms_sql in Oracle 9i (as you pointed to do piece wise execution).
'insert into bft_temp_tab select * from '|| sql_me||sql_apac||sql_us||sql_uk;
Any sample examples you have for doing this?
It would be useful if you can point any links with in this site.
Thanks.
December 01, 2009 - 10:28 am UTC
OK
rama, December 02, 2009 - 5:21 am UTC
Hi Tom,
I tried this one but I am getting ORA-6502 - PL/SQL NUMERIC OR VALUE ERROR.
-- sqlstmt := 'insert into bft_temp_tab select * from ('|| SQL_ME||SQL_APAC||SQL_US||SQL_UK||SQL_ORDER_BY ||')';
I am converting the above sqlstmt into a dynamic sql stmt using dbms_sql. Please refer below
SQL_ME,SQL_APAC,SQL_US have a UNION appended in the statement itself.
declare
.....
begin
dsql_piece(0) := 'insert into bft_temp_tab select * from (';
dsql_piece(1) := SQL_ME;
dsql_piece(2) := SQL_APAC;
dsql_piece(3) := SQL_US;
dsql_piece(4) := SQL_UK;
dsql_piece(5) := SQL_ORDER_BY;
dsql_piece(6) := ')';
....
DBMS_SQL.PARSE(dyn_sql,dsql_piece,dsql_piece.first,dsql_piece.last,false,dbms_sql.native);
DBMS_SQL.BIND_VARIABLE(dyn_sql,':p_maxrow',p_maxrow);
ret := DBMS_SQL.EXECUTE(dyn_sql);
DBMS_SQL.CLOSE_CURSOR(dyn_sql);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error:'||sqlerrm);
DBMS_SQL.CLOSE_CURSOR(dyn_sql);
END;
Can you please give some idea on this?
December 04, 2009 - 7:59 am UTC
you would sort of need to supply a full test case.
this is a pretty simple error message, look at the line of code that causes it, debug it - it is going to be very simple.
I cannot see how you defined anything.
I cannot see what line raises this error.
I cannot see anything, you can see it all - surely you must be able to debug a numeric or value error? Basically - in this case - it'll mean "you put too large of a string into too small of a string"
I hate your code by the way.
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error:'||sqlerrm);
DBMS_SQL.CLOSE_CURSOR(dyn_sql);
END;
that MUST be:
EXCEPTION
WHEN OTHERS THEN
if dbms_sql.is_open(dyn_sql)
then
DBMS_SQL.CLOSE_CURSOR(dyn_sql);
end if;
<b>RAISE;</b>
END;
anything else makes you look like a newbie coder.