CODEWITHSUNDEEP
oracle-databaseplsqloracle-sqldeveloper
zappee
zappee

Reputation: 22726

Change Oracle prompt to variable

I have the following PLSQL code and I would like to change the prompt to variable but I am not sure about the syntax. Unfortunately Google search did not help.

PLSQL code snippet:

set define off
set define on
set define $
set serveroutput on

spool abc.log

accept schema_owner prompt "schema owner: "
accept tbsp prompt "tablespace: "
alter session set current_schema=$schema_owner;
...

I would like to have something like this:

...
schema_owner := "apple"
tbsp := "apple_tbl"
...

Could you please help me in?

Upvotes: 0

Views: 370

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31676

I think you are looking to use substitution variables.

set define off
set define on
set define $
set serveroutput on
spool abc.log
DEFINE schema_owner = 'HR'
DEFINE tbsp = 'DATA'

alter session set current_schema = $schema_owner;


SQL>         alter session set current_schema = $schema_owner;
old   1:     alter session set current_schema = $schema_owner
new   1:     alter session set current_schema = HR

Session altered.

Upvotes: 1

Related Questions