PLS-00363: expression '0' cannot be used as an assignment target

[samdba]

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.

[LKBrwn_DBA]

You cannot use a shell variable as 'OUT' parameter.

[samdba]

Hi,

Thanks for the reply.

Is there a workaround for this?

Please let me know.

Thanks in Adv.

[LKBrwn_DBA]

Try this:

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

[samdba]

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

[LKBrwn_DBA]

What OS are you using?

Maybe you need to code it this way::

# -- 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

[samdba]

Hi again,

OS is AIX.

With the above, I am getting NULL in ReturnStatus. Anyway, I found out the simple workaround for this issue. I have added the following.

grep "Failed Clone" $LOG > check_fail_clone.txt
if [ -s check_fail_clone.txt ]; then
error_exit "$LINENO $ER Executing CLONE_MAIN - Clone package has failed. Check Log file for more details"
fi

This resolved the problem.

Thanks for your help.

[LKBrwn_DBA]

Another way is:

TargetInstance=""
SourceInstance=""
Rerun="N"
ReturnStatus=0

sqlplus -s /nolog << !EOF >> $LOG 2>&1
whenever sqlerror exit 99
connect apps/$PWD
var l_return_status number;
exec :l_return_status := 0;
declare
begin
  CLONE_SETUP.clone_main('$TargetInstance','$SourceInstance','$Rerun', :l_return_status);
  if :l_return_status = 0 then
    commit;
  else
    rollback;
  end if;
end;
/
exit :l_return_status
!EOF
ReturnStatus=$?
if [ ReturnStatus -eq 0 ]; then
  echo 'Rapid Clone succeeded, Commit performed'
else 
  echo 'Rapid Clone failed, Rollback performed'
fi
exit