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||'''); '
'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
/
