Create package Vs Create type [Résolu]

[leftyy]

Bonjour à tous,

je suis actuellement en plein dilemme et à la recherche de quelqu'un pour m'éclairer.

Mon problème est assez simple, j'ai un package A qui utilise une variable de type table (mon_tableau). j'ai une fonction dans un deuxième package B qui retourne comme valeur un tableau du type A.mon_tableau.

Mon but est de ne plus rendre dépendant le package B du package A pour que d'autre package puissent utilisé ma fonction.

j'ai donc envisagé deux solutions :
- créer un package C avec juste une partie spécification qui contiendrait mon type tableau
- créer un type directement en base de données

La notion de type est un peu opaque pour moi. sur le papier ca à l'air très sympa. est ce que quelqu'un a déjà utilisé cette fonction et peut me dire si c'est aussi intéressant que ca en a l'air ou si ca a des mauvais cotés?

Merci de vos réponses

Leftyy

[mnitu]

Je ne vois pas bien ce qui empêcherais un autre package d’utiliser la fonction en question.
Si le type tableau est défini dans la base il devient visible pour le moteur SQL.

[leftyy]

Quand on crée un type objet de type tableau genre :

Code :

1
2
3
4
5
6
7
8
9
10
 
CREATE OR REPLACE TYPE type_record AS OBJECT
    ( seq          NUMBER (10),
	  table_name   VARCHAR2 (50)
    ) ;
/
 
CREATE OR REPLACE TYPE t_tab_res_test
AS TABLE OF type_record;
/

est ce que ca se manipule ensuite en PL/SQL comme une variable de type tableau PL/SQL classique?

[mnitu]

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
SQL> SET serveroutput ON
SQL> 
SQL> declare
  2    l_tab   t_tab_res_test := t_tab_res_test();
  3  Begin
  4    l_tab.extend;
  5    l_tab(1) := type_record(1,'TOTO');
  6    l_tab.extend;
  7    l_tab(2) := type_record(2,'TiTi');
  8    FOR i IN 1..l_tab.Count Loop
  9      Dbms_Output.put_line(To_Char(l_tab(i).seq)||l_tab(i).table_name);
 10    End Loop;
 11  End;
 12  /
 
1TOTO
2TiTi
 
PL/SQL procedure successfully completed

[leftyy]

Ok ca a l'air pas mal.

j'imagine que le .extend c'est pour rajouter une ligne

par contre la question subsidiaire à 1 000 000€, il y a un moyen de combiner mon type avec le bulk collect dans ce style la :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
	FUNCTION ma_fonction
	RETURN t_tab_res_test
	IS
 
		CURSOR c_tab  IS
		SELECT sec , table_name
                FROM ma_table
 
		v_tab_res t_tab_res_test := t_tab_res_test();
 
	BEGIN
		OPEN c_tab;
		v_tab_res.extend;
		FETCH c_tab
		BULK COLLECT INTO v_tab_res;
		CLOSE c_tab;
 
		RETURN v_tab_res;
	END ma_fonction;

[mnitu]

Lisez SELECT INTO Statement with BULK COLLECT Clause pour exemple.
Dans ce cas nul besoin d'initialiser la collection ou d'extend. Mais, assurez-vous que vous comprenez bien l'implication de la lecture d'une table entière dans la mémoire.

[leftyy]

Ca va pour l'utilisation mémoire je gère. l'exemple que j'ai mis est une version simplifiée dans mon cas j'ai une requête plus complexe qui ne retourne que 2-3 lignes.

par contre j'ai essayé de mettre en place le bulk collect dans mon code et j'ai toujours la même erreur

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
	FUNCTION ma_fonction
	RETURN t_tab_res_test
	IS
 
		CURSOR c_tab  IS
		SELECT sec , table_name
                FROM ma_table
 
		v_tab_res t_tab_res_test;
 
	BEGIN
		OPEN c_tab;
		FETCH c_tab
		BULK COLLECT INTO v_tab_res;
		CLOSE c_tab;
 
		RETURN v_tab_res;
	END ma_fonction;

Avec cet exemple de code j'ai une erreur à la compilation qui me dit :

Code :

1
2
3
 
46/21          PLS-00386: non-concordance de type à 'V_TAB_RES' entre curseur FETCH et VARIABLES INTO
45/3           PL/SQL: SQL Statement ignored

