Sponsored Content
Top Forums Shell Programming and Scripting Why i can't execute the procedure in shell script? Post 302909370 by feilhk on Wednesday 16th of July 2014 12:26:43 PM
Old 07-16-2014
Why i can't execute the procedure in shell script?

i have the following code inside a shell script .prog in oracle server when i call the program

Code:
DBMS_OUTPUT.PUT_LINE(x_return_status|| ln_rep_req_id);

will return 0 , it is very strange , i try to submit the concurrent request in oracle , and it can successfully executed, what am i missing ? i can't get it working in the shell script.

Code:
echo "1_FILE_NAME_String:"${DOWNLOADFILE}";2_USER_ID_String:"${2}";3_FILE_ID_String:"${ln_group_id}";4_REQUEST_ID_String:"${4}
sqlplus $LOGIN <<ENDOFSQL
 connect $LOGIN
     SET SERVEROUTPUT ON                                
DECLARE
ld_int_date date;   
P_ERRBUFF VARCHAR2(4000);
   P_ERRCODE VARCHAR2(100);
   P_FILE_NAME VARCHAR2(250);
   P_REQUEST_ID VARCHAR2(100);
   P_ERROR_CODE VARCHAR2(100);
   x_return_status VARCHAR2 (3000);
   ln_rep_req_id NUMBER;

BEGIN
 P_FILE_NAME := '${DOWNLOADFILE}';
   P_REQUEST_ID := TO_CHAR(${4});
   P_ERROR_CODE := 'ERR_003_Incorrect_file_format';

 ln_rep_req_id:= fnd_request.submit_request(application => 'XXGL',
                                                          program     => 'XXGL_PRCERP_INT_BAD',
                                                          description => NULL,
                                                          start_time  => NULL,
                                                          sub_request => FALSE,
                                                          argument1   => P_FILE_NAME,
                                                          argument2   => P_REQUEST_ID,
                                                          argument3   => P_ERROR_CODE
                                                          );
DBMS_OUTPUT.PUT_LINE(x_return_status|| ln_rep_req_id); 
DBMS_OUTPUT.PUT_LINE(x_return_status|| P_FILE_NAME); 
DBMS_OUTPUT.PUT_LINE(x_return_status|| P_REQUEST_ID); 
DBMS_OUTPUT.PUT_LINE(x_return_status|| P_ERROR_CODE);
 END;
/

 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Execute an Oracle stored procedure from a shell scrip

Here is a snippet of my code: if then echo "\n Deleting all reports older than 24 hours. \n" >> $logfile ls -l $FileName >> $logfile ... (1 Reply)
Discussion started by: mh53j_fe
1 Replies

2. UNIX for Dummies Questions & Answers

execute store procedure

Hi, can someone point out the tutorial or syntax for how to execute store procedure through shell scripting . thanks. (7 Replies)
Discussion started by: epall
7 Replies

3. Shell Programming and Scripting

Calling stored procedure from shell script

HI, I have a similar problem to thread 18264, only I couldn't get it to work. https://www.unix.com/showthread.php?t=18264 I have a stored procedure which is called by a shell script program. When I run the stored procedure alone or through the shell script, it works fine with output text... (3 Replies)
Discussion started by: dorisw
3 Replies

4. Shell Programming and Scripting

How to execute an Oracle procedure using shell

Hi , i have created an .sh file that has the following code: #!/bin/ksh sqlplus -s p1istuat/p1istuat@CWS_IST6 @Procedure_Execute.sql & sqlplus -s p1istuat/p1istuat@CWS_IST6 << EOF exit EOF The mentioned Procedure_Execute.sql file inside has the following code: exec TEST; ... (5 Replies)
Discussion started by: vins_san
5 Replies

5. Shell Programming and Scripting

How to execute the stored procedure from shell script

How to execute the stored procedure from shell script and is there any possibility to print the dbms output in a log file. (2 Replies)
Discussion started by: dineshmurs
2 Replies

6. Shell Programming and Scripting

Shell Linux to connect to a database and execute store procedure

HI, i want to write a script (Linux) that: 1) connect to a database oracle 2) execute some store procedure. Can anybody help me, please? Thanks a lot Andrew (3 Replies)
Discussion started by: manichino74
3 Replies

7. Shell Programming and Scripting

Execute stored procedure through script in sybase database and store the output in a .csv file

Hi, I have a sybase stored procedure which takes two input parameters (start_date and end_date) and when it get executed, it gives few records as an output. I want to write a unix script (ksh) which login to the sybase database, then execute this stored procedure (takes the input parameter as... (8 Replies)
Discussion started by: amit.mathur08
8 Replies

8. Shell Programming and Scripting

Executing Procedure from shell script..

Hello, I created a sql file to create a Procedure, and it was successfully created. I created a sql file to execute the procedure, and it did without any errors, but i dont see the data been updated. The Execute procedure.sql script is: BEGIN set serveroutput on size 1000000 execute... (5 Replies)
Discussion started by: msrahman
5 Replies

9. Shell Programming and Scripting

Execute a shell script from Oracle procedure

Hi Gurus, Want to execute a shell script from a oracle procedure and get the status of the same, any assistance in this regard will be appreciated. proc_data.sh is script name which I want to execute from oracle procedure It should work something like below procedure test begin... (1 Reply)
Discussion started by: palanisvr
1 Replies

10. UNIX for Beginners Questions & Answers

Shell script to execute Oracle procedure and trigerring email on success and failure

Here is the shell script which need to trigger a stored procedure and when the record count is 0 then it should capture in the log that 0 duplicate records are found and if it's more than 0 then log with no of records. Also if there is any sqlerror then it should write the same in the log file and... (17 Replies)
Discussion started by: senmng
17 Replies
ATF-SH(1)						    BSD General Commands Manual 						 ATF-SH(1)

NAME
atf-sh [-s shell] -- interpreter for shell-based test programs SYNOPSIS
atf-sh script DESCRIPTION
atf-sh is an interpreter that runs the test program given in script after loading the atf-sh(3) library. atf-sh is not a real interpreter though: it is just a wrapper around the system-wide shell defined by ATF_SHELL. atf-sh executes the inter- preter, loads the atf-sh(3) library and then runs the script. You must consider atf-sh to be a POSIX shell by default and thus should not use any non-standard extensions. The following options are available: -s shell Specifies the shell to use instead of the value provided by ATF_SHELL. ENVIRONMENT
ATF_LIBEXECDIR Overrides the builtin directory where atf-sh is located. Should not be overridden other than for testing purposes. ATF_PKGDATADIR Overrides the builtin directory where libatf-sh.subr is located. Should not be overridden other than for testing purposes. ATF_SHELL Path to the system shell to be used in the generated scripts. Scripts must not rely on this variable being set to select a specific interpreter. EXAMPLES
Scripts using atf-sh(3) should start with: #! /usr/bin/env atf-sh Alternatively, if you want to explicitly choose a shell interpreter, you cannot rely on env(1) to find atf-sh. Instead, you have to hardcode the path to atf-sh in the script and then use the -s option afterwards as a single parameter: #! /path/to/bin/atf-sh -s/bin/bash ENVIRONMENT
ATF_SHELL Path to the system shell to be used in the generated scripts. SEE ALSO
atf-sh(3) BSD
September 27, 2014 BSD
All times are GMT -4. The time now is 10:33 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy