Getting table name for SELECT from formal param in stored procedure 2

[cadoltt]

Hi everybody,

I am trying to create a stored procedure which can process different tables. The general idea is to make something like this:

CREATE OR REPLACE PROCEDURE my_proc
 (
  field_name       in VARCHAR2,
  tbl_name         in VARCHAR2
 )
AS
  value        NUMBER;
BEGIN
  ...
  SELECT field_name INTO value 
  FROM tbl_name ...
  ...
END;

However such a construct gives me the following error:

PL/SQL: ORA-00942: table or view does not exist


Can anyone tell me a way around?


Thanks,

Alex

 

[BJCooperIT]

You need to use dynamic SQL for this. This means building a text string ('select myfld from mytable') and using execute_immediate. There are good examples if you search on "execute_immediate".

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[cadoltt]

Thank you very much, BJCooperIT!

Alex

 

[SantaMufasa]

Alex,

Don't you think Barb (BJCooper) deserves a Purple Star (by clicking the "Thank BJCooperIT for this valuable post" button)?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[cadoltt]

You are right, SantaMufasa, I am clicking the button right away.

Moreover, since your reminder was also valuable, I'll hit the thank-you-button for you too.



By the way, I need to ask you guys something else, I am in the middle of writing another question.

Thank you all again!

Alex