 Select column values into array [message #595369] |
Tue, 10 September 2013 12:42  |
mvrk
Messages: 5
Registered: May 2009
Location: Coimbra/Portugal
|
Junior Member |
|
|
Hi,
Is there any way in PL/SQL to select the values from all columns of a table record into an array?
For example:
C1|C2|C3
0 |1 |2
v_array(0) value is 0
v_array(1) values is 1
v_array(2) values is 2
or
v_array(C1) value is 0
v_array(C2) values is 1
v_array(C3) values is 2
But i need to do this without mention the column names, something like: SELECT * FROM TABLE WHERE id=1 INTO v_array;
|
|
|
|
|
|
| Re: Select column values into array [message #595373 is a reply to message #595372] |
Tue, 10 September 2013 13:04  |
mvrk
Messages: 5
Registered: May 2009
Location: Coimbra/Portugal
|
Junior Member |
|
|
I need to do this without mention the column names, something like: SELECT * FROM TABLE WHERE id=1 INTO v_array;
My objective is to print the 348 column values of a table record using UTL_FILE, but i need to do this dynamically without worrying about the table column names.
Populate the v_array and then:
FOR i IN 0..347
LOOP
UTL_FILE.PUT_LINE(v_array(i)||',');
END LOOP;
or
FOR cn IN (SELECT COLUMN_NAME FROM sys.all_tab_columns WHERE table_name='XPTO_TABLE' AND owner='XPTO_USER' ORDER BY column_id)
LOOP
UTL_FILE.PUT_LINE(v_array(cn)||',');
END LOOP;
|
|
|
|
|
|
|
|
| Re: Select column values into array [message #595376 is a reply to message #595374] |
Tue, 10 September 2013 13:21 |
mvrk
Messages: 5
Registered: May 2009
Location: Coimbra/Portugal
|
Junior Member |
|
|
This is my current code:
DECLARE
v_header VARCHAR2(8000);
v_line VARCHAR2(8000);
v_file_handle UTL_FILE.FILE_TYPE;
v_file_dir VARCHAR2 (256) := 'TMP';
BEGIN
v_file_handle := UTL_FILE.FOPEN (v_file_dir, 'OUT.txt', 'A', 32767);
v_header := 'TID';
FOR r IN (SELECT COLUMN_NAME FROM sys.all_tab_columns WHERE table_name='TABLE2' AND owner='XPTO' AND column_name!='TID' ORDER BY column_id)
LOOP
v_header := v_header||','||r.column_name;
END LOOP;
UTL_FILE.PUT_LINE (v_file_handle,v_header);
UTL_FILE.FFLUSH (v_file_handle);
FOR rec IN (SELECT id FROM TABLE1)
LOOP
-SELECT * INTO v_array FROM TABLE2 WHERE id=rec.id;
v_line := v_array(0);
FOR cn IN (SELECT COLUMN_NAME FROM sys.all_tab_columns WHERE table_name='TABLE2' AND owner='XPTO' AND column_name!='TID' ORDER BY column_id)
LOOP
v_line := v_line||','||v_array(cn);
END LOOP;
UTL_FILE.PUT_LINE (v_file_handle,v_line);
UTL_FILE.FFLUSH (v_file_handle);
END LOOP;
UTL_FILE.FCLOSE (v_file_handle);
END;
The part in bold (put the values into the array) is what i'm missing...
There are 348 columns in my table and around 500000 records in the table, so i can't export them with TOAD (i've tried and i run out of memory) so need to do this by script.
|
|
|
|
|
|
|
|
| Re: Select column values into array [message #595379 is a reply to message #595376] |
Tue, 10 September 2013 13:49 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Few things in your code -
1. mvrk wrote on Tue, 10 September 2013 23:51This is my current code:
DECLARE
v_header VARCHAR2(8000);
v_line VARCHAR2(8000);
Why 8000? Why not 32767 or 1000000?
2. Quote:SELECT COLUMN_NAME FROM sys.all_tab_columns
Why SYS?
3. Quote:i can't export them with TOAD
TOAD is a tool. You might catch up unwanted things with a tool(not necessarily though, agree that sometimes it is useful). T.KYTE's explained and executed his code in SQL*Plus perfectly!
|
|
|
|
| Re: Select column values into array [message #595380 is a reply to message #595377] |
Tue, 10 September 2013 13:51 |
 |
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
|
Senior Member |
|
|
Here is a solution perhaps closer to your requirement:
SQL> set serverout on size 1000000;
SQL> set lin 4000 pages 0
SQL> VAR eref REFCURSOR;
SQL> DECLARE
2 TYPE c_typ IS TABLE OF VARCHAR2 (33)
3 INDEX BY PLS_INTEGER;
4
5 col c_typ;
6
7 sqltxt VARCHAR2 (4000);
8 BEGIN
9 SELECT column_name
10 BULK COLLECT INTO col
11 FROM user_tab_columns
12 WHERE table_name = 'EMP'
13 ORDER BY column_id;
14
15 sqltxt := 'SELECT ''** ''' ;
16
17 FOR i IN 1 .. col.COUNT
18 LOOP
19 sqltxt := sqltxt || '||''' || col ( i) || ':''||' || col ( i) ||'|| CHR ( 10)' || CHR ( 10)
20 ;
21 END LOOP;
22
23 sqltxt := sqltxt || 'FROM emp WHERE rownum < 11';
24
25 DBMS_OUTPUT.put_line ( sqltxt);
26
27 OPEN :eref FOR sqltxt;
28 END;
29 /
SELECT '** '||'EMPNO:'||EMPNO|| CHR ( 10)
||'ENAME:'||ENAME|| CHR ( 10)
||'JOB:'||JOB|| CHR ( 10)
||'MGR:'||MGR|| CHR ( 10)
||'HIREDATE:'||HIREDATE|| CHR ( 10)
||'SAL:'||SAL|| CHR ( 10)
||'COMM:'||COMM|| CHR ( 10)
||'DEPTNO:'||DEPTNO|| CHR ( 10)
FROM emp WHERE rownum < 11
PL/SQL procedure successfully completed.
SQL>
SQL> PRINT eref
** EMPNO:7369
ENAME:SMITH
JOB:CLERK
MGR:7902
HIREDATE:17-Dec-1980 00:00:00
SAL:800
COMM:
DEPTNO:20
** EMPNO:7499
ENAME:ALLEN
JOB:SALESMAN
MGR:7698
HIREDATE:20-Feb-1981 00:00:00
SAL:1600
COMM:300
DEPTNO:30
** EMPNO:7521
ENAME:WARD
JOB:SALESMAN
MGR:7698
HIREDATE:22-Feb-1981 00:00:00
SAL:1250
COMM:500
DEPTNO:30
** EMPNO:7566
ENAME:JONES
JOB:MANAGER
MGR:7839
HIREDATE:02-Apr-1981 00:00:00
SAL:2975
COMM:
DEPTNO:20
** EMPNO:7654
ENAME:MARTIN
JOB:SALESMAN
MGR:7698
HIREDATE:28-Sep-1981 00:00:00
SAL:1250
COMM:1400
DEPTNO:30
** EMPNO:7698
ENAME:BLAKE
JOB:MANAGER
MGR:7839
HIREDATE:01-May-1981 00:00:00
SAL:2850
COMM:
DEPTNO:30
** EMPNO:7782
ENAME:CLARK
JOB:MANAGER
MGR:7839
HIREDATE:09-Jun-1981 00:00:00
SAL:2450
COMM:
DEPTNO:10
** EMPNO:7788
ENAME:SCOTT
JOB:ANALYST
MGR:7566
HIREDATE:19-Apr-1987 00:00:00
SAL:3000
COMM:
DEPTNO:20
** EMPNO:7839
ENAME:KING
JOB:PRESIDENT
MGR:
HIREDATE:17-Nov-1981 00:00:00
SAL:5000
COMM:
DEPTNO:10
** EMPNO:7844
ENAME:TURNER
JOB:SALESMAN
MGR:7698
HIREDATE:08-Sep-1981 00:00:00
SAL:1500
COMM:0
DEPTNO:30
10 rows selected.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
