CODEWITHSUNDEEP
oracle-databaseshellconcurrencyplsql
Lam Chin Fei
Lam Chin Fei

Reputation: 101

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

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.

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;
/

exit
ENDOFSQL

Upvotes: 0

Views: 1939

Answers (2)

Lam Chin Fei
Lam Chin Fei

Reputation: 101

i have forgotten to include the following in my procedure, so it cannot be run. thanks for all of your help.

fnd_global.apps_initialize(user_id => lc_user_id, resp_id => ln_resp_id, resp_appl_id => ln_appl_id);

Upvotes: 0

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

If FND_REQUEST.SUBMIT_REQUEST returns a zero it means that an error occurred. To find out what the error(s) are you'll need to call FND_MESSAGE.RETRIEVE and FND_MESSAGE.ERROR to retrieve, format, and display the errors.

Documentation for FND_REQUEST.SUBMIT_REQUEST here.

Documentation for FND_MESSAGE.RETRIEVE and FND_MESSAGE.ERROR here.

Share and enjoy.

Upvotes: 2

Related Questions