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.

>Solution :

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>