Re: Dynamic SQL statements

Note x-post to c.d.o.server, c.d.o.misc

In article <964718003.19038.0.pluto.d4ee154e_at_news.demon.nl>,   "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:
> IMO the distinction between the three newsgroups is the following
> .server: anything that comes with the server bundle, so SQL, PL/SQL,
 the
> server itself, and the server utilities
> export/import, sqlloader, and servermanager. Sqlnet and OEM also
 belong to
> this category
> .tools : any Oracle supplied front-end program, so developer and
 designer,
> webdb, jdeveloper and possibly OAS
> .misc : anything not in one of the above categories, preferably third-
 party
> products like ODBC
>
> So your topic is a typical .server topic, as it involves PL/SQL
> I'm elaborating on it, not to bash you, but most people don't know the
> distinction, and/or don't want to know it,
> and post indiscriminately to all three newsgroups.
I figure if I'm going to ask for help, the least I can do is post correctly. I apologize for sending to c.d.o.misc, but am keeping it here (and xposting to c.d.o.server) just for this thread, since it originated in c.d.o.misc.

> That all said,
> your request will be somewhat difficult to handle in Oracle 7 and
 Oracle
> 8.0, unless only one record needs to be returned.
I should have said we have Oracle 7.3.4 but might move to 8i soon. However, my question would be for 7.3.4

While I would need to handle multiple records, I can also use one that will handle single records if that makes it easier.

> In Oracle 8i you can combine REF CURSORS (returning a recordset) with
> dynamic sql, this is impossible in 7 and 8.
>
> Your assumption on dbms_sql is partially incorrect.
...so partially correct?

> You'll need to use the following calls in succession
> declare
> cur_handle integer;
> sqlstr varchar2(2000);
> res integer;
> begin
> cur_handle := dbms_sql.open_cursor;
> dbms_sql.parse(cur_handle, sqlstr, dbms_sql.native);
> -- calls to dbms.define_column depending on statement
Does calling dbmd.define_column mean I have to define the columns in advance? That's not what I want to do here. Really all I want to do is have a user enter in a SQL statement (via http request, through OAS to send to a package/procedure), and have PL/SQL parse that statement from which I can construct a string to return. I won't know, in advance, what the SQL statement will be (although it would be a SELECT statement)

> res:= dbms_sql.execute_and_fetch(cur_handle);
> -- you won't need the loop when returning only one record
> while res <> 0 loop
> -- calls to dbms_sql.column_value depending on statement
> res := dbms_sql.fetch(cur_handle);
> end loop;
> dbms_sql.close_cursor(cur_handle);
> exception
> when others then
> dbms_output.put_line(dbms_utility.format_error_stack);
> if cur_handle > 0 then
> dbms_sql.close_cursor(cur_handle);
> end if;
> end;
>
> Hth,
> Sybrand Bakker, Oracle DBA

It will, thank you. I'm a little confused partly because I haven't done much on this. I'll figure it out, but was wondering if you know of a few websites where I can learn the basics, and have sample code.

-Mark

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jul 27 2000 - 00:00:00 CDT