-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathto_csv.sql
More file actions
81 lines (57 loc) · 1.67 KB
/
to_csv.sql
File metadata and controls
81 lines (57 loc) · 1.67 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
71
72
73
74
75
76
77
78
79
80
81
--
-- SQL> @to_csv file_with_sqlstatement.sql c:\temp\result.csv
--
-- Executue an SQL-query (select statement) that is stored in
-- the file with the name passed as the first argument (file_with_sqlstatement.sql)
-- and store the result as csv (with ; as seperators)
--
-- Additionally, create a header line with the column names of the
-- result set.
--
-- Needs ./spool.sql and ./spool_off.sql
--
-- TODO: Currently, the SQL statement must not have an ending /
--
--
--
define sqlFile=&1
define csvFile=&2
@spool &csvFile
declare
sql_stmt varchar2(32000) :=
q'{
@&sqlFile
}';
cur number;
cntCols number;
cols dbms_sql.desc_tab;
header varchar2(32000);
selected_columns varchar2(32000);
result sys_refcursor;
v varchar2(5000);
begin
--Remove possible trailing semicolon «;» in sqlFile.
sql_stmt := regexp_replace(sql_stmt, ';\s*$', '');
cur := dbms_sql.open_cursor;
dbms_sql.parse(cur, sql_stmt, dbms_sql.native);
dbms_sql.describe_columns(cur, cntCols, cols);
for col in 1 .. cntCols loop
if selected_columns is not null then
selected_columns := selected_columns || '||'',''||';
header := header || ',';
end if;
header := header || cols(col).col_name;
selected_columns := selected_columns || '"' || cols(col).col_name || '"';
end loop;
dbms_sql.close_cursor(cur);
dbms_output.put_line(header);
open result for 'select ' || selected_columns || ' v from (' || sql_stmt || ')';
loop
fetch result into v;
exit when result%notfound;
dbms_output.put_line(v);
end loop;
close result;
end;
/
@spool_off