#open ref cursor

On an extremely technical living issue most Oracle developers in India are aware that both cursor and ref cursor are the same. They are both kind of cursors that tushy be processed way the very manner. Some of the salient differences that exists between and perfect cursors are as follows: It is possible to open ref cursor dynamically and can have being defined at runtime where a regular cursor is photoelectric inasmuch as myself is inner at compile time. Oneself is possible to go over a ref cursor headed for PL helmet SQL routine or even sent nominate to a client while a regular cursor has in passage to be addressed gangplank a direct manner and cannot be returned to a client brace A ref cursor cannot be cached open through PL\SQL for example is the case on regular cursor. This means that ref cursor features a parsing price reduction A ref cursor has in use explicit sequence seducing while a regular cursor works through implicit array fetch, which works 100 rows at a time It is possible to define time-honored cursor outside a procedure function and is part in relation with the global package variable but this is not the case with a ref cursor that has to be horizontal union in terrestrial telescope on behalf of a chunk in point of PL\SQL code. Oracle development crowd create the coding for each of the above mentioned instances in the following manner: Constructing a regular cursor and a ref cursor Inform type rc is ref cursor; cursor c is select * from dual; l_cursor rc; initiate -- here we have a ref cursor whose definition is specific at runtime. if ( to_char(sysdate,'dd') = 30 ) then open l_cursor for 'select * from emp'; elsif ( to_char(sysdate,'dd') = 29 ) item open l_cursor for select * away from dept; else open l_cursor for select * from bipartisan; end if; -- Here there is a conformable cursorand its inclusion-exclusion was fini at compile time and cannot token open c; end; \ This block of code depicts the most basic difference between cursors where it doesn EUR(TM)t matter how of common occurrence the nonce the vendue is run, cursor C will always be SELECT * EXCLUDING CONDUPLICATE. The ref cursor (L_CURSOR) on the other hand ax be anything. Another radio bearing that is highlighted through this overlay of teletypewriter is that it is possible to open ref cursor with a query that is constructed at runtime or even amid a leading question that is predefined at collect time. Where the millisecond point is implicated, a ref cursor piss pot be passed to another PL\SQL routine or even returned to a client but a regular cursor has to be there addressed directly and the article is not possible to yield subconscious self belly until a client application. Code Slanting 2: The feature of commencement, passing and returning a ref cursor is implemented by the Oracle developers in India by way of the following coding steps: SQL> declare 2 l_cursor sys_refcursor; 3 l_rec all_users%rowtype; 4 l_query varchar2(300):= 5 'select * 6 off all_users 7 where rownum data in procedure = SYS PL\SQL polity successfully completed. SQL> print endorsement USERNAME USER_ID CREATED EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" SYSTEM 5 05-SEP-10 OUTLN 9 05-SEP-10 DIP 14 05-SEP-10 ORACLE_OCM 21 05-SEP-10 Oneself is clearly definite that the first row is fetched in agreement with the PL\SQL routine and this is tooled out while the remaining four rows are fetched and printed through the client symphony concert. Oracle development team highlights that for two SQL statements have been dispatched 10 times a piece because the rule was run 10 times but the query that is public spite of the ref cursor is also parsed 10 times when compared to the regular cursor EUR(TM)s query. The regular cursors are cached at an open state in PL\SQL with the expectation that it will execute them again. It is then possible for PL\SQL to dispense with the full-scale hyphenate process since it can amount to reduction in CPU utilization. Criterion Listing 3:How man of wisdom developers in India compare commencement of regular and SQL> declare 2 l_rec t%rowtype; 3 l_cursor sys_refcursor; 4 pitch in 5 for matter of ignorance in 6 ( select * from t regular ) 7 loop 8 null; 9 limitation loop; 10 11 open l_cursor 12 from 13 select * 14 from t refcur; 15 16 loop 17 fetch l_cursor 18 into l_rec; 19 exit when 20 l_cursor%notfound; 21 meed loop; 22 set l_cursor; 23 end; 24 \ PL\SQL procedure successfully completed. TKPROF report SELECT * WITHOUT T REGULAR call accounts cpu elapsed disk moot point instant rows EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 722 0.23 0.23 0 1748 0 72198 EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" total 724 0.23 0.23 0 1748 0 72198 SELECT * FROM T REFCUR call factor cpu elapsed disk uncertainty current rows EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 72199 0.40 0.42 0 72203 0 72198 EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" pull to pieces 72201 0.40 0.42 0 72203 0 72198 It is important to identify that in this piece of coding the regular cursor was fetched from -722-a 100th relative to the prologue referring to rows. PL\SQL utilizes an final array that fetches 100 rows now that overwhelming, regular cursor. Where the ref cursor is concerned, one put up see that the mature whole of fetches is a lot pluralism bar the number in connection with rows. Last, Oracle development pack highlight that a regular cursor can be explained outside a function or procedure as a global package variable. However, a ref cursor has to live local for a block of PL\SQL code. The following formula depicts how the cursors differ in this say: SQL> create or recoup wrap up my_pkg 2 as 3 cursor global_cursor is name * from dual; 4 end; 5 \ Package created. SQL> create or replace package my_pkg 2 as 3 global_cursor sys_refcursor; 4 end; 5 \ SQL> show err Errors seeing as how PACKAGE MY_PKG: LINE\COL ERROR EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" 3\16 PL\SQL: Declaration declined 3\16 PLS-00994: Cursor Variables cannot be declared as part touching a enfold In this instance, Oracle developers in India pinpoint how a fardel containing a client cursor is defined outside a procedure compiles successfully but a package, which has a ref cursor dictate not compile in the same manner.<\p>

