Re: Dynamic Cursors - How?

From: <andreas.prusch_at_sintec.de>
Date: Wed, 03 Jun 1998 07:57:31 GMT
Message-ID: <6l2vla$e7a$1@nnrp1.dejanews.com>

That depends on the version of the server. In Oracle 8 the dbms_sql package contents describe functionality. With this functionality you can determine the column types.
In Oracle 7 this functionality does not exist. You can then match all column types, accept long and long raw, to a varchar2 2000. Here's an example of such a procedure:

CREATE OR REPLACE
PROCEDURE select_(sqlStmt IN VARCHAR2) IS   varNotInSelectList EXCEPTION;

  cur                INTEGER := dbms_sql.open_cursor;
  retVal             INTEGER := 0;
  resultLine         VARCHAR2(32000) := '';
  column             VARCHAR2(2000) := '';
  last_col_idx       INTEGER := 0;

  PRAGMA EXCEPTION_INIT(varNotInSelectList, -1007); BEGIN
  dbms_sql.parse(cur, sqlStmt, dbms_sql.native);

  DECLARE
    i INTEGER := 1;
  BEGIN
    LOOP

      dbms_sql.define_column(cur, i, column, 2000);
      i := i + 1;

    END LOOP;
  EXCEPTION
    WHEN varNotInSelectList THEN

      last_col_idx := i - 1;
      dbms_output.put_line('define columns: ... ok');

  END;   retVal := dbms_sql.execute(cur);

  WHILE dbms_sql.fetch_rows(cur) > 0 LOOP     resultLine := '';
    FOR i IN 1 .. last_col_idx LOOP

      dbms_sql.column_value(cur, i, column);
      resultLine := resultLine || rpad(column,30);

    END LOOP;
    dbms_output.put_line(resultLine);
  END LOOP;   dbms_sql.close_cursor(cur);
END;
/
show errors

In article <6l21st$5hi$1_at_nnrp1.dejanews.com>,   dov_shnaider_at_my-dejanews.com wrote:
>
> Hi,
> I need to create a cursor dynamically ( including the Select, The From and
The
> Where statements ). I am trying to use the DBMS_SQL package but... I don't
see
> how to create a dynamic "dbms_sql.define_column(" and
"dbms_sql.column_value"
> statements. So, I can parse a dynamic SELECT statement but I can not
> dynamically retrieve the values. Any ideas ?
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/ Now offering spam-free web-based newsreading
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Wed Jun 03 1998 - 02:57:31 CDT