Procédures et DBLINKS

Version imprimable

22/09/2006, 16h36
vinczente

Procédures et DBLINKS

Bonjour,

Je cherche a creer un cursor afin de recuperer des données d'une base distante.
Ma procédure prendrais donc en parametre le nom de la base (qui correspond au DBLINK) et afficherais la table correspondante

PROCEDURE do_view (v_base VARCHAR2) IS
SELECT owner
FROM ALL_TABLES@v_base;
END;

Le probleme est donc qu'il ne veux pas bien sur du parametre v_base.. mais comment faire pr lui faire commprendre ce que je desire?

Merci pour vos suggestions.

Vincent
22/09/2006, 17h08
pifor

Oracle ne permet pas d'exécuter un SELECT directement en PL/SQL comme un INSERT, un UPDATE ou un DELETE: il faut utiliser un curseur si le SELECT retourne un ensemble de valeurs (voir http://sheikyerbouti.developpez.com/...=Chap1#L1.2.22) ou SELECT INTO si on est sûr que le SELECT va retourner 1 seule ligne.

Pour le problème du database link, voir http://www.developpez.net/forums/sho...=database+link
22/09/2006, 17h21
vinczente

Désolé de ne pas avoir mis toute la procedure mais celle ci est tres longue et je ne voulais pas remplir la page, j'ai donc simplement mis le minimum pour comprendre mon prbleme.

Le souscis de l'execute immediate, c'est que lorque je met create cursor, lui derriere il attends un select et non l'execution d'une chaine de caractere (execute immediate)...
donc je suis bloque..
si vous ne comprennez tjs pas mon prblm c'est que j'explique donc mal et que je mettrais un exemple plus applique.

merci pour votre aide
vincent

voila ce que j'essaye de faire.

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
 
PROCEDURE cpu_conso (v_host ALL_DB_LINKS.HOST%type DEFAULT NULL) IS
CURSOR c_datalink IS
SELECT nvl(s.sid, 0) sid,
      nvl(s.username, '_') username,
      nvl(UPPER(DECODE(command,
              1,'Create Table',2,'Insert',3,'Select',
              4,'Create Cluster',5,'Alter Cluster',6,'Update',
              7,'Delete', 8,'Drop Cluster', 9,'Create Index',
              10,'Drop Index', 11,'Alter Index', 12,'Drop Table',
              13,'Create Sequencfe', 14,'Alter Sequence', 15,'Alter Table',
              16,'Drop Sequence', 17,'Grant', 18,'Revoke',
              19,'Create Synonym', 20,'Drop Synonym', 21,'Create View',
              22,'Drop View', 23,'Validate Index', 24,'Create Procedure',
              25,'Alter Procedure', 26,'Lock Table', 27,'No Operation',
              28,'Rename', 29,'Comment', 30,'Audit',
              31,'NoAudit', 32,'Create Database Link', 33,'Drop Database Link',
              34,'Create Database', 35,'Alter Database', 36,'Create Rollback Segment',
              37,'Alter Rollback Segment', 38,'Drop Rollback Segment', 39,'Create Tablespace',
              40,'Alter Tablespace', 41,'Drop Tablespace', 42,'Alter Sessions',
              43,'Alter User', 44,'Commit', 45,'Rollback',
              46,'Savepoint', 47,'PL/SQL Execute', 48,'Set Transaction',
              49,'Alter System Switch Log', 50,'Explain Plan', 51,'Create User',
              52,'Create Role', 53,'Drop User', 54,'Drop Role',
              55,'Set Role', 56,'Create Schema', 57,'Create Control File',
              58,'Alter Tracing', 59,'Create Trigger', 60,'Alter Trigger',
              61,'Drop Trigger', 62,'Analyze Table', 63,'Analyze Index',
              64,'Analyze Cluster', 65,'Create Profile', 66,'Drop Profile',
              67,'Alter Profile', 68,'Drop Procedure', 69,'Drop Procedure',
              70,'Alter Resource Cost', 71,'Create Snapshot Log', 72,'Alter Snapshot Log',
              73,'Drop Snapshot Log', 74,'Create Snapshot', 75,'Alter Snapshot',
              76,'Drop Snapshot', 79,'Alter Role', 85,'Truncate Table',
              86,'Truncate Cluster', 88,'Alter View', 91,'Create Function',
              92,'Alter Function', 93,'Drop Function', 94,'Create Package',
              95,'Alter Package', 96,'Drop Package', 97,'Create Package Body',
              98,'Alter Package Body', 99,'Drop Package Body')), '_') command,
      nvl(s.osuser, '_') osuser,
      nvl(s.machine, '_') machine,
      nvl(s.process, '_') process,
      nvl(s.SERIAL#, 0) serial,
      nvl(si.PHYSICAL_READS, 0) PHYSIC_READS,
      MOD(rownum,   2) ISIMPAIR,
      nvl(t.value, 0) value
      FROM
      sys.v_$session@v_host s,
      sys.V_$SESSTAT@v_host t,
      sys.V_$STATNAME@v_host n,
      sys.V_$SESS_IO@v_host  si
      WHERE
              s.sid = t.sid
      AND
      t.statistic# = n.statistic#
      AND
      s.SID = si.SID(+)
      AND
      n.name = 'CPU used by this session'
      AND
      t.value > 0
      AND
      audsid > 0;

22/09/2006, 17h27
pifor

Le dernier exemple de http://sheikyerbouti.developpez.com/..._immediate/#L5
contient un exemple de curseur dont la requête est construite dynamiquement: cela semble plus correspondre à ce que vous cherchez.

quelque chose du style ?

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
 
SQL> create or replace function f(db varchar2) return sys_refcursor as
  2  s sys_refcursor;
  3  begin
  4  open s for 'select username from all_users@'||db;
  5  return s;
  6  end;
  7  /
Function created.
 
SQL> select f('LSC01') from dual;
F('LSC01')
--------------------
CURSOR STATEMENT : 1
 
CURSOR STATEMENT : 1
USERNAME
------------------------------
EXFSYS
OPS$LSC
U
SCOTT
DBSNMP
TSMSYS
DIP
OUTLN
SYSTEM
SYS
 
10 rows selected.
 
 
SQL> select f('LSC02') from dual;
F('LSC02')
--------------------
CURSOR STATEMENT : 1
 
CURSOR STATEMENT : 1
USERNAME
------------------------------
SYS
SYSTEM
OUTLN
DBSNMP
SCOTT

ou plutôt quelque chose qui retourne une table de varchar2?

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
 
SQL> create or replace function f(db varchar2) return table_of_varchar2 pipelined is
  2  s sys_refcursor;
  3  u varchar2(30);
  4  begin
  5  open s for 'select username from all_users@'||db;
  6  loop
  7  fetch s into u;
  8  exit when s%notfound;
  9  pipe row(u);
 10  end loop;
 11  return;
 12  end;
 13  /
 
Function created.
 
SQL> select * from table(f('LSC01'));
COLUMN_VALUE
------------------------------
EXFSYS
OPS$LSC
U
SCOTT
DBSNMP
TSMSYS
DIP
OUTLN
SYSTEM
SYS
 
10 rows selected.
 
SQL> select * from table(f('LSC02'));
COLUMN_VALUE
------------------------------
SYS
SYSTEM
OUTLN
DBSNMP
SCOTT

22/09/2006, 17h30
laurentschneider

en relisant ce qui a été posté depuis que j'ai commencé d'écrire ma réponse, je pense que la solution se résume à SYS_REFCURSOR, non?
22/09/2006, 17h35
vinczente

merci a vous deux. Je pense que pifor a trouve la solution a mon probleme, mais j'ai aps pu tester car je dois m'absenter d'ici 10 minutes.

Merci beaucoup pour votre aide, Je reviendrais vers vous en cas de probleme ;)

