Home » SQL & PL/SQL » SQL & PL/SQL » How to use cursor with execute immediate
|
|
|
Re: How to use cursor with execute immediate [message #208758 is a reply to message #208742] |
Tue, 12 December 2006 02:51   |
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   |
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 #232873 is a reply to message #209013] |
Mon, 23 April 2007 14:04   |
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 #232908 is a reply to message #208687] |
Tue, 24 April 2007 00:14   |
flyboy
Messages: 1903 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).
|
|
|
|
Goto Forum:
Current Time: Tue Apr 29 03:03:47 CDT 2025
|