Hi Tom,


I am using arrays extensively for about 1 year. It provides everything you need in order to centralize business logic in database instead of statement execution on a client/app server side, provide security, keep code consistent with database changes, tune up etc. etc.

For some reason Oracle is not supporting idea of stored procedures as well as it was before. Java, Java beans are not even close to benefits of stored procedures, but it is a big push to implement Oracle applications using EJB instead of stored procs with arrays as I/O.

Unfortunately arrays/ pl/sql tables are not truly integrated with client side programming.
Even if pl/sql tables are faster and easier to program I have to use
1) INDEXBYTABLE arrays for internal to database stored procedures, OCI and asp/odbc clients. Please note that only Microsoft odbc driver supports PL/SQL tables as input/output parameters. Oracle ODBC does not support and as far as I found does not plan to support it
2) VARARRAY with extend overhead looks like single way to do business with java (jdbc) clients

Please correct me if I am wrong.

Thanks
Leon

Hi Tom,
When I allocate the space for my table(TABLE OF NUMBER(17,4)) array using extend, my stored procedure takes more time but when I don't use extend, it takes less time.

Is it compulsary to use extend to allocate space ?
If Yes or No then Why ?
Can you list the Advantages and Disadvantages .
Thanks
Ravi

It depends on the type of collection (as demonstrated above).
<b>
NESTED TABLES, VARRAYS => you MUST allocate space using extend:
</b>
ops$tkyte@ORA8I.WORLD> declare
  2          type array is table of number(17,4);
  3  
  4          l_data array := array();
  5  begin
  6          l_data(1) := 1;
  7  end;
  8  /
declare
*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at line 6

<b>
PLSQL index by tables => you do not have too, in fact, you CANNOT use extend</b>

ops$tkyte@ORA8I.WORLD> declare
  2          type array is table of number(17,4) INDEX BY BINARY_INTEGER;
  3  
  4          l_data array;
  5  begin
  6          l_data(1) := 1;
  7  end;
  8  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA8I.WORLD> declare
  2          type array is table of number(17,4) INDEX BY BINARY_INTEGER;
  3  
  4          l_data array;
  5  begin
  6          l_data.extend;
  7  end;
  8  /
        l_data.extend;
        *
ERROR at line 6:
ORA-06550: line 6, column 2:
PLS-00306: wrong number or types of arguments in call to 'EXTEND'
ORA-06550: line 6, column 2:
PL/SQL: Statement ignored

 

Hi Tom,

Thanks for your reply and 8.1.7 info.

My experience make me think that using arrays much more flexible and efficient.

1) Inside of a database
Calling 1 stored proc from another with output as an array of values/cursor was twice faster with array.
2) Outside
Calling array guaranties that Oracle would not allocate resources to fetch records for a client. Idea is: ASP is too slow to fetch records from cursor. It is better to return everything in 1 call.

Flexibility:
Using cursor make you work with Oracle database in similar fashion as Microsoft SQL Server. Stored procedure is container for single sql query. This query could join number of tables and become difficult to optimize.

Arrays are giving ability
1) Utilize true procedural approach (use functions and programming logic)
2) Customizing (you can return sqlerrm with every row for example)


Your feedback about benefits of cursors over arrays is most welcome.

Thanks
Leon

we had a scenario in which we were storing our
intermediate result into a sql object type.
After the entire operation was over, we transfered
the result into a table via insert statement. The performance improved dramatically when we replaced
the code for extending and adding an entry (which was
in a loop) to a direct insert statement in the
table itself. What could have been the main reason
for this? One reason could be that we were consuming
unnecessary memory - but still why is it that the
insert into a table is much faster (about 5-10 times)
than insert
into the plsql variable itself. Or is it that in the
combination of <insert into sql type object +
final insert statement inserting all rows into
the table>, the final insert was the bottleneck?
I guess I should use dbms_profiler for this - but
may be you can answer from your vast experience!:)
Thanx!:)

hi tom
"you are doing tons less work. you removed procedural code.
you did things in bulk."

I guess the procedural code and the loop in which
we were performing inserts are still there.
Except inside the loop, we do a direct insert
instead of
inserting into the sql table and
doing a final insert.

So, the number of steps has not changed
(except for the final step where in one
case there is one final insert into db table)
only what we did in the loop changed..

