CODEWITHSUNDEEP
sqloracle-databaseexport
iPadDevloperJr
iPadDevloperJr

Reputation: 1004

Generate Oracle SQL file as a result of query request

I'm using a shell script to call Oracle sqlplus and I want to export the result of a query to an sql file.

This request give me a lot of values:

SELECT * FROM CUST_TABLE WHERE  CUST_CITY = 'San Fran';

And I want my script to generate an sql file like this:

evariste@Unix-Server$cat first_query_result.sql

INSERT INTO CUST_TABLE (COLUMN_1, COLUMN_2, ....)
VALUES (CUST1_COLUMN_1, CUST1_COLUMN_2, ...);
INSERT INTO CUST_TABLE (COLUMN_1, COLUMN_2, ....)
VALUES (CUST2_COLUMN_1, CUST2_COLUMN_2, ...);
INSERT INTO CUST_TABLE (COLUMN_1, COLUMN_2, ....)
VALUES (CUST3_COLUMN_1, CUST3_COLUMN_2, ...);

Any idea (I have a little background in Oracle and SQL) on how to perform that in my script?

Upvotes: 2

Views: 1006

Answers (1)

DCookie
DCookie

Reputation: 43523

Something along these lines, perhaps:

sqlplus -silent uid/pw@db <<+EOF
set heading off
set feedback off
set pagesize 0
set linesize 32000
set trimspool on
set termout off
set echo off
set verify off
set sqlblanklines off
spool somefile.sql
WITH cust_table AS 
(SELECT 1 id, 'San Francisco' city, 'C1V1' col1, 'C2V1' col2 FROM dual
 UNION ALL SELECT 2, 'Los Angeles', 'C1V2', 'C2V2' FROM dual
 UNION ALL SELECT 3, 'San Jose', 'C1V3', 'C2V3' FROM dual
 UNION ALL SELECT 4, 'San Francisco', 'C1V4', 'C2V4' FROM dual)
SELECT 'INSERT INTO cust_table (id, city, col1, col2)'||CHR(10)||'VALUES ('||
       id||','''||city||''','''||col1||''','''||col2||''');'
  FROM cust_table
 WHERE city = 'San Francisco';
quit;
+EOF

This results in a file somefile.sql with the following contents:

INSERT INTO cust_table (id, city, col1, col2)
VALUES (1,'San Francisco','C1V1','C2V1');
INSERT INTO cust_table (id, city, col1, col2)
VALUES (4,'San Francisco','C1V4','C2V4');

Upvotes: 3

Related Questions