Yes, what you ask is possible, here is a minimal but working example:
create or replace function test(obj_name in varchar2) return sys_refcursor is
result sys_refcursor;
sql_string varchar2(2000);
begin
sql_string := 'select * from user_objects';
if obj_name is not null then
sql_string := sql_string || ' where object_name like ''' || obj_name || '%''';
end if;
open result for sql_string;
return result;
end test;
Please note that main reason I use stored procedure is to decouple the physical data
structure from the application. In order to keep this separation clean in your
scenario I would in some way "abstract" the dynamic filters (i.e. I would not use
column or table names as parameters).
Note also that dynamic sql is not as performant as precompiled pl/sql code (but not
less performant than a client-built query) and more error-prone, so unless the logic
gets *very* complicated I prefer using if/elsif/else/end blocks and have some code
duplicated.
Bye, Fabio.
> -----Messaggio originale-----
> Da: Chris Cranford [mailto:[EMAIL PROTECTED]
> Inviato: gioved� 24 giugno 2004 13:27
> A: Struts Users Mailing List
> Oggetto: Re: Struts and PL/SQL
>
>
> You done any type of experimentation with dynamic SQL in your
> procedures?
>
> We have a form that accepts tons of input values from the web
> user which
> eventually get translated into 1 SQL query to pull back the
> data-set the
> user is looking for. The problem is that nesting select
> statements slows
> done the query, so in some cases, we create a
> callablestatement, generate
> the list of IDs on the java side and simply make those part
> of the query
> itself inline causing the query to run much faster. There
> are other form
> values that would reqiure we either append or not append additional
> constraints to our SQL.
>
> In my procedure, I could easily include all this logic and
> have a multitude
> of IF/ELSIF/ENDIF statements which describe the logic but then I have
> multiple copies of the SQL query to maintain on each
> IF-structure branch
> which I would prefer not to do. Is there a way where the
> string-append
> approach can be used in Oracle so that after all the
> conditions have been
> met, I simply open my cursor once to retreive the data in a PL/SQL
> procedure?
>
> Thanks
> Chris
>
> ----- Original Message -----
> From: "Grassi Fabio" <[EMAIL PROTECTED]>
> To: "Lucas Gonzalez" <[EMAIL PROTECTED]>; "Struts Users
> Mailing List" <[EMAIL PROTECTED]>
> Sent: Thursday, June 24, 2004 5:31 AM
> Subject: R: Struts and PL/SQL
>
>
> Hi, in my application all DB access goes through PL/SQL
> stored procedures. I
> use Oracle JPublisher to generate the Java classes that map
> PL/SQL packages.
> It works fine enough.
>
> Bye, Fabio.
>
> > -----Messaggio originale-----
> > Da: Lucas Gonzalez [mailto:[EMAIL PROTECTED]
> > Inviato: mercoled� 23 giugno 2004 20:07
> > A: Struts Users Mailing List
> > Oggetto: Struts and PL/SQL
> >
> >
> > Hi all!
> >
> > I�ve been using Struts a lot with EJB and Hibernate with no
> problems.
> >
> > But I always wondered if it�s possible to use an architecture
> > that uses
> > STRUTS and goes directly to PL/SQL for the database layer. I
> > know it is
> > possible in many way, but I would like to know if there is
> any special
> > product or package that integrates with PL ( the only one I found is
> > http://portalstudio.oracle.com/servlet/page?_pageid=473&_dad=o
> ps&_schema=OPSTUDIO ) ... any pointers?
>
> Thanks a lot
> Lucas
>
> Ai sensi del D.Lgs. 196/2003 si precisa che le informazioni
> contenute in
> questo messaggio sono riservate ed a uso esclusivo del
> destinatario. Qualora
> il messaggio in parola Le fosse pervenuto per errore, La invitiamo ad
> eliminarlo senza copiarlo e a non inoltrarlo a terzi,
> dandocene gentilmente
> comunicazione. Grazie.<BR><BR>Pursuant to Legislative Decree
> No. 196/2003,
> you are hereby informed that this message contains
> confidential information
> intended only for the use of the addressee. If you are not
> the addressee,
> and have received this message by mistake, please delete it
> and immediately
> notify us. You may not copy or disseminate this message to
> anyone. Thank
> you.
>
>
>
Ai sensi del D.Lgs. 196/2003 si precisa che le informazioni contenute in questo
messaggio sono riservate ed a uso esclusivo del destinatario. Qualora il messaggio in
parola Le fosse pervenuto per errore, La invitiamo ad eliminarlo senza copiarlo e a
non inoltrarlo a terzi, dandocene gentilmente comunicazione. Grazie.<BR><BR>Pursuant
to Legislative Decree No. 196/2003, you are hereby informed that this message contains
confidential information intended only for the use of the addressee. If you are not
the addressee, and have received this message by mistake, please delete it and
immediately notify us. You may not copy or disseminate this message to anyone. Thank
you.
