Creating report using a loop

Giedre · #1 (**permalink**) 09-24-03, 18:23

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.

andrewst · #2 (**permalink**) 09-25-03, 06:34

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

Code:

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech