Exécution d’une procédure distante avec type DATA1 en paramètre

[whenti]

J’ai N serveur avec le même programme : package Flux_trf qui utilise un type d’objet DATA1 créé pour la circonstance.

Sous le user : A1 du serveur A :

Code :

CREATE OR REPLACE TYPE DATA1 AS OBJECT (  d1 varchar2(10), d2 varchar2(20) );

Package Flux_trf
Dont une procedure trf_data(X in varchar2, Y in varchar2, d in out DATA1)
Cette procédure exécute de manière dynamique le transfert de données d’un user X vers un user Y comme cela :
Si même serveur

Code :

1
2
	w_requete := 'BEGIN '|| w_user_dest||'.Flux_trf.CreData(:1 ); END;';
   execute immediate w_requete USING IN OUT d;

Si serveur distant

Code :

1
2
	w_requete := 'BEGIN '|| w_user_dest||'.Flux_trf.CreData(:1 ); END;';
   execute immediate w_requete USING IN OUT d;

Sous le user A2 du même serveur A :
J’ai également le package Flux_trf.
Au départ j’avais créé également le type DATA1 sous le user A2, mais lors de l’exécution de la procédure j’avais le message : PLS-00306: numéro ou types d'arguments erronés

J’ai donc supprimé le type DATA1 du user A2 et créé un synonym public DATA1 pointant sur le type DATA1 du user A1.
Cela a bien fonctionné.

Seulement voilà il peut y avoir des serveurs distants
Donc sous le user B2 du serveur B , je pensais également crée un synonym public distant sur le user A1 avec un databaselink.
Mais lorsque j’accède à ce type de synonym distant DATA1 j’ai le message :

ORA-00902: type de données non valide

Comment puis-je déclarer un type DATA1 unique dans le cas de plusieurs serveurs ou base Oracle différente ?

[skuatamad]

Pour moi lorsque tu te connectes via le serveur B au serveur A tu le fais via un user A3 créé sur le seveur A.