I am doing a bulk collect , then validating each record and putting the record into one of 2 arrays... insert or updates (using extend to expand the arrays)... when i am done I do a FORALL for the inserts and also for the updates.

can this be done more efficiently if extend has any kind of performance impact ?

I think i am doing far too much work here (code below)?

regards
phil


CREATE OR REPLACE PROCEDURE ImportAccountsXX IS

TYPE l_pk IS TABLE OF accountimport.pk%TYPE;
TYPE l_servicinginst_bic IS TABLE OF accountimport.servicinginstitution_bic%TYPE;
TYPE l_accountnumber IS TABLE OF accountimport.accountnumber%TYPE;
TYPE l_accountowner_bic IS TABLE OF accountimport.accountowner_bic%TYPE;
TYPE l_accountname IS TABLE OF accountimport.accountname%TYPE;
TYPE l_currency IS TABLE OF accountimport.currency%TYPE;
TYPE l_exportflag IS TABLE OF accountimport.exportflag%TYPE;
TYPE l_datasource IS TABLE OF accountimport.datasource%TYPE;
TYPE l_timestamp IS TABLE OF accountimport.timestamp%TYPE;

TYPE l_a_sendstatus_fk IS TABLE OF account.sendstatus_fk%TYPE;

l_pk_array l_pk ;
l_servicinginst_bic_array l_servicinginst_bic;
l_accountnumber_array l_accountnumber;
l_accountowner_bic_array l_accountowner_bic;
l_accountname_array l_accountname;
l_currency_array l_currency;
l_exportflag_array l_exportflag;
l_datasource_array l_datasource;
l_timestamp_array l_timestamp;

currentAccount PLS_INTEGER;


l_u_pk_array l_pk := l_pk();

l_i_pk_array l_pk := l_pk();
l_i_servicinginst_bic_array l_servicinginst_bic := l_servicinginst_bic() ;
l_i_accountnumber_array l_accountnumber := l_accountnumber();
l_i_accountowner_bic_array l_accountowner_bic := l_accountowner_bic();
l_i_accountname_array l_accountname := l_accountname();
l_i_currency_array l_currency := l_currency();
l_i_datasource_array l_datasource := l_datasource() ;
l_i_timestamp_array l_timestamp := l_timestamp();
l_i_sendstatus_array l_a_sendstatus_fk := l_a_sendstatus_fk();
l_i_exportflag_array l_exportflag := l_exportflag();


n pls_integer;
j pls_integer :=0;
k pls_integer :=0;

l_nErrorCode pls_integer;
laccountpk pls_integer;

CURSOR c1 IS
SELECT pk,servicinginstitution_bic, accountnumber,
accountowner_bic,accountname,currency,exportflag,datasource,timestamp
FROM accountimport;



BEGIN

open c1;
loop
fetch c1 bulk collect into
l_pk_array, l_servicinginst_bic_array,
l_accountnumber_array,l_accountowner_bic_array, l_accountname_array,
l_currency_array,l_exportflag_array,l_datasource_array,l_timestamp_array
LIMIT 500;

for i in l_pk_array.first .. l_pk_array.last
loop
declare
validation_error EXCEPTION;
begin

currentAccount := i;
laccountpk := null;

/* ...do some validation here

/* ... determine wether we are inserting or updating

/*
we need to extend the arrays
and determine if we are updating or inserting
populate the inserts with the inserts and updates with the
updates
*/
if laccountpk IS NULL then
/* we will be inserting*/
j := j +1;
l_i_pk_array.extend;
l_i_servicinginst_bic_array.extend;
l_i_accountnumber_array.extend;
l_i_accountowner_bic_array.extend;
l_i_accountname_array.extend;
l_i_currency_array.extend;
l_i_datasource_array.extend;
l_i_timestamp_array.extend;
l_i_sendstatus_array.extend;
l_i_exportflag_array.extend;

l_i_pk_array(j) := l_pk_array(i);
l_i_servicinginst_bic_array(j) := l_servicinginst_bic_array(i);
l_i_accountnumber_array(j) := l_accountnumber_array(i);
l_i_accountowner_bic_array(j) := l_accountowner_bic_array(i);
l_i_accountname_array(j) := l_accountname_array(i);
l_i_currency_array(j) := l_currency_array(i);
l_i_datasource_array(j) := l_datasource_array(i);
l_i_timestamp_array(j) := l_timestamp_array(i);

