| Procedure., [message #608467] |
Thu, 20 February 2014 03:32  |
 |
Rarawshenkiv
Messages: 5
Registered: February 2014
Location: Chennai
|
Junior Member |
|
|
How to execute this procedure, where I'm passing table name as input?
Create or replace procedure p1( emp_name in varchar2, table_name in varchar2, emp_id in number, o out varchar2)
as
begin
execute immediate 'select'|| emp_name ||'into'|| o ||'from'|| table_name ||'where emp_id='|| emp_id ;
dbms_output.put_line(o);
end;
I can compile this procedure, But while executing, I'm getting error as below:
declare
x varchar2(50);
begin
p1('emp', 'Vicky',5, x);
dbms_output.put_line(x);
end;
Error report:
ORA-00900: invalid SQL statement
ORA-06512: at "PORTALADMIN.P1", line 4
ORA-06512: at line 4
00900. 00000 - "invalid SQL statement"
|
|
|
|
|
|
|
|
|
|
| Re: Procedure., [message #608484 is a reply to message #608468] |
Thu, 20 February 2014 04:33  |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You need to learn to debug these things yourself:
cookiemonster wrote on Thu, 20 February 2014 09:42
When using dynamic sql you should always display the dynamic string you've built up to check it's correct.
you're already displaying thing with dbms_output, just assign the dynamic query to a variable and display that as well.
|
|
|
|
| Re: Procedure., [message #608497 is a reply to message #608478] |
Thu, 20 February 2014 05:56 |
 |
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
A space is missing after "select".
And
Quote:what happens if there is no "EMP_ID" column in the table in table_name variable?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
