Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Dynamic Cursors - How?
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;
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;
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