PLS-00363: expression ‘TO_NUMBER(SQLDEVBIND1Z_1)’ cannot be used as an assignment target

PLS-00363: expression cannot be used as an assignment target error occurs when it identifies an incorrect argument type in a stored procedure or function call’s parameter list. The IN parameter variable is a read-only parameter in the stored procedure. If the IN parameter variable is set to a value in a stored procedure, Oracle will throw this error. Similarly, the OUT parameter is used to return a stored procedure’s value to the PL/SQL code block that invoked it. If the OUT parameter is not allowed to assign a value, Oracle will throw an error PLS-00363: expression cannot be used as an assignment target.

The IN parameter is used to send a value to a stored procedure from a PL/SQL calling code block. In the stored procedure, the IN parameter acts as a read-only parameter. If a value is assigned to the IN parameter, the IN parameter variable cannot change that value. An error will be thrown by Oracle. The OUT parameter is used to return a value from the Stored Procedure to the PL/SQL code block that called it. The OUT parameter variable should allow a value from a stored procedure to be assigned. The value cannot be allocated to the OUT argument if it is passed with a literal or constant variable. The Oracle error PLS-00363: expression cannot be used as an assignment target will be thrown.



The Problem

If you create a stored procedure with an IN and OUT parameters and assigns a value to the IN parameter variable in the stored procedure code, the error PLS-00363: expression cannot be used as an assignment target will occur. If you pass a constant variable or a literal to an OUT parameter, the error will be shown.

CREATE OR REPLACE Procedure myprocedure
   ( nameIn IN varchar2, msgOut OUT varchar2 )

IS
    name varchar2(100);
    msg varchar2(100);
begin
    dbms_output.put_line('nameIn = ' || nameIn);
    dbms_output.put_line('name = ' || name);
    dbms_output.put_line('msg = ' || msg);
end;

declare 
    msgvar varchar2(100);
begin
    myprocedure('test', 1);
end;

Error

Error report -
ORA-06550: line 4, column 27:
PLS-00363: expression 'TO_NUMBER(SQLDEVBIND1Z_1)' cannot be used as an assignment target
ORA-06550: line 4, column 5:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:


Solution 1

The stored procedure won’t be able to assign or change the values if you use a constant variable in the IN or OUT parameter. There will be an error. Constant variables cannot be included in the IN and OUT parameters. If a variable is declared constant, it should be changed to a changeable variable. Replace the parameter with a normal variable in the stored procedure. This will resolve the error PLS-00363: expression cannot be used as an assignment target.

PL/SQL code

declare 
    msgvar constant varchar2(100);
begin
    myprocedure('test', msgvar);
end;

Error

Error report -
ORA-06550: line 2, column 5:
PLS-00322: declaration of a constant 'MSGVAR' must contain an initialization assignment
ORA-06550: line 2, column 12:
PL/SQL: Item ignored
ORA-06550: line 4, column 26:
PLS-00363: expression 'MSGVAR' cannot be used as an assignment target
ORA-06550: line 4, column 5:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.

Solution

declare 
    msgvar varchar2(100);
begin
    myprocedure('test', msgvar);
end;


Solution 2

When a literal value is set to an OUT parameter variable, the return value cannot be assigned to the literal value. The OUT parameter should have a variable to which a value received by the stored procedure can be allocated. If the OUT parameter contains a literal value, it should be changed to a variable.

PL/SQL code

begin
    myprocedure('test', 10);
end;

Error

Error starting at line : 49 in command -
begin
    myprocedure('test', 10);
end;
Error report -
ORA-06550: line 2, column 27:
PLS-00363: expression 'TO_NUMBER(SQLDEVBIND1Z_1)' cannot be used as an assignment target
ORA-06550: line 2, column 5:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Solution

declare 
    msgvar varchar2(100);
begin
    myprocedure('test', msgvar);
end;


Solution 3

If a stored procedure creates an IN parameter and then assigns a value to the IN parameter variable within the stored procedure, the stored procedure will fail to assign the value to the IN parameter. The IN parameter will have the same behaviour as a read-only variable. Within the stored procedure, the value cannot be altered. If you try to modify the value of the IN parameter variable, an error PLS-00363: expression cannot be used as an assignment target will occur.

PL/SQL code

CREATE OR REPLACE Procedure myprocedure
   ( nameIn IN varchar2, msgOut OUT varchar2 )

IS
    name varchar2(100);
    msg varchar2(100);
begin
    nameIn:='t';
    dbms_output.put_line('nameIn = ' || nameIn);
    dbms_output.put_line('name = ' || name);
    dbms_output.put_line('msg = ' || msg);
end;

Error

Procedure MYPROCEDURE compiled

LINE/COL  ERROR
--------- -------------------------------------------------------------
8/5       PL/SQL: Statement ignored
8/5       PLS-00363: expression 'NAMEIN' cannot be used as an assignment target
Errors: check compiler log

Solution

CREATE OR REPLACE Procedure myprocedure
   ( nameIn IN varchar2, msgOut OUT varchar2 )

IS
    name varchar2(100);
    msg varchar2(100);
begin
    --nameIn:='t';
    dbms_output.put_line('nameIn = ' || nameIn);
    dbms_output.put_line('name = ' || name);
    dbms_output.put_line('msg = ' || msg);
end;


Solution 4

The IN parameter of a stored procedure cannot be altered if it is used as the OUT parameter of another stored procedure. The error PLS-00363: expression cannot be used as an assignment target is going to be thrown.

PL/SQL code

CREATE OR REPLACE Procedure myprocedure
   ( nameIn IN varchar2, msgOut OUT varchar2 )

IS
    name varchar2(100);
    msg varchar2(100);
begin
    myprocedure('test',nameIn);
end;

Error

Procedure MYPROCEDURE compiled

LINE/COL  ERROR
--------- -------------------------------------------------------------
8/5       PL/SQL: Statement ignored
8/24      PLS-00363: expression 'NAMEIN' cannot be used as an assignment target
Errors: check compiler log

Solution

CREATE OR REPLACE Procedure myprocedure
   ( nameIn IN varchar2, msgOut OUT varchar2 )

IS
    name varchar2(100);
    msg varchar2(100);
begin
    myprocedure('test',msg);
end;



Leave a Reply