Home » SQL & PL/SQL » SQL & PL/SQL » Use Associative Arrays in sql (10.2.0.4.0)
Use Associative Arrays in sql [message #419367] Fri, 21 August 2009 10:18 Go to next message
davholla
Messages: 116
Registered: August 2009
Location: London
Senior Member
In a PLSQL I am creating I want to use an Associative Array.

E.g. I would have this query
select count (*) into v_numberrecordsnew
from apps.fa_additions fas
where creation_date > sysdate-:NUMBER_DAYS_AGO
and asset_number not in

Now I know you can not use Associative Array in sql statements but is there a way round this?
Re: Use Associative Arrays in sql [message #419369 is a reply to message #419367] Fri, 21 August 2009 10:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't see any associative array in what you posted.

Post a working Test case: create table, insert statements and PL/SQL code along with the result you want with these data.
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Use Associative Arrays in sql [message #419370 is a reply to message #419367] Fri, 21 August 2009 10:33 Go to previous messageGo to next message
niteshsabharwal
Messages: 17
Registered: June 2009
Location: Manchester, UK
Junior Member
The concept of pl/sql TABLE TYPE is identical to the concept of Arrays. try searching the FAQ for examples on TABLE TYPE.

Basically, you can create a cursor based on the above sql, populate a table type with it using BULK FTECH and the use the records fetched in a for/forall loop, just like you would use an array in c/c++.
Re: Use Associative Arrays in sql [message #419372 is a reply to message #419367] Fri, 21 August 2009 10:38 Go to previous messageGo to next message
davholla
Messages: 116
Registered: August 2009
Location: London
Senior Member
declare
TYPE asset_no_tab IS TABLE OF VARCHAR2(500) INDEX BY BINARY_INTEGER;
asset_nos asset_no_tab;
TYPE asset_no_entered_tab IS TABLE OF VARCHAR2(500) INDEX BY BINARY_INTEGER;
asset_nos_entered asset_no_tab;
i number;
j number;
v_numberrecords number;
v_numberrecordsnew number;
outputtext varchar2 (500);
 begin
     asset_nos(1) := 'Test';
     asset_nos(2) := 'Hobson Audley C/NLshold Improve.Generic1REAL1250';
 --dbms_output.put_line (asset_no_entered_tab);
  -- dbms_output.put_line('asset_no(2) is '||asset_nos(2));
  j := 1;
 for i in 1..2
  loop  dbms_output.put_line ('Test');--((asset_nos(i));
  outputtext := asset_nos(i);
  select  count (*) into v_numberrecords 
from apps.fa_additions fas
where creation_date > sysdate-:NUMBER_DAYS_AGO
and description||Attribute_Category_code||current_units||PROPERTY_TYPE_CODE||PROPERTY_1245_1250_CODE = outputtext ;
if v_numberrecords =0
THEN    dbms_output.put_line ('Not imported Asset no '||i || 'Desc ' ||asset_nos(i));
ELSIF v_numberrecords =1
THEN dbms_output.put_line ('Is 1');
select asset_number into asset_nos_entered(j)
from apps.fa_additions fas
where creation_date > sysdate-:NUMBER_DAYS_AGO
and description||Attribute_Category_code||current_units||PROPERTY_TYPE_CODE||PROPERTY_1245_1250_CODE =outputtext;
j := j+1;
ELSIF v_numberrecords >1
THEN dbms_output.put_line ('Is > 1');
END IF;
 end loop;
 dbms_output.put_line(j);
 --for k in 1 in j
 create table assets_added (asset_no number )
 
 select  count (*) into v_numberrecordsnew 
from apps.fa_additions fas
where creation_date > sysdate-:NUMBER_DAYS_AGO
and asset_number not in (select * from asset_nos_entered);  
 end;


Apologies Here is the complete code.
asset_no_entered_tab is the Associative Arrays.
Re: Use Associative Arrays in sql [message #419374 is a reply to message #419372] Fri, 21 August 2009 11:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Unreadable.
If you write your code like this the one that will have to do its maintenance is grealty to be pitied.

Regards
Michel
Re: Use Associative Arrays in sql [message #419380 is a reply to message #419374] Fri, 21 August 2009 13:11 Go to previous messageGo to next message
davholla
Messages: 116
Registered: August 2009
Location: London
Senior Member
a) It is my only second attempt at writing pl/sql for a real application. So apologies for the bad style - and thanks for the help.
b) It does not really need reuse - it is just to check a very large Fixed Assets dataload, to see which rows were either :-
a) loaded incorrectly
b) not loaded
c) loaded correctly

However I will try to correct it now.
Re: Use Associative Arrays in sql [message #419381 is a reply to message #419367] Fri, 21 August 2009 13:17 Go to previous messageGo to next message
davholla
Messages: 116
Registered: August 2009
Location: London
Senior Member
declare
TYPE asset_no_tab IS TABLE OF VARCHAR2(500) INDEX BY BINARY_INTEGER; --Associative array to hold all the rows
--we used in dataload - most of these have been removed 
--for clariy
asset_nos asset_no_tab;
TYPE asset_no_entered_tab IS TABLE OF VARCHAR2(500) INDEX BY BINARY_INTEGER;
asset_nos_entered asset_no_tab;
i number;
j number;
v_numberrecords number;
v_numberrecordsnew number;
outputtext varchar2 (500);
 begin
--create the array
     asset_nos(1) := 'Test';
     asset_nos(2) := 'Hobson Audley C/NLshold Improve.Generic1REAL1250';
 --dbms_output.put_line (asset_no_entered_tab);
  -- dbms_output.put_line('asset_no(2) is '||asset_nos(2));
  j := 1;
 for i in 1..2
  loop  dbms_output.put_line ('Test');--((asset_nos(i));
  outputtext := asset_nos(i);
--see how many records there are should be 1
  select  count (*) into v_numberrecords 
from apps.fa_additions fas
where creation_date > sysdate-:NUMBER_DAYS_AGO
and description||Attribute_Category_code||current_units||PROPERTY_TYPE_CODE||PROPERTY_1245_1250_CODE = outputtext ;
if v_numberrecords =0
THEN    dbms_output.put_line ('Not imported Asset no '||i || 'Desc ' ||asset_nos(i));
ELSIF v_numberrecords =1
THEN --if one record then stored the asset number for reuse
--to check there are no incorrect assets created
--this is the bit I am having problems with
select asset_number into asset_nos_entered(j)
from apps.fa_additions fas
where creation_date > sysdate-:NUMBER_DAYS_AGO
and description||Attribute_Category_code||current_units||PROPERTY_TYPE_CODE||PROPERTY_1245_1250_CODE =outputtext;
j := j+1;
ELSIF v_numberrecords >1
THEN dbms_output.put_line ('Too many assets - add useful text later');
END IF;
 end loop;
 dbms_output.put_line(j);
 --for k in 1 in j
 create table assets_added (asset_no number )
  select  count (*) into v_numberrecordsnew 
from apps.fa_additions fas
where creation_date > sysdate-:NUMBER_DAYS_AGO
and asset_number not in (select * from asset_nos_entered)
--the above line (I do not have access to the Database now
--so I am not sure what the message was does not work
;  
 end;
Re: Use Associative Arrays in sql [message #419382 is a reply to message #419370] Fri, 21 August 2009 13:21 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>TYPE asset_no_tab IS TABLE OF VARCHAR2(500) INDEX BY BINARY_INTEGER;
>asset_nos asset_no_tab;
>TYPE asset_no_entered_tab IS TABLE OF VARCHAR2(500) INDEX BY BINARY_INTEGER;
>asset_nos_entered asset_no_tab;

what is gained by reading data into tables above before process it?

Why can't records be read directly from Oracle tables & prcessed?
Re: Use Associative Arrays in sql [message #419384 is a reply to message #419382] Fri, 21 August 2009 14:08 Go to previous messageGo to next message
davholla
Messages: 116
Registered: August 2009
Location: London
Senior Member
BlackSwan wrote on Fri, 21 August 2009 19:21
>TYPE asset_no_tab IS TABLE OF VARCHAR2(500) INDEX BY BINARY_INTEGER;
>asset_nos asset_no_tab;
>TYPE asset_no_entered_tab IS TABLE OF VARCHAR2(500) INDEX BY BINARY_INTEGER;
>asset_nos_entered asset_no_tab;

what is gained by reading data into tables above before process it?

Why can't records be read directly from Oracle tables & prcessed?


To be honest. I just modified it from an example I found by googling. I thought that you had to declare collections in the declaration part of the code.

How could I re-write it?
Re: Use Associative Arrays in sql [message #419386 is a reply to message #419384] Fri, 21 August 2009 14:16 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Usually, when it can be done with an SQL statement do it in a simple SQL statement.

If you need more complicated processing, declare a cursor over the data you want to process, and then process that data.

A few more points:

Select count(*)s to see if anything needs to be processed is usually a bad idea.

Creating tables on the fly like you do with assets_added is ALWAYS a bad idea.

As to how you can rewrite it, what are you actually trying to do? I can't figure that out from the messy code.

[Updated on: Fri, 21 August 2009 14:17]

Report message to a moderator

Re: Use Associative Arrays in sql [message #419387 is a reply to message #419367] Fri, 21 August 2009 14:19 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
eliminate all references to asset_nos & asset_nos_entered
Re: Use Associative Arrays in sql [message #419388 is a reply to message #419387] Fri, 21 August 2009 14:31 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Ah, OK. Should have re-read the first post. Razz

Still, it would be good to know what the FINAL goal of this thing is.

If it's just about blocking duplicate entries, then if there is a primary key on the table in question one can just try an row-by-row insert in a cursor loop, and then catch the DUP_VAL_ON_INDEX rows into a log table or something.
Re: Use Associative Arrays in sql [message #419390 is a reply to message #419384] Fri, 21 August 2009 14:59 Go to previous messageGo to next message
niteshsabharwal
Messages: 17
Registered: June 2009
Location: Manchester, UK
Junior Member
Are you sure what you want to do ? Smile

Looking at your queries I m sure after the WHAT is clear the HOW part would be walk in the park

FORMATTED CODE
DECLARE
TYPE asset_no_tab IS TABLE OF VARCHAR2(500) INDEX BY BINARY_INTEGER; 
--Associative array to hold all the rows
--we used in dataload - most of these have been removed 
--for clariy
asset_nos asset_no_tab;

TYPE asset_no_entered_tab IS TABLE OF VARCHAR2(500) INDEX BY BINARY_INTEGER;
asset_nos_entered asset_no_tab;
i                  NUMBER;
j                  NUMBER;
v_numberrecords    NUMBER;
v_numberrecordsnew NUMBER;
outputtext         VARCHAR2 (500);

BEGIN
   --create the array
   asset_nos(1) := 'Test';
   asset_nos(2) := 'Hobson Audley C/NLshold Improve.Generic1REAL1250';

   j := 1;
   FOR i in 1..2
   LOOP  
       DBMS_OUTPUT.PUT_LINE ('Test');--((asset_nos(i));
       outputtext := asset_nos(i);
  
       --see how many records there are should be 1
       SELECT count (*) 
       INTO   v_numberrecords 
       FROM   apps.fa_additions fas
       WHERE  creation_date > sysdate-:NUMBER_DAYS_AGO
       AND    description||attribute_category_code||current_units||property_type_code||property_1245_1250_code = outputtext ;

       IF v_numberrecords =0
       THEN
           DBMS_OUTPUT.PUT_LINE ('Not imported Asset no '||i || 'Desc ' ||asset_nos(i));
       ELSIF v_numberrecords =1
       THEN 
       --if one record then stored the asset number for reuse
       --to check there are no incorrect assets created
       --this is the bit I am having problems with
       SELECT ASSET_NUMBER INTO ASSET_NOS_ENTERED(j)
       FROM   APPS.FA_ADDITIONS FAS
       WHERE  CREATION_DATE > SYSDATE-:NUMBER_DAYS_AGO
       AND    DESCRIPTION||ATTRIBUTE_CATEGORY_CODE||CURRENT_UNITS||PROPERTY_TYPE_CODE||PROPERTY_1245_1250_CODE =OUTPUTTEXT;
     
       j := j+1;

       ELSIF v_numberrecords >1
       THEN 
           DBMS_OUTPUT.PUT_LINE('Too many assets - add useful text later');
       END IF;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE(J);

   CREATE TABLE ASSETS_ADDED (ASSET_NO NUMBER )
   SELECT  COUNT (*)
   INTO    V_NUMBERRECORDSNEW 
   FROM    APPS.FA_ADDITIONS FAS
   WHERE   CREATION_DATE > SYSDATE-:NUMBER_DAYS_AGO
   AND     ASSET_NUMBER NOT IN (SELECT * FROM ASSET_NOS_ENTERED)
   --the above line (I do not have access to the Database now
   --so I am not sure what the message was does not work
   ;  
END;
Re: Use Associative Arrays in sql [message #419392 is a reply to message #419390] Fri, 21 August 2009 15:15 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
You really should pay attention to what Thomas said. SELECTing count(*) is a bad design. If this table has many insert and deletes and your high water has never been reset that statement could take hours.
Creating the table on the fly will fail miserable when you run it a second time.
There is more...
Re: Use Associative Arrays in sql [message #419393 is a reply to message #419388] Fri, 21 August 2009 15:38 Go to previous messageGo to next message
davholla
Messages: 116
Registered: August 2009
Location: London
Senior Member
ThomasG wrote on Fri, 21 August 2009 20:31
Ah, OK. Should have re-read the first post. Razz

Still, it would be good to know what the FINAL goal of this thing is.

If it's just about blocking duplicate entries, then if there is a primary key on the table in question one can just try an row-by-row insert in a cursor loop, and then catch the DUP_VAL_ON_INDEX rows into a log table or something.

Thank you all so much for the help.


Dataloader is used to load data via the front end (i.e not via sql).
We have one that we will use to loads of assets.
However sometime it screws up either :-
i) It loads a line twice (or we make a mistake and start from the same place twice). The duplicate line can not be blocked cos you can load identical assets from the front end more than once. You just get 3 assets which have 3 different numbers.
ii) It loads a line incorrectly
iii) It misses out a line.

The script's aim is to do
a) takes some columns from the dataloader and concatenated them
b) Query the database see if they are there and tell us what
there is only one row.

I haven't used PL/SQL for 6 years and am really a beginner so this is the way I thought I use

Re: Use Associative Arrays in sql [message #419394 is a reply to message #419393] Fri, 21 August 2009 15:51 Go to previous messageGo to next message
niteshsabharwal
Messages: 17
Registered: June 2009
Location: Manchester, UK
Junior Member
Where COL1, COL2 and COL3 are the columns which you are hoping to use to identify unique assets in table TABLE_NAME

SELECT COL1,
       COL2,
       COL3,
       COUNT(1)
FROM   TABLE_NAME
GROUP BY
	COL1,
	COL2,
	COL3
HAVING COUNT(1) > 1


Here you may add columns or modify the having clause to match your search criteria.

Similary select can be used in your plsql code to identify records that need to be processed to suite your requirement.

regards
nitesh
Re: Use Associative Arrays in sql [message #419396 is a reply to message #419394] Fri, 21 August 2009 16:22 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Regarding your point i):

Case 1: You really have three identical items to put in the asset table, and you load them with the dataloader, and the dataloader gives them three different ids.

Case 2: You really intend to load only one item, but the data loader messed up and creates three items instead, again with three different ids.

Would there be even a chance for Oracle to figure out if it's case 1 or case 2 based on the data that arrives? If not, then there is not much you can to on the Oracle end.

Regarding point ii and iii):
If a line is missed, then the date never gets to Oracle, so no chance of fixing that. The same when the data is wrong somehow.


One more thing: Do the problems only occur with new data, or does data that has already been loaded days or months ago has to be checked also?




Previous Topic: Question about refernce with 2 condition in oracle
Next Topic: Create sp
Goto Forum:
  


Current Time: Fri Dec 09 21:09:13 CST 2016

Total time taken to generate the page: 0.11180 seconds