0

Basically, I don't want to be asked about the value like this::

SQL> select &test from dual;
Enter value for test:

I want only declare the &test along the script, something like it:

&test varchar2(100):= 'some value'; --of course, this don't work.
Potter
  • 434
  • 8
  • 21
  • As per the linked duplicate, `DEFINE test = 'some value'` then `SELECT &test FROM DUAL`. Note: this is a substitution variable that is processed by the SQL client, i.e. SQL*Plus, and (typically) not by the database and only a few client applications support substitution variables. – MT0 Mar 17 '22 at 20:42

1 Answers1

1

Execute

SQL> set define off

before running your code.

SQL> select '&test' from dual;

'&TES
-----
&test

SQL>

If you want to "declare" it, then use var:

SQL> var test varchar2(200);
SQL> exec :test := 'some value';

PL/SQL procedure successfully completed.

SQL> print test

TEST
----------------------------------------------------------------------------------------------------
some value

SQL>

In dynamic SQL: I won't lock anyone, but - I'll change my password.

SQL> connect scott/tiger
Connected.
SQL> var test varchar2(200);
SQL> exec :test := 'lion';

PL/SQL procedure successfully completed.

SQL> print test

TEST
----------------------------------------------------------------------------------------------------
lion

SQL> begin
  2    execute immediate 'alter user scott identified by ' || :test;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> connect scott/tiger
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> connect scott/lion
Connected.
SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • But how I declare &test? – Potter Mar 17 '22 at 20:28
  • "Declare" it? If it contains ampersand, it is a substitution variable. If you don't want it to be, you SET DEFINE OFF, and then it becomes an ordinary **string** so you enclose it into single quotes (see demo in the answer). – Littlefoot Mar 17 '22 at 20:30
  • No, isn't what I look for, &test need some value like in my example, I don't what the literal &test. I only don't want a interactive script asking for values. – Potter Mar 17 '22 at 20:30
  • Sorry, I misread the question; have a look, again :) – Littlefoot Mar 17 '22 at 20:32
  • not work inside dynamic SQL, I've tried already. only &var works. I have PLSQL code with: EXECUTE IMMEDIATE 'alter user username_here account lock' The dynamic SQL don't get the :var command. Only &var works. – Potter Mar 17 '22 at 20:36
  • It works (if you use it correctly). See another demo. – Littlefoot Mar 17 '22 at 20:45
  • Great, worked after separate the :test from strings ' ' with ||. Thanks. unfortunately the question was closed by the community, maybe I won't specific with the question, But many thanks. – Potter Mar 17 '22 at 20:50
  • 1
    You're welcome, I'm glad if it helped. – Littlefoot Mar 17 '22 at 20:51