sqlplus - à l'aide d'un lier des variables dans le “dans” la clause

Je suis en train d'une liaison variable dans une PL/SQL bloc, et j'essaie de l'utiliser dans une autre requête DANS l'expression. Quelque chose comme ceci:

variable x varchar2(255)

declare
    x varchar2(100);
begin
    for r in (select id from other_table where abc in ('&val1','&val2','&val3') ) loop
    x := x||''''||r.id||''',';
    end loop;
    --get rid of the trailing ','
    x:= substr(x,1,length(x)-1);

    select x into :bind_var from dual;
end;
/

print :bind_var;

select *
from some_table
where id in (:bind_var);

Et j'obtiens une erreur (ORA-01722: nombre non valable) sur la requête qui tente d'utiliser la liaison de la variable dans la liste.

L'instruction print yiels '123','345' qui est ce que j'attends.

Est-il possible d'utiliser la liaison de la variable comme ceci ou devrais-je essayer une approche différente?

(à l'aide d'Oracle 10g)

Précisions:

C'est pour une réconciliation genre de chose. Je veux courir

select *
from some_table
where id in (select id from other_table where abc in ('&val1','&val2','&val3'))

avant la partie principale du script (non représentée ici) supprime tout un tas de dossiers. Je veux l'exécuter à nouveau par la suite pour vérifier que les enregistrements dans some_table n'ont PAS été supprimés. Toutefois, les données other_table NE supprimés par ce processus, de sorte que je ne peux pas juste faire référence aux données other_table parce qu'il n'y a rien. J'ai besoin d'un moyen de préserver la other_table.id valeurs afin que je puisse vérifier les enregistrements parents par la suite.

OriginalL'auteur FrustratedWithFormsDesigner | 2011-02-11

6

Je voudrais stocker les other_table.id's en PL/SQL de la table de référence et que la table de la requête par la suite:
```
type t_id_table is table OF other_table.id%type index by binary_integer;
v_table t_id_table;

-- fill the table
select id
bulk collect into v_table
from other_table 
where abc in ('&val1','&val2','&val3');     

-- then at a later stage...    

select *
from some_table st
,    table(cast(v_table AS t_id_table)) idt
where st.id = idt.id;
```
Nice. J'étais juste à la recherche de la syntaxe, de l'essentiel de recueillir, et ne savait même pas sur la table "(cast(v_table COMME t_id_table))". Je pense que c'est la solution idéale pour l'OP!
Dans les dernières versions d'Oracle, le CASTING devrait être inutile-- vous pouvez simplement faire TABLE(v_table)
Intéressant... je vais essayer!
Hoogenboom: Est-il possible d'exécuter "stade ultérieur" instruction SQL (celui qui fait référence à v_table) à l'extérieur d'une PL/SQL bloc? La requête qu'il remplace est à la fin du script et à l'extérieur du PL/SQL bloc (pour faire du bobinage sur le fichier de sortie plus simple) et cela me donne un type de données non valide erreur pour t_id_table. A-t-elle à être exécuté dans le même bloc que t_id_table est déclarée?
Vous pouvez déclarer le pl/sql de la table dans un package afin de conserver les données pendant la session.

OriginalL'auteur diederikh
1

Vous ne pouvez pas utiliser de valeurs séparées par des virgules dans un lier des variables.

On pourrait dire:
```
select * from some_table where id in (:bind_var1, :bind_var2)
```
si

Vous êtes mieux d'utiliser quelque chose comme:
```
select * from some_table where id in ("select blah blah blah...");
```
Malheureusement, je n'ai pas toujours savoir combien de valeurs dans la clause in. Sinon, je n'aurais pas essayé de le faire de façon dynamique dans la première place. ...attendez, je pourrais définir le lier à la variable SQL texte d'une sous-requête? Hmm... donnez-moi une minute...
Droit, mais pourquoi ne pas simplement le faire en une seule instruction?
Hm réglage de la liaison variable d'une chaîne de requête ne semble pas fonctionner non plus.
Pas de. Ce n'est pas ce que je recevais. Vous avez clarifié la question & montré exactement ce que je recevais à l' (il suffit de sélectionner dans 1 stmt).

OriginalL'auteur Gerrat

Je voudrais utiliser une table temporaire globale pour cette fin

create global temporary table gtt_ids( id number ) ;

puis

...
for r in (select id from other_table where ... ) loop
   insert into gtt_ids(id) values (r.id) ;
end loop;
...

et à la fin

select *
from some_table
where id in (select id from gtt_ids);

OriginalL'auteur Marcin Wroblewski

Ok, j'ai une espèce de moche solution qui utilise également des variables de substitution...

col idList NEW_VALUE v_id_list /* This is NEW! */
variable x varchar2(255)
declare
    x varchar2(100);
begin
    for r in (select id from other_table where abc in ('&val1','&val2','&val3') ) loop
    x := x||''''||r.id||''',';
    end loop;
    --get rid of the trailing ','
    x:= substr(x,1,length(x)-1);

    select x into :bind_var from dual;
end;
/

print :bind_var;

select :x idList from dual;  /* This is NEW! */

select *
from some_table
where id in (&idList);  /* This is CHANGED! */

Il fonctionne, mais je vais accepter une réponse de quelqu'un d'autre si c'est plus élégant.

OriginalL'auteur FrustratedWithFormsDesigner

changé la boucle à utiliser listagg (malheureusement cela ne fonctionnera que dans 11gr2).

mais pour la variable dans la liste, j'ai utilisé un expression régulière pour atteindre l'objectif (mais avant 10g vous pouvez utiliser substr à faire de même) c'est levée à partir de la asktom question liée.

    variable bind_var varchar2(255)
variable dataSeperationChar varchar2(255)

declare
    x varchar2(100);
begin

select listagg(id,',')  within group(order by id) idList
into x
  from(select level id 
         from dual  
        connect by level < 100 ) 
where id in (&val1,&val2,&val3) ;
    select x into :bind_var from dual;
    :dataSeperationChar := ',';
end;
/

print :bind_var;

/



select *
  from (
        select level id2
          from dual
         connect by level < 100
        )
    where id2 in(
            select  -- transform the comma seperated string into a result set        
            regexp_substr(:dataSeperationChar||:bind_var||','
                        , '[^'||:dataSeperationChar||']+'
                      ,1
                      ,level)    as parsed_value
            from dual
            connect by level <= length(regexp_replace(:bind_var, '([^'||:dataSeperationChar||'])', '')) + 1    
    )
;

/*
values of 1,5, and 25

BIND_VAR
------
1,5,25

ID2                    
---------------------- 
1                      
5                      
25   
*/

MODIFIER

Oups viens de remarquer que vous n'avez marque 10g, la seule chose à faire est de ne PAS utiliser le listagg que j'ai fait au début

OriginalL'auteur Harrison

Vous devez vous connecter pour publier un commentaire.