Home » SQL & PL/SQL » SQL & PL/SQL » assign table name to a variable
assign table name to a variable [message #360458] Fri, 21 November 2008 01:39 Go to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Can i use a variable after assigning a table name to that variable?

For eg - product_table {its a table} as pt and using pt i can use any of the columns in select queries ? pt.column_name.
Is that possible ?

Please guide me.

Re: assign table name to a variable [message #360462 is a reply to message #360458] Fri, 21 November 2008 02:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm afraid your question is very different to understand.

Can i use a variable after assigning a table name to that variable?


Yes. Of course you can.
DECLARE
  v_var  varchar2(30);
BEGIN
  SELECT table_name
  INTO   v_var
  FROM   all_tables
  WHERE  rownum = 1;

  DBMS_OUTPUT.PUT_LINE(v_var);
END;
/

I'm pretty sure that's not what you wanted, but it is what you asked for,

It's possible that you're talking about table aliases, in which case you need to know that a table alias only has any meaning inside the query that it is defined in. You can't alias a table as 'PT' in query 1 and then use this alias to transfer data to a second query.
Re: assign table name to a variable [message #360471 is a reply to message #360458] Fri, 21 November 2008 02:30 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Actually in my stored procedure i have a input variable "table_name" .

For eg :
CREATE OR REPLACE PROCEDURE hist_procedure(
IN ID VARCHAR2(20),
IN step NUMBER(3),
IN table_name VARCHAR2(40))

And i was trying to ask that can i assign the table_name into a variable?

Re: assign table name to a variable [message #360476 is a reply to message #360471] Fri, 21 November 2008 02:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes you can.

Regards
Michel
Re: assign table name to a variable [message #360479 is a reply to message #360476] Fri, 21 November 2008 02:38 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Michel,

Please tell me how i can do that ?
Re: assign table name to a variable [message #360480 is a reply to message #360479] Fri, 21 November 2008 02:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For instance with JRowbottom example.


Regards
Michel
Re: assign table name to a variable [message #360484 is a reply to message #360480] Fri, 21 November 2008 02:46 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Sorry Michel but i couldn't get that.

CREATE OR REPLACE PROCEDURE hist_procedure(
IN ID VARCHAR2(20),
IN step NUMBER(3),
IN table_name VARCHAR2(40))

If for table_name i want to assign into a variable.

In which block i need to put this declaration. Either in IS or BEGIN ?
Re: assign table name to a variable [message #360487 is a reply to message #360484] Fri, 21 November 2008 02:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can do it in either the DECLARE section, or the main body of the code.
CREATE OR REPLACE PROCEDURE TEST (p_var in varchar2) AS
v_var   varchar2(30) := p_var;
BEGIN
.....

or
CREATE OR REPLACE PROCEDURE TEST (p_var in varchar2) AS
v_var   varchar2(30);
BEGIN
v_var := p_var;
.....


I don't see the relation between this and your initial question though.
Re: assign table name to a variable [message #360490 is a reply to message #360487] Fri, 21 November 2008 02:57 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
THANKS JROW..... I AM A BEGINNER SO THINGS ARE NOT CLEAR WHEN I START , BUT AS QUESTIONS COME FROM YOUR SIDE .. THINGS STARTED TO BECOME CLEAR... I ALSO THAT BECAUSE OF THIS, MANY SENIOR MEMBERS GOT ANGRY..BUT THANKS FOR REPLYING AND HELPING ME OUT FROM SUCH SILLY QUESTIONS.
Re: assign table name to a variable [message #360512 is a reply to message #360490] Fri, 21 November 2008 04:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't post in UPPER case.

Regards
Michel
Re: assign table name to a variable [message #360524 is a reply to message #360512] Fri, 21 November 2008 04:42 Go to previous message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Ok Michel
Previous Topic: Reg : update only one row
Next Topic: PLSQL and SQL optimization
Goto Forum:
  


Current Time: Sun Dec 04 15:00:27 CST 2016

Total time taken to generate the page: 0.14117 seconds