Home » SQL & PL/SQL » SQL & PL/SQL » How can I return a ref cursor from dbms_sql. (10g)
How can I return a ref cursor from dbms_sql. [message #384670] Wed, 04 February 2009 14:24 Go to next message
bpeasey
Messages: 46
Registered: March 2005
Member
Hi,

Can anyone show me how to return a ref cursor from this dbms_sql based procedure? I see 11g has a dbms_sql.to_refcursor(cursor_handle). How can this be done is 10g?

Thx.

CREATE OR REPLACE PROCEDURE Sample_Get_t
  (
    p_sample_id sample.sample_id%TYPE,
    p_contract_id sample.contr_id%TYPE
  )
  IS
    cursor_handle INT;
    sql_stmnt varchar2(500);
    rows_processed NUMBER;
    
  BEGIN

  sql_stmnt := 
    'SELECT
      sample_id,
      contr_id,
      rcpt_id
    FROM
      sample s
    WHERE
      s.contr_id = :1 
      and s.sample_id = :2
    ORDER BY
      sample_id';

  cursor_handle := dbms_sql.open_cursor;
  
  dbms_sql.parse(cursor_handle, sql_stmnt, dbms_sql.native);
  
  dbms_sql.bind_variable(cursor_handle, ':1', p_contract_id); 
  dbms_sql.bind_variable(cursor_handle, ':2', p_sample_id);
  
  rows_processed := dbms_sql.execute(cursor_handle);  
  
  dbms_sql.close_cursor(cursor_handle); 
  
  END Sample_Get_t;
Re: How can I return a ref cursor from dbms_sql. [message #384674 is a reply to message #384670] Wed, 04 February 2009 15:07 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This can't be done with dbms_sql in 10g.
But you can do something like:
SQL> create or replace function f (p_no integer) return sys_refcursor
  2  is
  3    curs sys_refcursor;
  4  begin
  5    open curs for 'select * from emp where empno = :1' using p_no;
  6    return curs;
  7  end;
  8  /

Function created.

SQL> var c refcursor;
SQL> exec :c := f(7369);

PL/SQL procedure successfully completed.

SQL> print c
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17/12/1980 00:00:00        800                    20

1 row selected.

Regards
Michel
Previous Topic: Extended ASCII character problems
Next Topic: update statement
Goto Forum:
  


Current Time: Mon Dec 05 19:01:36 CST 2016

Total time taken to generate the page: 0.12507 seconds