Home » SQL & PL/SQL » SQL & PL/SQL » execute immediate (merged)
| execute immediate (merged) [message #672747] |
Fri, 26 October 2018 05:45  |
 |
mail_valeria
Messages: 7
Registered: October 2018
|
Junior Member |
|
|
Hi all, I have a SQL string to execute via the immediate execute:
I did print the string on the screen and it is correct.
executing the printed query the insert in the table is executed correctly.
Executing EXECUTE IMMEDIATE STRQUERY; COMMIT; the insert is not executed.
I entered the SQL% ROWCOUNT checks and this happens:
when it generates the string of the dynamic sql, before the 'EXECUTE IMMEDIATE' the count is rightly 1
as the immediate execute performs the count mysteriously becomes 0 and therefore does not insert the record in the table.
I inserted an EXCEPTION WHEN OTHERS to see if the statement returned any type of error but did not return any errors.
|
|
|
|
|
|
|
|
|
|
| Re: execute immediate (merged) [message #672754 is a reply to message #672752] |
Fri, 26 October 2018 07:33  |
|
mail_valeria
Messages: 7
Registered: October 2018
|
Junior Member |
|
|
This is may code:
BEGIN
dbms_output.put_line ('dentro ZVM_F_LINEA_ATTESTATA v_montanteEI*' ||v_montanteEI||'*v_changeset_id*'||v_changeset_id|| '*p_data_estrazione*'|| p_data_estrazione );
q_montanteEI:= v_montanteEI;
q_changeset_id:=v_changeset_id;
q_data_estrazione := to_char(p_data_estrazione,'yyyymmdd');
dbms_output.put_line ('dentro ZVM_F_LINEA_ATTESTATA q_montanteEI*' ||q_montanteEI||'*q_changeset_id*'||q_changeset_id|| '*q_data_estrazione*'|| q_data_estrazione );
STRQUERY:= 'insert into aui_legacy.stm_linea
(DATA_VALI,
COD_ORG_LINEA,COD_LINEA,STATO ,COD_ORG_NODO,SER_NODO ,NUM_NODO ,ID_SIST_SBAR ,ID_MONTANTE,
TIPO_MORS,NUME_MORS,
DENOMINAZ,GR_PESSE ,PRES_AUTO ,N_SB_STM ,DATA_ESTRAZIONE,
stm_linea_ID,im_nodo,ik_nodo,il_nodo,ACTION)
select l.data_vali as v_DATA_VALI,
trim(l.cod_org) as v_COD_ORG_LINEA,
trim(l.cod_linea) as v_COD_LINEA,
l.stato as v_STATO,
trim(n.cod_org) v_COD_ORG_NODO,
n.ser_nodo v_SER_NODO,
lpad(n.num_nodo,6,''0'') as v_NUM_NODO,
ss.id_sist_sbar v_ID_SIST_SBAR,
m.ID_MONTANTE as v_ID_MONTANTE,
L.TIPO_MORS_P as v_TIPO_MORS,
L.MORSETTO_P v_NUME_MORS,
L.DENOMINAZIONE v_DENOMINAZ,
null,
null,
null, to_date(''' || q_data_estrazione || ''', ''yyyymmdd'') ,
stm_linea_seq.nextval,
l.im_nodo_p im_nodo,
l.ik_nodo_p ik_nodo,
l.il_nodoe_p il_nodo ,
''M'' as v_action ---???? da controllare non sono sicura
from aui_legacy.linea_incr l, -- per passare da linea_dat a linea sostituito il_nodo_p con il_nodoe_p
aui_legacy.nodo n, -- sostituito tam_nodo_dat con tam_nodo
tam_montante m, -- -- sostituito tam_montante_dat con tam_montante
aui_legacy.sist_sbar ss
,( select substr(l.line_cod,1,instr(l.line_cod,''.'')-1) as CO ,substr(l.line_cod,instr(l.line_cod,''.'')+1) as CL
from ten_bay b, ten_connection_node cn,
ten_connection_arc ca, ten_line_segment ls, ten_line l, ten_station s
where b.bay_id = '|| q_montanteEI ||'
and b.bay_id = cn.bay_id
and cn.connection_node_id = ca.connection_node_id
and ca.line_segment_id = ls.line_segment_id
and ls.line_id = l.line_id
and cn.station_id = s.station_id
and s.legacy_node_type = 1
and cn.legacy_node_type in (1,8) ) LA
where 1=1
and L.IM_NODO_P=N.IM_NODO and L.IK_NODO_P=N.IK_NODO and L.IL_NODOE_P=n.il_nodo
and L.IM_MONTANTE_P=M.IM_MONTANTE and L.IK_MONTANTE_P=M.IK_MONTANTE and L.IL_MONTANTE_P=M.IL_MONTANTE
and M.IM_SIST_SBAR=SS.IM_SIST_SBAR and M.IK_SIST_SBAR=SS.IK_SIST_SBAR and M.IL_SIST_SBAR=SS.IL_SIST_SBAR
and ((l.stato in (''I'',''C'', ''E'', ''F'', ''U'', ''D'') and '|| q_changeset_id ||' is not null) or (l.stato=''E'' and '|| q_changeset_id ||' is null))
and l.assetto = ''S''
and trim(l.cod_org) like trim(LA.CO) and trim(l.cod_linea) like trim(LA.CL)';
/* and to_date(''' ||q_data_estrazione ||''' ,''yyyymmdd'') >= nvl(l.data_vali, to_date ('''|| q_data_estrazione|| ''' ,''yyyymmdd'') )
and to_date('''|| q_data_estrazione ||''' ,''yyyymmdd'') >= nvl(n.data_vali(+), to_date ('''|| q_data_estrazione || ' ,''yyyymmdd''))
and to_date(''' || q_data_estrazione ||''' ,''yyyymmdd'') >= nvl(m.data_mod, to_date ('''|| q_data_estrazione || ''' ,''yyyymmdd''))
and to_date(''' || q_data_estrazione ||''' ,''yyyymmdd'') >= nvl(ss.data_vali, to_date ('''|| q_data_estrazione || ''' ,''yyyymmdd'')) ';
*/
dbms_output.put_line ('conto: prima di execute immediate e commit ' ||SQL%ROWCOUNT);
dbms_output.put_line ('Query: ' ||STRQUERY);
execute immediate STRQUERY;
commit;
dbms_output.put_line ('conto dopo execute e commit: ' ||SQL%ROWCOUNT); |
|
|
|
| Re: execute immediate (merged) [message #672755 is a reply to message #672754] |
Fri, 26 October 2018 07:40 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
at the following place in your code you are using the variable Q_changeset_id. You have it bare in your string and it won't work. for example if the value if the variable is "123" the string to be executed would be
and ((l.stato in ('I','C', 'E', 'F', 'U', 'D') and 123 is not null) or (l.stato='E' and 123 is null))
Your code should be
and ((l.stato in (''I'',''C'', ''E'', ''F'', ''U'', ''D'') and '''|| q_changeset_id ||''' is not null) or (l.stato=''E'' and '''|| q_changeset_id ||''' is null))
[Updated on: Fri, 26 October 2018 07:48] Report message to a moderator |
|
|
|
| Re: execute immediate (merged) [message #672759 is a reply to message #672755] |
Fri, 26 October 2018 07:48 |
|
mail_valeria
Messages: 7
Registered: October 2018
|
Junior Member |
|
|
q_changeset_id is a number;
if I execute the string through TOAD and not from procedures with EXECUTE IMMEDIATE the INSERT is executed correctly
this is the result of the dbms_output.put_line:
insert into aui_legacy.stm_linea
(DATA_VALI,
COD_ORG_LINEA,COD_LINEA,STATO ,COD_ORG_NODO,SER_NODO ,NUM_NODO ,ID_SIST_SBAR ,ID_MONTANTE,
TIPO_MORS,NUME_MORS,
DENOMINAZ,GR_PESSE ,PRES_AUTO ,N_SB_STM ,DATA_ESTRAZIONE,
stm_linea_ID,im_nodo,ik_nodo,il_nodo,ACTION)
select l.data_vali as v_DATA_VALI,
trim(l.cod_org) as v_COD_ORG_LINEA,
trim(l.cod_linea) as v_COD_LINEA,
l.stato as v_STATO,
trim(n.cod_org) v_COD_ORG_NODO,
n.ser_nodo v_SER_NODO,
lpad(n.num_nodo,6,'0') as v_NUM_NODO,
ss.id_sist_sbar v_ID_SIST_SBAR,
m.ID_MONTANTE as v_ID_MONTANTE,
L.TIPO_MORS_P as v_TIPO_MORS,
L.MORSETTO_P v_NUME_MORS,
L.DENOMINAZIONE v_DENOMINAZ,
null,
null,
null, to_date('20181026', 'yyyymmdd') ,
stm_linea_seq.nextval,
l.im_nodo_p im_nodo,
l.ik_nodo_p ik_nodo,
l.il_nodoe_p il_nodo ,
'M' as v_action ---???? da controllare non sono sicura
from aui_legacy.linea_incr l, -- per passare da linea_dat a linea sostituito il_nodo_p con il_nodoe_p
aui_legacy.nodo n, -- sostituito tam_nodo_dat con tam_nodo
tam_montante m, -- -- sostituito tam_montante_dat con tam_montante
aui_legacy.sist_sbar ss
,( select substr(l.line_cod,1,instr(l.line_cod,'.')-1) as CO ,substr(l.line_cod,instr(l.line_cod,'.')+1) as CL
from ten_bay b, ten_connection_node cn,
ten_connection_arc ca, ten_line_segment ls, ten_line l, ten_station s
where b.bay_id = 1677121
and b.bay_id = cn.bay_id
and cn.connection_node_id = ca.connection_node_id
and ca.line_segment_id = ls.line_segment_id
and ls.line_id = l.line_id
and cn.station_id = s.station_id
and s.legacy_node_type = 1
and cn.legacy_node_type in (1,8) ) LA
where 1=1
and L.IM_NODO_P=N.IM_NODO and L.IK_NODO_P=N.IK_NODO and L.IL_NODOE_P=n.il_nodo
and L.IM_MONTANTE_P=M.IM_MONTANTE and L.IK_MONTANTE_P=M.IK_MONTANTE and L.IL_MONTANTE_P=M.IL_MONTANTE
and M.IM_SIST_SBAR=SS.IM_SIST_SBAR and M.IK_SIST_SBAR=SS.IK_SIST_SBAR and M.IL_SIST_SBAR=SS.IL_SIST_SBAR
and ((l.stato in ('I','C', 'E', 'F', 'U', 'D') and 642 is not null) or (l.stato='E' and 642 is null))
and l.assetto = 'S'
and trim(l.cod_org) like trim(LA.CO) and trim(l.cod_linea) like trim(LA.CL)
|
|
|
|
| Re: execute immediate (merged) [message #672762 is a reply to message #672759] |
Fri, 26 October 2018 07:53 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
asking if a number is not null makes no sense. lets look at the following examples.
The variable q_changeset_i is equal to 6, the code generated would be
) and 6 is not null)
which would be meaningless since a number will always be not null.
The variable q_changeset_i is null. The code generated would be
) and is not null)
Which would fail.
The way to do it correctly is to surround it with quotes so the test for the number would be
) and '123' is not null)
or
) and '' is not null)
Which would correctly work
|
|
|
|
|
|
|
|
|
|
| Re: execute immediate (merged) [message #672768 is a reply to message #672766] |
Fri, 26 October 2018 08:35 |
cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
BlackSwan wrote on Fri, 26 October 2018 14:20>execute immediate STRQUERY;
I believe that above is done in/by a different session & not by same session that prepared STRQUERY variable.
You believe wrong
|
|
|
|
|
|
| Re: execute immediate (merged) [message #672770 is a reply to message #672762] |
Fri, 26 October 2018 08:38 |
cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Bill B wrote on Fri, 26 October 2018 13:53asking if a number is not null makes no sense.
Just because it doesn't make logical sense doesn't mean oracle has a problem with it:
SQL> SELECT * FROM dual WHERE 1 IS NULL;
DUMMY
-----
SQL> SELECT * FROM dual WHERE 1 IS NOT NULL;
DUMMY
-----
X
|
|
|
|
|
|
| Re: execute immediate (merged) [message #672772 is a reply to message #672770] |
Fri, 26 October 2018 08:43 |
cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Your problem is you're not counting what you think you're counting.
sql%rowcount gives the number of rows processed by the last SQL statement issued.
COMMIT is a SQL statement.
So you're counting the rows processed by commit - which always gives 0 since oracle obviously can't be bothered to keep track.
Move the rowcount check so it's before the commit.
|
|
|
|
| Re: execute immediate (merged) [message #672774 is a reply to message #672771] |
Fri, 26 October 2018 08:46 |
cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Bill B wrote on Fri, 26 October 2018 14:40I never said it wouldn't work, I said it made no sense to do it but if hes building a string and the number is null his query will fail
True, didn't think about it that way, should have read your previous post more carefully.
Though really the correct solution to that issue is use bind variables rather than concatenate in. Then the null check will work just fine.
e.g.
and ((l.stato in (''I'',''C'', ''E'', ''F'', ''U'', ''D'') and :3 is not null) or (l.stato=''E'' and :4 is null))
......
......
execute immediate STRQUERY USING q_data_estrazione, q_montanteEI, q_changeset_id, q_changeset_id;
|
|
|
|
| Re: execute immediate (merged) [message #672775 is a reply to message #672774] |
Fri, 26 October 2018 08:49 |
cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
|
All that said the true correct solution to all issues here is to stop using dynamic SQL altogether since there's absolutely nothing dynamic about that insert statement and just use normal static SQL instead.
|
|
|
|
| Re: execute immediate (merged) [message #672776 is a reply to message #672774] |
Fri, 26 October 2018 08:50 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
|
My solution was to try to fit it within his current code, but I agree with you 100%. I ALWAYS use binds if I am using execute immediate and it is used passed variables. Directly building a string can make you vulnerable to sql injection.
|
|
|
|
| Re: execute immediate (merged) [message #672778 is a reply to message #672774] |
Fri, 26 October 2018 08:52 |
|
mail_valeria
Messages: 7
Registered: October 2018
|
Junior Member |
|
|
this is my code, but I have not solved the problem
dbms_output.put_line ('dentro ZVM_F_LINEA_ATTESTATA v_montanteEI*' ||v_montanteEI||'*v_changeset_id*'||v_changeset_id|| '*p_data_estrazione*'|| p_data_estrazione );
q_montanteEI:= v_montanteEI;
q_changeset_id:=v_changeset_id;
q_data_estrazione := to_char(p_data_estrazione,'yyyymmdd');
dbms_output.put_line ('dentro ZVM_F_LINEA_ATTESTATA q_montanteEI*' ||q_montanteEI||'*q_changeset_id*'||q_changeset_id|| '*q_data_estrazione*'|| q_data_estrazione );
STRQUERY:= 'insert into aui_legacy.stm_linea
(DATA_VALI,
COD_ORG_LINEA,COD_LINEA,STATO ,COD_ORG_NODO,SER_NODO ,NUM_NODO ,ID_SIST_SBAR ,ID_MONTANTE,
TIPO_MORS,NUME_MORS,
DENOMINAZ,GR_PESSE ,PRES_AUTO ,N_SB_STM ,DATA_ESTRAZIONE,
stm_linea_ID,im_nodo,ik_nodo,il_nodo,ACTION)
select l.data_vali as v_DATA_VALI,
trim(l.cod_org) as v_COD_ORG_LINEA,
trim(l.cod_linea) as v_COD_LINEA,
l.stato as v_STATO,
trim(n.cod_org) v_COD_ORG_NODO,
n.ser_nodo v_SER_NODO,
lpad(n.num_nodo,6,''0'') as v_NUM_NODO,
ss.id_sist_sbar v_ID_SIST_SBAR,
m.ID_MONTANTE as v_ID_MONTANTE,
L.TIPO_MORS_P as v_TIPO_MORS,
L.MORSETTO_P v_NUME_MORS,
L.DENOMINAZIONE v_DENOMINAZ,
null,
null,
null, to_date(''' || q_data_estrazione || ''', ''yyyymmdd'') ,
stm_linea_seq.nextval,
l.im_nodo_p im_nodo,
l.ik_nodo_p ik_nodo,
l.il_nodoe_p il_nodo ,
''M'' as v_action ---???? da controllare non sono sicura
from aui_legacy.linea_incr l, -- per passare da linea_dat a linea sostituito il_nodo_p con il_nodoe_p
aui_legacy.nodo n, -- sostituito tam_nodo_dat con tam_nodo
tam_montante m, -- -- sostituito tam_montante_dat con tam_montante
aui_legacy.sist_sbar ss
,( select substr(l.line_cod,1,instr(l.line_cod,''.'')-1) as CO ,substr(l.line_cod,instr(l.line_cod,''.'')+1) as CL
from ten_bay b, ten_connection_node cn,
ten_connection_arc ca, ten_line_segment ls, ten_line l, ten_station s
where b.bay_id = '|| q_montanteEI ||'
and b.bay_id = cn.bay_id
and cn.connection_node_id = ca.connection_node_id
and ca.line_segment_id = ls.line_segment_id
and ls.line_id = l.line_id
and cn.station_id = s.station_id
and s.legacy_node_type = 1
and cn.legacy_node_type in (1,8) ) LA
where 1=1
and L.IM_NODO_P=N.IM_NODO and L.IK_NODO_P=N.IK_NODO and L.IL_NODOE_P=n.il_nodo
and L.IM_MONTANTE_P=M.IM_MONTANTE and L.IK_MONTANTE_P=M.IK_MONTANTE and L.IL_MONTANTE_P=M.IL_MONTANTE
and M.IM_SIST_SBAR=SS.IM_SIST_SBAR and M.IK_SIST_SBAR=SS.IK_SIST_SBAR and M.IL_SIST_SBAR=SS.IL_SIST_SBAR
and ((l.stato in (''I'',''C'', ''E'', ''F'', ''U'', ''D'') and '''|| q_changeset_id ||''' is not null) or (l.stato=''E'' and '''|| q_changeset_id ||''' is null))
and l.assetto = ''S''
and trim(l.cod_org) like trim(LA.CO) and trim(l.cod_linea) like trim(LA.CL)';
/* and to_date(''' ||q_data_estrazione ||''' ,''yyyymmdd'') >= nvl(l.data_vali, to_date ('''|| q_data_estrazione|| ''' ,''yyyymmdd'') )
and to_date('''|| q_data_estrazione ||''' ,''yyyymmdd'') >= nvl(n.data_vali(+), to_date ('''|| q_data_estrazione || ' ,''yyyymmdd''))
and to_date(''' || q_data_estrazione ||''' ,''yyyymmdd'') >= nvl(m.data_mod, to_date ('''|| q_data_estrazione || ''' ,''yyyymmdd''))
and to_date(''' || q_data_estrazione ||''' ,''yyyymmdd'') >= nvl(ss.data_vali, to_date ('''|| q_data_estrazione || ''' ,''yyyymmdd'')) ';
*/
dbms_output.put_line ('conto: prima di execute immediate e commit ' ||SQL%ROWCOUNT);
dbms_output.put_line ('Query: ' ||STRQUERY);
execute immediate STRQUERY;
commit; |
|
|
|
|
|
Goto Forum:
Current Time: Fri Jun 02 03:16:59 CDT 2023
|
] 
Search
Help
Members
Register
Login
Home
