Project and Portfolio Management Practitioners Forum
cancel

How to continue next command line after error?

Highlighted
adshocker
Frequent Contributor.

How to continue next command line after error?

Hi,

 

I have a requirement wherein I still need to proceed to the next command even though the previous command caused an error.

 

Basically, the first command will be to execute SQL*Loader i.e. sqlldr userid=user/pass control=file.ctl log=file.log. Next command right after this line is cat file.log. Unfortunately if the sqlldr has errors, it won't execute the next command.

 

Is there a way to execute the next line?

 

Thanks.

5 REPLIES
philipwood
Super Contributor.

Re: How to continue next command line after error?

Hi,

We had a similar issue since sqlldr returns non-zero exit codes for warnings even on success and the workflow engine regards a non-zero exit code as failure and stops execution.

 

We used the shell to execute the command and then used the following construct

 

(SQLLDR COMMAND 2>&1);echo $?

 

Basically bash will first execute the command sequence inside the brackets (redirecting the standard error stream to standard output). After this it will echo the exit value of the last command executed (i.e. what happened in the brackets)

 

 2>&1

redirect the standard error output to standard output so it shows in the logs

 

$?

The exit value of the last command executed.

 

We used the following commands in an execution:

(in this example the SAP_BASE is a custom server conf variable).

 

Step 1

Description: Execute SQL*Loader

Condition: Always Executes

Steps:

ksc_local_exec sh -c '( sqlldr userid=[SOURCE_ENV.DB_USERNAME]/[SOURCE_ENV.DB_PASSWORD]@[SOURCE_ENV.DB_ORACLE_SID] control=[AS.SAP_BASE]/Scripts/loadsapuserdata.ctrl bad=[AS.SAP_BASE]/Logs/sapdatabad.ldrbad discard=[AS.SAP_BASE]/Logs/sapdatadisc.ldrdsc errors=1000 log=[AS.SAP_BASE]/Logs/sapdata.log data=[AS.SAP_BASE]/Datafiles/Sapdata.txt 2>&1);echo $?'

Notes:

The special command ksc_local_exec executes the remainder of the command as a local operating system process. The command shell (sh) is invoked with the remainder of the line as parameters. Parameters explanations are as follows: -c Read and execute commands from string after processing the options, then exit. The single-quoted text after the c parameter is executed as a command by the shell.

 

Step 2

Description:

Set Success

Condition:

'[EXEC.OUTPUT]' = '0' OR '[EXEC.OUTPUT]' = '2'

Steps:

 ksc_set_exit_value "SUCCESS","Succeeded"

 

Step 3

Description:

Set Failure

Condition:

'[EXEC.OUTPUT]' <> '0' AND '[EXEC.OUTPUT]' <> '2'

Steps:

ksc_set_exit_value "FAILURE", "Failed"

Step 4

Description:

Output report

Condition:

Always Executes

Steps:

ksc_local_exec sh -vxb -c 'cat [AS.SAP_BASE]/Logs/sapdata.log'

Utkarsh_Mishra
Acclaimed Contributor.

Re: How to continue next command line after error?

Would it be possible to use SPOOL, same as we do in SQL file...

 

 'spool [P.P_SPOOL_FILENAME]' >> $driverecho 'set term on' >> $driverecho '@[P.P_FILENAME]' >> $driverecho 'exit;' >> $drivercat $driver

 

 

here you can use your loader file...

Cheers..
Utkarsh Mishra

-- Remember to give Kudos to answers! (click the KUDOS star)
adshocker
Frequent Contributor.

Re: How to continue next command line after error?

Thanks. I will try this and get back to you for updates.
adshocker
Frequent Contributor.

Re: How to continue next command line after error?

Hi,

Unfortunately SPOOL is an SQL*Plus command and is not available in SQL*Loader as far as I know.
adshocker
Frequent Contributor.

Re: How to continue next command line after error?

Hi,

 

Unfortunately this did not work for me as it seems that we have 2 unix machines in which the executing the ksc_local_exec points to the other machine that doesn't have my scripts. And I can't put my scripts on the other machine as well.

 

I did however find a solution. Added the codes " || echo" right after the sqlldr command and it worked. For example:

 

sqlldr userid=user/pass control=file.ctl 2>/dev/null || echo

 Regards,