Hello, help me please.
I have stored procedure in Oracle server. This is simple fragment:


procedure ABCD()

IS
ora_table_name VARCHAR2(20);

BEGIN
ora_table_name := 'CLIENTS';

SELECT * FROM ora_table_name WHERE client = '1';



I want to ask about variable "ora_table_name" - What do I have to make this
construction to act?
What I have to make to in variable "ora_table_name" to place the true table
name, to to starting command SELECT?

Signs ":" or "&" before SELECT they do not act.

I greet,
Artur.

"artur" <chwilowyadres (AT) WYCINANKAwp (DOT) pl> wrote

Hello,

Oracle won't allow this. You have to dynamically create a string and use
"EXECUTE IMMEDIATE" to execute it.

Example :

DECLARE
ora_table_name VARCHAR2(20);
sqlstring varchar2(2000);
BEGIN
ora_table_name := 'CLIENTS';
sqlstring := 'SELECT * FROM '|| ora_table_name ||' WHERE client =
''1''';
EXECUTE IMMEDIATE (sqlstring);
END;

I haven't tried this example myself, there might be a syntax error in it.
But this will give you an idea how it should work.

Matthias

Hello!

Thank you!!
This is it!
I added small modification. This looks so:

DECLARE
ora_table_name VARCHAR2(20);
sqlstring varchar2(2000);

clients_record CLIENTS%ROWTYPE;
BEGIN
ora_table_name := 'CLIENTS';
sqlstring := 'SELECT * FROM '|| ora_table_name ||' WHERE client =
''1''';
EXECUTE IMMEDIATE (sqlstring) INTO clients_record;


Thank you, thank you!!
Artur.

artur wrote:

Just be aware that using EXECUTE IMMEDIATE can potentially get rid of
all the benefit a stored procedure gives. Normally a SP precompiles the
queries which can be a speed up, but here it's exactly the same as
running the query without using a SP.

This may or may not be an issue for you - if the SP does more then just
run one query, then fine, but if all the SP does is run a query, then I
suggest you just run the query directly, and forget about sending it via
a SP.

-Ariel

Unfortunately, I have to execute this in stored procedure (my bosses'
orders).

Thank you and greet,

Artur