Home » SQL & PL/SQL » SQL & PL/SQL » Execute Immediate / Forall (Oracle 9i)
Execute Immediate / Forall [message #334737] Thu, 17 July 2008 15:43 Go to next message
leonardo_siza
Messages: 6
Registered: July 2008
Junior Member
Dears, i'm using:

DECLARE
TYPE T_REPOSITORIO IS TABLE OF OTC_T_TRAFICOLOCUTORIOS%ROWTYPE;
VT_REPOSITORIO T_REPOSITORIO;
P_CICLO NUMBER:= 80208;
P_CODIGO NUMBER:= 123456;
BEGIN

V_SQL := 'SELECT NUM_CLIE, NUM_ABON, RECORD_TYPE, '||
'IND_BILLETE, COD_AGRL, A_SUSC_NUMBER, '||
'B_SUSC_NUMBER, DATE_START_CHARG, TIME_START_CHARG, '||
'COD_CICLFACT, DUR_REAL, DES_DESTINO '||
'FROM FA_DETCELULAR_'||P_CICLO||
' WHERE NUM_CLIE='||P_CODIGO;

EXECUTE IMMEDIATE V_SQL BULK COLLECT INTO VT_REPOSITORIO;

FORALL J IN 1..VT_REPOSITORIO.COUNT SAVE EXCEPTIONS
INSERT INTO ECU_REPORTES.OTC_T_TRAFICOLOCUTORIOS VALUES VT_REPOSITORIO(J);

COMMIT;
end;

This select contains more than 7 millions of records.
How could I use LIMIT same as :
"FETCH a_cur BULK COLLECT INTO cur_array LIMIT 100"

Regards
Leonardo Siza
Re: Execute Immediate / Forall [message #334738 is a reply to message #334737] Thu, 17 July 2008 15:47 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above


As with most programming, first make WORK, then make it fancy!

Learn how to properly code BULK COLLECT using static names before getting fancy with EXECUTE IMMEDIATE!
Re: Execute Immediate / Forall [message #334741 is a reply to message #334738] Thu, 17 July 2008 15:59 Go to previous messageGo to next message
leonardo_siza
Messages: 6
Registered: July 2008
Junior Member
If I could make static statement. I would not use this forum
Don't you think?
Re: Execute Immediate / Forall [message #334743 is a reply to message #334741] Thu, 17 July 2008 16:06 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
leonardo_siza wrote on Thu, 17 July 2008 13:59
If I could make static statement. I would not use this forum
Don't you think?


So you are admitting that you are incapable or unwilling to RTFM to obtain valid syntax.

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/tuning.htm#sthref2216

You're On Your Own (YOYO)!
Re: Execute Immediate / Forall [message #334744 is a reply to message #334737] Thu, 17 July 2008 16:06 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
You have all those information in the oracle reference manual.

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/dynamic.htm

But what puzzles me is why do you want to do it in pl/sql when you can do it in straight forward sql ?

Regards

Raj
Re: Execute Immediate / Forall [message #334746 is a reply to message #334737] Thu, 17 July 2008 16:09 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
I think this is the syntax you want:

OPEN v_cur FOR '...'

LOOP

    FETCH v_cur BULK COLLECT
    INTO v_rowid
    LIMIT c_limit;

    EXIT WHEN v_rowid.COUNT = 0;

END LOOP;
Re: Execute Immediate / Forall [message #334747 is a reply to message #334744] Thu, 17 July 2008 16:12 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
OK, I'll bite. How can he do this is with "straight forward sql" if the table name is dynamic?
Re: Execute Immediate / Forall [message #334748 is a reply to message #334747] Thu, 17 July 2008 16:17 Go to previous messageGo to next message
leonardo_siza
Messages: 6
Registered: July 2008
Junior Member
yes!
You are right!

The ideal would be to replace '...' in

OPEN v_cur FOR '...'

by

'SELECT NUM_CLIE, NUM_ABON, RECORD_TYPE, '||
'IND_BILLETE, COD_AGRL, A_SUSC_NUMBER, '||
'B_SUSC_NUMBER, DATE_START_CHARG, TIME_START_CHARG, '||
'COD_CICLFACT, DUR_REAL, DES_DESTINO '||
'FROM FA_DETCELULAR_'||P_CICLO||
' WHERE NUM_CLIE='||P_CODIGO
Re: Execute Immediate / Forall [message #334749 is a reply to message #334746] Thu, 17 July 2008 16:19 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
Oh ya, I assuming you have a very good reason you can't just do this
DECLARE
    v_sql VARCHAR2(4000);
BEGIN

    v_sql := '
    INSERT INTO ecu_reportes.otc_t_traficolocutorios
    SELECT num_clie
          ,num_abon
          ,record_type
          ,ind_billete
          ,cod_agrl
          ,a_susc_number
          ,b_susc_number
          ,date_start_charg
          ,time_start_charg
          ,cod_ciclfact
          ,dur_real
          ,des_destino
    FROM fa_detcelular_ ' || p_ciclo || '
    WHERE num_clie = :p_codigo';

    EXECUTE IMMEDIATE v_sql USING p_codigo;
END;
Re: Execute Immediate / Forall [message #334751 is a reply to message #334749] Thu, 17 July 2008 16:27 Go to previous messageGo to next message
leonardo_siza
Messages: 6
Registered: July 2008
Junior Member
Exactly. There are 7 millions of records for each :p_codigo

I'm trying to use bulk collect to improve delay time ot put data into ecu_reportes.otc_t_traficolocutorios

And i dont know how to use this sentence to save each 100000 records maybe in my process:

"FETCH a_cur BULK COLLECT INTO cur_array LIMIT 100"
Re: Execute Immediate / Forall [message #334753 is a reply to message #334751] Thu, 17 July 2008 16:39 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
Hmmm, it seems you need to back up here. The default method would be the single insert statement. Unless there is a good reason not to use it, then use it. I don't understand what 7 million records has to do with anything. Are you concerned with rollback? Do you need row level error checking? If you are looking for performance, nothing is going to be faster. If you still think you need the cursor, then just do what you put your SELECT in the cursor as you have noted. It will work.
Re: Execute Immediate / Forall [message #334754 is a reply to message #334753] Thu, 17 July 2008 16:52 Go to previous messageGo to next message
leonardo_siza
Messages: 6
Registered: July 2008
Junior Member
Well, thank you for your time scottwmackey.
I will trying to find a way to improve insert select process in time response of many rows using bulk collect.

Bye
Re: Execute Immediate / Forall [message #334815 is a reply to message #334754] Fri, 18 July 2008 03:37 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
@leonardo,

You might have missed Scotts point.

What he's telling you, quite correctly, is that nothing involving Pl/Sql is going to be faster than doing the single INSERT statement that he showed.
If speed is your requirement, that is the way to go.
Re: Execute Immediate / Forall [message #334846 is a reply to message #334747] Fri, 18 July 2008 05:58 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I saw that. Probably I should have re-worded slightly better. I know that it is not possible doing it in native sql. The reason why I put such a comment is atleast OP might think about re-visiting the design, if they want the solution to be scalable. It is always a very bad practice deriving your table_name at runtime.

Regards

Raj

[Updated on: Fri, 18 July 2008 06:01]

Report message to a moderator

Re: Execute Immediate / Forall [message #335037 is a reply to message #334846] Sat, 19 July 2008 18:36 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
OK, then maybe you could explain how deriving the table name at run time effects scalability. Other than the soft parse, there is no difference.
Re: Execute Immediate / Forall [message #335062 is a reply to message #335037] Sun, 20 July 2008 11:09 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:
Other than the soft parse, there is no difference

How about context switching ?
How about dependency ?
How about sql injection ?

I am not saying the code you suggested contains all these things. What i am trying to convey is most of the dynamic sql I have seen contains all these above mentioned features which is not good for a healthy database.

Regards

Raj
Re: Execute Immediate / Forall [message #335085 is a reply to message #335062] Sun, 20 July 2008 22:25 Go to previous message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
What do any of these have to do with dynamic SQL's impact on scalability, which was original stated concern with the OP using dynamic SQL?
Previous Topic: ORA-00942: table or view does not exist
Next Topic: How to add storage parameters after creation of table for LOB column
Goto Forum:
  


Current Time: Fri Dec 09 23:29:24 CST 2016

Total time taken to generate the page: 0.33890 seconds