With a given scope do not use the variable more than once in PLSQL

It’s always good that we should not use a variable more than once with a given scope, as only one declaration of a variable is allowed in Oracle. If we write more variable then we are going to get PLSQL error i.e. PLS-00371 will be raised during runtime.

The Non-Compliant way of writing the code is as shown below :

SET SERVEROUTPUT ON

DECLARE
  my_var VARCHAR2(30) := 'input1';
  my_var VARCHAR2(30) := 'input2'; --
BEGIN
  DBMS_OUTPUT.PUT_LINE(my_var); 
END;
/

The below example will show how PLS-00371 error is raised during runtime :


SQL> SET SERVEROUTPUT ON

SQL> DECLARE
  2    my_var VARCHAR2(30) := 'input1';
  3    my_var VARCHAR2(30) := 'input2';
  4  BEGIN
  5    DBMS_OUTPUT.PUT_LINE(my_var);
  6  END;
  7  /

DECLARE
  my_var VARCHAR2(30) := 'input1';
  my_var VARCHAR2(30) := 'input2'; 
BEGIN
  DBMS_OUTPUT.PUT_LINE(my_var); 
END;

ORA-06550: line 5, column 24:
PLS-00371: at most one declaration for 'MY_VAR' is permitted
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored

We can re-factor the above code to compliant way as like below:


SET SERVEROUTPUT ON

DECLARE
        my_var  VARCHAR2(30) := 'input1';
        my_var1 VARCHAR2(30) := 'input2';
BEGIN
        dbms_output.put_line(my_var);
END;
/

The above code will display the proper result what we will be looking for:


SQL> DECLARE
2          my_var  VARCHAR2(30) := 'input1';
3          my_var1 VARCHAR2(30) := 'input2';
4  BEGIN
5          dbms_output.put_line(my_var);
6  END;
7  /

input1
PL/SQL procedure successfully completed

There could be various reason we need to make use of same variable name but still we can make use of this by using global variable.

For example as like below compliant way of writing the global variable without getting PLS-00371 error.

DECLARE
             -- Global variables
           my_var1 VARCHAR2(30) := 'input1';
           my_var2 VARCHAR2(30) := 'input2';

BEGIN
           dbms_output.put_line('Outer Variable my_var1: ' || my_var1);
           dbms_output.put_line('Outer Variable my_var2: ' || my_var2);

           DECLARE       -- Local variables
           my_var1 VARCHAR2(30) := 'input3';
           my_var2 VARCHAR2(30) := 'input4';
           BEGIN       dbms_output.put_line('Inner Variable my_var1: ' || my_var1);
              dbms_output.put_line('Inner Variable my_var2: ' || my_var2);
           END;
END;

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s