Re: PL/SQL Loop performance

Here is a way to search only for code examples in the Oracle database server docs:

http://tahiti.oracle.com/pls/tahiti/tahiti.drilldown?word=bulk+collect+and+nested+table&book=&preference=Examples

This search turns up only one example, at:

http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/appdev.817/a77069/11_elems.htm#12185

John

On Wed, 24 Jan 2001 23:05:09 GMT, eric_levin_at_my-deja.com wrote:

>Do you have any syntax examples how to iterrate over a nested table
>using bulkcopy.
>
>In article <94nj8v$u88$1_at_nnrp1.deja.com>,
> jdarrah_co_at_my-deja.com wrote:
>> if you are running 8.1.5 or higher you can BULK COLLECT the data into
>> varrays or nested tables then iterate over the array.
>>
>> In article <94ndl1$ojn$1_at_nnrp1.deja.com>,
>> eric_levin_at_my-deja.com wrote:
>> > I am writing a procedure that loops through symbols and their
 phrases
 and
>> > prints them to an output file. One symbol and many phrases on a
 line.
 The
>> > cursors run quickly but the entire procedure takes 40 seconds. Is
 their a way
>> > to improve the speed. The procedure can be seen below.
>> >
>> > CREATE OR REPLACE PROCEDURE symbols_gram(login2 varchar2)
>> > AS
>> > --Variables to input into the file
>> > v_sym_num lists.symbol.sym_num%TYPE;
>> > v_ticker lists.symbol.ticker%TYPE;
>> > v_class lists.symbol.class%TYPE;
>> > v_exchange lists.symbol.sym_exchange%TYPE;
>> > v_sym_num2 lists.symbol_phrase.sym_num%TYPE;
>> > v_symbol_phrase lists.symbol_phrase.phrase%TYPE;
>> > v_sym_num_bind lists.symbol.sym_num%TYPE;
>> > --File Handle
>> > v_filehandle UTL_FILE.FILE_TYPE;
>> > --Cursors
>> > CURSOR c_symbol IS
>> > select distinct s.sym_num, s.ticker, s.class, s.sym_exchange
>> > from
>> > lists.symbol s,
>> > lists.symbol_phrase sp,
>> > lists.phrase_user_xref xref,
>> > lists.umevoice_user u
>> > where
>> > u.login = login2
>> > and u.user_num = xref.user_num
>> > and xref.sym_phrase_num = sp.sym_phrase_num
>> > and sp.sym_num = s.sym_num;
>> >
>> > CURSOR c_phrase IS select sp.sym_num, sp.phrase from
>> > lists.symbol_phrase sp, lists.phrase_user_xref xref,
>> > lists.umevoice_user u where u.login = login2 and
 sp.sym_num =
>> > v_sym_num_bind and u.user_num = xref.user_num and
>> > xref.sym_phrase_num = sp.sym_phrase_num; BEGIN --Open file to write
 

>> > v_filehandle := UTL_FILE.FOPEN('c:\grammars','symbols.gram','w'); --
 Static
>> > Data before first loop UTL_FILE.PUT_LINE
 (v_filehandle,'grammar
>> > com.umevoice.uvautex.'||login2||'.symbols;');
>> > UTL_FILE.PUT_LINE(v_filehandle,'<uv_new_symbols>=( ('); --Loop
 through
>> > c_symbol cursor OPEN c_symbol; LOOP
 

>> > FETCH c_symbol INTO v_sym_num, v_ticker, v_class, v_exchange;
 

>> > EXIT WHEN c_symbol%NOTFOUND; IF c_symbol%
 ROWCOUNT =
>> > 1 THEN
>> > UTL_FILE.PUT(v_filehandle,'<'||lower(v_ticker)
>> ||v_class||v_exchange||'>');
>> > ELSIF c_symbol%FOUND THEN
 

>> > UTL_FILE.PUT(v_filehandle,' |
>> > <'||lower(v_ticker)||v_class||v_exchange||'>');
 

>> > ELSE EXIT; END IF;
 
>> > UTL_FILE.PUT_LINE(v_filehandle,null); END LOOP;
>> > CLOSE c_symbol; UTL_FILE.PUT_LINE(v_filehandle,') {$TICKER})');
>> > --static data before second loop UTL_FILE.PUT_LINE
 (v_filehandle,'{');
>> > UTL_FILE.PUT_LINE(v_filehandle,'symbolAction=setsymbol;');
>> > UTL_FILE.PUT_LINE(v_filehandle,'symbolValue =$TICKER.SYM;');
>> > UTL_FILE.PUT_LINE(v_filehandle,'};'); --Loop through table and input
 into
>> > file OPEN c_symbol; LOOP FETCH c_symbol
 INTO
>> > v_sym_num, v_ticker, v_class, v_exchange; EXIT WHEN
>> > c_symbol%NOTFOUND;
>> > UTL_FILE.PUT(v_filehandle,'<'||lower(v_ticker)
>> ||v_class||v_exchange||'>=(');
>> > v_sym_num_bind := v_sym_num; OPEN
>> > c_phrase; LOOP FETCH

 c_phrase
>> > INTO v_sym_num2, v_symbol_phrase; EXIT WHEN
>> > c_phrase%NOTFOUND; IF c_phrase%ROWCOUNT = 1 THEN
 

>> > UTL_FILE.PUT(v_filehandle,v_symbol_phrase);
 

>> > ELSE UTL_FILE.PUT(v_filehandle,' | '||v_symbol_phrase);
 

>> > END IF; END LOOP;
 
>> > CLOSE c_phrase;
>> > UTL_FILE.PUT_LINE(v_filehandle,')');
>> > UTL_FILE.PUT_LINE(v_filehandle,'{');
>> >
>> > UTL_FILE.PUT_LINE

 (v_filehandle,'SYM='||v_ticker||':'||v_class||':'||v_exchange||';');
>> > UTL_FILE.PUT_LINE(v_filehandle,'};');
>> > END LOOP;
>> >
>> > CLOSE c_symbol;
>> > --Close file
>> > UTL_FILE.FCLOSE(v_filehandle);
>> > --Error Messages
>> > EXCEPTION
>> > WHEN OTHERS THEN
>> > DBMS_OUTPUT.PUT_LINE('ERROR '||to_char(SQLCODE)
>> ||SQLERRM);
>> > NULL;
>> > END;
>> > /
>> >
>> > Sent via Deja.com
>> > http://www.deja.com/
>> >
>>
>> Sent via Deja.com
>> http://www.deja.com/
>>
>
>
>Sent via Deja.com
>http://www.deja.com/
Received on Thu Jan 25 2001 - 01:40:44 CST