-
PLS-00363: expression '0' cannot be used as an assignment target
Hi all,
I have a Unix shell script something like this.
TargetInstance=""
SourceInstance=""
Rerun="N"
ReturnStatus=0
sqlplus -s /nolog << !EOF >> $LOG 2>&1
set serveroutput on size 1000000
whenever sqlerror exit sql.sqlcode
connect apps/$PWD
declare
l_return_status number := 0;
begin
CLONE_SETUP.clone_main('$TargetInstance','$SourceInstance','$Rerun',$ReturnStatus);
l_return_status := $ReturnStatus;
dbms_output.put_line('Return Status : '||l_return_status);
if l_return_status = 0 then
commit;
dbms_output.put_line('Rapid Clone succeeded, Commit performed');
else
rollback;
dbms_output.put_line('Rapid Clone failed, Rollback performed');
end if;
end;
/
exit
!EOF
if [ $ReturnStatus -ne 0 ]; then
exit
fi
When I execute unix script, I get following error
CLONE_SETUP.clone_main('ohfpch','ohfrpd','N',0);
*
ERROR at line 4:
ORA-06550: line 4, column 51:
PLS-00363: expression '0' cannot be used as an assignment target
ORA-06550: line 4, column 2:
PL/SQL: Statement ignored
I want to pass the unix parameter ReturnStatus to the procedure CLONE_SETUP.clone_main. This procedure has parameters (p_target_instance IN varchar2, p_source_instance IN varchar2, p_rerun IN varchar2,
p_return_status IN OUT NUMBER). After this procedure gets executed, I want to return the value of p_return_status back to the Unix script and into the unix variable ReturnStatus which then I assign local pl/sql variable l_return_code which I use to decide whether to commit or rollback. Also, later I use ReturnStatus to decide if I want to exit from the Unix script execution alltogether.
How do I achieve this?
Thanks in advance.
Last edited by samdba; 11-18-2008 at 10:44 AM.
Sam
------------------------
To handle yourself, use your head. To handle others, use your heart
-
Bad boy, bad boy...
You cannot use a shell variable as 'OUT' parameter.
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
Hi,
Thanks for the reply.
Is there a workaround for this?
Please let me know.
Thanks in Adv.
Sam
------------------------
To handle yourself, use your head. To handle others, use your heart
-
Not tested, but...
Try this:
Code:
TargetInstance=""
SourceInstance=""
Rerun="N"
ReturnStatus=`
sqlplus -s /nolog << !EOF >> $LOG 2>&1
set serveroutput on size 1000000
whenever sqlerror exit sql.sqlcode
connect apps/$PWD
declare
l_return_status number := 0;
begin
CLONE_SETUP.clone_main('$TargetInstance','$SourceInstance','$Rerun',l_return_status);
dbms_output.put_line(l_return_status);
if l_return_status = 0 then
commit;
else
rollback;
end if;
end;
/
exit
!EOF`
if [ $ReturnStatus -eq 0 ]; then
echo 'Rapid Clone succeeded, Commit performed'
else
echo 'Rapid Clone failed, Rollback performed'
fi
exit
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
Hi,
Thanks again!
Now, I get error "test: argument expected" on the line where I have
if [ $ReturnStatus -eq 0 ]; then
Also, it returns NULL value for ReturnStatus variable if I do echo "ReturnStatus = " $ReturnStatus right before the above if condition.
Do I need to make any change in the pl/sql procedure in the database to make the Unix script to run?
Thanks,
Samir
Sam
------------------------
To handle yourself, use your head. To handle others, use your heart
-
Not null...
What OS are you using?
Maybe you need to code it this way::
Code:
# -- Etc...
typeset -i ReturnStatus=`
sqlplus -s /nolog << !EOF
-- etc --
!EOF`
echo "ReturnStatus=${ReturnStatus}"
# -- Try these:
if [ ReturnStatus -eq 0 ]; then # or:
if [[ "$ReturnStatus" = "0" ]]; then
# -- Etc...
Also make sure CLONE_SETUP.clone_main returns a value!
PS: Notice I removed the redirection to the $LOG from the sqlplus statement
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|