Re: How to accessing columns of a query-result by index

From: <jmayer_at_ratundtat.com>
Date: 21 Sep 2005 08:26:34 -0700
Message-ID: <1127316394.718280.162750@g44g2000cwa.googlegroups.com>

Hi folks,

many thanks for your advices !

I've solved the problem with dbms_sql. Now I am able to work with a resultset without having any informations about it's structure before execution. I tested it several times, it seems to work. I cannot say anything about performance and scalability this time, but in this case, I need it to work with resultset with less then 1000 rows.

An implementation in Java would have worked as well, but there's no other use of java in the application, so I decided to use pl/sql.

for anybody who cares, here's the code.
I was too lazy to strip it down, so it contains stuff, which is only needed in my application - but time is money ;-)

greets, Jens

create or replace package Dynamic is

• Author : Jens Mayer
• Hamburg, Germany
• Purpose : Dynamic examining a Resultset
• Public type declarations type typ_Content is record (cValue varchar2(100), nValue number, dValue date, ColType char, ColName varchar2(32), ColLen number, ColPrec number, ColScale number);

  type typColContent is table of typ_Content;

  type typRows is table of typColContent;

• Public function and procedure declarations function ExecuteQuery(cStatement in char) return typRows;

end Dynamic;
/
create or replace package body Dynamic is

• Private type declarations type typColTypes is table of char index by binary_integer; type typColDesc is table of typ_Content;

  tabColTypes typColTypes; -- Mappingtable for supported Datatypes (see initalization)
  null_content typ_Content; -- used for resetting variables of type typ_Content;

• Function and procedure implementations function ExecuteQuery(cStatement in char) return typRows is

    dynCursor integer;                     -- Handle for dynamic cursor
    tabDescription dbms_sql.desc_tab;      -- Oracle-defined
description for a column
    rec_Content typ_Content;               -- contains Informations

about each value of the resultset

    tabColDesc typColDesc := typColDesc(); -- contains my own column-description (subset of desc_tab)

    tabColContent typColContent := typColContent(); -- column-values and -descriptions of one single row

    tabRows typRows := typRows(); -- contaains the whole resultset

    nColCnt integer;
    nColNum integer;
    ignore number;
    nRowCount number;
    dBuffer date;
    cBuffer varchar2(100);
    nBuffer number;

  begin

• get cursor-handle dynCursor := dbms_sql.open_cursor;
• Parse and execute the statement, then get description of each column dbms_sql.parse(dynCursor,cStatement,dbms_sql.native); ignore := dbms_sql.execute(dynCursor); dbms_sql.describe_columns(dynCursor,nColCnt,tabDescription);
• Extracting the importatnt column-infos into tabColDesc nColNum := tabDescription.first;

    while nColNum is not null loop

       rec_Content := null;
       rec_Content.ColType  :=
tabColTypes(tabDescription(nColNum).col_type);
       rec_Content.ColName  := tabDescription(nColNum).col_name;
       rec_Content.ColLen   := tabDescription(nColNum).col_max_len;
       rec_Content.ColPrec  := tabDescription(nColNum).col_precision;
       rec_Content.ColScale := tabDescription(nColNum).col_scale;

       tabColDesc.extend;
       tabColDesc(nColNum) := rec_Content;

       nColNum := tabDescription.next(ncolNum);
     end loop;

     -- Use the description for defining columns (needs to be done
before fetching)
     nColNum := tabColDesc.first;

     while nColNum is not null loop

        case tabColDesc(nColNum).colType
          when 'N' THEN dbms_sql.define_column(DynCursor,nColNum,
nBuffer);
          when 'D' THEN dbms_sql.define_column(DynCursor,nColNum,
dBuffer);
          when 'C' THEN dbms_sql.define_column(DynCursor,nColNum,
cBuffer, tabColDesc(nColNum).ColLen);
        end case;

        nColNum := tabColDesc.next(ncolNum);
     end loop;

     -- no need for additional execute (i am astonished...)
     -- ignore := dbms_sql.execute(dynCursor);

     -- Examining the resultset
     nRowCount := 0;
     loop
        tabColContent := typColContent();
        if dbms_sql.fetch_rows(dynCursor) > 0 then

           nColNum := tabColDesc.first;

           while (nColNum is not null) loop

              tabColContent.extend;
              tabColContent(nColNum) := tabColDesc(nColNum);
              case tabColDesc(nColNum).colType
                when 'N' THEN
                   dbms_sql.column_value(dynCursor,nColNum,nBuffer);
                   tabColContent(nColNum).cValue := to_char(nBuffer);
                when 'D' THEN
                   dbms_sql.column_value(dynCursor,nColNum,dBuffer);
                   tabColContent(nColNum).cValue := to_char(dBuffer);
                when 'C' THEN
                   dbms_sql.column_value(dynCursor,nColNum,cBuffer);
                   tabColContent(nColNum).cValue := cBuffer;
              end case;

              nColNum := tabColDesc.next(ncolNum);
           end loop;
           nRowCount := nRowCount + 1;
           tabRows.extend;
           tabRows(nRowCount) := tabColContent;
        else
           exit;
        end if;
     end loop;

     -- done...
     dbms_sql.close_cursor(dynCursor);

     return tabRows;

  end ExecuteQuery;
begin

• Initialization
  --
  tabColTypes(1) := 'C'; tabColTypes(2) := 'N'; tabColTypes(12) := 'D';
  --
  null_Content.cValue := null; null_Content.nValue := null; null_Content.dValue := null; null_Content.ColType := null; null_Content.ColName := null; null_Content.ColLen := null; null_Content.ColPrec := null; null_Content.ColScale := null;
  --
  

end Dynamic;
/ Received on Wed Sep 21 2005 - 10:26:34 CDT