Home » SQL & PL/SQL » SQL & PL/SQL » Calling a table through a varchar variable. (Oracle 9.2.0.0)
Calling a table through a varchar variable. [message #395539] Wed, 01 April 2009 17:24 Go to next message
sberger86
Messages: 5
Registered: March 2009
Location: Eisenach, Thüringen, D...
Junior Member
Hi everybody!

I am still a newbie, so please be pacience, I know you are.
* I would like to know if I can call a table through a variable,
for example:
select name from intermediate.item_attributes;


like:

Declare
    my_table  varchar(50) := 'intermediate.item_attributes';
begin
    select name from my_table;
end;


and I got errors like: table or view doesnt exist.

** Can I use it also with a column name?

*** and what can I do if my column name is a reserv word from sql, for example: intermediate.item_attributes.remark , just when I entered the REM , SQL recognize it as a comment.

Thanks a lot!
Re: Calling a table through a varchar variable. [message #395540 is a reply to message #395539] Wed, 01 April 2009 17:31 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I would like to know if I can call a table through a variable,
No, you can not do so.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

How to do valid SQL can be found in RTFM below
http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/toc.htm

How to do valid PL/SQL can be found in RTFM below
http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/toc.htm

>what can I do if my column name is a reserv word from sql,
Fire yourself!
Re: Calling a table through a varchar variable. [message #395602 is a reply to message #395539] Thu, 02 April 2009 01:02 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
sberger86 wrote on Thu, 02 April 2009 00:24

*** and what can I do if my column name is a reserv word from sql, for example: intermediate.item_attributes.remark , just when I entered the REM , SQL recognize it as a comment.

Thanks a lot!

SQL> create table test (
  2  id number,
  3  remark varchar2(10)
  4  );

Table created.

SQL> select id,
  2  remark
  3  from test;

no rows selected


REMARK can be used as a column name. I bet you are using an editor (or worse: TOAD) and saw the line change colour after the first three letters.
REM only denotes a comment if followed by whitespace.
Re: Calling a table through a varchar variable. [message #395603 is a reply to message #395539] Thu, 02 April 2009 01:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
and I got errors like: table or view doesnt exist.

** Can I use it also with a column name?

You have to use dynamic SQL (execute immediate or dbms_sql, search for these in http://tahiti.oracle.com) but it is not a good practice. Use as much as you can static SQL even if it means duplicating code.

Quote:
and what can I do if my column name is a reserv word from sql, for example: intermediate.item_attributes.remark , just when I entered the REM , SQL recognize it as a comment.

REM or REMARK is NOT a reserved word for SQL (but it is a command for SQL*PLus).
Never use the words that are in V$RESERVED_WORDS view; always check this view when you design an application.

Regards
Michel

Re: Calling a table through a varchar variable. [message #396095 is a reply to message #395539] Fri, 03 April 2009 17:40 Go to previous message
sberger86
Messages: 5
Registered: March 2009
Location: Eisenach, Thüringen, D...
Junior Member
Thank you very much Michel, Blackswan and Frank for your answers.

Yes I am using SQLPlus. I am going to try againg.
Previous Topic: sql problem
Next Topic: Oracle Analytical functions question
Goto Forum:
  


Current Time: Fri Dec 09 17:20:41 CST 2016

Total time taken to generate the page: 0.29067 seconds