Procédure avec nom table et valeur d'un champ en paramètre

Version imprimable

Bonjour à tous,
Alors, j'ai deux problèmes dans la conception de ma procédure stockée sous oracle avec SQL Developer.
Premier souci:
En fait, ma procédure prend en paramètre le nom d'une table et une valeur d'un des champs de cette table. Je souhaite alors conserver dans une variable (booléenne) le résultat du select sur cette table avec comme condition la valeur du champ concerné. Pour celà j'utilise "EXECUTE IMMEDIATE" pour mon select et "decode" pour vérifier la valeur de retour du select. Mais EXECUTE IMMEDIATE ne prend pas en compte la variable "var_Found_table_attribut"
Second souci: les cotes dans 'N' et 'Y' que j'utilise dans ma requête n'ont pas l'air d'etre apprécié par le codes.

Voilà mon code:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 CREATE TABLE TABLE_EIM ( ROW_ID VARCHAR2(30), IF_ROW_BATCH_NUM VARCHAR2(60), PRIMARY KEY (ROW_ID) ); create or replace PROCEDURE DEBUT_ETAPE(NOM_TABLE IN VARCHAR2, BATCH_NUM IN VARCHAR2) IS var_Found_table_attribut varchar2(30); BEGIN EXECUTE IMMEDIATE 'SELECT decode(ROW_ID,null,'N','Y') INTO' || var_Found_table_attribut|| '||NOM_TABLE||' WHERE IF_ROW_BATCH_NUM='||BATCH_NUM; --dbms_output.put_line('var_Found_table_attribut); END;
Merci d'avance pour vos réponses ou suggestions.

1/ C'est une mauvaise idée d'utiliser row_id comme PK, row_id est interne à oracle et ne doit que très très rarement être utilisé.
Crée ta propre PK que tu incrémenteras via une séquence.

2/ Le SQL dynamique est dangereux pour des raisons d'injonction SQL
Pour la table il faut valider que c'est bien une table existante (dbms_assert.sql_object_name) et pour les variables il faut les binder en utilisant USING.

3/ Pour débugguer du SQL dynamique il faut afficher la requête que l'on souhaite faire exécuter via DBMS_OUPUT.
Dans ton code il manque FROM, des espaces et le INTO ne se place pas là.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 SQL> declare l_query varchar2(1000); l_nb number; l_usr varchar2(30) := 'SYS'; begin l_query := 'select count(*) as nb from all_users where username = :usr'; dbms_output.put_line ('la requete est : ' || l_query); execute immediate l_query into l_nb using l_usr; dbms_output.put_line ('nombre = ' || l_nb); end; / la requete est : select count(*) as nb from all_users where username = :usr nombre = 1 PL/SQL procedure successfully completed.

11/02/2012, 19h20
dianroca

Oui, j'ai du oublié le FROM. mais ça ne répond pas forcément à ce que jee veux dans la mesure où ton (skuatamad) code ne prend pas en compte mes deux paramètres en entrée en l'occurence le nom de la table et la valeur d'un des champ. En outre j'aimerai savoir ce que me retoune le select raison pour laquelle j'ai utilisé le decode.

Voilà un exemple, il faut doubler les quotes pour les echapper.
Mais dans ton code le DECODE est inutil car la PK est forcément NOT NULL.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SQL> create or replace procedure p (p_nom_table varchar2, p_param varchar2) as 
  l_verif_nom_table varchar2(30);
  l_query varchar2(1000);
  l_usr_bool varchar2(1);
begin
  l_verif_nom_table := dbms_assert.sql_object_name(p_nom_table);
  l_query := 'select decode(username,''SYS'',''T'',''F'') as usr_bool from '|| l_verif_nom_table || ' where username = :usr';
  dbms_output.put_line(l_query);
  execute immediate l_query into l_usr_bool using p_param;
  dbms_output.put_line(l_usr_bool);
end;
/
 
Procedure created.
 
SQL> exec p('ALL_USERS','SYSTEM');
select decode(username,'SYS','T','F') as usr_bool from ALL_USERS where username = :usr
F
 
PL/SQL procedure successfully completed.
 
SQL>

Par ailleurs ta requête avec SELECT INTO risque de tomber en erreur NO_DATA_FOUND ou TOO_MANY_ROWS sauf si IF_ROW_BATCH_NUM est déclaré en NOT NULL UNIQUE.

Convertir les données d´un tableau entré en paramètre

j' aimerais savoir s' il est possible en Plsql d' écrire une procédure qui prendrait un tableau comme paramètre afin d' effectuer certaines convertions dans celui-ci.
Pour être plus claire voici ce que je tente de faire:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
 
 
create or replace ma_proc(table_name in varchar2) as 
verif varchar2(40):= dbms_assert.sql_object(table_name); -- pour verifier l´existence de la table dans le dictionnaire de données.
 
type rty_tab is table of verif%rowtype; -- ici j´ai un prob car le compilateur ne reconnaît pas "verif" comme une table seulement je n´ai pas trouvé d'autre moyen.
 
tab rty_tab;
 
res1 varchar2(18);
 
query1 varchar2(1000):= 'select col1, col2 bulk collect into tab from ' ||verif;
query2 varchar2(1000):= 'update' ||verif|| 'set tab(i).col1 =  res1 where rowid = v_rowid';

merci pour toutes suggestions

29/08/2012, 15h25
McM

Citation:

Envoyé par skuatamad

1/ C'est une mauvaise idée d'utiliser row_id comme PK, row_id est interne à oracle et ne doit que très très rarement être utilisé.
Crée ta propre PK que tu incrémenteras via une séquence.

Tu t'es trompé, c'est ROWID qui est interne à oracle :)
Là, c'est juste un Row_Id en varchar2.. pas de soucis, donc.
29/08/2012, 15h30
McM

Teddi, Je ne comprends pas ton dernier post.

Peux tu donner un exemple avec des valeurs de ce que tu veux faire ?