Procedure.,

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"

 

Hi,

Problem with Execute immediate.

So you can use like this....

Create or replace procedure p1( emp_name in varchar2, table_name in varchar2, emp_id in number, o out varchar2)
as
begin
o:= execute immediate 'select'|| emp_name ||'from'|| table_name ||'where emp_id='|| emp_id ;
dbms_output.put_line(o);
end;

 

Tnx dude., The procedure is not compliling.,.,

Error(5,13): PLS-00103: Encountered the symbol "IMMEDIATE" when expecting one of the following: . ( * @ % & = - + ; < / > at in is mod remainder not rem <an exponent (**)> <> or != or ~= >= <= <> and or like like2 like4 likec between || multiset member submultiset The symbol "( was inserted before "IMMEDIATE" to continue.

Error(5,92): PLS-00103: Encountered the symbol ";" when expecting one of the following: . ( ) , * @ % & = - + < / > at in is mod remainder not rem => <an exponent (**)> <> or != or ~= >= <= <> and or like like2 like4 likec as between from using || member submultiset The symbol ")" was substituted for ";" to continue.

 

Hi vicky,

i am sorry,

Remove execute immediate in that.

Create or replace procedure p1( emp_name in varchar2, table_name in varchar2, emp_id in number, o out varchar2)
as
begin
o:= 'select'|| emp_name ||'from'|| table_name ||'where emp_id='|| emp_id ;
dbms_output.put_line(o);
end;

 

Dude I got the o/p as:

declare
x varchar2(200);
begin
p1('emp_name', 'emp', 1, x);
dbms_output.put_line(x);
end;


anonymous block completed
selectemp_namefromempwhere emp_id=1
selectemp_namefromempwhere emp_id=1

All I need is to create a procedure for the basic sql queries like select, Insert, update & delete.,,.

 

And also I've get table name and column name as inputs.,.,

 

Hi Vky,

Try with the below Code.

Code (SQL):

CREATE OR REPLACE PROCEDURE p1( emp_name IN varchar2, TABLE_NAME IN varchar2, emp_id IN NUMBER, o OUT varchar2)
AS

sqlstmt VARCHAR2(300);

BEGIN

  sqlstmt := ' select '|| emp_name ||' from '|| TABLE_NAME ||' where emp_id = '|| emp_id ;
 
 EXECUTE IMMEDIATE sqlstmt INTO o ;


dbms_output.put_line(o);


END;
/
 

Regds
Sambasiva Reddy.K

 

Tnk UU so much Sambasiva Reddy., It works.,.

 

Can you do an another help, I need to know what is Dynamic SQL means.,?!

 

Oh! that's great samba.

 

Because I've used Dbms_output twice.,.

 

How can I execute this procedure, If I use sys_refsursor to return more than 1 row,.?

CREATE OR REPLACE PROCEDURE p1( emp_name IN varchar2, TABLE_NAME IN varchar2, o OUT sys_refcursor)
AS

sqlstmt VARCHAR2(300);

BEGIN

sqlstmt := ' select '|| emp_name ||' from '|| TABLE_NAME;

EXECUTE IMMEDIATE sqlstmt INTO o ;

END;

 

Now I can easily understand what Dynamic SQl is!!! Thank you very much David Fitzjarrell !!!

 

Can any 1 tell me, how to execute a procedure, where I used sys_refcursor to display the output.,.?!

 

Hi,

Check This, It may useful for you.

 

Thanks Jagadekara Reddy. But I've to use sys_refcursor to display the output, while I'm getting table_name as input.,.

 

Sure., It's the procedure below.,. How can I execute the procedure to display the output.,.???!!!

CREATE OR REPLACE PROCEDURE p1(TABLE_NAME IN varchar2, o OUT sys_refcursor)
AS
sqlstmt VARCHAR2(300);
BEGIN
sqlstmt := ' select * from '|| TABLE_NAME;
EXECUTE IMMEDIATE sqlstmt INTO o ;
END;