Home » SQL & PL/SQL » SQL & PL/SQL » execute immediate vs ref cursor (Oracle 10g)
execute immediate vs ref cursor [message #573835] Thu, 03 January 2013 08:01 Go to next message
sss111ind
Messages: 480
Registered: April 2012
Location: India
Senior Member

Hi All,

What is differece, what to use where, and which is better in perspective of dynamic construct.

1.Limit could not applicable to execute immediate.



DECLARE
Type ref_cursor
IS
  Ref
  CURSOR;
    c1 ref_cursor;
  Type Emp_Tab
IS
  TABLE OF emp%Rowtype;
  T_Emp_Tab Emp_Tab;
  v_str VARCHAR2(100):='select * from emp';
BEGIN
  OPEN C1 FOR V_Str;
  LOOP
    FETCH C1 Bulk Collect INTO T_Emp_Tab;
  EXIT
WHEN C1%Notfound;
END LOOP;
CLOSE C1;

EXECUTE immediate v_str bulk collect INTO T_Emp_Tab;
END;


Please suggest...

Regards,
Nathan
Re: execute immediate vs ref cursor [message #573844 is a reply to message #573835] Thu, 03 January 2013 08:54 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no difference.

Regards
Michel
Re: execute immediate vs ref cursor [message #573846 is a reply to message #573844] Thu, 03 January 2013 08:59 Go to previous message
BlackSwan
Messages: 22792
Registered: January 2009
Senior Member
PL/SQL will NEVER be faster than plain SQL to move data
Previous Topic: DBMS_PARALLEL_EXECUTE help needed.
Next Topic: ORA-06508: PL/SQL: could not find program unit being called:
Goto Forum:
  


Current Time: Sat Sep 20 13:12:20 CDT 2014

Total time taken to generate the page: 0.08048 seconds