A bientot :)
vincent
25/09/2006, 10h58
vinczente

Oui,
donc en fait la solution proposée ne marche pas car la procedure compile va esayer de joindre la base v_host... :s

les databases links sont des objets spéciaux et on ne peut pas faire tout comme on veut comme avec le reste...

Je suis donc tjs ds une impasse.... :aie:

Auriez vous d'autres idées?

MErci.
Vincent

ps : laurent, je ne comprends pas ta question :p

tu remplace

Code:

1
2
3
4
5
6
7
8
9
10
 
PROCEDURE cpu_conso (v_host ALL_DB_LINKS.HOST%type DEFAULT NULL) IS
CURSOR c_datalink IS
SELECT 'blabla'
      FROM
      sys.v_$session@v_host
begin
null;
end;
/

par

Code:

1
2
3
4
5
6
7
8
 
PROCEDURE cpu_conso (v_host ALL_DB_LINKS.HOST%type DEFAULT NULL) IS
c_datalink sys_refcursor;
begin
  open c_datalink for 
    'SELECT ''blabla'' FROM sys.v_$session@'||v_host:
end;
/

Je n'y arrive vraiment pas.. voici la procedure simplifiée...

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
 
PROCEDURE cpu_conso ( v_host IN ALL_DB_LINKS.HOST%type) IS
c_datalink sys_refcursor;
v_sid NUMBER (30);
 BEGIN
  OPEN c_datalink FOR
  'SELECT nvl(s.sid, 0) sid FROM sys.v_$session@' || v_host ||' s where rownum < 10';
  LOOP
   FETCH c_datalink INTO v_sid;
   DBMS_OUTPUT.PUT_LINE(v_sid);
   END LOOP;
   close c_datalink;
END cpu_conso;

Merci pour votre aide.

Vincent

25/09/2006, 17h50
laurentschneider

où est le problème?
25/09/2006, 17h59
pifor

Ici ?

Code:

'SELECT nvl(s.sid, 0) sid FROM sys.v_$session@' || v_host ||' s where rownum <
25/09/2006, 18h02
laurentschneider

à première vue ça a l'air ok, non?

quel est le message d'erreur?
25/09/2006, 18h03
pifor

En effet :oops:

Il manque peut-être le test sur la fin du du curseur ?
26/09/2006, 11h01
vinczente

désolé pour mon temps de reponse mais j'ai eu du mal a comprendre ce qui se passait.

A chaques fois que j'execute la procedure, la connexion coupait....

En fait le processeur monte a 100%.. et je devais redemerar mon serveur.

Je pense qu'il doit y avoir une boucle dans ma requete, ceci expliquerais cela....:cry:
26/09/2006, 11h27
vinczente

bon, c bon g trouve...

Il me manquais le exit when dans ma boucle, ce qui fesait qu'il bouclait tt le temps.....

:D

Merci a vous tous de m'avoir aider dans ma peine :p
26/09/2006, 12h33
laurentschneider

donc c'est :resolu: ?