Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Location
    Lithuania
    Posts
    4

    Unanswered: 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.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Creating report using a loop

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •