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? [message #664300 is a reply to message #664299] |
Tue, 11 July 2017 10:38 |
|
moracles
Messages: 16 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 #664321 is a reply to message #664300] |
Tue, 11 July 2017 15:34 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
moracles wrote on Tue, 11 July 2017 08:38Thanks, 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 #666414 is a reply to message #664322] |
Fri, 03 November 2017 05:42 |
|
moracles
Messages: 16 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 #666501 is a reply to message #666417] |
Thu, 09 November 2017 04:00 |
|
moracles
Messages: 16 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
|
|
|
Goto Forum:
Current Time: Fri Sep 27 17:30:59 CDT 2024
|