| dump the result of SELECT query in ARRAY [message #601558] |
Fri, 22 November 2013 03:53  |
 |
nischalinn
Messages: 118
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  |
 |
dariyoosh
Messages: 538
Registered: March 2009
Location: 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 #601570 is a reply to message #601564] |
Fri, 22 November 2013 04:34 |
|
dariyoosh
Messages: 538
Registered: March 2009
Location: France
|
Senior Member |
|
|
nischalinn wrote on Fri, 22 November 2013 11:15Thank 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 |
 |
Kevin Meade
Messages: 2103
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
