Re: Dynamic SQL statements

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. Usually they also don't crosspost, so your are forced to download the header at least three times. While this is already annoying, they might also get different and conflicting answers, and as the person responding responds to one newsgroup only,
 if the answer is incorrect, you simply won't see it.

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

<rmarkd_at_my-deja.com> wrote in message news:8lpo7g$ot1$1_at_nnrp1.deja.com...
> (Note: I posted this to c.d.o.tools previously. I think that was the
> wrong newsgroup. Hopefully this is the right one)
>
> All,
>
> I was wondering if there was a general way in a PL/SQL function where
> you take an SQL statement as an IN parameter and return a string that
> is the output of that SQL statement. For instance, say I have a table
> called...
>
> CUSTOMERS (firstname varchar2(10), zip number(5))
>
> And let's say I wanted to get all the information in a semi-colon
> delimited format (pipe delimiter separating the records) for those
> people in zip 94000. I would say something like "select firstname, zip
> from customers where zip between 94000 and 95000".
>
> Is there a procedure that will take in that statement as a parameter
> and return something like this?
> Mark;94000|homer;94001|tyler;94505
>
> I know how to create the string once I have the sql statement, what I
> don't know is how to take the IN parameter and turn that into a SQL
> statement which PL/SQL can parse and execute. I'm looking at
> DBMS_SQL.Parse, but I don't think that'll do it. Any suggestions?
>
> Thanks,
> Mark
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Jul 27 2000 - 00:00:00 CDT