Home » SQL & PL/SQL » SQL & PL/SQL » dump the result of SELECT query in ARRAY (Oracle 11g, Win7)
dump the result of SELECT query in ARRAY [message #601558] Fri, 22 November 2013 03:53 Go to next message
nischalinn
Messages: 117
Registered: May 2012
Location: nepal
Senior Member
Can we dump the result of SELECT query in array.
The SELECT query is as follows:

SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'DWK_CHK_DEPART';

I have to dump the result in an array. How can I do it?
Re: dump the result of SELECT query in ARRAY [message #601560 is a reply to message #601558] Fri, 22 November 2013 04:06 Go to previous messageGo to next message
dariyoosh
Messages: 536
Registered: March 2009
Location: Iran / France
Senior Member
If by the word "dump" you mean put the list of column names in a collection, then you can use bulk collect into clause in your select.

Here is an example:

SQL> <<bk>>
  2  declare
  3    type colname_tab_ty is table of all_tab_columns.column_name%type;
  4    colname_tab colname_tab_ty;
  5  begin
  6    select t1.column_name bulk collect into bk.colname_tab
  7    from   all_tab_columns t1
  8    where  t1.table_name = 'EMPLOYEES' and t1.owner = 'HR';
  9  --
 10    for idx in 1 .. bk.colname_tab.count loop
 11      sys.dbms_output.put_line('column name = ' || bk.colname_tab(idx));
 12    end loop;
 13  end;
 14  /

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> /
column name = EMPLOYEE_ID
column name = FIRST_NAME
column name = LAST_NAME
column name = EMAIL
column name = PHONE_NUMBER
column name = HIRE_DATE
column name = JOB_ID
column name = SALARY
column name = COMMISSION_PCT
column name = MANAGER_ID
column name = DEPARTMENT_ID

PL/SQL procedure successfully completed.

SQL> 

[Updated on: Fri, 22 November 2013 04:12]

Report message to a moderator

Re: dump the result of SELECT query in ARRAY [message #601564 is a reply to message #601560] Fri, 22 November 2013 04:15 Go to previous messageGo to next message
nischalinn
Messages: 117
Registered: May 2012
Location: nepal
Senior Member
Thank you for your reply.
I still have some query, can we put the result of the SELECT query i.e. the names of COLUMNS in an array.
Re: dump the result of SELECT query in ARRAY [message #601570 is a reply to message #601564] Fri, 22 November 2013 04:34 Go to previous messageGo to next message
dariyoosh
Messages: 536
Registered: March 2009
Location: Iran / France
Senior Member
nischalinn wrote on Fri, 22 November 2013 11:15
Thank you for your reply.
I still have some query, can we put the result of the SELECT query i.e. the names of COLUMNS in an array.

Isn't that the output of the example I provided? my example was for a nested table but it can also work for an associative array.

[Updated on: Fri, 22 November 2013 04:36]

Report message to a moderator

Re: dump the result of SELECT query in ARRAY [message #601637 is a reply to message #601570] Sat, 23 November 2013 10:50 Go to previous message
Kevin Meade
Messages: 1957
Registered: December 1999
Location: Connecticut USA
Senior Member
it all depends upon what you mean by "ARRAY". Array is a programming concept and although it is generic, once you get down to writing code, you have to deal with the reality of an ARRAY being whatever the language you are coding in says it is. Thus we have the dilemma of a COBOL array vs. a FORTRAN array vs. a VB array vs. a JAVA array vs. a .NET array vs. an Oracle associative array vs. an Oracle COLLECTION.

So to answer your question we need to know what kind of array structure will be receiving your data. We also need to know the version of Oracle you are using as the answer will change depending upon the capabilities of your release.

So what are you doing?
query=>ORACLE COLLECTION
query=>ORACLE ASSOCIATIVE ARRAY
query=>JAVA ELEMENTAL DATATYPE ARRAY (one column string, number, date)
query=>JAVA SET OF ELEMENTAL DATATYPE ARRAYS
--
query=>JAVA USER DEFINED ORACLE.ARRAYTYPE
--
query=>JAVA NATIVE JAVA MULTI-ELEMENT ARRAY (whatever they call it in java)


The first four are easy as they all involve one or more single column arrays with predefined data types on both sides of the fence along with native language syntaxes that allow for the translation of query column to array. The next requires additional work requiring you do to do things like define custom data types and adding them to your code so you can then use them (I use java as just one example of a host language, could be anything). Tricky business. The last is not even doable. You must revert back to the one before it requiring all those custom data type definitions.

I suggest a review however of your goals and needs. Unless you have some already existing code you are trying to reuse, or you are re-iterating over a set of rows multiple times, there may be no need for these arrays in your code. If you are sending the data outside of Oracle, just use REF CURSORS. They are easy and all languages support them. If you are keeping the data inside Oracle then just use SQL and don't bother with PL/SQL as it will only slow your process down.

The sweet spot for arrays is < 10,000 rows each array (OK this number depends upon A LOT on your environment and the language hosting the array, and I have seen some arrays do millions of rows (Oracle hash tables for hash joins can do billions of rows in the right circumstances)) for which you potentially reference MOST rows MANY times. It this what you are using these arrays for.

Kevin

[Updated on: Sat, 23 November 2013 10:59]

Report message to a moderator

Previous Topic: Procedure to compare date and time separately
Next Topic: User table and their corresponding numbers
Goto Forum:
  


Current Time: Thu Nov 27 13:44:35 CST 2014

Total time taken to generate the page: 0.14813 seconds