Discussions
Categories
- 385.5K All Categories
- 5K Data
- 2.5K Big Data Appliance
- 2.5K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
PL/SQL 101 : Understanding Ref Cursors
Comments
-
Hi blueshadow,
Nice explanation
Just quick question in the below code you are closing the ref cursor before fetching is that correct?
SQL> ed
Wrote file afiedt.buf
1 declare
2 v_rc sys_refcursor;
3 v_empno number;
4 v_ename varchar2(10);
5 v_mgr number;
6 v_sal number;
7 begin
8 v_rc := get_dept_emps(10); -- This returns an open cursor
9 loop
10 fetch v_rc into v_empno, v_ename, v_mgr, v_sal;
11 exit when v_rc%NOTFOUND; -- Exit the loop when we've run out of data
12 dbms_output.put_line('Row: '||v_rc%ROWCOUNT||' # '||v_empno||','||v_ename||','||v_mgr||','||v_sal);
13 end loop;
14 close v_rc;
15 fetch v_rc into v_empno, v_ename, v_mgr, v_sal;
16* end;
SQL> /
Row: 1 # 7782,CLARK,7839,2450
Row: 2 # 7839,KING,,5000
Row: 3 # 7934,MILLER,7782,1300
declare
*
ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at line 15 -
user10857924 wrote:It is if you were actually reading the article:
Hi blueshadow,
Nice explanation
Just quick question in the below code you are closing the ref cursor before fetching is that correct?And what happens if we try and fetch more data after it's finished, just like we tried to do in SQL*Plus..
{snip code}
As expected we get an error. -
Thanks Blue..
you have explained the concept in a beautiful way -
i am trying to execute the below code.
but it's returning date only.
i want date and time.
declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
en date;
begin
open c_emp for select sysdate from dual;
loop
fetch c_emp into en;
exit when c_emp%notfound;
dbms_output.put_line(en);
end loop;
close c_emp;
end;
O/P:25-JAN-12
i want output as date with time. -
user7540156 wrote:That has absolutely nothing to do with ref cursors.
i am trying to execute the below code.
but it's returning date only.
i want date and time.
declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
en date;
begin
open c_emp for select sysdate from dual;
loop
fetch c_emp into en;
exit when c_emp%notfound;
dbms_output.put_line(en);
end loop;
close c_emp;
end;
O/P:25-JAN-12
i want output as date with time.
a) you should declare ref cursors using the inbuilt type SYS_REFCURSOR. There's absolutely no need to decalre your own type for it.
b) the display format of your date is exactly that, a display format issue.SQL> declare 2 c_emp SYS_REFCURSOR; 3 en date; 4 begin 5 open c_emp for select sysdate from dual; 6 loop 7 fetch c_emp into en; 8 exit when c_emp%notfound; 9 dbms_output.put_line(en); 10 end loop; 11 close c_emp; 12 end; 13 / 25-JAN-12 PL/SQL procedure successfully completed.
The date variable is being implicitly converted to a varchar2 datetype by the put_line statement which is expecting a varchar2 input parameter. This implicit conversion is being done based on your sessions NLS_DATE_FORMAT setting.
You can either change your sessions NLS_DATE_FORMAT...SQL> alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'; Session altered. SQL> declare 2 c_emp SYS_REFCURSOR; 3 en date; 4 begin 5 open c_emp for select sysdate from dual; 6 loop 7 fetch c_emp into en; 8 exit when c_emp%notfound; 9 dbms_output.put_line(en); 10 end loop; 11 close c_emp; 12 end; 13 / 25-JAN-2012 09:29:13 PL/SQL procedure successfully completed.
or you can explicitly convert the date to a varchar2 in your code using the to_char function, specifying the display format you want...SQL> ed Wrote file afiedt.buf 1 declare 2 c_emp SYS_REFCURSOR; 3 en date; 4 begin 5 open c_emp for select sysdate from dual; 6 loop 7 fetch c_emp into en; 8 exit when c_emp%notfound; 9 dbms_output.put_line(to_char(en,'DD-MM-YYYY HH24:MI')); 10 end loop; 11 close c_emp; 12* end; SQL> / 25-01-2012 09:29 PL/SQL procedure successfully completed.
As I said, absolutely nothing to do with ref cursors and all to do with a complete lack of understanding of basic datatypes and implicit conversions. -
Good one, Blue.
Thx. -
Nicely done. I would suggest adding a section with a "pipelined" version of your "populate_emps" table function, as often there's no need to fully materialize the table...
SQL> ed Wrote file afiedt.buf 1 create or replace function /*populate*/pipe_emps(deptno in number := null) 2 return t_emptype pipelined is 3 -- v_emptype t_emptype := t_emptype(); -- Declare a local table structure and initialize it 4 -- v_cnt number := 0; 5 v_rc sys_refcursor; 6 v_empno number; 7 v_ename varchar2(10); 8 v_mgr number; 9 v_sal number; 10 begin 11 v_rc := get_dept_emps(deptno); 12 loop 13 fetch v_rc into v_empno, v_ename, v_mgr, v_sal; 14 exit when v_rc%NOTFOUND; 15 --v_emptype.extend; 16 --v_cnt := v_cnt + 1; 17 /*v_emptype(v_cnt) :=*/ pipe row ( emptype(v_empno, v_ename, v_mgr, v_sal) ); 18 end loop; 19 close v_rc; 20 return /*v_emptype*/; 21* end; SQL> / Function created.
Gerard -
gaverill wrote:Thanks for the suggestion, however pipelining the data is beyond the 101 basics of ref cursors which is what the article is designed to demonstrate.
Nicely done. I would suggest adding a section with a "pipelined" version of your "populate_emps" table function, as often there's no need to fully materialize the table...
This discussion has been closed.