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.