else
/* we will be updating */
k := k +1;
l_u_pk_array.extend;
l_u_pk_array(j) := l_pk_array(i);
end if;

exception
when validation_error
then
dbms_output.put_line( 'error,' || l_pk_array(currentaccount) );
/* we have an error so update the error code */
update accountimport
set errorcode_fk = l_nerrorcode,
validationstatus = 'E'
where pk = l_pk_array(i);

end;
end loop;

n := l_i_pk_array.count;
-- insert any accounts
forall i in 1 .. l_i_pk_array.count
insert into account (pk) values ( l_i_pk_array(i));
-- needs all fields

-- update any accounts
forall i in 1 .. l_u_pk_array.count
update account set pk = l_u_pk_array(i);
-- needs all fields


exit when c1%notfound;
end loop;
close c1;
END ImportAccountsXX;

version

oracle ... 9.2.0.2.1

sorry !

any help would be appreciated here as I am getting my self into a pickle I think !

to empty arrays do i just .delete ?

Tom - if the memory used for the aforementioned types is only allocated on an extend, is there any problem with defining a larger than needed array type to safely handle isolated occurances of larger than "usual" arrays? Is there a memory hit for example? What is the overhead of defining an array of 200,000 if you usually extend only 4 or 500 but every now and then there may be 20,000 entries?

I don't mean extending more than I need.
I mean defining it overly large to begin with.
I mean like -
CREATE TYPE quitelarge AS VARRAY(500000) OF WHATEVER;
If I'm only going to extend it to 20,000 most of the time.

Hi Tom,
why am I getting subscript beyond count though I used ENTEND... in the following code, please let me know.

Thanks in advance.....Anita.



DECLARE
TYPE TYP_MEASURES_REC IS RECORD ( measure_data VARCHAR2(100)
,pfactor fsb_accum_data.pfactor%TYPE
,xdtindvol fsb_accum_data.xdtindvol%TYPE);

-- Maintain the Order as : week_code, geo_seq, iri_store, var_seq
TYPE TYP_WEEK_CODES IS TABLE OF TYP_MEASURES_REC;
TYPE TYP_GEOS IS TABLE OF TYP_WEEK_CODES;
TYPE TYP_IRI_STORES IS TABLE OF TYP_GEOS;
TYPE TYP_VAR_SEQS IS TABLE OF TYP_IRI_STORES;


arr_accum_data TYP_VAR_SEQS := TYP_VAR_SEQS(TYP_IRI_STORES(TYP_GEOS(TYP_WEEK_CODES())));
v_extend pls_integer;
BEGIN
---Get the max of var_seq
SELECT MAX(var_seq)
INTO v_extend
FROM fsb_variables;

arr_accum_data.EXTEND(v_extend);

FOR REC2 IN (SELECT
week_code
,iri_store_num
,xdtindvol
,pfactor
,NVL(TO_CHAR(measure_data),'NULL') measure_data
,geo_seq
,var_seq
FROM fsb_accum_data
WHERE week_code BETWEEN 1250 AND 1250
AND (5 = 0 OR geo_seq = 5)
) LOOP

arr_accum_data(rec2.var_seq)(rec2.iri_store_num)(rec2.geo_seq)(rec2.week_code).measure_data := rec2.measure_data;

arr_accum_data(rec2.var_seq)(rec2.iri_store_num)(rec2.geo_seq)(rec2.week_code).pfactor := rec2.pfactor;

arr_accum_data(rec2.var_seq)(rec2.iri_store_num)(rec2.geo_seq)(rec2.week_code).xdtindvol := rec2.xdtindvol;
END LOOP;
END;

what might you use instead of select max() in this case?

Hi Tom,

Do you have code examples which would demonstrate the advantage of using nested table or associated array in PL/SQL?

I was not able to find such examples on your site or in Oracle manual.

Thanks so much for your help

Hi Tom,

I always heard that collection in PL/SQL can be used to store small table, such as lookup table in the memory to have better performance, as stated in Chapter 5 of PL/SQL User’s Guide:

“For simple temporary storage of lookup data, associative arrays let you avoid using the disk space and network operations required for SQL tables.”

If you agree with this opinion, do you have code example to demo that the performance is better by using collection. ( such as put DEPT table data in the collection of PL/SQL and join EMP table with DEPT data in the collection).

Thanks so much for your help.