Home » SQL & PL/SQL » SQL & PL/SQL » Tablename as parameter. How to use it in the SQL Statement in the PL/SQL Code?
Tablename as parameter. How to use it in the SQL Statement in the PL/SQL Code? [message #279808] Sat, 10 November 2007 13:03 Go to next message
novalex
Messages: 19
Registered: November 2007
Junior Member
Hello!

I tried to find an answer on this question but all examples shows something different than I'm looking for.

It's a simple problem but I cannot solve it.
I wrote this procedure:
procedure test_proc(tab_name in varchar2) as
    var number;
BEGIN
    SELECT count(*) into var FROM tab_name;
    dbms_output.put_line(var);
    NULL;
END test_proc;

The problem is that I don't know how to use the parameter tab_name in the SQL-statement? I mean when this code is executed with this SQL Statement (see above):
SELECT count(*) into var FROM tab_name;

I want to have replaced the 'tab_name' with its value (the name of the table). Instead I get an error that the table cannot be found.
All examples I found use directly tablenames on that place. Sad

Hope you can help me.

Thank you.

Novalex
Re: Tablename as parameter. How to use it in the SQL Statement in the PL/SQL Code? [message #279809 is a reply to message #279808] Sat, 10 November 2007 13:07 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
search this forum for "EXECUTE IMMEDIATE"

IMO, this 'generalized' approach is a BAD idea for any number of reasons.

[Updated on: Sat, 10 November 2007 13:08] by Moderator

Report message to a moderator

Re: Tablename as parameter. How to use it in the SQL Statement in the PL/SQL Code? [message #279813 is a reply to message #279808] Sat, 10 November 2007 13:35 Go to previous messageGo to next message
novalex
Messages: 19
Registered: November 2007
Junior Member
Thank you, but I already know 'Execute immediate'. I'm looking for another possibility. Maybe something with %type or something else?
Re: Tablename as parameter. How to use it in the SQL Statement in the PL/SQL Code? [message #279815 is a reply to message #279808] Sat, 10 November 2007 13:37 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
>I'm looking for another possibility.
Good luck with your search for the Great Pumpkin.
Re: Tablename as parameter. How to use it in the SQL Statement in the PL/SQL Code? [message #279817 is a reply to message #279808] Sat, 10 November 2007 13:50 Go to previous messageGo to next message
novalex
Messages: 19
Registered: November 2007
Junior Member
Is there no other possibility?
Re: Tablename as parameter. How to use it in the SQL Statement in the PL/SQL Code? [message #279818 is a reply to message #279817] Sat, 10 November 2007 14:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To do exactly what you want? No.
Do do it correctly. Yes.

Regards
Michel
Re: Tablename as parameter. How to use it in the SQL Statement in the PL/SQL Code? [message #279850 is a reply to message #279813] Sun, 11 November 2007 01:56 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
novalex wrote on Sat, 10 November 2007 20:35

Thank you, but I already know 'Execute immediate'. I'm looking for another possibility. Maybe something with %type or something else?

Why?
Previous Topic: Need logic for row1 - row2
Next Topic: formal parameter constraining
Goto Forum:
  


Current Time: Fri Dec 09 11:48:29 CST 2016

Total time taken to generate the page: 0.17083 seconds