Calculate the substitution variable length in oracle plsql

Question

I have simple plsql program, basically what I would like to do is to calculate the length of the name which is dynamic input to plsql and then loop based on the length.

When I give pre-defined value like v_name = 'Dex' it works but when I give v_name = &name it throws an error message saying Dex must be declared.

Appreciate if any one can shed light on this issue. Please find error and program details below:

Error report: ORA-06550: line 6, column 13: PLS-00201: identifier 'DEX' must be declared ORA-06550: line 6, column 3: PL/SQL: Statement ignored 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

PLsql Program:

declare
v_name varchar2(30);
v_loop_count number;
v_len number;
begin
  v_name := &name;
  v_loop_count := 0;
  v_len := length(v_name);
  for v_loop_count in 0 .. v_len-1
  loop
    dbms_output.put_line('Entered Name is :: '||v_name);
  end loop;
end;

You still need to put the quotes around the substitution variable or include them in your variable as Oracle is doing a straight substition in your script so the quotes need to wind up there around your string literal. — Michael Broughton
I disagree that this is a typographical error. The OP didn't forget the single-quotes; the OP didn't know that single quotes were required due to the nature of the substitution variables. I'm voting to reopen. — rgettman

rgettman rgettman · Accepted Answer · 2016-03-29T20:16:51

In SQL Developer (and in sqlplus), you can define substitution variables such as you're doing that start with &. But these substitution variables are unlike variables or literals in your code. These substitution variables will only be replaced textually, before the code gets parsed and executed.

If you have

v_name := &name;

Then the textual replacement will yield this code:

v_name := Dex;

Because you don't have a variable named DEX, you get the error you mentioned.

As mentioned in the comments, placing single-quotes around the substitution variable is the answer.

v_name := '&name';

This will get replaced with

v_name := 'Dex';

which is what you wanted -- the string value 'Dex'.

Calculate the substitution variable length in oracle plsql

1 Answers