dans ton exemple tu insérais des type_record dans le type tableau mais de ce que je comprend de l'erreur, ca doit insérer des types de données mais pas un record.

est ce que c'est possible de faire fonctionner dans l'état actuel? ou alors faut que je bricole quelque chose dans mes types...

[SheikYerbouti]

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
 
SQL> SET serveroutput ON
SQL> SELECT * FROM fd_test1 WHERE ROWNUM <= 1 ;
        C1
----------
C2
--------------------------------------------------------------------------------
         1
value_1
 
SQL>  CREATE OR REPLACE TYPE type_tab_number AS TABLE OF NUMBER ;
  2  /
Type créé.
SQL>  CREATE OR REPLACE TYPE type_tab_char AS TABLE OF VARCHAR2(100) ;
  2  /
Type créé.
SQL> DECLARE
  2    t1  type_tab_number ;
  3    t2  type_tab_char ;
  4  BEGIN
  5    SELECT c1,c2
  6    BULK COLLECT INTO t1,t2
  7    FROM fd_test1 ;
  8    Dbms_Output.put_line( t1(1) || ',' || t2(1) ) ;
  9  END;
 10  /
1,value_1
Procédure PL/SQL terminée avec succès.
SQL>

[leftyy]

Ta réponse me confirme dans ce que j'étais en train de tester

du coup on peut pas faire un objet type table de record et y faire du bulk collect.

il faut soit passer par une boucle classique avec un traitement ligne par ligne ou alors découper le tableau en créant un type tableau par colonne.

c'est bien ca?

[SheikYerbouti]

BULK COLLECT fonctionne bien avec des tableaux de RECORDS, mais ces derniers ne peuvent pas être stockés en base, donc la technique de la table PL/SQL déclarée dans une spécification de package est une bonne solution:

Code :

1
2
3
4
5
 
CREATE OR REPLACE PACKAGE PKG_TABLE_PLSQL
IS
  TYPE Typ_Tab_Rec_Emp IS TABLE OF EMP%ROWTYPE INDEX BY BINARY_INTEGER ;
END;

Que vous pouvez utiliser n'importe ou dans votre code PL/SQL

Code :

1
2
3
4
5
6
7
8
9
 
Declare
  tt PKG_TABLE_PLSQL.Typ_Tab_Rec_Emp;
Begin
  SELECT  *
  BULK COLLECT
  INTO  tt
  FROM  EMP;
End;

[leftyy]

Ok.

Je voulais tester un peu les CREATE TYPE voir ce que ca avait dans le ventre comparé à un type table créé localement.
au final mon tableau va être utilisé par plusieurs utilisateurs dans plusieurs procédures du coup je cherchait une solution pour "mutualiser" mon tableau.

En tout cas merci pour vos réponses j'y vois beaucoup plus clair

Bonne continuation

[remi4444]

Envoyé par leftyy

du coup on peut pas faire un objet type table de record et y faire du bulk collect.

Si en fait un peu mais faut peut etre faire des tests de perfs sur les diverses solutions (d'ailleurs si tu les fais, les resultats m'interessent ):

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
CREATE OR REPLACE TYPE type_record AS OBJECT
    ( seq          NUMBER (10),
      table_name   VARCHAR2 (50)
    ) ;
/
 
CREATE OR REPLACE TYPE t_tab_res_test
AS TABLE OF type_record;
/
 
SET serveroutput ON 10000
 
declare
   l_tab   t_tab_res_test := t_tab_res_test();
Begin
    SELECT 
      type_record(length(TABLE_NAME),TABLE_NAME)
      bulk collect INTO l_tab 
    FROM ALL_TABLES WHERE rownum < 10;
    FOR i IN 1..l_tab.Count Loop
      Dbms_Output.put_line(To_Char(l_tab(i).seq)||' '||l_tab(i).table_name);
    End Loop;
End;
/

[leftyy]

Pas mal cette solution je vais essayer de la tester. Pour ce qui est des perfs je bosse sur des petites bases avec des gros serveur donc je penses pas que je pourrais obtenir des résultats significatifs. mais bon si j'ai un peu de temps cet après midi je vais me créer un environnement et essayer de tester ca

[remi4444]

Y'a un truc qu'il faut prendre en compte qui me pousse moi à plutot utiliser des type globaux, c'est la portée des variables tableau.

