Home » SQL & PL/SQL » SQL & PL/SQL » How to store the content of a csv file into an array in oracle (Oracle 9i database)
How to store the content of a csv file into an array in oracle [message #448752] Thu, 25 March 2010 02:05 Go to next message
vi0leena
Messages: 23
Registered: March 2010
Junior Member
How to store the content of a csv file into an array in oracle and then get the distinct values from the array in oracle. The requirement is only to extract the first column value into an array.
Thank you very much.

Best Regards,
Violeena
Re: How to store the content of a csv file into an array in oracle [message #448759 is a reply to message #448752] Thu, 25 March 2010 02:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use an external table then SELECT DISTINCT on this table.


Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand


Re: How to store the content of a csv file into an array in oracle [message #448768 is a reply to message #448759] Thu, 25 March 2010 02:34 Go to previous messageGo to next message
vi0leena
Messages: 23
Registered: March 2010
Junior Member
Thanks for the quick reply. Appreciate for the help. I will look into it.Have a doubt. Is it not possible using associatve array ? Initially we wrote code to do this using java and now we have to do it in back end.
Thank you very much.

Best Regards,
Violeena
Re: How to store the content of a csv file into an array in oracle [message #448775 is a reply to message #448768] Thu, 25 March 2010 02:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can fill the result of the query into a varray if you want.
The best solution depends on what you actually want to do in the end.

Regards
Michel
Re: How to store the content of a csv file into an array in oracle [message #448776 is a reply to message #448775] Thu, 25 March 2010 02:59 Go to previous messageGo to next message
vi0leena
Messages: 23
Registered: March 2010
Junior Member
Thanks for the quick response once again. Appreciate your help.

I have 3 columns in the csv file namely
ID,NAME,MARKS
and now I want to store the ID's into varray and then get the distinct values from the array.

Thank you very much.

Best Regards,
Violeena
Re: How to store the content of a csv file into an array in oracle [message #448780 is a reply to message #448776] Thu, 25 March 2010 03:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
now I want to store the ID's into varray and then get the distinct values from the array

Why do you want to do this? That is the question.

Regards
Michel
Re: How to store the content of a csv file into an array in oracle [message #448781 is a reply to message #448780] Thu, 25 March 2010 03:32 Go to previous messageGo to next message
vi0leena
Messages: 23
Registered: March 2010
Junior Member
to get the distinct values from the duplicates in csv and to gt how ID's are there
Re: How to store the content of a csv file into an array in oracle [message #448782 is a reply to message #448781] Thu, 25 March 2010 03:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why IN THE END do you want this?
As I said you don't need varray to get distinct values.
So if you only need of varray is to get distinct values then you don't need it.

Regards
Michel
Re: How to store the content of a csv file into an array in oracle [message #448792 is a reply to message #448782] Thu, 25 March 2010 04:17 Go to previous messageGo to next message
vi0leena
Messages: 23
Registered: March 2010
Junior Member
then how can i get the distinct values from the csv file where ID have duplicates
Re: How to store the content of a csv file into an array in oracle [message #448801 is a reply to message #448792] Thu, 25 March 2010 04:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SELECT DISTINCT from an external table associated to the file.

Regards
Michel
Re: How to store the content of a csv file into an array in oracle [message #448807 is a reply to message #448801] Thu, 25 March 2010 04:52 Go to previous messageGo to next message
vi0leena
Messages: 23
Registered: March 2010
Junior Member
I know how to create external table. I even do understood how to get the distinct count. But the thing is that I can not create
external table. Please let us know if we can do using array.


I created an array type. Assigned the a_array(i):=substr(col1,1,5)
and finally tried to get select ditinct (column_value) from table(a_array).But unable to figure out why its failing.
Anyway thank you very much for your time.
Best Regards,
Violeena

[Updated on: Thu, 25 March 2010 04:53]

Report message to a moderator

Re: How to store the content of a csv file into an array in oracle [message #448809 is a reply to message #448752] Thu, 25 March 2010 04:59 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why can't you use an external table? The only privs you need are the same ones you'd need to use utl_file - which is the only other way you're going to be able to read a csv file in the DB.

If you want us to help with your code you need to post it along with the error message.

Re: How to store the content of a csv file into an array in oracle [message #448817 is a reply to message #448807] Thu, 25 March 2010 05:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
and finally tried to get select ditinct (column_value) from table(a_array).But unable to figure out why its failing.

An obvious error is that it is diStinct and not ditinct.

In addition, if you have to choose a collection for this, it would be a table not a varray.

Regards
Michel
Re: How to store the content of a csv file into an array in oracle [message #448834 is a reply to message #448817] Thu, 25 March 2010 06:02 Go to previous messageGo to next message
vi0leena
Messages: 23
Registered: March 2010
Junior Member
yes, i used pl/sql table not varray
Getting ORA-06532 Subscript outside of limit

[Updated on: Thu, 25 March 2010 06:03]

Report message to a moderator

Re: How to store the content of a csv file into an array in oracle [message #448835 is a reply to message #448834] Thu, 25 March 2010 06:06 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
ORA-06532: Subscript outside of limit

Cause: 	   A subscript was greater than the limit of a varray 
           or non-positive for a varray or nested table.

Action:    Check the program logic and increase the varray 
           limit if necessary.


What you tried is wrong. Unfortunately you never post what you actually really do, so there is no way anyone else can see what is wrong.
Re: How to store the content of a csv file into an array in oracle [message #448837 is a reply to message #448835] Thu, 25 March 2010 06:22 Go to previous messageGo to next message
vi0leena
Messages: 23
Registered: March 2010
Junior Member
Its something like this:
  
declare
   ...
   type ID_var is table of VARCHAR2(2000);   --
   a_ID ID_var:=ID_var();   --
   indx   number := 0;  
   var_1 number;
begin
	--open fille in read mode logic
loop
	--read the csv file logic
	ID :=SUBSTR(COL1,1,5);
	a_id(indx):=  v_id;
	indx := indx +1;   --   
        
end loop;
select count(distinct column_value) into var_1 from table( a_id);
end;

whats wrong when i m using array? is there anything I am missing when dealing with array?

Best Regards,
Violeena

[Updated on: Thu, 25 March 2010 06:23]

Report message to a moderator

Re: How to store the content of a csv file into an array in oracle [message #448839 is a reply to message #448752] Thu, 25 March 2010 06:31 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
That doesn't look like a PL/SQL table to me.
try this:
type ID_var is table of VARCHAR2(2000) INDEX BY BINARY_INTEGER;
a_ID ID_var;
Re: How to store the content of a csv file into an array in oracle [message #448840 is a reply to message #448837] Thu, 25 March 2010 06:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Before you can use an element in a table you have to allocate it.
So before "a_id(indx):= v_id;" you have to use "a_id.extend;"

Regards
Michel
Re: How to store the content of a csv file into an array in oracle [message #448852 is a reply to message #448840] Thu, 25 March 2010 06:44 Go to previous messageGo to next message
vi0leena
Messages: 23
Registered: March 2010
Junior Member
I tried that also just after the begin.let me check once again.

even after using a_id.extend() i m getting error

[Updated on: Thu, 25 March 2010 06:59]

Report message to a moderator

Re: How to store the content of a csv file into an array in oracle [message #448861 is a reply to message #448852] Thu, 25 March 2010 07:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
vi0leena wrote on Thu, 25 March 2010 12:44
I tried that also just after the begin.let me check once again.

even after using a_id.extend() i m getting error

Without the code we can reproduce we can't tell you what is the error but you undoubtfully made an error.

Regards
Michel

Re: How to store the content of a csv file into an array in oracle [message #448863 is a reply to message #448861] Thu, 25 March 2010 07:26 Go to previous messageGo to next message
vi0leena
Messages: 23
Registered: March 2010
Junior Member
declare
   ...
   type ID_var is table of VARCHAR2(2000);   --
   a_ID ID_var:=ID_var();   --
   indx   number := 0;  
   var_1 number;
begin
      a_id.extend();
	--open fille in read mode logic
loop
	--read the csv file logic
	ID :=SUBSTR(COL1,1,5);
	a_id(indx):=  v_id;
	indx := indx +1;   --   
        
end loop;
select count(distinct column_value) into var_1 from table( a_id);
end;

Is there anything wrong in
select count(distinct column_value) into var_1 from table( a_id);
end;

or anything wrong with loop because except the use of array the code is working fine.

Re: How to store the content of a csv file into an array in oracle [message #448864 is a reply to message #448863] Thu, 25 March 2010 07:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste your session.


Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand

Re: How to store the content of a csv file into an array in oracle [message #448865 is a reply to message #448752] Thu, 25 March 2010 07:33 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
You have to use extend each time you add a new row to the array.
Re: How to store the content of a csv file into an array in oracle [message #448866 is a reply to message #448863] Thu, 25 March 2010 07:35 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
I think your subscript/index variable should start from 1 and not zero


scott@orcl> declare
  2  type p_table is table of date;
  3  l_table p_table := p_table();
  4  indx int := 0;
  5  begin
  6  l_table.extend;
  7  l_table(indx) := trunc(sysdate);
  8  end;
  9  /
declare
*
ERROR at line 1:
ORA-06532: Subscript outside of limit
ORA-06512: at line 7


scott@orcl> declare
  2  type p_table is table of date;
  3  l_table p_table := p_table();
  4  indx int := 1;
  5  begin
  6  l_table.extend;
  7  l_table(indx) := trunc(sysdate);
  8  end;
  9  /

PL/SQL procedure successfully completed.





But unless you post your copy/paste your sqlplus session we may not really know exact error.
Re: How to store the content of a csv file into an array in oracle [message #448877 is a reply to message #448866] Thu, 25 March 2010 08:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good point! Indexes in Oracle start at 1 not 0 as in many language.
Maybe OP's error but who knows?


Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand

Re: How to store the content of a csv file into an array in oracle [message #448889 is a reply to message #448877] Thu, 25 March 2010 09:23 Go to previous messageGo to next message
vi0leena
Messages: 23
Registered: March 2010
Junior Member
	
declare
   ...
   type ID_var is table of VARCHAR2(2000) index by pls_integer;   --
   a_ID ID_var;   --
   indx   number := 0;  
   var_1 number;
begin
     --a_id.extend();
	--open fille in read mode logic
loop
	--read the csv file logic
	ID :=SUBSTR(COL1,1,5);
	a_id(indx):=  v_id;
	indx := indx +1;   --   
        
end loop;
select count(distinct column_value) into var_1 from table( a_id);
end;

found that I m getting error for last select statement.
Can access rows from non nested table
Re: How to store the content of a csv file into an array in oracle [message #448891 is a reply to message #448752] Thu, 25 March 2010 09:25 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
To use types in sql queries they need to be created as SQL types using the CREATE TYPE syntax. You've created a PL/SQL type that the SQL engine can't see.
Re: How to store the content of a csv file into an array in oracle [message #448893 is a reply to message #448752] Thu, 25 March 2010 09:31 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
To be honest I really don't understand why don't use an external table for this, if you had you probably would have got it done already with a lot less code. You don't need any more privileges than you'd need to do what your doing now.
Are you running a really old version of oracle?
Re: How to store the content of a csv file into an array in oracle [message #448894 is a reply to message #448889] Thu, 25 March 2010 09:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
found that I m getting error for last select statement.

When you say you get an error, it is mandatory to say WHICH error you get (even if it is the same one as in a previous post as we can't know it is the same one), and it is FAR FAR better to copy and paste the session. We make the effort to do it for you, you should make at least the same effort for us to help you.

Regards
Michel

[Updated on: Thu, 25 March 2010 09:33]

Report message to a moderator

Re: How to store the content of a csv file into an array in oracle [message #448996 is a reply to message #448894] Fri, 26 March 2010 03:52 Go to previous messageGo to next message
vi0leena
Messages: 23
Registered: March 2010
Junior Member
whats wrong here when I wanted to replace select statement mentioned above by array logic to get distince value from the array)
 for i in a_id.first..a_id.last   
	loop
	   flag:=0;
	   for j in a_id.first..a_id.last  
	   loop
	    if a_id[i] = a_id[j] then
		 
		 l:=l+1;
			
		 end if; 
		 
	   end loop;   
	    if l=1 then -- l is initialized to zero at declaration section
				b_id[j]:=a_id[i];
				
		end if;
      
	end loop;   
	


Thank you!
Re: How to store the content of a csv file into an array in oracle [message #449002 is a reply to message #448996] Fri, 26 March 2010 04:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is there any question in your post? Or are you happy with your last code?

Regards
Michel
Re: How to store the content of a csv file into an array in oracle [message #449004 is a reply to message #449002] Fri, 26 March 2010 04:15 Go to previous messageGo to next message
vi0leena
Messages: 23
Registered: March 2010
Junior Member
Thanks for the response. How to get the distinct value from an array without creating type
outside pl/sql.
I wrote the above syntax but it is wrong.
Re: How to store the content of a csv file into an array in oracle [message #449015 is a reply to message #449004] Fri, 26 March 2010 04:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you want to make the thing the worst you can?
Anyway, algorithms to get distinct values in a set are numerous on the web.

Regards
Michel
Re: How to store the content of a csv file into an array in oracle [message #449019 is a reply to message #449015] Fri, 26 March 2010 04:51 Go to previous messageGo to next message
vi0leena
Messages: 23
Registered: March 2010
Junior Member
Why do you want to make the thing the worst you can?
Because I have some limitations with which I am working on.Hope you understand.

And it would be of great help if you provide an example to implement how to do it with pl/sql

Many thanks!!!
Re: How to store the content of a csv file into an array in oracle [message #449028 is a reply to message #448752] Fri, 26 March 2010 05:39 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you use the data you want to count as the index of the PL/SQL table it's fairly straight forward:
Connected to:
Oracle Database 10g Release 10.2.0.2.0 - 64bit Production

SQL> set serveroutput on
SQL> DECLARE
  2  
  3  TYPE l_type IS TABLE OF NUMBER INDEX BY VARCHAR2(10);
  4  l_tab l_type;
  5  
  6  BEGIN
  7  
  8    FOR rec IN (SELECT 'ABC' a FROM dual
  9                UNION
 10                SELECT 'ABC' a FROM dual
 11                UNION
 12                SELECT 'DEF' a FROM dual
 13                UNION
 14                SELECT 'XYZ' a FROM dual) LOOP
 15               
 16      l_tab(rec.a) := 0;
 17      
 18    END LOOP;
 19    
 20    dbms_output.put_line('number of distinct values is '||l_tab.count);
 21    
 22  END;
 23  /
number of distinct values is 3

PL/SQL procedure successfully completed.

SQL> 


It would have helped if you had actually told us what restrictions you are working under. I'm guessing you are not allowed to create any objects, which to be honest is just silly as it prevents you from using all sorts of functionality.
Re: How to store the content of a csv file into an array in oracle [message #449034 is a reply to message #449028] Fri, 26 March 2010 06:05 Go to previous messageGo to next message
vi0leena
Messages: 23
Registered: March 2010
Junior Member
The example you have demonstrated is easier for me. I dont have privilege to create database objects.Why you dont try to understand? and why you ask the same question again and again when I said that I have limitations. Its just irritating
If you dont have answer or dont want to provide help its ok.

My question is too simple.. how to get the distinct value from an array ? Till now I am not getting any help but doing by own and now i feel that its just wastage of time asking people here.


The previous code is working fine except that I can not select distinct value from table(array) as I can not create object. Thats why I need help if I can get the distinct value from array?
Did not get any helpful answer yet MANY thanks!

Re: How to store the content of a csv file into an array in oracle [message #449036 is a reply to message #449034] Fri, 26 March 2010 06:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no deafer person than the one that doesn't want to hear.

Regards
Michel
Re: How to store the content of a csv file into an array in oracle [message #449037 is a reply to message #449034] Fri, 26 March 2010 06:16 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
vi0leena wrote on Fri, 26 March 2010 11:05
The example you have demonstrated is easier for me. I dont have privilege to create database objects.Why you dont try to understand? and why you ask the same question again and again when I said that I have limitations. Its just irritating
If you dont have answer or dont want to provide help its ok.

You said you have limitations. You didn't say what they were. Do you think we don't find having to guess what the limitations are irritating?

vi0leena wrote on Fri, 26 March 2010 11:05

My question is too simple.. how to get the distinct value from an array ? Till now I am not getting any help but doing by own and now i feel that its just wastage of time asking people here.

That's just ungrateful, you got lots of help. It wasn't the right help because:
a) you didn't tell use what the limitations were.
b) you didn't always post the code you were using.
c) you didn't always post the error you were getting.

So we constantly had to guess what the issue was.

vi0leena wrote on Fri, 26 March 2010 11:05

The previous code is working fine except that I can not select distinct value from table(array) as I can not create object. Thats why I need help if I can get the distinct value from array?
Did not get any helpful answer yet MANY thanks!


My last example will work just fine without you having to create any objects, just try it. Admittedly I forgot you want the list and not just the count but all you have to do is loop over the PL/SQL table and extract each value in turn.
Re: How to store the content of a csv file into an array in oracle [message #449038 is a reply to message #449037] Fri, 26 March 2010 06:24 Go to previous messageGo to next message
vi0leena
Messages: 23
Registered: March 2010
Junior Member
(SELECT 'ABC' a FROM dual
9 UNION ALL
10 SELECT 'ABC' a FROM dual
11 UNION ALL
12 SELECT 'DEF' a FROM dual
13 UNION ALL
14 SELECT 'XYZ' a FROM dual) Now can you do this without using distinct ?????


My question is too simple now
My array is holing some values like


a[1]:= a23
a[2]:= b24
a[3]:= ab3
a[4]:= b24

Now I want to get the distinct values from it
a23,b24,ab3

The example you provides does not help in my scenario..in the pl/sql that I provided. Had i have the privilege to create
TYPE i would have get the count using simple select.
User does not provide the privilige to create type..and I have to find alternative way.


declare
   ...
   type ID_var is table of VARCHAR2(2000) index by pls_integer;   --
   a_ID ID_var;   --
   indx   number := 0;  
   var_1 number;
begin
     --a_id.extend();
	--open fille in read mode logic
loop
	--read the csv file logic
	ID :=SUBSTR(COL1,1,5);
	a_id(indx):=  v_id;
	indx := indx +1;   --   
        
end loop;

I CAN NOT USE THE BELOW STATEMENT. I NEED TO HAVE AN ALTER NATIVE TO GET THE DISTINCT VALUE THE ARRAY IS HOLDING

select count(distinct column_value) into var_1 from table( a_id);
end;


[Updated on: Fri, 26 March 2010 06:30]

Report message to a moderator

Re: How to store the content of a csv file into an array in oracle [message #449039 is a reply to message #449038] Fri, 26 March 2010 06:28 Go to previous messageGo to previous message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
vi0leena wrote on Fri, 26 March 2010 11:24
(SELECT 'ABC' a FROM dual
9 UNION ALL
10 SELECT 'ABC' a FROM dual
11 UNION ALL
12 SELECT 'DEF' a FROM dual
13 UNION ALL
14 SELECT 'XYZ' a FROM dual) Now can you do this without using distinct ?????

That was just there so I could mimic getting some data from a file without actually having to set one up.

vi0leena wrote on Fri, 26 March 2010 11:24

My question is too simple now
My array is holing some values like


a[1]:= a23
a[2]:= b24
a[3]:= ab3
a[4]:= b24

Now I want to get the distinct values from it
a23,b24,ab3

The example you provides does not help in my scenario..in the pl/sql that I provided. Had i have the privilege to create
TYPE i would have get the count using simple select.
User does not provide the privilige to create type..and I have to find alternative way.



cookiemonster wrote on Fri, 26 March 2010 11:16

My last example will work just fine without you having to create any objects, just try it.


Did you actually read that?
Previous Topic: Timestamp in ISO 8601 compliant standard
Next Topic: PL/SQL, Static SQL slower then Dynamic SQL
Goto Forum:
  


Current Time: Thu Dec 08 01:54:21 CST 2016

Total time taken to generate the page: 0.11192 seconds