SET DEFINE:-
'SET DEFINE' is useful when you are working with sql*plus.
There are three combinations of 'SET DEFINE' and those are:
1. Set define on.
2. Set define off.
3. Set define x.
1. SET DEFINE X: set define x specifies the prefix-character for substitution variables. The default is the ampersand (&).
set define &;
select * from dba_objects where object_name like '%&object_name%';
2. SET DEFINE ON: Turns on substitution variables.
set define on;
select '&hello' from dual;
If define is set to on and SQL*Plus finds the current substitution prefix, it asks for a string to be entered. In the following example, I entered: agarwal
Enter value for hello: this string was entered
old 1: select '&hello' from dual
new 1: select 'this string was entered' from dual
'AGARWAL'
---------------
agarwal
It might be annoying to see the following lines printed by SQL*Plus:
old 1: select '&hello' from dual
new 1: select 'this string was entered' from dual
This behaviour can be turned off by setting verify off.
3. SET DEFINE OFF
Turns off substitution variables.
set define off;
select '&hello' from dual;
'&HELL
----------------
&hello
'SET DEFINE' is useful when you are working with sql*plus.
There are three combinations of 'SET DEFINE' and those are:
1. Set define on.
2. Set define off.
3. Set define x.
1. SET DEFINE X: set define x specifies the prefix-character for substitution variables. The default is the ampersand (&).
set define &;
select * from dba_objects where object_name like '%&object_name%';
2. SET DEFINE ON: Turns on substitution variables.
set define on;
select '&hello' from dual;
If define is set to on and SQL*Plus finds the current substitution prefix, it asks for a string to be entered. In the following example, I entered: agarwal
Enter value for hello: this string was entered
old 1: select '&hello' from dual
new 1: select 'this string was entered' from dual
'AGARWAL'
---------------
agarwal
It might be annoying to see the following lines printed by SQL*Plus:
old 1: select '&hello' from dual
new 1: select 'this string was entered' from dual
This behaviour can be turned off by setting verify off.
3. SET DEFINE OFF
Turns off substitution variables.
set define off;
select '&hello' from dual;
'&HELL
----------------
&hello
Hello There,
ReplyDeleteThe challenge however, is we don’t yet know how it will be used making too hard to assess their value proposition and consequently value.
how to apply refcursor in package with multiple instead of
multiple cursors in oracle?
Give one example.
Thank you very much and will look for more postings from you.
Regards,
Nisha
Halo,
ReplyDeleteFully agree on #topic. We’re seeing a lot of projects tackle big complex problems but few seem to have taken into consideration and in particular reasons to adopt.
My table column type is clob. Field value contain double quotes
id,text_clob,date
1,"\"data1\",0,1,1,1,\"data2\"","2018-03-03 09:08:45"
can you kindly help loader query. i cant use replace in clob field.
It was cool to see your article pop up in my google search for the process yesterday. Great Guide.
Keep up the good work!
Thank you,
Hi There,
ReplyDeleteHot! That was HOT! Glued to the set-define-in-sql-with-examples.html your proficiency and style!
select name,value from v$parameters abc
minus
select name,value from v$parameters@test xyz
and xyz.value >abc.abc
Can anybody please help me above sql?
Follow my new blog if you interested in just tag along me in any social media platforms!
Many Thanks,
Preethi.