Home » SQL & PL/SQL » Client Tools » How to substitute value from a query in sqlplus (oracl 11g)
How to substitute value from a query in sqlplus [message #549643] Mon, 02 April 2012 12:57 Go to next message
gentleman777us
Messages: 122
Registered: April 2005
Senior Member
Hi

Here is my requirment.

I need generate a script like this.

spool loadvalues.sql

INSERT INTO SALES(column1, column2, column2) SELECT( COLUMN1, COLUMN2, COLUMN3) FROM TABLE_NAME;

spool off;


I would like to substitute TABLE_NAME with each value from the following query;

select table_name from user_tables
where table_name like 'SALES%'

so there are about 45 SALES tables ex: SALES_IDAHO,SALES_MICHIGAN etc.

so my spool file(loadvalues.sql) should look like this
ex:

INSERT INTO SALES(column1, column2, column2) SELECT( COLUMN1, COLUMN2, COLUMN3) FROM SALES_IDAHO;

sales_michingan should have script like
INSERT INTO SALES(column1, column2, column2) SELECT( COLUMN1, COLUMN2, COLUMN3) FROM SALES_MICHIGAN;

Can someone provide me the scirpt

Thanks

Re: How to substitute value from a query in sqlplus [message #549644 is a reply to message #549643] Mon, 02 April 2012 13:06 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Set heading   off
Set feedback  off
Set pagesize  0
Set termout   off
Set trimout   on
Set trimspool on
Set recsep    off
Set linesize  120
Spool loadvalues.sql
Select 'INSERT INTO SALES(column1, column2, column2) SELECT( COLUMN1, COLUMN2, COLUMN3) FROM '||table_name 
from user_tables
where table_name like 'SALES%'
/
spool off

Regards
Michel
Re: How to substitute value from a query in sqlplus [message #549645 is a reply to message #549644] Mon, 02 April 2012 14:15 Go to previous message
gentleman777us
Messages: 122
Registered: April 2005
Senior Member
Thanks! it works.
Previous Topic: Oracle Services for Microsoft Transaction Server
Next Topic: problem in installation of oracle 10g at window 7 64 bit
Goto Forum:
  


Current Time: Sun Feb 25 12:36:10 CST 2018

Total time taken to generate the page: 0.03346 seconds