OraFAQ Forum: Client Tools » How to substitute value from a query in sqlplus

Home » Other » Client Tools » How to substitute value from a query in sqlplus (oracl 11g)

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

How to substitute value from a query in sqlplus [message #549643]

Mon, 02 April 2012 12:57

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

Report message to a moderator

Re: How to substitute value from a query in sqlplus [message #549644 is a reply to message #549643]

Mon, 02 April 2012 13:06

Michel Cadot
Messages: 68501
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

Report message to a moderator

Re: How to substitute value from a query in sqlplus [message #549645 is a reply to message #549644]

Mon, 02 April 2012 14:15

gentleman777us
Messages: 122
Registered: April 2005

Senior Member

Thanks! it works.

Report message to a moderator

Previous Topic:	Oracle Services for Microsoft Transaction Server
Next Topic:	problem in installation of oracle 10g at window 7 64 bit

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Sep 24 22:08:22 CDT 2023