-
Notifications
You must be signed in to change notification settings - Fork 9
/
oracle.sas
68 lines (58 loc) · 1.42 KB
/
oracle.sas
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
%let ora_user = rene;
%let ora_password = secret_garden;
%let ora_server = ora.test.renenyffenegger.ch;
options
sastrace = ',,,d'
sastraceloc = saslog;
%macro select_stmt;
proc sql feedback /* _method */;
select
upcase (obj.object_name ) as obj_name_up,
lowcase (obj.object_name ) as obj_name_lo,
obj.last_ddl_time as obj_ddl_tm ,
datepart(obj.last_ddl_time) as obj_ddl_day
from
tq84_ora.all_objects obj
where
datepart(obj.last_ddl_time) = today() or
lower (obj.owner ) ="tq84";
quit;
%mend select_stmt;
%macro lib_tq84_ora(additional);
libname tq84_ora
oracle
path = &ora_server
user = &ora_user
password = &ora_password
&additional
;
%mend lib_tq84_ora;
%lib_tq84_ora();
%select_stmt;
/*
Without the SQL_FUNCTIONS options, the where condition
is ignored when the SQL statement is passed to Oracle:
SELECT
"OBJECT_NAME",
"LAST_DDL_TIME",
"OWNER"
FROM
ALL_OBJECTS
*/
%lib_tq84_ora(%bquote(sql_functions = all));
%select_stmt;
/*
With the SQL_FUNCTIONS options, SAS is abl0e to translate
SAS' DATEPART to Oracle's TRUNC and to recognize Oracle's LOWER()
function:
SELECT
"OBJECT_NAME",
"LAST_DDL_TIME",
"OWNER"
FROM
ALL_OBJECTS
WHERE (
( TRUNC("LAST_DDL_TIME") =TO_DATE('01SEP2017','DDMONYYYY','NLS_DATE_LANGUAGE=American') ) OR
( LOWER("OWNER") = 'tq84' )
)
*/