Hello all,
I have a requirement where I want to query table and print all the column values of that table in comma separated format Like for scott.emp it will print
7839,KING,,17-NOV-81,5000,,10 |
7698,BLAKE,7839,01-MAY-81,2850,,30 |
7782,CLARK,7839,09-JUN-81,2450,,10 |
7566,JONES,7839,02-APR-81,2975,,20 |
7788,SCOTT,7566,19-APR-87,3000,,20 |
7902,FORD,7566,03-DEC-81,3000,,20 |
7369,SMITH,7902,17-DEC-80,800,,20 |
7499,ALLEN,7698,20-FEB-81,1600,300,30 |
7521,WARD,7698,22-FEB-81,1250,500,30 |
7654,MARTIN,7698,28-SEP-81,1250,1400,30 |
7844,TURNER,7698,08-SEP-81,1500,0,30 |
7876,ADAMS,7788,23-MAY-87,1100,,20 |
7900,JAMES,7698,03-DEC-81,950,,30 |
7934,MILLER,7782,23-JAN-82,1300,,10 |
But following conditions needs to be maintained:
- Table name is dynamic, so first time user can execute it for Scott.EMP and 2nd time he can execute for scott.dept and so on. And if asked for dept table result will be like
10,ACCOUNTING,NEW YORK |
20,RESEARCH,DALLAS |
30,SALES,CHICAGO |
40,OPERATIONS,BOSTON |
2.In case table structure change, like addition of new column, no code change is allowed, code will start displaying new fields value also.
To achieve the requirement tried to implement below code:
DECLARE
type rec_type is record
(column_name varchar2(254),
data_type varchar2(254));
type aat is table of rec_type index by pls_integer;
vaat aat;
str1 varchar2(4000);
str2 varchar2(4000);
str3 varchar2(4000);
str4 varchar2(4000);
refcur sys_refcursor;
refcur2 sys_refcursor;
type nt_emp is table of scott.emp%rowtype;
vnt_emp nt_emp;
BEGIN
str1 := 'select column_name, data_type from all_tab_columns where table_name = ' ||'''EMP'''|| ' order by column_id ';
OPEN refcur FOR str1;
FETCH refcur BULK COLLECT INTO vaat;
CLOSE refcur;
str2 := 'SELECT ';
FOR INDX IN 1..VAAT.COUNT LOOP
str2 := str2||VAAT(INDX).COLUMN_NAME ||', ';
END LOOP;
str2 := RTRIM(str2,', ')|| ' from '|| 'SCOTT.EMP';
OPEN refcur2 FOR str2;
FETCH refcur2 BULK COLLECT INTO vnt_emp;
CLOSE refcur2;
FOR indx IN 1..vnt_emp.COUNT LOOP
str3 := NULL;
FOR indx2 IN 1..vaat.COUNT LOOP
str3 := str3|| 'VNT_EMP(INDX).'||vaat(indx2).column_name||'||'',''||' ;
END LOOP;
str4 := 'begin dbms_output.put_line('||RTRIM(str3, q'{||','||}') ||'); end;';
dbms_output.put_line (str4);
EXECUTE IMMEDIATE STR4;
END LOOP;
END;
dbms_output used for own testing purpose only. though Table_name is fixed in this code, but this code is to display what approach I am trying to taking, so please ignore those parts, will modify them once able to identify the solution.
Getting following error for the EXECUTE IMMEDIATE.
ORA-06550: line 1, column 28: PLS-00201: identifier 'VNT_EMP' must be declared ORA-06512: at line 42 ORA-06512: at "SYS.DBMS_SQL", line 1721
Any modification in this approach can solve this problem? or can you please suggest any alternate approach. Please help.