| Sys_refcursor [message #608508] |
Thu, 20 February 2014 06:41  |
 |
Rarawshenkiv
Messages: 5
Registered: February 2014
Location: Chennai
|
Junior Member |
|
|
How to execute this procedure to get the output???
CREATE OR REPLACE PROCEDURE p1( TABLE_NAME IN varchar2, o OUT sys_refcursor)
AS
sqlstmt VARCHAR2(300);
BEGIN
sqlstmt := ' select * from '|| TABLE_NAME;
EXECUTE IMMEDIATE sqlstmt INTO o ;
END;
|
|
|
|
|
|
| Re: Sys_refcursor [message #616919 is a reply to message #608510] |
Mon, 23 June 2014 00:50  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
msol25 wrote on Thu, 20 February 2014 18:16please read the location:
https://community.oracle.com/thread/331109
That link dooesn't seem to answer OP's question. It talks about the difference between ref cursor and explicit cursors.
@OP, Why do you want to unnecessarily complicate a simple select statement. What you are trying to achieve is 100% possible, however, not every possible thing is good to do.
1. You should not use dynamic sql for this purpose.
2. Table name should be static. Having said that, it means, you must always know the table name, and thus avoid using it dynamically.
Rarawshenkiv wrote on Thu, 20 February 2014 18:11How to execute this procedure to get the output???
May be you should read documentation first and then go through Using Ref Cursors To Return Recordsets
Regards,
Lalit
|
|
|
|
| Re: Sys_refcursor [message #616923 is a reply to message #608508] |
Mon, 23 June 2014 01:10 |
 |
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
It would be better to change your procedure code slightly and use "OPEN o FOR sqlstmt;", as shown below, instead of "EXECUTE IMMEDIATE sqlstmt INTO o;".
SCOTT@orcl12c> CREATE OR REPLACE PROCEDURE p1
2 (table_name IN VARCHAR2,
3 o OUT SYS_REFCURSOR)
4 AS
5 sqlstmt VARCHAR2(300);
6 BEGIN
7 sqlstmt := 'SELECT * FROM ' || table_name;
8 OPEN o FOR sqlstmt;
9 END p1;
10 /
Procedure created.
SCOTT@orcl12c> SHOW ERRORS
No errors.
SCOTT@orcl12c> VARIABLE g_ref REFCURSOR
SCOTT@orcl12c> EXEC p1 ('DEPT', :g_ref)
PL/SQL procedure successfully completed.
SCOTT@orcl12c> PRINT g_ref
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
4 rows selected.
[Updated on: Mon, 23 June 2014 01:11] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