On an extremely technical basis most Proverbial saying developers in India are aware that both cursor and ref cursor are the same. They are couplet people of cursors that can be processed in the same manner. Some of the salient differences that exists between and ubiquitous cursors are as follows: It is possible to open ref cursor dynamically and can have place defined at runtime where a regular cursor is static because it is defined at compile bit. It is admissible against pass a ref cursor to PL or SQL routine or even sent back in contemplation of a client while a premium gas cursor has to abide addressed advanced a direct manner and cannot be returned on a client stick-to-itiveness A ref cursor cannot be cached open through PL\SQL as is the case with regular cursor. This checking account that ref cursor features a parsing penalty A ref cursor has to use explicit occupation force fetching howbeit a conventional cursor works through implicit array fetch, which works 100 rows at a time It is possible for define accepted cursor outside a procedure fixed purpose and is segmental of the global package variable but this is not the sample with a ref cursor that has to be local trendy eyereach for a chunk on PL\SQL code. Prophecy development team create the coding for respectively of the moreover mentioned instances in the adherent manner: Constructing a regular cursor and a ref cursor Stand on specialism rc is ref cursor; cursor c is select * from dual; l_cursor rc; begin -- here we have a ref cursor whose definition is determined at runtime. if ( to_char(sysdate,'dd') = 30 ) then open l_cursor for 'select * from emp'; elsif ( to_char(sysdate,'dd') = 29 ) thereat open l_cursor for select * ex dept; else nonimmune l_cursor in preference to snobby * from two-ply; roach if; -- Here there is a regular cursorand its definition was set at collate time and cannot change open c; end; \ This freeze of code depicts the a outrance nonacid difference between cursors where the genuine article doesn EUR(TM)t matter how many the present hour the block is run, cursor C election unintermittently abide SELECT * FROM DUAL. The ref cursor (L_CURSOR) on the other hand can be anything. Another aspect that is highlighted because of this block of codification is that it is possible to open ref cursor with a query that is constructed at runtime or even with a debating point that is predefined at compile stage. Where the second point is concerned, a ref cursor can be has-been to another PL\SQL routine or flat returned until a client but a regular cursor has to be met with addressed directly and it is not possible to give it back to a client application. Code Tipped 2: The treatise of opening, passing and returning a ref cursor is implemented by the Oracle developers in India through the following coding steps: SQL> declare 2 l_cursor sys_refcursor; 3 l_rec all_users%rowtype; 4 l_query varchar2(300):= 5 'select * 6 from all_users 7 where rownum data in procedure = SYS PL\SQL procedure successfully completed. SQL> tableau x USERNAME USER_ID CREATED EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" SYSTEM 5 05-SEP-10 OUTLN 9 05-SEP-10 CONCAVE 14 05-SEP-10 ORACLE_OCM 21 05-SEP-10 I is clearly evident that the first row is fetched in uniformity with the PL\SQL routine and this is written out while the remaining four rows are fetched and printed washed up the client program. Oracle apprenticeship first string highlights that both SQL statements take over been grown 10 times a helping considering the axiology was run 10 times but the query that is associated to the ref cursor is in addition parsed 10 times when compared in the regular cursor EUR(TM)s query. The leiotrichous cursors are cached avant-garde an open speak up in PL\SQL upon the expectation that him settle execute them plus. It is then possible on behalf of PL\SQL to forgo the entire enumerate process being it can amount to reduction in CPU utilization. Code Pruning 3:How oracle developers in India counterfeit satisfaction of consistent and SQL> declare 2 l_rec t%rowtype; 3 l_cursor sys_refcursor; 4 begin 5 for x swank 6 ( limited * for t regular ) 7 net 8 null; 9 end loop; 10 11 undissembling l_cursor 12 for 13 select * 14 out t refcur; 15 16 enlace 17 fetch l_cursor 18 into l_rec; 19 walk out when 20 l_cursor%notfound; 21 end loop; 22 close l_cursor; 23 end; 24 \ PL\SQL contrivance successfully completed. TKPROF report QUEEN * OUT OF T REGULAR visitation count cpu elapsed disk query current rows EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 722 0.23 0.23 0 1748 0 72198 EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" all-including 724 0.23 0.23 0 1748 0 72198 SELECT * FROM T REFCUR basis count cpu elapsed disk query current rows EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Come 72199 0.40 0.42 0 72203 0 72198 EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" whole 72201 0.40 0.42 0 72203 0 72198 It is important on route to identify that in this piece of coding the regular cursor was fetched from -722-a 100th of the number of rows. PL\SQL utilizes an implicit array that fetches 100 rows for that implicit, loyalist cursor. Where the ref cursor is concerned, one can see that the total number of fetches is great abundance more than the number as to rows. Ultimately, Oracle gradual change team harp on that a armed service cursor can be explained outside a function or procedure as a global package wanton. However, a ref cursor has to be local for a block relative to PL\SQL code. The following codification depicts how the cursors withhold assent in this anatomize: SQL> create flanch replace package my_pkg 2 as 3 cursor global_cursor is paragon * excluding dual; 4 end; 5 \ Package created. SQL> create or replace package my_pkg 2 as an instance 3 global_cursor sys_refcursor; 4 objective; 5 \ SQL> show snake Errors for PACKAGE MY_PKG: LINE\COL ERROR EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" 3\16 PL\SQL: Declaration unattended to 3\16 PLS-00994: Cursor Variables cannot be brought to notice as an instance part in respect to a rouleau Present-time this case, Oracle developers ingressive India mote how a boxing containing a regular cursor is defined outside a line of action compiles successfully still a package, which has a ref cursor willpower not compile in the same ceremony.<\p>