Home » SQL & PL/SQL » SQL & PL/SQL » execute immediate (merged)
execute immediate (merged) [message #672747] Fri, 26 October 2018 05:45 Go to next message
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.
Execute Immediate [message #672748 is a reply to message #672747] Fri, 26 October 2018 05:50 Go to previous messageGo to next message
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 #672750 is a reply to message #672747] Fri, 26 October 2018 06:46 Go to previous messageGo to next message
John Watson
Messages: 8344
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read the OraFAQ Forum Guide and How to use code tags and make your code easier to read

You need to show what you did.
Re: execute immediate (merged) [message #672752 is a reply to message #672750] Fri, 26 October 2018 07:21 Go to previous messageGo to next message
Bill B
Messages: 1969
Registered: December 2004
Senior Member
It's really hard to troubleshoot your code, without actually seeing the code.
Re: execute immediate (merged) [message #672754 is a reply to message #672752] Fri, 26 October 2018 07:33 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Bill B
Messages: 1969
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Bill B
Messages: 1969
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 #672764 is a reply to message #672762] Fri, 26 October 2018 08:01 Go to previous messageGo to next message
mail_valeria
Messages: 7
Registered: October 2018
Junior Member
ok, i try

thank you
Re: execute immediate (merged) [message #672766 is a reply to message #672754] Fri, 26 October 2018 08:20 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
>execute immediate STRQUERY;
I believe that above is done in/by a different session & not by same session that prepared STRQUERY variable.
Re: execute immediate (merged) [message #672767 is a reply to message #672766] Fri, 26 October 2018 08:27 Go to previous messageGo to next message
mail_valeria
Messages: 7
Registered: October 2018
Junior Member
sorry @BlackSwan , no I understand, what do you mean?
Re: execute immediate (merged) [message #672768 is a reply to message #672766] Fri, 26 October 2018 08:35 Go to previous messageGo to next message
cookiemonster
Messages: 13894
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 #672769 is a reply to message #672767] Fri, 26 October 2018 08:38 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
mail_valeria wrote on Fri, 26 October 2018 06:27
sorry @BlackSwan , no I understand, what do you mean?
For this discussion assume that Session ID=123 for the session executing posted PL/SQL code.
The Session ID for "execute immediate STRQUERY;" would be 124.
The COMMIT executed by #123 has NO impact on the DML issued by #124.
Re: execute immediate (merged) [message #672770 is a reply to message #672762] Fri, 26 October 2018 08:38 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Bill B wrote on Fri, 26 October 2018 13:53
asking 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 #672771 is a reply to message #672770] Fri, 26 October 2018 08:40 Go to previous messageGo to next message
Bill B
Messages: 1969
Registered: December 2004
Senior Member
I 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
Re: execute immediate (merged) [message #672772 is a reply to message #672770] Fri, 26 October 2018 08:43 Go to previous messageGo to next message
cookiemonster
Messages: 13894
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 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Bill B wrote on Fri, 26 October 2018 14:40
I 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 Go to previous messageGo to next message
cookiemonster
Messages: 13894
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 Go to previous messageGo to next message
Bill B
Messages: 1969
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 Go to previous messageGo to next message
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;
Re: execute immediate (merged) [message #672779 is a reply to message #672776] Fri, 26 October 2018 08:53 Go to previous message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Plus it forces a hard-parse every time it's run and makes a mess of the SGA with lots of copies of what is really same SQL statement
Previous Topic: Cast to index by table
Next Topic: Replace strings [merged by jd]
Goto Forum:
  


Current Time: Mon Sep 21 00:55:43 CDT 2020