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
82
83
84
85
86
87
88
89
90
| FUNCTION get_table_columns(name_of_table IN varchar2, separator IN char, schema_name IN varchar2) RETURN varchar2
IS
txt_retour varchar2(2000);
amount number(3);
nb_cols number(3);
col_traitee ALL_TAB_COLUMNS.COLUMN_NAME%TYPE;
CURSOR colonnes(tab_name ALL_TAB_COLUMNS.TABLE_NAME%TYPE, sch_name varchar2) IS
SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE UPPER(TABLE_NAME)=UPPER(tab_name) AND UPPER(OWNER)=UPPER(sch_name);
BEGIN
-- Count the number of columns in the table
SELECT COUNT(*) INTO nb_cols FROM ALL_TAB_COLUMNS WHERE UPPER(TABLE_NAME)=UPPER(name_of_table) AND UPPER(OWNER)=UPPER(schema_name);
IF (nb_cols > 0) THEN
-- Browse the cursor in order to obtain the name of the columns
FOR colonne IN colonnes(name_of_table, schema_name) LOOP
col_traitee := CONCAT(colonne.COLUMN_NAME, separator);
txt_retour := txt_retour || col_traitee;
END LOOP;
txt_retour := substr(txt_retour, 0, (LENGTH(txt_retour)-1));
-- Return the columns
RETURN txt_retour;
ELSE
RAISE empty_columns;
END IF;
END;
PROCEDURE export_data(name_of_table IN varchar2, rep IN varchar2, fname IN varchar2, separator IN char, schema_name IN varchar2 DEFAULT USER) IS
data_p_cursor integer;
column_value varchar2(255);
nb_enreg integer;
nb_columns number;
configured_separator char;
i number(3);
backup_file utl_file.file_type;
CURSOR table_columns (tab_name varchar2, sch_name varchar2) IS
SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE UPPER(table_name)=UPPER(tab_name) AND UPPER(OWNER)=UPPER(sch_name);
BEGIN
-- Initialize the file used to store our data
backup_file := UTL_FILE.FOPEN(rep, fname, 'a');
-- Initialize our dynamic cursor with the DBMS_SQL package
data_p_cursor := DBMS_SQL.OPEN_CURSOR;
-- Configure our cursor with a SQL request (parenthesis are only there for visibility)
DBMS_SQL.PARSE(data_p_cursor, ('SELECT ' || get_table_columns(name_of_table, ',', schema_name) || ' FROM ' || name_of_table), DBMS_SQL.native);
-- Count number of columns
SELECT COUNT(*) AS nb_columns INTO nb_columns FROM ALL_TAB_COLUMNS WHERE UPPER(table_name)=UPPER(name_of_table) AND UPPER(OWNER)=UPPER(schema_name);
-- Browse the table to find its columns' name
FOR i IN 1 .. nb_columns LOOP
DBMS_SQL.DEFINE_COLUMN(data_p_cursor, i, column_value, 255);
END LOOP;
-- Define the first column of our table (for the next steps)
DBMS_SQL.DEFINE_COLUMN(data_p_cursor, 1, column_value, 255);
-- Execute the cursor and collect the data
nb_enreg := DBMS_SQL.EXECUTE(data_p_cursor);
-- Fetch the data and order them by column
LOOP
EXIT WHEN (DBMS_SQL.FETCH_ROWS(data_p_cursor) <= 0);
-- Little trick to avoid the separator at the end of the row
-- We hide it by placing an empty separator at the begining and then placing the real separator at the end of the previous column
-- Example ( separator = ';' ) : First column = NUMERO Second column = ;NAME ...
-- Finally, the row is presented like 'NUMERO;NAME;BIRTHDATE;EMAIL', so without any separator at the end
configured_separator := '';
FOR i IN 1 .. nb_columns LOOP
DBMS_SQL.COLUMN_VALUE(data_p_cursor, i, column_value);
UTL_FILE.PUT(backup_file, configured_separator || '"' || column_value || '"');
configured_separator := separator;
END LOOP;
-- Add a next line instruction to the file
UTL_FILE.NEW_LINE(backup_file);
END LOOP;
-- Add a new line just for the file's aesthetics
UTL_FILE.NEW_LINE(backup_file);
-- Close the dynamic cursor, we don't need it anymore
DBMS_SQL.CLOSE_CURSOR(data_p_cursor);
-- Close the file, finally, its work has finished
UTL_FILE.FCLOSE(backup_file);
END; |