Lorsque tu déclares une table de manière globale, tu peux l'utiliser comme table dans une requête, et ce que ce soit dans un autre package ou directement.

Ceci n'est pas le cas pour un type d'un package, enfin à ma connaissance, si quelqu'un a une solution je suis preneur!

Code :

1
2
3
4
5
6
7
8
9
CREATE OR REPLACE TYPE type_record AS OBJECT
    ( seq          NUMBER (10),
      table_name   VARCHAR2 (50)
    ) ;
/
 
CREATE OR REPLACE TYPE t_tab_res_test
AS TABLE OF type_record;
/

Code :

1
2
3
4
5
6
7
8
9
10
11
CREATE OR REPLACE package pk_test_table IS
type t_rec_local IS record  
    ( seq          NUMBER (10),
      table_name   VARCHAR2 (50)) ;
 
type t_tab_local IS TABLE of t_rec_local;
 
FUNCTION f_tab_global RETURN t_tab_res_test;
FUNCTION f_tab_local RETURN t_tab_local;
end pk_test_table;
/

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
CREATE OR REPLACE package body pk_test_table IS
 
FUNCTION f_tab_global RETURN t_tab_res_test IS
 l_tab t_tab_res_test := t_tab_res_test();
BEGIN
    SELECT 
      type_record(length(TABLE_NAME),TABLE_NAME)
      bulk collect INTO l_tab 
    FROM ALL_TABLES WHERE rownum < 10;
    RETURN l_tab;
END f_tab_global;
 
FUNCTION f_tab_local RETURN t_tab_local IS
 l_tab t_tab_local;
BEGIN
    SELECT 
      length(TABLE_NAME),TABLE_NAME
      bulk collect INTO l_tab 
    FROM ALL_TABLES WHERE rownum < 10;
    RETURN l_tab;
END f_tab_local;
 
end pk_test_table;
/

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
  1  declare
  2  tt t_tab_res_test;
  3  nn number;
  4  begin
  5   tt := pk_test_table.f_tab_global;
  6   SELECT count(*) INTO nn FROM TABLE(tt);
  7* END;
SQL> /
 
Procédure PL/SQL terminée avec succès.
 
  1  declare
  2  tt pk_test_table.t_tab_local;
  3  nn number;
  4  begin
  5   tt := pk_test_table.f_tab_local;
  6   SELECT count(*) INTO nn FROM TABLE(tt);
  7* END;
SQL> /
declare
*
ERREUR à la ligne 1 :
ORA-21700: l'objet n'existe pas ou va être supprimé
ORA-06512: à ligne 6

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SQL> SELECT * FROM TABLE(pk_test_table.f_tab_global);
 
       SEQ TABLE_NAME
---------- --------------------------------------------------
         4 DUAL
        20 SYSTEM_PRIVILEGE_MAP
        19 TABLE_PRIVILEGE_MAP
        21 STMT_AUDIT_OPTION_MAP
        13 AUDIT_ACTIONS
         8 PSTUBTBL
        22 WRI$_ADV_ASA_RECO_DATA
        11 PLAN_TABLE$
         3 OL$
 
SQL> SELECT * FROM TABLE(pk_test_table.f_tab_local);
SELECT * FROM TABLE(pk_test_table.f_tab_local)
                    *
ERREUR à la ligne 1 :
ORA-00902: type de données non valide

[mnitu]

Envoyé par mnitu

...Si le type tableau est défini dans la base il devient visible pour le moteur SQL.

En termes des performance passer par les objets coûte plus cher.

[leftyy]

Bonjour tout le monde

Un petit UP de ce topic pour ceux que ca interresse je suis tombé sur un article du site asktom.oracle.com sur le sujet des objets PL/SQL et des types (en Anglais par contre dsl ).

http://asktom.oracle.com/pls/asktom/f?p=100:11:0:::11_QUESTION_ID:246014735810

Dans les commentaires j'ai pu voir que quelqu'un avait tester les types et les tables et il dit ca :

I did my test on comparing the time on fetching into pl/sql table vs. fetching into a SQL type and
then CAST it, and yes the SQL type option is much faster,28% on average, in a 30,000 records
resultset.

ce qui a l'air de dire que les SQL types sont plus rapide que les tables PL/SQL.

[mnitu]

Il y a quelqu’un qui a dit que la Terre est plate.
Par contre si vous avez un petit test qui démontre ce que quelqu’un (et vous) dites …