Page 1 of 1
Connect & Execute Oracle SQL by script
Posted: Sun Aug 26, 2007 2:04 am
by 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
Posted: Sun Aug 26, 2007 2:18 am
by 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?
Posted: Sun Aug 26, 2007 2:34 am
by 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,
Posted: Sun Aug 26, 2007 3:01 am
by ray.wurlod
What is logged as the command executed?
I'd suggest you need a statement
to initialize the dynamic array, prior to the statement that assigns a value to Command<1>
Posted: Sun Aug 26, 2007 3:17 am
by 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
Posted: Sun Aug 26, 2007 3:27 am
by 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
Posted: Sun Aug 26, 2007 4:55 am
by 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,
Posted: Sun Aug 26, 2007 5:52 am
by ArndW
The '$ORACLE_HOME/bin/sqlplus' is for UNIX, replace it with the appropriate command for Windows.
Posted: Sun Aug 26, 2007 6:33 am
by 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,
Posted: Sun Aug 26, 2007 6:59 am
by 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.
Posted: Sun Aug 26, 2007 7:52 am
by kcbland
Just use CALL DSExecute(...) API (read your DSBASIC manual) instead of the EXECUTE statement Arnd supplied.
Posted: Sun Aug 26, 2007 10:17 am
by thirupri
Thanks a lot ArndW & RAY & kcbland.
Finally it worked.
Thanks with Regards
Posted: Sun Aug 26, 2007 7:22 pm
by ArndW
kcbland wrote: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.