CODEWITHSUNDEEP
sqloracle-database
user2462438
user2462438

Reputation: 21

How to execute stored procedures containing dynamic SQL in oracle?

I've created the following procedure

Create or replace procedure abcd
(
    tab_name in USER_TABLES.table_name%type
)
is
begin
execute immediate
'select * from'||tab_name;
end abcd;

The procedure gets compiled.

I am trying to get the output using the following

select abcd('Table') from dual ;

I am new to dynamic SQL and this does not seem to work for me. I keep getting the error

[Error] Execution (44: 8): ORA-00904: "ABCD": invalid identifier

Can someone please help ?

Regards, Kshitij

Upvotes: 2

Views: 1727

Answers (3)

the_slk
the_slk

Reputation: 2182

It will not work.

When you invoke EXECUTE IMMEDIATE the sql statement is send to SQL engine. No results are passed back to the PL/SQL.

Writing "SELECT * FROM a_table" is not that hard and much safer.

Upvotes: 0

Prashant Srivastava
Prashant Srivastava

Reputation: 1

you cannot perform a select on a procedure, a function will work only if single record return. use

begin abcd(); end; or use execute keyword

ALSO use a space after from in query

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191560

You're missing a space before your table name:

create or replace procedure abcd (tab_name in USER_TABLES.table_name%type )
is
begin
    execute immediate 'select * from '||tab_name;
end abcd;

This won't work because you're trying to call it as a function, not a procedure:

select abcd('Table') from dual ;

Your second attempt should now work:

exec abcd('Table');

... but will now get a different error. In PL/SQL you have to select into something. In this case you probably want to open a cursor with the dynamic string and do something with the results. Not really sure what your end goal is though.

You should also read up about SQL injection while you learn about dynamic SQL.

Upvotes: 1

Related Questions