DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

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

  1. #1
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620

    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

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Angry 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

  3. #3
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    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

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Wink 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

  5. #5
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    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

  6. #6
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool 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

  7. #7
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    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.
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  8. #8
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Talking Whenever?

    Another way is:
    Code:
    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
    "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
  •  


Click Here to Expand Forum to Full Width