OraFAQ Forum: Client Tools » how to use variables in sql using sqlplus

Home » Other » Client Tools » how to use variables in sql using sqlplus

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to use variables in sql using sqlplus [message #650827]

Wed, 04 May 2016 13:11

TorontoTrader
Messages: 8
Registered: January 2007

Junior Member

variable v_Date1 varchar2(20)
exec :v_Date1:='DT' || TO_CHAR ( SYSDATE, 'YYYYMMDD');
alter table system.DAILYSTATS_ROWCOUNT
   add :v_Date1 NUMBER;

i get an error when i use the variable as column name. any ideas on how to fix it?

Report message to a moderator

Re: how to use variables in sql using sqlplus [message #650828 is a reply to message #650827]

Wed, 04 May 2016 13:35

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Well, what you do doesn't look right to me. In my opinion, this table should have a DT column (which would store any date values), as well as any other column(s) you need.

Suppose you want to store the highest daily temperature. Your way:

select dt_20160501, dt20160502, dt20160503, dt20160504
from your_table

My way:

select dt, temp
from your_table
where dt between date '2016-05-01' and date '2016-05-04'

My way scales. Yours does not.

Report message to a moderator

Re: how to use variables in sql using sqlplus [message #650829 is a reply to message #650827]

Wed, 04 May 2016 13:37

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

any ideas on how to fix it?

Use a substitution variable.

Report message to a moderator

Re: how to use variables in sql using sqlplus [message #650832 is a reply to message #650827]

Wed, 04 May 2016 16:32

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

This is a bad design. You should be inserting rows, not adding columns, as Littlefoot suggested. Please see another example below, similar to what you seem to need.

SCOTT@orcl> create table dailystats_rowcount (dt  date, rowcount  number)
  2  /

Table created.

SCOTT@orcl> insert into dailystats_rowcount (dt) values (trunc(sysdate))
  2  /

1 row created.

SCOTT@orcl> select * from dailystats_rowcount
  2  /

DT                ROWCOUNT
--------------- ----------
Wed 04-May-2016

1 row selected.

Just to answer your original question, here are a couple of methods. The first uses a SQL*Plus substitution variable and the second uses execute immediate. These are not recommended designs.

SCOTT@orcl> create table dailystats_rowcount (whatever	varchar2(30))
  2  /

Table created.

SCOTT@orcl> column v_date new_value today
SCOTT@orcl> select 'DT' || to_char (sysdate, 'YYYYMMDD') v_date from dual;

V_DATE
----------
DT20160504

1 row selected.

SCOTT@orcl> alter table dailystats_rowcount add &&today number;
old   1: alter table dailystats_rowcount add &&today number
new   1: alter table dailystats_rowcount add DT20160504 number

Table altered.

SCOTT@orcl> describe dailystats_rowcount
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 WHATEVER                                           VARCHAR2(30)
 DT20160504                                         NUMBER

SCOTT@orcl>

SCOTT@orcl> create table dailystats_rowcount (whatever	varchar2(30))
  2  /

Table created.

SCOTT@orcl> begin
  2    execute immediate
  3  	 'alter table dailystats_rowcount add DT' || to_char (sysdate, 'YYYYMMDD') || ' number';
  4  end;
  5  /

PL/SQL procedure successfully completed.

SCOTT@orcl> describe dailystats_rowcount
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 WHATEVER                                           VARCHAR2(30)
 DT20160504                                         NUMBER

SCOTT@orcl>

Also note that you should not be creating tables in the system schema.

[Updated on: Wed, 04 May 2016 16:49]

Report message to a moderator

Previous Topic:	spool not running from sql script
Next Topic:	Query lines to mimic excel formula

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 22 01:03:40 CST 2024