Thursday, March 8, 2012

Creating report using a loop

Hello everybody

Maybe some of you had faced such kind of problem:
I have to out query results into html page.
Ok. It's more than simple using a cursor. BUT it becomes a problem, when tables have 100 or 200 or more columns.
I mean this:

cursor cur is
select * from table_name;
cur_record table_name%rowtype;

and after that

OPEN cur;
LOOP
FETCH cur INTO cur_record;
EXIT WHEN cur%notfound;
HTP.P('columns: '||cur_record.col1||','||cur_record.col2||'...... .'||cur_record.col259||'...');
END LOOP;
CLOSE cur;

Becomes uncomfortable. And, I think, it should be possible to put into loop. But i cannot find a decision how to realize such thing like
cur_record.col(i)

The other decision could be

TYPE RES_ARRAY AS VARRAY (300) OF NUMBER;

RESULTS RES_ARRAY;
BEGIN
SELECT * INTO RESULTS FROM table_name WHERE condition;

In that table are ONLY numbers.

BUT I receive an error
ORA-00932: inconsistent datatypes: expected UDT got NUMBER

As I understand, UDT is User Defined Type...... And all tutorials and Oracle documentations show only examples with UDT.

IS IT POSSIBLE SOME KIND OF SOLUTIONS?

Thanks very much.You would need to look at the DBMS_SQL package. This may get you started:

declare
v_cursor integer := dbms_sql.open_cursor;
v_status integer;
v_desc_tab dbms_sql.desc_tab;
v_numcols number;
v_value varchar2(4000);
v_record varchar2(32767);
v_query varchar2(32767) := 'SELECT * FROM emp';
begin

dbms_sql.parse( v_cursor, v_query, dbms_sql.native );
dbms_sql.describe_columns( v_cursor, v_numcols, v_desc_tab );

for i in 1 .. v_numcols loop
dbms_sql.define_column(v_cursor, i, v_value, 4000);
end loop;

v_status := dbms_sql.execute(v_cursor);

while ( dbms_sql.fetch_rows(v_cursor) > 0 ) loop
v_record := 'columns: ';
for i in 1 .. v_numcols loop
dbms_sql.column_value( v_cursor, i, v_value );
v_record := v_record || ',' || v_value;
end loop;
dbms_output.put_line(v_record);
end loop;
end;

/

columns: ,7369,SMITH,CLERK,7902,17-DEC-1980,800,,20,42
columns: ,7499,ALLEN,SALESMAN,7698,20-FEB-1981,1600,300,30,42
columns: ,7521,WARD,SALESMAN,7698,22-FEB-1981,1250,500,30,42
columns: ,7566,JONES,MANAGER,7839,02-APR-1981,2975,,20,42
columns: ,7654,MARTIN,SALESMAN,7698,28-SEP-1981,1250,1400,30,42
columns: ,7698,BLAKE,MANAGER,7839,01-MAY-1981,2850,,30,42
columns: ,7782,CLARK,MANAGER,7839,09-JUN-1981,2450,,10,42
columns: ,7788,SCOTT,ANALYST,7566,19-APR-2087,3000,,20,42
columns: ,7839,KING,PRESIDENT,,17-NOV-1981,5000,,10,42
columns: ,7844,TURNER,SALESMAN,7698,08-SEP-1981,1500,0,30,42
columns: ,7876,ADAMS,CLERK,7788,23-MAY-2087,1100,,20,42
columns: ,7900,JAMES,CLERK,7698,03-DEC-1981,950,,30,43
columns: ,7902,FORD,ANALYST,7566,03-DEC-1981,3000,,20,42
columns: ,7934,MILLER,CLERK,7782,23-JAN-1982,1300,,10,42

PL/SQL procedure successfully completed.

No comments:

Post a Comment