Home » SQL & PL/SQL » SQL & PL/SQL » Re: PL/SQL:How to use VARRAY in the USING clause of EXECUTE IMMEDIATE? split from http://www.orafaq.com/forum/t/161791/
Re: PL/SQL:How to use VARRAY in the USING clause of EXECUTE IMMEDIATE? split from http://www.orafaq.com/forum/t/161791/ [message #664298] Tue, 11 July 2017 09:48 Go to next message
moracles
Messages: 6
Registered: July 2017
Junior Member
Hi
I try to use this solution and it works fine with :
v_set1    sys.OdciVarchar2List :=sys.OdciVarchar2List('A','B');
But it fails with
declare 
my_data varchar2(100) := '''A'',''B'''
BEGIN
v_set1    sys.OdciVarchar2List :=sys.OdciVarchar2List(my_data);

So, could you help me : how can I use a PLSQL variable with sys.OdciVarchar2List ?

Regards
Re: PL/SQL:How to use VARRAY in the USING clause of EXECUTE IMMEDIATE? [message #664299 is a reply to message #664298] Tue, 11 July 2017 10:12 Go to previous messageGo to next message
cookiemonster
Messages: 12927
Registered: September 2008
Location: Rainy Manchester
Senior Member
Have a read of varying in list
Re: PL/SQL:How to use VARRAY in the USING clause of EXECUTE IMMEDIATE? [message #664300 is a reply to message #664299] Tue, 11 July 2017 10:38 Go to previous messageGo to next message
moracles
Messages: 6
Registered: July 2017
Junior Member
Thanks, but it is still not clear for me.

When I run this code :
DECLARE
  stmt varchar2(1000);
  lval varchar2(2000);
  var_val   sys.OdciVarchar2List ; --DBMS_ASSERT.ENQUOTE_LITERAL
BEGIN
  for c1 in (select LISTAGG(case when DEFAULT_BD is null and DEFAULT_ITF is null then  'DBMS_ASSERT.ENQUOTE_LITERAL('||CHAMP_ITF||')'
                                 else  'DBMS_ASSERT.ENQUOTE_LITERAL('''||NVL(DEFAULT_BD, DEFAULT_ITF)||''')'
                             end,'||'',''||')  WITHIN GROUP (ORDER BY NUM_ORDRE) liste_champ_itf
               from ITF_PAR_MAP 
              where NOM_ITF = 'UT' AND TAB_BD = NVL('COSWIN.T_EQUIPMENT', TAB_BD) AND CHAMP_BD is not null AND FORMAT_BD like 'V2%' and num_ordre < 5 group by nom_itf) LOOP
                
     stmt :=  q'[select  ]'|| c1.liste_champ_itf|| '  from "IMMOSIS"."UT"  where num_ligne=1' ;
     dbms_output.put_line('stmt='||stmt);
     execute immediate stmt into lval;
     dbms_output.put_line('lval='||lval);
  END LOOP;
  var_val := sys.OdciVarchar2List(lval);
  FOR i in var_val.FIRST..var_val.LAST LOOP
    dbms_output.put_line('var_val('||i||')='||var_val(i));
  END LOOP;
END;

The ouptup is :

bloc anonyme terminé
stmt=select DBMS_ASSERT.ENQUOTE_LITERAL(CODE_UT)||','||DBMS_ASSERT.ENQUOTE_LITERAL(LIBELLE_UT)||','||DBMS_ASSERT.ENQUOTE_LITERAL('Z/DEF') from "IMMOSIS"."UT" where num_ligne=1
lval='000002S','ARTONGES GARE Château-Thierry','Z/DEF'
var_val(1)='000002S','ARTONGES GARE Château-Thierry','Z/DEF'


Why var_val is not splited, as expected on :
var_val(1)='000002S'
var_val(2)='ARTONGES GARE Château-Thierry'
var_val(3)='Z/DEF'
?

Regards
Re: PL/SQL:How to use VARRAY in the USING clause of EXECUTE IMMEDIATE? [message #664317 is a reply to message #664298] Tue, 11 July 2017 14:59 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8751
Registered: November 2002
Location: California, USA
Senior Member
moracles wrote on Tue, 11 July 2017 07:48
Hi
I try to use this solution and it works fine with :
v_set1    sys.OdciVarchar2List :=sys.OdciVarchar2List('A','B');
But it fails with
declare 
my_data varchar2(100) := '''A'',''B'''
BEGIN
v_set1    sys.OdciVarchar2List :=sys.OdciVarchar2List(my_data);

So, could you help me : how can I use a PLSQL variable with sys.OdciVarchar2List ?

Regards
SCOTT@orcl_12.1.0.2.0> declare
  2    my_data	varchar2(100)	     := '''A'',''B''';
  3    v_set1	sys.OdciVarchar2List;
  4    stmt	varchar2(32767);
  5  begin
  6    stmt := 'select sys.OdciVarchar2List(' || my_data || ') from dual';
  7    execute immediate stmt into v_set1;
  8    for i in 1 .. v_set1.count loop
  9  	 dbms_output.put_line ('v_set(' || i || ')=' || v_set1(i));
 10    end loop;
 11  end;
 12  /
v_set(1)=A
v_set(2)=B

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0>




Re: PL/SQL:How to use VARRAY in the USING clause of EXECUTE IMMEDIATE? [message #664321 is a reply to message #664300] Tue, 11 July 2017 15:34 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8751
Registered: November 2002
Location: California, USA
Senior Member
moracles wrote on Tue, 11 July 2017 08:38
Thanks, but it is still not clear for me.

When I run this code :
DECLARE
  stmt varchar2(1000);
  lval varchar2(2000);
  var_val   sys.OdciVarchar2List ; --DBMS_ASSERT.ENQUOTE_LITERAL
BEGIN
  for c1 in (select LISTAGG(case when DEFAULT_BD is null and DEFAULT_ITF is null then  'DBMS_ASSERT.ENQUOTE_LITERAL('||CHAMP_ITF||')'
                                 else  'DBMS_ASSERT.ENQUOTE_LITERAL('''||NVL(DEFAULT_BD, DEFAULT_ITF)||''')'
                             end,'||'',''||')  WITHIN GROUP (ORDER BY NUM_ORDRE) liste_champ_itf
               from ITF_PAR_MAP 
              where NOM_ITF = 'UT' AND TAB_BD = NVL('COSWIN.T_EQUIPMENT', TAB_BD) AND CHAMP_BD is not null AND FORMAT_BD like 'V2%' and num_ordre < 5 group by nom_itf) LOOP
                
     stmt :=  q'[select  ]'|| c1.liste_champ_itf|| '  from "IMMOSIS"."UT"  where num_ligne=1' ;
     dbms_output.put_line('stmt='||stmt);
     execute immediate stmt into lval;
     dbms_output.put_line('lval='||lval);
  END LOOP;
  var_val := sys.OdciVarchar2List(lval);
  FOR i in var_val.FIRST..var_val.LAST LOOP
    dbms_output.put_line('var_val('||i||')='||var_val(i));
  END LOOP;
END;

The ouptup is :

bloc anonyme terminé
stmt=select DBMS_ASSERT.ENQUOTE_LITERAL(CODE_UT)||','||DBMS_ASSERT.ENQUOTE_LITERAL(LIBELLE_UT)||','||DBMS_ASSERT.ENQUOTE_LITERAL('Z/DEF') from "IMMOSIS"."UT" where num_ligne=1
lval='000002S','ARTONGES GARE Château-Thierry','Z/DEF'
var_val(1)='000002S','ARTONGES GARE Château-Thierry','Z/DEF'


Why var_val is not splited, as expected on :
var_val(1)='000002S'
var_val(2)='ARTONGES GARE Château-Thierry'
var_val(3)='Z/DEF'
?

Regards
You seem to do alright as far as the population of lval and I don't have your tables and data, so I simulated the processing up to that point in the demonstration below.

SCOTT@orcl_12.1.0.2.0> declare
  2    stmt	varchar2(32767);
  3    lval	varchar2(1000);
  4    var_val	sys.OdciVarchar2List;
  5  begin
  6  -- simulation:
  7    for c1 in
  8  	 (select 'select ''''''000002S'''',''''ARTONGES GARE Château-Thierry'''',''''Z/DEF'''''' from dual' col
  9  	  from	 dual)
 10    loop
 11  	 stmt := c1.col;
 12  	 dbms_output.put_line ('stmt='||stmt);
 13  	 execute immediate stmt into lval;
 14  -- end of simulation
 15  	 dbms_output.put_line('lval='||lval);
 16  	 stmt := 'select sys.OdciVarchar2List(' || lval || ') from dual';
 17  	 dbms_output.put_line ('stmt='||stmt);
 18  	 execute immediate stmt into var_val;
 19  	 for i in 1 .. var_val.count loop
 20  	   dbms_output.put_line ('var_val(' || i || ')=' || var_val(i));
 21  	 end loop;
 22    end loop;
 23  end;
 24  /
stmt=select '''000002S'',''ARTONGES GARE Château-Thierry'',''Z/DEF''' from dual
lval='000002S','ARTONGES GARE Château-Thierry','Z/DEF'
stmt=select sys.OdciVarchar2List('000002S','ARTONGES GARE
Château-Thierry','Z/DEF') from dual
var_val(1)=000002S
var_val(2)=ARTONGES GARE Château-Thierry
var_val(3)=Z/DEF

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0>

Re: PL/SQL:How to use VARRAY in the USING clause of EXECUTE IMMEDIATE? [message #664322 is a reply to message #664321] Tue, 11 July 2017 15:45 Go to previous messageGo to next message
moracles
Messages: 6
Registered: July 2017
Junior Member
Hi Barbara,
it's clear now.
Thanks a lot for your replys

Regards
Re: PL/SQL:How to use VARRAY in the USING clause of EXECUTE IMMEDIATE? [message #666414 is a reply to message #664322] Fri, 03 November 2017 05:42 Go to previous messageGo to next message
moracles
Messages: 6
Registered: July 2017
Junior Member
Hi
I'd like to print the final UPDATE statement issue inside this function :



FUNCTION F_TRT_MAJ(P_TAB_BD VARCHAR2, P_STMT_SET VARCHAR2, P_STMT_WHERE VARCHAR2, P_VARRAY_VA2 sys.OdciVarchar2List, P_VARRAY_NUM sys.OdciNumberList) RETURN NUMBER IS
 stmt varchar2(32000);
 cptr number := 0;
BEGIN
  --
  stmt :=q'[
  DECLARE
    arr_va2 sys.OdciVarchar2List;
    arr_num sys.OdciNumberList;
  BEGIN
    arr_va2 := :1;
    arr_num := :2;
     --
    UPDATE ]'||P_TAB_BD||q'[
       SET ]'|| P_STMT_SET ||q'[
     WHERE ]'|| P_STMT_WHERE ||q'[;
    :x := sql%rowcount;  
  END;
  ]';
  --dbms_output.put_line('stmt='||stmt);
  IF (utl_file.IS_OPEN(pck_igoitf.pfile) AND PCK_IGOITF.ITF_MODE > PCK_IGOITF.TRC_MAX) THEN utl_file.putf(pck_igoitf.pfile,'F_TRT_MAJ stmt=%s \n',stmt); END IF;
  execute immediate stmt using P_VARRAY_VA2, P_VARRAY_NUM, OUT cptr; -- passage de nombre variable de paramètres via 2 tableaux (strig/num)
  IF (utl_file.IS_OPEN(pck_igoitf.pfile) AND PCK_IGOITF.ITF_MODE > PCK_IGOITF.TRC_MID) THEN utl_file.putf(pck_igoitf.pfile,'F_TRT_MAJ cptr=%s \n',cptr); END IF;
  --DBMS_OUTPUT.PUT_LINE('F_TRT_MAJ ctr='||cptr);
  RETURN cptr;
END;


What I obtain is :
F_TRT_MAJ stmt=
  DECLARE
    arr_va2 sys.OdciVarchar2List;
    arr_num sys.OdciNumberList;
  BEGIN
    arr_va2 := :1;
    arr_num := :2;
     --
    UPDATE COSWIN.T_EQUIPMENT
       SET EREQ_FUNCTION=arr_va2(27),
EREQ_PARENT_EQUIPMENT=arr_va2(13),
EREQ_SYSTEM_EQUIPMENT=arr_va2(13),
EREQ_NUMBER1=arr_num(5),
EREQ_NUMBER2=arr_num(7),
EREQ_DATE1=arr_va2(15),
EREQ_STRING2=arr_va2(8),
EREQ_ENTITY=arr_va2(13),
EREQ_STRING3=arr_va2(11),
EREQ_STRING4=arr_va2(10),
EREQ_STRING5=arr_va2(28),
EREQ_STRING9=arr_va2(12),
EREQ_STRING10=arr_va2(5),
EREQ_STRING11=arr_va2(30),
EREQ_STRING12=arr_va2(18),
EREQ_NUMBER4=arr_num(2),
EREQ_BOOLEAN1=arr_num(3),
EREQ_BOOLEAN2=arr_num(4),
EREQ_BOOLEAN3=arr_num(1),
EREQ_LONG_STRING=arr_va2(1),
EREQ_EQUIPMENT_STATUS=arr_va2(21),
EREQ_SYSTEM_STATUS=arr_num(6),
EREQ_DESCRIPTION_EXTRA2=arr_va2(22),
EREQ_DESCRIPTION_EXTRA3=arr_va2(25),
EREQ_STRING13=arr_va2(9),
EREQ_STRING14=arr_va2(4),
EREQ_STRING15=arr_va2(7),
EREQ_STRING16=arr_va2(6),
EREQ_STRING23=arr_va2(26),
EREQ_STRING25=arr_va2(17),
EREQ_LONG_STRING1=arr_va2(24),
EREQ_LONG_STRING2=arr_va2(23),
EREQ_LONG_STRING3=arr_va2(20),
EREQ_LONG_STRING4=arr_va2(19),
EREQ_LONG_STRING5=arr_va2(19),
EREQ_SYSTEM_FUNCTION=arr_va2(27)
     WHERE EREQ_CODE=arr_va2(3);
    :x := sql%rowcount;  
  END;
   
F_TRT_MAJ cptr=1 


How can I print the statement wih the values of array's elements (instead of elements names) ?

Regards

[Updated on: Fri, 03 November 2017 05:44]

Report message to a moderator

Re: PL/SQL:How to use VARRAY in the USING clause of EXECUTE IMMEDIATE? [message #666415 is a reply to message #666414] Fri, 03 November 2017 05:58 Go to previous messageGo to next message
John Watson
Messages: 7148
Registered: January 2010
Location: Global Village
Senior Member
Can you use a RETURNING clause?
orclx>
orclx> var n number
orclx> update emp set sal=sal+1 where ename='KING' returning sal into :n;

1 row updated.

orclx> print n

         N
----------
      5001

orclx>
Re: PL/SQL:How to use VARRAY in the USING clause of EXECUTE IMMEDIATE? [message #666416 is a reply to message #666415] Fri, 03 November 2017 06:50 Go to previous messageGo to next message
moracles
Messages: 6
Registered: July 2017
Junior Member
Thanks John,
but it isn't what I need. I'd like to print something like :
'    UPDATE COSWIN.T_EQUIPMENT
       SET EREQ_FUNCTION='aaa' -- arr_va2(27),
EREQ_PARENT_EQUIPMENT='bbb' --arr_va2(13),
EREQ_SYSTEM_EQUIPMENT='ccc' --arr_va2(13),
...'
Any idea ?


Re: PL/SQL:How to use VARRAY in the USING clause of EXECUTE IMMEDIATE? [message #666417 is a reply to message #666416] Fri, 03 November 2017 06:54 Go to previous messageGo to next message
John Watson
Messages: 7148
Registered: January 2010
Location: Global Village
Senior Member
You want to know the values, so return them into variables and print them. I've shown you an example in SQL*Plus, I'm not going to write the code for you.
Re: PL/SQL:How to use VARRAY in the USING clause of EXECUTE IMMEDIATE? [message #666501 is a reply to message #666417] Thu, 09 November 2017 04:00 Go to previous message
moracles
Messages: 6
Registered: July 2017
Junior Member
John,
As I use the varrays parameters, the returning clause does not seem adequate, unless I did not understand your idea ...
The number of elements in each 'IN' varray parameter is variable.
In the simple example below, P_STMT_WHERE contains only one value.
But we have other, similar PLSQL functions with multiple parameters for insert and update statements.
I'd like to be able to log the final executed code.

Ex; the function R_REC_ENR runs with :

Quote:

OK : arr_va2(7)=000001W=
KO : P_STMT_WHERE=CHEN_CODE=arr_va2(7)=
The parameter's value is returned for arr_va2(7) in the line 'OK:', but not in the following line 'KO:'
I don't know if it's possible to print the complete SQL statement containing the values of bind varray variables.
Such as :
Quote:

P_STMT_WHERE=CHEN_CODE=000001W=
EX:
FUNCTION F_REC_ENR(P_TAB_BD VARCHAR2, P_STMT_WHERE VARCHAR2, P_VARRAY_VA2 sys.OdciVarchar2List, P_VARRAY_NUM sys.OdciNumberList) RETURN NUMBER IS
stmt varchar2(32000);
cptr number := 0;
BEGIN
    --
  stmt :=q'[
  DECLARE
    arr_va2 sys.OdciVarchar2List;
    arr_num sys.OdciNumberList;
    res varchar2(10);
  BEGIN
    arr_va2 := :1;
    arr_num := :2;
    -- ***
    dbms_output.put_line('OK : arr_va2(7)='||arr_va2(7)||'=');
    dbms_output.put_line('KO : P_STMT_WHERE=]'||P_STMT_WHERE||q'[= ');
    SELECT null into res FROM ]'||P_TAB_BD||q'[ WHERE ]'|| P_STMT_WHERE ||q'[;
    :x := sql%rowcount;  
  END;
  ]';
  execute immediate stmt using P_VARRAY_VA2, P_VARRAY_NUM, OUT cptr; 
  --goal : dbms_output.put_line('F_REC_ENR stmt='||stmt||'='); END IF;
  RETURN cptr;
END;

Regards
Previous Topic: Merging of Interval Partitions
Next Topic: Pivot columns and dynamically assign values based on column name
Goto Forum:
  


Current Time: Sun Nov 19 23:45:43 CST 2017

Total time taken to generate the page: 0.08612 seconds