Connect & Execute Oracle SQL by script
Moderators: chulett, rschirm, roy
Connect & Execute Oracle SQL by script
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
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
Best Regards,
Thiruma Valavan
Thiruma Valavan
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,
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,
Best Regards,
Thiruma Valavan
Thiruma Valavan
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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>
I'd suggest you need a statement
Code: Select all
Command = ""
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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
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
Best Regards,
Thiruma Valavan
Thiruma Valavan
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)
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 ![Smile :)](./images/smilies/icon_smile.gif)
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. **
![Smile :)](./images/smilies/icon_smile.gif)
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,
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,
Best Regards,
Thiruma Valavan
Thiruma Valavan
Just use CALL DSExecute(...) API (read your DSBASIC manual) instead of the EXECUTE statement Arnd supplied.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle