Home » SQL & PL/SQL » SQL & PL/SQL » query help
query help [message #225888] Wed, 21 March 2007 17:13 Go to next message
hungman
Messages: 16
Registered: September 2006
Junior Member
hi guys,

just wondering if somebody can offer some insight on this simple procedure:

procedure p_test(in_name      in  varchar2,
                 out_result   out t_cursor)
is
begin
   
    open out_result for
    select     *
    from       in_name;

end;


it's not compiling properly, can somebody offer any hints of how I can dynamically select from a table?

thanks,
John.
Re: query help [message #225891 is a reply to message #225888] Wed, 21 March 2007 17:54 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
so many errors & no time to read the fine PL/SQL Reference manual. Sad
http://tahiti.oracle.com -- contains the complete Oracle Doc. set
http://asktom.oracle.com -- has many fine coding examples
Do you usually start coding without ever consulting any reference?
Poor coding technique which requires EXECUTE IMMEDIATE
Re: query help [message #225904 is a reply to message #225888] Wed, 21 March 2007 20:58 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
In order to open a ref cursor dynamically, you need to enclose it within single quotes and concatenate any table or column names. Also, if you are using a currently supported version of Oracle, like 9i or 10g, you can use sys_refcursor; Otherwise, you need to declare your type within a package. Please see the demostrations below.

SCOTT@10gXE> create or replace procedure p_test
  2  	 (in_name      in  varchar2,
  3  	  out_result   out sys_refcursor)
  4  is
  5  begin
  6  	 open out_result for
  7  	 'select * from ' || in_name;
  8  end p_test;
  9  /

Procedure created.

SCOTT@10gXE> show errors
No errors.
SCOTT@10gXE> variable g_ref refcursor
SCOTT@10gXE> exec p_test ('dept', :g_ref)

PL/SQL procedure successfully completed.

SCOTT@10gXE> print g_ref

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 PERSONNEL      DALLAS



SCOTT@10gXE> create or replace package p_pkg
  2  as
  3    type t_cursor is ref cursor;
  4    procedure p_test
  5  	 (in_name      in  varchar2,
  6  	  out_result   out t_cursor);
  7  end p_pkg;
  8  /

Package created.

SCOTT@10gXE> show errors
No errors.
SCOTT@10gXE> create or replace package body p_pkg
  2  as
  3    procedure p_test
  4  	 (in_name      in  varchar2,
  5  	  out_result   out t_cursor)
  6    is
  7    begin
  8  	 open out_result for
  9  	 'select * from ' || in_name;
 10    end p_test;
 11  end p_pkg;
 12  /

Package body created.

SCOTT@10gXE> show errors
No errors.
SCOTT@10gXE> variable g_ref refcursor
SCOTT@10gXE> exec p_pkg.p_test ('dept', :g_ref)

PL/SQL procedure successfully completed.

SCOTT@10gXE> print g_ref

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 PERSONNEL      DALLAS

SCOTT@10gXE> 


Previous Topic: how to convert clob to long
Next Topic: Urgent: creating a record in RAC
Goto Forum:
  


Current Time: Wed Dec 07 16:20:53 CST 2016

Total time taken to generate the page: 0.28258 seconds