dynamic sql

[tsusnik]

Does anybody use dynamic sql in their Oracle stored procedures. I'm just testing it and I'm getting the following error:
ORA-00903: Invalid table name
at line 8 (open-for)

What am I doing wrong?

Thanks for any help!

Regards, Tomaz


CREATE OR REPLACE PROCEDURE cr_test(
k_test in out crystal.tip_kurzor,
table_name VARCHAR2) IS
query_str VARCHAR2(1000);
con_id NUMBER;
BEGIN
query_str := 'SELECT contract_id FROM ' || table_name || ' WHERE account_id = 301271';
OPEN k_test FOR query_str; --line 8
LOOP
FETCH k_test INTO con_id;
EXIT WHEN k_test%NOTFOUND;
END LOOP;
CLOSE k_test;
END;

 

[Naith]

Are you in the right forum, Tomaz?

Anyway, you might want to try using an "EXECUTE IMMEDIATE" after you assign your variable value in the BEGIN section.

Naith

 

[tsusnik]

Sorry, maybe I really should have asked this somewhere else. But now I have the solution and I would like to write it here.

It is very simple - you don't need the loop:

CREATE OR REPLACE PROCEDURE cr_test(
k_test in out crystal.tip_kurzor,
table_name in VARCHAR2) IS
query_str VARCHAR2(1000);
BEGIN
query_str := 'SELECT contract_id FROM ' || table_name || ' WHERE account_id = 301271';
OPEN k_test FOR query_str;
END;

Of course, this dynamic sql works only on Oracle 8i.
I hope it will be helpful.

Tomaz