[Générique] Passer un type en paramètre [Résolu]

[WebManiaK]

Bonjour,

Je travaille actuellement sur un projet d'export de données (tuples) en PL/SQL. Mon projet est de faire une procédure PL/SQL qui accepte en paramètre un nom de table et qui exporte ses données (par exemple dans un fichier, mais ce n'est pas le problème).

Exemple de signature:

Code :

PROCEDURE exportData(tableName IN varchar2, schemaName IN varchar2);

Mon problème est de gérer la généricité, soit que je puisse lui passer n'importe quelle nom de table en paramètre, la procédure sera capable d'aller me rechercher les données.

Même si j'ai pu partiellement avancer en utilisant un pointeur de curseur (REF CURSOR), je n'ai malheureusement pas encore trouvé de moyen de récupérer un type de table de manière dynamique.
Auriez-vous une astuce pour ce faire ?

Merci d'avance pour vos propositions

P.-S: voici un exemple de code que j'ai déjà réalisé

Code :

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
DECLARE
	sql_request varchar2(255);
	my_gen_table varchar2(100);
    data_to_return clob;
	returned_line OBJECT;
 
    TYPE t_cursor_result IS REF CURSOR;
    data_cursor t_cursor_result;
BEGIN	
	my_gen_table := 'EMP';
	DBMS_LOB.CREATETEMPORARY(data_to_return, true);
    DBMS_LOB.OPEN(data_to_return, DBMS_LOB.lob_readwrite);
 
	sql_request := 'SELECT * FROM '||my_gen_table;
	OPEN data_cursor FOR sql_request;
 
	LOOP
		FETCH data_cursor INTO returned_line;
			DBMS_LOB.WRITEAPPEND(data_to_return, LENGTH(), carrier_return);
		EXIT WHEN returned_line%NotFound;
    END LOOP;
 
	CLOSE data_cursor;
	DBMS_OUTPUT.PUT_LINE(data_to_return);
	DBMS_LOB.CLOSE(data_to_return);
	DBMS_LOB.FREETEMPORARY(data_to_return);
END;

Naturellement, le problème survient en ligne 18 lors du FETCH...
La variable "returned_line" affichée ici est celle qui contiendra chaque ligne retournée par la base de donnée et, par conséquent, celle qui m'intéresse le plus et celle que je suis actuellement incapable de paramétrer correctement.

[ojo77]

Une solution pourrait être d'écrire un sql dynamique qui va pouvoir manipuler une variable de type table%rowtype et exécuter ce SQL dans un execute immediate.

MAIS
C'est très difficile à débugger et à maintenir.
Il y a beaucoup plus simple pour exorter dans un fichier plat les données d'une table.

[mnitu]

How Can I unload data to a flat file

[WebManiaK]

Bonjour,

Merci à vous deux pour vos solutions qui m'ont toutes deux été d'une grande aide. J'ai donc choisi la voie du SQL dynamique avec le package DBMS_SQL. J'avoue qu'il est d'une grande utilité lorsqu'on sait l'utiliser en ce qui concerne la généricité.

Pour celles et ceux qui pourraient être confrontés au même problème que moi, voici comment j'ai procédé pour exporter les données d'une table. Notez que j'ai intégré ici la sauvegarde dans un fichier placé dans un répertoire (directory Oracle). Il faut bien entendu procéder à la création de celui-ci et posséder les droits nécessaires pour l'utiliser.

J'utilise ici le format d'export CSV.

Code :

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;

J'espère que ceci pourra apporter l'aide nécessaire aux personnes qui pourraient être confronté au même genre de problème que moi.

Cordialement,