Home » SQL & PL/SQL » SQL & PL/SQL » How to use cursor with execute immediate
How to use cursor with execute immediate [message #208687] Mon, 11 December 2006 21:51 Go to next message
shyamjha
Messages: 11
Registered: September 2006
Location: Mumbai
Junior Member

Hi all,

Please help me in solving the following problem:

When I am compiling theis procedure it gets compiled sucessfully but when executing it its giving error I feel Cursor does not work with Execute Immidiate. Pls help;
I want output as cursor:
Here is an example:

CREATE OR REPLACE PROCEDURE GetEmployeeDtl (p_deptno IN
emp.deptno%TYPE,
p_emp OUT SYS_REFCURSOR) AS
v_var varchar2(4000);
BEGIN
--v_var:='OPEN p_emp FOR';
v_var:= ' SELECT ename,';
v_var:= v_var || ' empno,';
v_var:= v_var || ' deptno';
v_var:= v_var || ' FROM emp';
v_var:= v_var || ' WHERE deptno = ' ||p_deptno;
v_var:= v_var || ' ORDER BY ename';

execute immediate ' OPEN p_emp FOR' || ' ' || v_var ;
END GetEmployeeDtl;

I am excuting it as:

SQL> var x refcursor
SQL> exec GetEmployeeDtl ('20',Mad);
Re: How to use cursor with execute immediate [message #208727 is a reply to message #208687] Tue, 12 December 2006 00:19 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

not tested your code.so just check if this works.

var x refcursor
exec GetEmployeeDtl(20,:x);

and remove the quotes around 20 it's not needed.



regards,
Re: How to use cursor with execute immediate [message #208742 is a reply to message #208727] Tue, 12 December 2006 01:12 Go to previous messageGo to next message
shyamjha
Messages: 11
Registered: September 2006
Location: Mumbai
Junior Member

Thanks !!
it does not matter if you put 20 in '' or not.
any way if hv time then go through the code and let me know if you can do somthing

Thanks,
skj
Re: How to use cursor with execute immediate [message #208758 is a reply to message #208742] Tue, 12 December 2006 02:51 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Thanks for not telling us what the actual error message you got was.

Having run your code, the error I get is:
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at "GETEMPLOYEEDTL", line 14
ORA-06512: at line 4
Line 14 of your procedure is (suprisingly) the Execute Immediate, which would suggest to anyone who bothered to do their own investigation work that the piece of sql that you'd built up was wrong.

The problems that you're getting happens because the variable p_emp is not defined inside the Execute Immediate statement.

If your homework tutor insists that you use Execute Immediate, then you'll need to construct a DECLARE BEGIN END block that defines a Sys_refcursor and opens it, and then execute this block, returning this into the p_emp value.

Once you've written that, you'll hopefully experience a Pl/Sql moment of clarity, and realise that the whole Execute Immediate is totally uneccessary, and that you could write this:
CREATE OR REPLACE PROCEDURE GetEmployeeDtl (p_deptno IN
emp.deptno%TYPE,
p_emp OUT SYS_REFCURSOR) AS
v_var varchar2(4000);
BEGIN
--v_var:='OPEN p_emp FOR';
v_var:= ' SELECT ename,';
v_var:= v_var || ' empno,';
v_var:= v_var || ' deptno';
v_var:= v_var || ' FROM emp';
v_var:= v_var || ' WHERE deptno = ' ||p_deptno;
v_var:= v_var || ' ORDER BY ename';

OPEN p_emp FOR v_var;

END GetEmployeeDtl;

About 6 months later, when you come back to look at this code having learned more about Oracle, you'll realise that this is grossly inefficient, and that you have no need of ANY dynamic sql for this problem at all, and that you could just write:
CREATE OR REPLACE PROCEDURE GetEmployeeDtl (p_deptno IN
emp.deptno%TYPE,
p_emp OUT SYS_REFCURSOR) AS
BEGIN
OPEN p_emp FOR 
  SELECT ename
        ,empno
        ,deptno 
  FROM   emp 
  WHERE  deptno = 40 
  ORDER BY ename;

END GetEmployeeDtl;


Edited to add missing END statement

[Updated on: Tue, 12 December 2006 05:27]

Report message to a moderator

Re: How to use cursor with execute immediate [message #208772 is a reply to message #208687] Tue, 12 December 2006 03:45 Go to previous messageGo to next message
rameshuddaraju
Messages: 69
Registered: June 2005
Location: India
Member

I tried the given code in 9i with small modification see my results.


  SQL> CREATE OR REPLACE PROCEDURE GetEmployeeDtl (p_deptno IN
  2  emp.deptno%TYPE,
  3  p_emp OUT SYS_REFCURSOR) AS
  4  v_var varchar2(4000);
  5  BEGIN
  6  v_var:='OPEN p_emp FOR';
  7  v_var:= ' SELECT ename,';
  8  v_var:= v_var || ' empno,';
  9  v_var:= v_var || ' deptno';
 10  v_var:= v_var || ' FROM emp';
 11  v_var:= v_var || ' WHERE deptno = ' ||p_deptno;
 12  v_var:= v_var || ' ORDER BY ename';
 13  execute immediate  v_var ;
 14* END GetEmployeeDtl;
 15  /

Procedure created.

SQL> var x refcursor
SQL> exec GetEmployeeDtl(20,:x)

PL/SQL procedure successfully completed.


[mod-edit]disabled smiley and added code tags

[Updated on: Tue, 12 December 2006 04:01] by Moderator

Report message to a moderator

Re: How to use cursor with execute immediate [message #208820 is a reply to message #208772] Tue, 12 December 2006 05:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Have you tried fetching from the cursor?
You haven't passed back an open cursor.
Re: How to use cursor with execute immediate [message #208928 is a reply to message #208772] Tue, 12 December 2006 11:16 Go to previous messageGo to next message
shyamjha
Messages: 11
Registered: September 2006
Location: Mumbai
Junior Member

Thanks !!

Yes procedure get excuted sucessfully but once you print the
cursor it will give you the error:

SQL> var x refcursor
SQL> exec GetEmployeeDtl(20,Mad)

PL/SQL procedure successfully completed.

SQL> print x;
ERROR:
ORA-24338: statement handle not executed

Can you please help me in solving this issues.
Actually I need to build a dyanamic query which will based on some selection criteria and then want output of that query. Is any other solution for this.

Pls. help
Thanks
SKJ
Re: How to use cursor with execute immediate [message #208932 is a reply to message #208758] Tue, 12 December 2006 11:28 Go to previous messageGo to next message
shyamjha
Messages: 11
Registered: September 2006
Location: Mumbai
Junior Member

Thanks !!

Actually my requirement is to build the query in a stored procedure based on user selection. user will select dept=d1, Div=div1 etc, which I need to pass as input paramater to a stored procedure and get back the output to dispaly on the screen.

Can you pls. help in fitting this requirement.

When I changed the query like this :
1 CREATE OR REPLACE PROCEDURE GetEmployeeDtl (p_deptno IN
2 emp.deptno%TYPE,
3 p_emp OUT SYS_REFCURSOR) AS
4 v_var varchar2(4000);
5 BEGIN
6 v_var:='OPEN p_emp FOR';
7 v_var:= ' SELECT ename,';
8 v_var:= v_var || ' empno,';
9 v_var:= v_var || ' deptno';
0 v_var:= v_var || ' FROM emp';
1 v_var:= v_var || ' WHERE deptno = ' ||p_deptno;
2 v_var:= v_var || ' ORDER BY ename';
3 execute immediate v_var ;
4* END GetEmployeeDtl;
5 /

Procedure created.

It geting compiled sucessfully.

When I am executing it gets executed too, but when printing cursor value its giving error as:

SQL> var x refcursor;
SQL> exec GetEmployeeDtl (20,Mad);

PL/SQL procedure successfully completed.

SQL> print x;
ERROR:
ORA-24338: statement handle not executed


SP2-0625: Error printing variable "x"
SQL>

Please help me in this context.
Thanks,
SKJ

Re: How to use cursor with execute immediate [message #209013 is a reply to message #208932] Tue, 12 December 2006 23:14 Go to previous messageGo to next message
kkinapps
Messages: 29
Registered: November 2006
Location: Hyderabad
Junior Member

Dear Shyam,

What are you waiting for. Does the below code as suggested by Mr JRowbottom doesn't fit in your requirement. Did you try this code. What JRowbottom said is exactly right, why do you want to use EXECUTE IMMEDIATE unnecessarily. First try this code

CREATE OR REPLACE PROCEDURE GetEmployeeDtl (p_deptno IN
emp.deptno%TYPE,
p_emp OUT SYS_REFCURSOR) AS
v_var varchar2(4000);
BEGIN
--v_var:='OPEN p_emp FOR';
v_var:= ' SELECT ename,';
v_var:= v_var || ' empno,';
v_var:= v_var || ' deptno';
v_var:= v_var || ' FROM emp';
v_var:= v_var || ' WHERE deptno = ' ||p_deptno;
v_var:= v_var || ' ORDER BY ename';

OPEN p_emp FOR v_var;

END GetEmployeeDtl;


I got perfect results with this code
Quote:

SQL> var y refcursor
SQL> exec getemployeedtl(20,:y);

PL/SQL procedure successfully completed.

SQL> print y

ENAME EMPNO DEPTNO
---------- ---------- ----------
ADAMS 7876 20
FORD 7902 20
JONES 7566 20
SCOTT 7788 20
SMITH 7369 20

SQL>



Thanks and regards
KK
Re: How to use cursor with execute immediate [message #212546 is a reply to message #209013] Fri, 05 January 2007 15:37 Go to previous messageGo to next message
shyamjha
Messages: 11
Registered: September 2006
Location: Mumbai
Junior Member

Thanks KK.
Wish you and your family a very happy and properous new year!!

SKJ
Re: How to use cursor with execute immediate [message #232873 is a reply to message #209013] Mon, 23 April 2007 14:04 Go to previous messageGo to next message
srinivasocp
Messages: 91
Registered: December 2005
Location: INDIA
Member
Hi All,

what is the problem in using Execute Immediate to open the cursor dynamically for the given select statement.Why is it throwing an error when we run this program even after compiling sucessfully.

1 CREATE OR REPLACE PROCEDURE GetEmployeeDtl (p_deptno IN
2 emp.deptno%TYPE,
3 p_emp OUT SYS_REFCURSOR) AS
4 v_var varchar2(4000);
5 BEGIN
6 v_var:='OPEN p_emp FOR';
7 v_var:= ' SELECT ename,';
8 v_var:= v_var || ' empno,';
9 v_var:= v_var || ' deptno';
0 v_var:= v_var || ' FROM emp';
1 v_var:= v_var || ' WHERE deptno = ' ||p_deptno;
2 v_var:= v_var || ' ORDER BY ename';
3 execute immediate v_var ;
4* END GetEmployeeDtl;
5 /

It throws the error
SQL>  variable x refcursor;
SQL> EXEC Getemployeedtl(20,:x)
SQL>  print x;
ERROR:
ORA-24338: statement handle not executed
SP2-0625: Error printing variable "x"
.
How to solve this problem?Can someone please help?

Regards
Srini..

[Updated on: Mon, 23 April 2007 14:09]

Report message to a moderator

Re: How to use cursor with execute immediate [message #232875 is a reply to message #232873] Mon, 23 April 2007 14:30 Go to previous messageGo to next message
srinivasocp
Messages: 91
Registered: December 2005
Location: INDIA
Member
Hi All,

I think Execute Immeidate will not work with Ref Cursor's. That is evident from the example given above.
execute immediate v_var ;

Am i rite?

Regards
Srini..

[Updated on: Mon, 23 April 2007 14:31]

Report message to a moderator

Re: How to use cursor with execute immediate [message #232908 is a reply to message #208687] Tue, 24 April 2007 00:14 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
If you are in doubt, the documentation may help you. As stated here:
Quote:
The EXECUTE IMMEDIATE statement prepares (parses) and immediately executes a dynamic SQL statement or an anonymous PL/SQL block. The main argument to EXECUTE IMMEDIATE is the string containing the SQL statement to execute. You can build up the string using concatenation, or use a predefined string.

As stated here, OPEN-FOR is a PL/SQL command, so you cannot run it using EXECUTE IMMEDIATE. You could wrap it into an anonymous PL/SQL block, however it would be visible only in this block, so you could not use it outside.

In other words, OPEN-FOR is a PL/SQL command, so it make no sense running it dynamically in PL/SQL. When the select statement is dynamic, you can use it this way.

Before further thoughts realize the difference between SQL and PL/SQL (it is really greater than three letters).
Re: How to use cursor with execute immediate [message #233100 is a reply to message #232908] Tue, 24 April 2007 12:42 Go to previous message
srinivasocp
Messages: 91
Registered: December 2005
Location: INDIA
Member
Thanks, Flyboy

Regards
Srini..
Previous Topic: remote_os_authent???!!!!!
Next Topic: DBMS_DATAPUMP.ADD_FILE : ORA-39001
Goto Forum:
  


Current Time: Thu Dec 08 20:31:44 CST 2016

Total time taken to generate the page: 0.13797 seconds