Home » SQL & PL/SQL » SQL & PL/SQL » Select using DBMS_SQL.EXECUTE (merged) (Oracle Linux 6.7, Oracle 12.1.0.2.0)
Select using DBMS_SQL.EXECUTE (merged) [message #670388] Mon, 02 July 2018 00:44 Go to next message
fabi88
Messages: 112
Registered: November 2011
Senior Member
Hi,

How can I retrieve result set from a procedure when use DBMS_SQL.EXECUTE for select from a table:
DBMS_SQL.PARSE(cursor_var, "SELECT * FROM persons WHERE person_id=:person_id", DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(cursor_var,  'person_id', in_person_id);
rows_complete := DBMS_SQL.EXECUTE(cursor_var);
DBMS_SQL.CLOSE_CURSOR(cursor_var);

I want the above code returns a cursor like following code, I mean I need a ref cursor as output of procedure:
sql_stsmt="SELECT * FROM persons WHERE person_id=:person_id"
OPEN ref_curs1 FOR sql_stsmt Using in_person_id

Thanks for your favor in advance.
Select using DBMS_SQL.EXECUTE [message #670389 is a reply to message #670388] Mon, 02 July 2018 00:44 Go to previous messageGo to next message
fabi88
Messages: 112
Registered: November 2011
Senior Member
Hi,

How can I retrieve result set from a procedure when use DBMS_SQL.EXECUTE for select from a table:
DBMS_SQL.PARSE(cursor_var, "SELECT * FROM persons WHERE person_id=:person_id", DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(cursor_var,  'person_id', in_person_id);
rows_complete := DBMS_SQL.EXECUTE(cursor_var);
DBMS_SQL.CLOSE_CURSOR(cursor_var);

I want the above code returns a cursor like following code, I mean I need a ref cursor as output of procedure:
sql_stsmt="SELECT * FROM persons WHERE person_id=:person_id"
OPEN ref_curs1 FOR sql_stsmt Using in_person_id

Thanks for your favor in advance.
Re: Select using DBMS_SQL.EXECUTE (merged) [message #670390 is a reply to message #670388] Mon, 02 July 2018 02:25 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

DBMS_SQL.TO_REFCURSOR

Re: Select using DBMS_SQL.EXECUTE (merged) [message #670391 is a reply to message #670390] Mon, 02 July 2018 03:09 Go to previous messageGo to next message
fabi88
Messages: 112
Registered: November 2011
Senior Member
Great, Thank you so much, The procedure is modified like this:

Procedure Get_Persosns
(
in_person_id IN NUMBER,
out_persons_info OUT ref_cursor
)
AS
BEGIN
DECLARE
cursor_var NUMBER := DBMS_SQL.OPEN_CURSOR;
rows_complete NUMBER := 0;

DBMS_SQL.PARSE(cursor_var, "SELECT * FROM persons WHERE person_id=:person_id", DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(cursor_var,  'person_id', in_person_id);
rows_complete := DBMS_SQL.EXECUTE(cursor_var);
out_persons_info  := DBMS_SQL.TO_REFCURSOR(cursor_var);
END;


But I had to remove this line :

DBMS_SQL.CLOSE_CURSOR(cursor_var);

Becuase the following error is raised:
Error Message=ORA-29471: DBMS_SQL access denied

Does it not make any problem?
Should I not close the out_persons_info cursor ?
When I use "Close out_persons_info", no records fetched.
Re: Select using DBMS_SQL.EXECUTE (merged) [message #670392 is a reply to message #670391] Mon, 02 July 2018 03:26 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
But I had to remove this line :

Of course otherwise the cursor is closed which means destroyed and so no more exists.
It is the same thing with the standard usage of "OPEN ... FOR ..." once you close it you can no more access its rows (or any property but, of course, %ISOPEN).

Quote:
Should I not close the out_persons_info cursor ?

You close it when you finished to use it that is, most likely, once you have fetched all rows.

And now the question raises: why don't you use the standard OPEN ... FOR ... instead of DBMS_SQL?
It'd be the correct way in this case.

Re: Select using DBMS_SQL.EXECUTE (merged) [message #670393 is a reply to message #670392] Mon, 02 July 2018 03:41 Go to previous messageGo to next message
fabi88
Messages: 112
Registered: November 2011
Senior Member
Thank you so much. Great help for me.

Quote:

And now the question raises: why don't you use the standard OPEN ... FOR ... instead of DBMS_SQL?
It'd be the correct way in this case.

Because I wrote a dynamic query using DBMS_SQL.Because filter parameters are not fixed in Where clause of query . in OPEN ... FOR we have to specify the list of bind arguments after USING which are unknown for us.
Re: Select using DBMS_SQL.EXECUTE (merged) [message #670394 is a reply to message #670393] Mon, 02 July 2018 04:07 Go to previous message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Good reasons. Smile

Previous Topic: Alter Table <<table name>> add partition by range does not work
Next Topic: Extract domain name from email address field
Goto Forum:
  


Current Time: Mon Sep 28 17:59:47 CDT 2020