Donc si le user A3 est correctement configuré (cf a les droits d'exécution sur le package et sur le type DATA1) ça devrait être bon... mais je ne suis pas trop sûr et évidemment je n'ai pas fait de test, les prolématiques via DBlink obligeant à créer une autre base...

Peux tu nous préciser comment le user B1 se connecte STP ?

Pour faire basique as tu testé une connexion depuis le serveur B vers le serveur A via le user A1, juste pour s'assurer en dev de la faisabilité ?

[whenti]

Meilleurs vœux à tous pour nouvelle année

Pour répondre à tes questions :
Les droits d’exécution sur le package sont corrects, mais c’est la variable de type DATA1 qui pose problème.
Le user B1 se connecte au serveur A via un Dblink et la connexion depuis le serveur B vers le serveur A via A1 fonctionne.
Ci-dessous les tests basiques que j’ai fait :

Sous user A1 serveur A j’exécute :

Code sql :

1
2
3
4
5
6
7
8
CREATE OR REPLACE TYPE DATA1 AS OBJECT (  d1 varchar2(10), d2 varchar2(20) )
/
GRANT ALL ON DATA1 TO PUBLIC
/
CREATE TABLE TOTO ( d1 varchar2(10), d2 varchar2(20) )
/
GRANT ALL ON TOTO TO PUBLIC
/

Sous user A2 serveur A l’exécution suivante est correcte :

Code sql :

1
2
DESC A1.DATA1
DESC A1.TOTO

Sous user B1 serveur B :

Code sql :

DESC A1.TOTO@BDL_B_VERS_A

est correcte

Mais

Code sql :

DESC A1.DATA1@BDL_B_VERS_A

me renvoie le message d’erreur :

Code :

ORA-00902: type de données non valide

Aurais-je oublié quelque chose ?

[skuatamad]

Apparemment il faut assigner un OID, mais je n'ai jamais testé, j'éspère que j'ai bien compris.
Sinon pour le desc il y a effectivement des limitations via DBlink.

[mnitu]

Restriction on Using User-Defined Types with a Remote Database

Restriction on Using User-Defined Types with a Remote Database
User-defined types (specifically, types declared with a SQL CREATE TYPE statement, as opposed to types declared within a PL/SQL package) are currently useful only within a single database. You cannot use a database link to do any of the following:

•Connect to a remote database to select, insert, or update a user-defined type or an object REF on a remote table

You can use the CREATE TYPE statement with the optional keyword OID to create a user-specified object identifier (OID) that allows an object type to be used in multiple databases. See the discussion on assigning an OID to an object type in the Oracle Database Data Cartridge Developer's Guide.

•Use database links within PL/SQL code to declare a local variable of a remote user-defined type

•Convey a user-defined type argument or return value in a PL/SQL remote procedure call.

[plabrevo]

Il faut associer un objet identifier au type
http://download.oracle.com/docs/cd/B.../obj_types.htm

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
DECLARE
l_statement   VARCHAR2(30000);
l_oid         VARCHAR2(240);
 
BEGIN
SELECT SYS_OP_GUID()
INTO   l_oid
FROM   sys.dual
;
 
l_statement := '
CREATE OR REPLACE TYPE data1 OID '''||l_oid||''' AS OBJECT
(d1        VARCHAR2(10)
,d2        VARCHAR2(10))'
;
 
EXECUTE IMMEDIATE l_statement ;
 
END;
/
 
DESCRIBE data1@BDL_B_VERS_A

[mnitu]

Envoyé par plabrevo

Il faut associer un objet identifier au type
...

Avez vous essayez de le transmettre comme paramètre dans une procédure avec appel distant ?

[whenti]

J’ai fait quelques tests effectivement l’utilisation des types utilisateurs ( ici DATA1 ) n’est pas utilisable comme paramètres de procédure dans le cas d’utilisation de DBLink. Même en utilisant l’option OID.
Apparemment cette option ne servirait que dans le cadre des import/export et de query distribué !!

Dans mon cas j’ai fait un exemple avec DATA1 qui est une structure simple, mais en réalité le type est beaucoup plus complexe ( 30 champs en entête et un tableau avec une structure également de 40 champs ).
Nous sommes à l’ère de la programmation Objet il me semble. Oracle no 1 mondial en parle aussi…

Alors quelque aurait-il une idée pour passer des paramètres complexes, similaires aux types utilisateurs, dans le cas d’une architecture avec plusieurs bases de données ?

[mnitu]

Envoyé par whenti

...
Alors quelque aurait-il une idée pour passer des paramètres complexes, similaires aux types utilisateurs, dans le cas d’une architecture avec plusieurs bases de données ?

Ca devrait aller en utilisant un package pour définir le type complexe. Bref, ce n’est pas tout à fait la même chose que l’utilisation des types utilisateur mais, parfois ça peut suffire.

[whenti]

Ca devrait aller en utilisant un package pour définir le type complexe

Pensais-tu à créer les

Code :

TYPE xxx IS RECORD xxx

dans les packages ou à autres choses ?

Le problème c'est que je veux executer ses procédure de manières dynamiques puisque je peux avoir N serveurs.

La seule solution que je voie c'est de passer par des tables temporaires ayant la même structure que les types. De faire des INSERT de manières dynamiques mais en détaillant chaque champs ( puisqu'on ne peut pas non plus utiliser de 'type utilisateur' lors des opérations INSERT en multi-bases ).
Puis d'exécuter une procédure qui lit la table temporaire.

En attendant qu'ORACLE développe cette fonctionnalité... peut-être qu'elle existe déjà en version 11G ?

[mnitu]

Oui en gros c’est ça.
Pourriez-toi mettre un petit exemple complet de ce que t’essaie d’accomplir (deux bases, création des users, création du database link, tables, package, données, résultat attendu, etc.)