Create Extract by sqlplus, from DB to sql statements File.

General

Several examples of creating an extract from database to a flat file.

Example 1 - Creates a file, set_connection.sql, that would hold connection properties.

set_connection.sql
define conn_user=mickey
define conn_pass=mouse

define conn_str=disney

Example 2 - Build file, called InsertList.sql with a bunch of INSERT statements.
@set_connection.sql
SET TERMOUT OFF

conn &&conn_user/&&conn_pass@&&conn_str

SET SHOW OFF 
SET VERIFY OFF 
SET HEAD OFF
SET LINE 500
SET FEEDBACK OFF
SET PAGES 0
SET TRIMS ON

-- This goes into InsertList.sql
SPOOL InserPList.sql
PROMPT @set_connection.sql
PROMPT SET TERMOUT OFF
PROMPT 
PROMPT &&conn_user/&&conn_pass@&&conn_str
PROMPT SET SHOW OFF
PROMPT SET HEAD OFF
PROMPT SET LINE 1000
PROMPT SET FEEDBACK OFF
PROMPT SET PAGES 0
PROMPT 

SELECT 'DELETE FROM MY_EXTRACT_LIST;' FROM DUAL;
PROMPT 

-- MY_EXTRACT_LIST
SELECT   
  'INSERT INTO MY_EXTRACT_LIST (provider_id) VALUES('||MY_LIST.provider_id||');'
FROM
  COUNTRIES,
  SERVICE_PROVIDERS,
  IMSI_LIST,
  IMSI_RANGES,
  RECORD_STATUSES  RECORD_STATUSES_IMSI,
  RECORD_STATUSES  RECORD_STATUSES_SP
WHERE TABLE_A.field_a = TABLE_B.field_a
  AND TABLE_C.field.b = TABLE_B.field_bORDER BY MY_LIST.provider_id;

SELECT 'COMMIT;' FROM DUAL;  
PROMPT EXIT
PROMPT /

SPOOL OFF
EXIT
/

The output file, InsertList.sql,would look like:

@set_connection.sql
SET TERMOUT OFF

conn mickey/mouse@disney
SET SHOW OFF
SET HEAD OFF
SET LINE 1000
SET FEEDBACK OFF
SET PAGES 0

DELETE FROM MY_EXTRACT_LIST;


INSERT INTO MY_EXTRACT_LIST(provider_id) VALUES(2);
INSERT INTO MY_EXTRACT_LIST(provider_id) VALUES(3);
INSERT INTO MY_EXTRACT_LIST(provider_id) VALUES(4);
INSERT INTO MY_EXTRACT_LIST(provider_id) VALUES(5);
INSERT INTO MY_EXTRACT_LIST(provider_id) VALUES(6);

COMMIT;

EXIT

/

Example 3

Build a flat file with a bunch of INSERT statements, based on a data in DB.

@set_connection.sql

SET TERMOUT OFF

conn &&conn_user/&&conn_pass@&&conn_str

SET SHOW OFF 

SET VERIFY OFF 

SET HEAD OFF

SET LINE 500

SET FEEDBACK OFF

SET PAGES 100 

SET TRIMS ON

SPOOL LoadExtractedData.sql

SELECT 'CREATION DATE:'||TO_CHAR(SYSDATE,'DD/MM/YYYY HH:MI')||chr(13)||chr(10) ||

'ITEM ID:&&1'||chr(13)||chr(10)  || 

'VERSION:400' ||chr(13)||chr(10) ||

'VERSION ID:'||ITEMGLOBAL_ID_SEQ.NEXTVAL FROM DUAL;

PROMPT SET DEFINE OFF

-- TEMP_REGIONS

