Procédures et DBLINKS

**vinczente** · 22/09/2006, 16h36

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

**pifor** · 22/09/2006, 17h08

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

**vinczente** · 22/09/2006, 17h21

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

**vinczente** · 22/09/2006, 17h26

voila ce que j'essaye de faire.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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;

**pifor** · 22/09/2006, 17h27

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.

**laurentschneider** · 22/09/2006, 17h27

quelque chose du style ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

**laurentschneider** · 22/09/2006, 17h30

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?

**vinczente** · 22/09/2006, 17h35

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

**vinczente** · 25/09/2006, 10h58

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....

Auriez vous d'autres idées?

MErci.
Vincent

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

**laurentschneider** · 25/09/2006, 12h29

tu remplace

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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;
/

Procédures et DBLINKS

Oracle

Vue hybride

Discussions similaires

Partager

Partager