Home » SQL & PL/SQL » SQL & PL/SQL » compiled with errors
compiled with errors [message #303055] Wed, 27 February 2008 17:08 Go to next message
shree_z
Messages: 75
Registered: February 2008
Member
Can anyone tell me please, what is wrong in this procedure

create or replace procedure XX_Call_ASN_Import_Prgm (fname IN varchar2,dir_name  IN varchar2)
IS
ret_val number;
declare
v_request_id number := 0;
v_status varchar2(100);
t_success boolean;
lv_phase varchar2(30);
lv_status varchar2(30);
lv_dev_phase varchar2(30);
lv_dev_status varchar2(30);
lv_message varchar2(50);
begin
FND_GLOBAL.APPS_INITIALIZE(34383, 52906, 175);
v_request_id := fnd_request.submit_request('EC'
                                            ,'fname'
                                            ,NULL
                                            ,SYSDATE
                                            ,FALSE
                                            ,'dir_name'
                                            ,'fname'
                                            ,'Yes'
                                            ,'ASNI' 
                                            ,'EC_ASNI_FF'
                                            ,0
                                            ,'WE8ISO8859P1');
commit;
fnd_file.put_line(fnd_file.log, 'Request Not Submitted due to "' || fnd_message.get || '".');
dbms_output.put_line('Request_id :'||v_request_id);
t_success := FND_CONCURRENT.WAIT_FOR_REQUEST (
                     v_request_id,
                     2,
                     0,
                     lv_phase,
                     lv_status,
                     lv_dev_phase,
                     lv_dev_status,
                     lv_message );
   dbms_output.put_line (lv_phase||'  '||lv_status) ;                 
   IF lv_phase = 'Completed' AND lv_status = 'Normal' THEN
  :ret_val := 10;
else
:ret_val := 20;
end if;
end;
/
exit :ret_val;
  
  exception
when others then
dbms_output.put_line('error');
end XX_Call_ASN_Import_Prgm; 

A shell script calls this procedure.
I need to use the ret_val as a variable that returns status to a shell script.

Please help!!

Thanks in advance

[Updated on: Wed, 27 February 2008 20:25] by Moderator

Report message to a moderator

Re: compiled with errors [message #303056 is a reply to message #303055] Wed, 27 February 2008 17:44 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
compile the procedure again and post the output of
show errors

BTW,
The commit is totally unnecessary.

Re: compiled with errors [message #303062 is a reply to message #303055] Wed, 27 February 2008 20:09 Go to previous messageGo to next message
shree_z
Messages: 75
Registered: February 2008
Member
Hi Mahesh,

Sorry for the delayed response

the error that I get when i run this code in TOAD is

Quote:
'Declare' is not a vailid identifier


And when i try to use sqlplus for running this code, the screen just diasappears.. I tried with set serveroutpput on also, but in vain. How can I see the results in Sqlplus?

Thanks

[Updated on: Wed, 27 February 2008 20:09]

Report message to a moderator

Re: compiled with errors [message #303091 is a reply to message #303062] Wed, 27 February 2008 23:34 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
Quote:
Quote:
'Declare' is not a vailid identifier


the answer is in your post itself.you don't need to use DECLARE in a procedure.and to display the output in SQLPLUS use SET SERVEROUTPUT ON.


regards,
Re: compiled with errors [message #303120 is a reply to message #303062] Thu, 28 February 2008 01:01 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
shree_z wrote on Thu, 28 February 2008 03:09

And when i try to use sqlplus for running this code, the screen just diasappears.. I tried with set serveroutpput on also, but in vain. How can I see the results in Sqlplus?

Do you know the difference between defining/compiling a stored procedure/function/package and executing it?
exit :ret_val;
  
  exception
when others then
dbms_output.put_line('error');
end XX_Call_ASN_Import_Prgm; 


All this is rubbish.
"exit" is a sqlplus command that, well, exits sqlplus.
The rest is out of place, because you are no longer in a pl/sql block
Re: compiled with errors [message #303257 is a reply to message #303120] Thu, 28 February 2008 09:35 Go to previous messageGo to next message
shree_z
Messages: 75
Registered: February 2008
Member
Hi all,

Thanks for all your inputs. I could fix the errors and run it sucessfully .

I didnt use the bind variable. I was using it to return a value to a shell script which calls this procedure.I think I could metion some out parameters inorder to return the values to the shell script.I have another question in this regard!

Now the shell script gives the IN parameters for the procedure .

The ksh is like this

Quote:
#!/bin/ksh
echo "=================================================="
echo "Beginning program " `date "+%m/%d/%y %H:%M:%S"` "\n"
echo "================================================="
datadir="/path/data";
archdir="/path/archive";

cd $datadir

ls -lrt A*.txt | awk '{print $9}' | while read fname

do

echo The file to be passed to importing program is $datadir/$fname

sqlplus -s apps/apps4you@GAMDEV << EOF

echo Connected

execute XX_Call_Import_Prgm('$fname','$datadir')

EOF

done

echo "=================================================="
exit



I want to return request_id,status and phase from the procedure to this shell script.

Also I want to include a condition in the shell script that is determined by the values returned from the pl/sql procedure

if phase is 'Normal' and status is 'Completed' then move the file to archdir(archive directory)

What modifcations should be made to the procedure and the shell script to achieve this?


Thanks again!!


[Updated on: Thu, 28 February 2008 09:50]

Report message to a moderator

Re: compiled with errors [message #303306 is a reply to message #303257] Thu, 28 February 2008 17:09 Go to previous message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
One way would be to have the procedure fail with an exception if the phase is not 'Normal' or status is not 'Completed', have SQL*Plus exit on failure using a WHENEVER SQLERROR clause, and have the shell script respond to SQL*Plus success/failure.

Or, you could have the procedure move the file using utl_file.frename.
Previous Topic: Foreign key constraint
Next Topic: to_date comparison problem
Goto Forum:
  


Current Time: Mon Dec 05 21:04:46 CST 2016

Total time taken to generate the page: 0.17680 seconds