-
Notifications
You must be signed in to change notification settings - Fork 13
Expand file tree
/
Copy pathparser.sql
More file actions
70 lines (60 loc) · 3.16 KB
/
parser.sql
File metadata and controls
70 lines (60 loc) · 3.16 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
/*
Parameter parser
This script transform all the parameters (&1-&16) into SQL*Plus variables
Created by Eduardo Claro, 2018-3-15
Last changes on 2021-11-12
Usage
@parser PAR=value PAR=value ...
You can pass up to 16 parameters in no specific order.
To pass parameters with spaces or other special characters, use "" and '' like in the examples below.
In order for this to work properly, the number variables should be pre-defined as NULL in the login.sql.
The general format to pass the parameters is PAR=value.
If caller script have defined a default variable, then you can call it just passing the parameter value (see examples).
If the first parameter is HELP, it creates a variable HELP=Y
I always issue "set term off" before running this script, and "set term on" after it, to not show the results in the terminal.
Examples:
@parser VAR1='X' "VAR2='A B C'" VAR3=
this would result in:
DEFINE VAR1 = "X"
DEFINE VAR2 = "A B C"
@parser ABC123 XYZ
this would result in:
DEFINE &DEFAULTVAR1 = "ABC123"
DEFINE &DEFAULTVAR2 = "XYZ"
*/
set term off
define HELP=""
store set set.tmp replace
set feed off ver off echo off head off timing off
spool parser.tmp REPLACE
select
case
when upper(q'!&1!') in ('HELP','HLP') then 'define HELP=Y'
when q'!&1!' not like '%=%' then 'define &DEFAULTVAR1=' || q'!&1!'
when q'!&1!' is not null then regexp_replace(q'!define &1."!','=','="',1,1) end || chr(10) ||
case
when q'!&2!' not like '%=%' then 'define &DEFAULTVAR2=' || q'!&2!'
when q'!&2!' is not null then regexp_replace(q'!define &2."!','=','="',1,1) end || chr(10) ||
case
when q'!&3!' not like '%=%' then 'define &DEFAULTVAR3=' || q'!&3!'
when q'!&3!' is not null then regexp_replace(q'!define &3."!','=','="',1,1) end || chr(10) ||
case
when q'!&4!' not like '%=%' then 'define &DEFAULTVAR4=' || q'!&4!'
when q'!&4!' is not null then regexp_replace(q'!define &4."!','=','="',1,1) end || chr(10) ||
case when q'!&5!' is not null then regexp_replace(q'!define &5."!','=','="',1,1) end || chr(10) ||
case when q'!&6!' is not null then regexp_replace(q'!define &6."!','=','="',1,1) end || chr(10) ||
case when q'!&7!' is not null then regexp_replace(q'!define &7."!','=','="',1,1) end || chr(10) ||
case when q'!&8!' is not null then regexp_replace(q'!define &8."!','=','="',1,1) end || chr(10) ||
case when q'!&9!' is not null then regexp_replace(q'!define &9."!','=','="',1,1) end || chr(10) ||
case when q'!&10!' is not null then regexp_replace(q'!define &10."!','=','="',1,1) end || chr(10) ||
case when q'!&11!' is not null then regexp_replace(q'!define &11."!','=','="',1,1) end || chr(10) ||
case when q'!&12!' is not null then regexp_replace(q'!define &12."!','=','="',1,1) end || chr(10) ||
case when q'!&13!' is not null then regexp_replace(q'!define &13."!','=','="',1,1) end || chr(10) ||
case when q'!&14!' is not null then regexp_replace(q'!define &14."!','=','="',1,1) end || chr(10) ||
case when q'!&15!' is not null then regexp_replace(q'!define &15."!','=','="',1,1) end || chr(10) ||
case when q'!&16!' is not null then regexp_replace(q'!define &16."!','=','="',1,1) end statement
from dual;
spool off
@parser.tmp
@set.tmp
set term on