Connect & Execute Oracle SQL by script

[thirupri]

Dear Guru's,

Please help me if any one knows how to connect ORACLE and execute the procedure and get the return value of the procedure by writing the script.

Thanks with Regards,

Thiruma

[ArndW]

Perhaps you could try a search on "stored procedure return value"; I got 50 hits on that.
Or do you wish to execut Oracle SQL from a UNIX script called from DataStage?

[thirupri]

Actually, I want to execute a function using the Script and get the return value. If the SQL is execute correctly the I will mark the record is send successfully. Else I write the Record to the rejection file for the investication. Please help how to execute and get the value. For Example I am writing like this

Lf = Char(10)
CrLf = char(13):Char(10)
*CrLf = char(59)
*RoutineName = "ExecuteSQL"

SqlStatement = "select ofdm_ods.ofdm_get_daily_as_of_date into a1 from dual;"

Username = 'xxxx'
Password = 'XXX'
Instance = 'YYYY'

Command<1> = 'sqlplus -S ': Username :'/': Password :'@': Instance
Command<2> = 'set serveroutput on;'
Command<3> = 'declare a1 varchar2(100);'
Command<4> = 'begin'
Command<5> = Convert(@FM, Lf, SqlStatement)
Command<6> = 'dbms_output.put_line(a1);'
Command<7> = 'end;'
Command<8> = '/'
Command<9> = ''
Command<10> = 'EXIT;'
Command<11> = 'END;'

Command = Ereplace(Command, @FM, CrLf)

Call DSExecute("NT", Command, ScreenOutput, SystemReturnCode)

call DSLogInfo("ORACLE SQLStatement - ": Command, "Result")
call DSLogInfo("ORACLE SQLStatement - ": ScreenOutput, "Result")
call DSLogInfo("ORACLE SQLStatement - ":SystemReturnCode, "SystemReturnCode")
"

Actually, the "ScreenOutput" is not showing the value which it suppose to display. And if I give the Function name wrong or the table name wrong in the script the DSExecute is giving me '0' only instated of '1'.

Thanks with Regards,

[ray.wurlod]

What is logged as the command executed?
I'd suggest you need a statement

Code: Select all

Command = ""

to initialize the dynamic array, prior to the statement that assigns a value to Command<1>

[thirupri]

Dear Ray,

I even changed as per your advice. If you see the Sql Statement I mention the table name wrong. So I expect the return value -1. Please see the log.

Lf = Char(10)
CrLf = char(13):Char(10)
*CrLf = char(59)
*RoutineName = "ExecuteSQL"

SqlStatement = "select ofdm_ods.ofdm_get_daily_as_of_date into a1 from dul;"

Username = 'admin'
Password = 'admin'
Instance = 'PXXX'
Command=" "
Command<1> = 'sqlplus -S ': Username :'/': Password :'@': Instance
Command<2> = 'set serveroutput on;'
Command<3> = 'declare a1 varchar2(100);'
Command<4> = 'begin'
Command<5> = Convert(@FM, Lf, SqlStatement)
Command<6> = 'dbms_output.put_line(a1);'
Command<7> = 'end;'
Command<8> = '/'
Command<9> = ''
Command<10> = 'EXIT;'
Command<11> = 'END;'

Command = Ereplace(Command, @FM, CrLf)

Call DSExecute("NT", Command, ScreenOutput, SystemReturnCode)

call DSLogInfo("ORACLE SQLStatement - ": Command, "Result")
call DSLogInfo("ORACLE SQLStatement - ": ScreenOutput, "Result")
call DSLogInfo("ORACLE SQLStatement - ":SystemReturnCode, "SystemReturnCode")



LOG:

Line1:
OracleTest: Set NLS locale to US-ENGLISH,US-ENGLISH,US-ENGLISH,US-ENGLISH,US-ENGLISH

Line2:
OracleTest..JobControl (Result): ORACLE SQLStatement - sqlplus -S admin/admin@PXXX
set serveroutput on;
declare a1 varchar2(100);
begin
select ofdm_ods.ofdm_get_daily_as_of_date into a1 from dul;
dbms_output.put_line(a1);
end;
/