SELECT 'INSERT INTO TEMP_REGIONS(REGION_ID, REGION_NAME, TS_LAST_MODIFIED) '||

       'VALUES ('|| REGION_ID || ','''|| SUBSTR(REPLACE(REGION_NAME, '''',''''''),1,50) || ''', SYSDATE); '

FROM ALL_REGIONS;

-- TEMP_GROUPS

SELECT 'INSERT INTO TEMP_GROUPS(GROUP_ID, GROUP_NAME, TS_LAST_MODIFIED) '||

       'VALUES ('|| GROUP_ID || ','''|| SUBSTR(REPLACE(GROUP_NAME, '''', ''''''),1,50) || ''', SYSDATE); '

FROM ALL_GROUPS;

-- TEMP_COUNTRIES

SELECT 'INSERT INTO TEMP_COUNTRIES(COUNTRY_ID, COUNTRY_NAME, REGION_ID, COUNTRY_CODE, MB_COUNTRY_CODE, GMT_OFFSET, SUPPORT_FLAG, IND_FLAG , TS_LAST_MODIFIED,CHN_FLAG) '||
  'VALUES ('|| COUNTRY_ID || ','''||
   SUBSTR(REPLACE(NAME, '''', ''''''),1,50) || ''','||
   DECODE(REGION_ID,NULL,'NULL',REGION_ID) ||','''||
   COUNTRY_CODE || ''',''' ||
   NVL(MB_COUNTRY_CODE,'000') ||''','||
   NVL(DELTA_HOURS * 60 * DECODE(IS_AHEAD_GMT,1,1,-1),0)
   || ',' ||
   NVL(MB_NUMBER,0) || ','||
   DECODE(IND_FLAG ,NULL,'NULL',''''||IND_FLAG ||'''') ||
   ',SYSDATE,'''||CHN_FLAG||'''); '

FROM COUNTRIES A, TIME_ZONES B

WHERE A.DEFAULT_TIME_ZONE = B.TIME_ZONE_ID(+)

  AND NAME NOT IN ( 'TEST')

  AND COUNTRY_ID NOT IN 

     (SELECT DISTINCT COUNTRY_ID FROM IGNORE_COUNTRIES);

The output would be something like:

CREATION DATE:12/02/2014 06:14

ITEM ID:24

VERSION:400

VERSION ID:422

SET DEFINE OFF

INSERT INTO TEMP_REGIONS(REGION_ID, REGION_NAME, TS_LAST_MODIFIED) VALUES (1,'Africa', SYSDATE);

INSERT INTO TEMP_REGIONS(REGION_ID, REGION_NAME, TS_LAST_MODIFIED) VALUES (2,'Asia (Rest)', SYSDATE);

INSERT INTO TEMP_REGIONS(REGION_ID, REGION_NAME, TS_LAST_MODIFIED) VALUES (3,'Asia Pacific', SYSDATE);

INSERT INTO TEMP_GROUPS(GROUP_ID, GROUP_NAME, TS_LAST_MODIFIED) VALUES (33,'Kuku', SYSDATE);

INSERT INTO TEMP_GROUPS(GROUP_ID, GROUP_NAME, TS_LAST_MODIFIED) VALUES (34,'Kuki', SYSDATE);

INSERT INTO TEMP_COUNTRIES(COUNTRY_ID, COUNTRY_NAME, REGION_ID, COUNTRY_CODE, MB_COUNTRY_CODE, GMT_OFFSET, SUPPORT_FLAG, IND_FLAG  TS_LAST_MODIFIED,CHN_FLAG) VALUES (34,'KAZAKHSTAN',2,'7','423',312,0,'KZ',SYSDATE,'KAZ');

INSERT INTO TEMP_COUNTRIES(COUNTRY_ID, COUNTRY_NAME, REGION_ID, COUNTRY_CODE, MB_COUNTRY_CODE, GMT_OFFSET, SUPPORT_FLAG,IND_FLAG , TS_LAST_MODIFIED,CHN_FLAG) VALUES (44,'SAUDI ARABIA',5,'342','23',180,1,'SA',SYSDATE,'SAU');

INSERT INTO TEMP_COUNTRIES(COUNTRY_ID, COUNTRY_NAME, REGION_ID, COUNTRY_CODE, MB_COUNTRY_CODE, GMT_OFFSET, SUPPORT_MNP_FLAG,IND_FLAG, TS_LAST_MODIFIED,CHN_FLAG) VALUES (54,'RUSSIA',4,'7','211',340,0,'RU',SYSDATE,'RUS');

COMMIT

/