Home » SQL & PL/SQL » SQL & PL/SQL » Problem with Procedure
Problem with Procedure [message #267687] Fri, 14 September 2007 04:50 Go to next message
donind
Messages: 95
Registered: February 2007
Member
Hi,

Below is my procedure. Its giving an compilation error


Error: PLS-00597: expression 'EMP_REC' in the INTO list is of wrong type
Line: 20
Text: EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;

Error: PL/SQL: Statement ignored
Line: 20
Text: EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;


create or replace procedure dyn_sql
(dept_id in d_dept.deptno%type, dept_name d_dept.dname%type, location d_dept.loc%type)
is
sql_stmt varchar2(100);

type emp_rec1 is record (w emp%rowtype);
emp_rec emp_rec1;

emp_id number:=7369;
begin

EXECUTE IMMEDIATE 'CREATE TABLE x (a NUMBER)';


sql_stmt := 'INSERT INTO d_dept VALUES (:1, :2, :3)';
EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;


sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;

end dyn_sql;


May I know where the mistake is.

Thanks in advance

[Updated on: Fri, 14 September 2007 04:51]

Report message to a moderator

Re: Problem with Procedure [message #267689 is a reply to message #267687] Fri, 14 September 2007 04:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
1/ Why do you use "execute immediate"?
2/ Why don't you use "emp_rec emp%rowtype"? (this is the origin of your error)

With the code you posted (with this dynamic and unused "create table"), I suspect there will be many other errors to come.

Regards
Michel

Re: Problem with Procedure [message #267692 is a reply to message #267689] Fri, 14 September 2007 05:01 Go to previous messageGo to next message
donind
Messages: 95
Registered: February 2007
Member
Hi,

I just want to know what is the problem if I use

type emp_rec1 is record (w emp%rowtype);
emp_rec emp_rec1;

In the above declaration I am declaring record type and using that.

Thanks in advance
Re: Problem with Procedure [message #267694 is a reply to message #267692] Fri, 14 September 2007 05:02 Go to previous message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Query returns a emp%rowtype type, you give a emp_rec1 type.

Regards
Michel
Previous Topic: Problem with trigger
Next Topic: Need help in formatting the displayed result
Goto Forum:
  


Current Time: Wed Nov 13 06:40:36 CST 2024