Home » Other » Client Tools » how to use variables in sql using sqlplus
how to use variables in sql using sqlplus [message #650827] Wed, 04 May 2016 13:11 Go to next message
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?
Re: how to use variables in sql using sqlplus [message #650828 is a reply to message #650827] Wed, 04 May 2016 13:35 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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.
Re: how to use variables in sql using sqlplus [message #650829 is a reply to message #650827] Wed, 04 May 2016 13:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
any ideas on how to fix it?


Use a substitution variable.

Re: how to use variables in sql using sqlplus [message #650832 is a reply to message #650827] Wed, 04 May 2016 16:32 Go to previous message
Barbara Boehmer
Messages: 9077
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:
  


Current Time: Thu Mar 28 03:55:34 CDT 2024