| HOW DO I INVOKE MULTIPLE SQL*LOADER COMMANDS? [message #71261] |
Thu, 03 October 2002 07:08  |
Lee Bennett
Messages: 3
Registered: September 2002
|
Junior Member |
|
|
I am trying to run several SQLLDR commands two different ways. My first method is via SQL*Plus and I execute the cmd :- SQLLDR uname/pwd@host CONTROL = SECURITY_MAINGROUP.CTL LOG = SECMAINGRP.LOG
This executes no problem but the cmd window sits there until I close it and if I have another SQL*Loader cmd line in the file it doesn't run. If I press CTRL C to close the SQL*Loader session, then the next one will run. My first question is how do I run multiple cmds this way without having to intervene?
The second method that I'm using is to run the cmds from a PL/SQL procedure. It's running on Unix, here's the basic code :-
CREATE OR REPLACE PROCEDURE LEEB_SQL_LOADER_CTL
IS
-- DECLARE
CURSOR C_GET_TABLE_NAMES
IS
SELECT TABLE_NAME
FROM LEEB_REQUIRED_TABLES
WHERE DATA_CONVERSION = 'Yes'
AND TABLE_NAME='CAP';
R_TABLE_NAME_REC C_GET_TABLE_NAMES%ROWTYPE;
V_INVOKE_LOADER VARCHAR2(200);
BEGIN
OPEN C_GET_TABLE_NAMES;
LOOP
FETCH C_GET_TABLE_NAMES INTO R_TABLE_NAME_REC;
EXIT WHEN C_GET_TABLE_NAMES%NOTFOUND
OR C_GET_TABLE_NAMES%NOTFOUND IS NULL;
V_INVOKE_LOADER := 'SQLLDR uname/pwd'||
' CONTROL = /home/isd/lxb/' ||R_TABLE_NAME_REC.TABLE_NAME||'.CTL'||
' LOG = /home/isd/lxb/'||R_TABLE_NAME_REC.TABLE_NAME||'.LOG';
dbms_output.put_line('$ SQLLDR TAROT/TAROT'||
'CONTROL = /home/isd/lxb/' ||R_TABLE_NAME_REC.TABLE_NAME||'.CTL'||
' LOG = /home/isd/lxb/'||R_TABLE_NAME_REC.TABLE_NAME||'.LOG');
EXECUTE IMMEDIATE V_INVOKE_LOADER;
END LOOP;
CLOSE C_GET_TABLE_NAMES;
EXCEPTION
WHEN NO_DATA_FOUND THEN
GENERALPACKAGE.CREATEERRORLOG('LEEB','LEEB_SQL_LOADER_CTL','OTHERS ERROR',NULL);
UTL_FILE.FCLOSE_ALL;
WHEN OTHERS THEN
GENERALPACKAGE.CREATEERRORLOG('LEEB','LEEB_SQL_LOADER_CTL','NO DATA FOUND',NULL);
UTL_FILE.FCLOSE_ALL;
END;
/
Basically, it doesn't work. What am I doing wrong? Everything probably!
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