EXIT;
END;

Line3:
OracleTest..JobControl (Result): ORACLE SQLStatement -

Line4:
OracleTest..JobControl (SystemReturnCode): ORACLE SQLStatement - 0

=========

Please advice.

Thanks a lot.

Thiruma

[ArndW]

I prefer to use a SQL command file instead of your approach. First of all, it makes testing a lot easier until you have the format of the file correct, then it just takes a small routine to make the file contents dynamic.

Take a look at the following routine which is designed to execute just a 1 line command.

ExecuteSQL(OracleUserId,OraclePassword,OracleServer,SQLCommand)

Code: Select all

****************************************************************************************************
** DataStage server function to create a simple SQL command file to execute the SQL command       **
** passed in as the SQLCommand Parameter.                                                         **
**                                                                                                **
** Vers.   Date       Author   Comments                                                           **
** =====   ========== ======== ===================================================================**
** 1.0.0   2006-08-05 AWussing Initial coding, testing and implementation                         **
**                                                                                                **
****************************************************************************************************
   EQUATE ProgramName    TO 'ExecuteSQL'
   EQUATE ProgramVersion TO '1.0.0'

   **********************************************************************************************
   ** Generate a unique temporary file name and sequential file, write the command into it.    **
   **********************************************************************************************
   TempFileName = @LOGNAME:DATE():TIME():RND(100000):'.plsql'
   OPENSEQ TempFileName TO TempFilePtr THEN WEOFSEQ TempFilePtr ELSE NULL
   WRITESEQ SQLCommand ON TempFilePtr ELSE CALL DSLogWarn('Unable to write data to sequential file "':TempFileName:'".',ProgramName)
   WRITESEQ "QUIT" ON TempFilePtr ELSE CALL DSLogWarn('Unable to write to sequential file "':TempFileName:'".',ProgramName)
   CLOSESEQ TempFilePtr

   ************************************************************************************************
   ** Build the command and execute it; also delete the temporary command file at the same time. **
   ************************************************************************************************
   Command = '$ORACLE_HOME/bin/sqlplus -S ':OracleUserId:'/':OraclePassword:'@':OracleServer:' @':TempFileName:' ; rm -f ':TempFileName
   EXECUTE 'SH -c "':Command:'"' CAPTURING ScreenIO RETURNING ErrorCode
   IF ErrorCode THEN CALL DSLogWarn('Warning, SQL command returned "':ScreenIO:'", error ':ErrorCode,ProgramName)

   Ans = ErrorCode:@FM:ScreenIO ;** Return the error code and raw result, let the calling program parse the contents approprately. **

You can see where you only need to add more write commands, perhaps to parse the SQLCommand into separate lines. Oh, and add some error detection on the ELSE open statement

[thirupri]

Dear ArndW,

I created the routine but when I am testing the routine I am getting the following error message.

"DSLogWarn called from : ExecuteSQL
Message to be logged is...
> Warning, SQL command returned "This command is not available on this platform
> ", error -1

Result = -1
This command is not available on this platform
"

Thanks with Regards,

[ArndW]

The '$ORACLE_HOME/bin/sqlplus' is for UNIX, replace it with the appropriate command for Windows.

[thirupri]

Thanks ArndW,

I correct it. But, the problem is in this line.

"EXECUTE 'SH -c "':Command:'"' CAPTURING ScreenIO RETURNING ErrorCode"

How can I change it for Window.

Please help.

Thanks a lot for your help to solve my problem.

Thanks with Regards,

[ArndW]

I'm not at a DS system so I'm not sure is the command is "DOS" instead of "SH" but it should be in your help file.

[kcbland]

Just use CALL DSExecute(...) API (read your DSBASIC manual) instead of the EXECUTE statement Arnd supplied.

[thirupri]

Thanks a lot ArndW & RAY & kcbland.

Finally it worked.

Thanks with Regards

[ArndW]

Just use CALL DSExecute(...)...

I guess I'm just too `olde skool' and used to using the direct call. You are correct, DSExecute is the correct routine to call.