Hi, You are talking about dynamic SQL in stored procedures. But there is a nice option that provides you dynamic SQL alike features: iBATIS SQL MAPS. When you declare your statements inside map config file, you can use a set of tags to build your statement according to parameters you pass to your statement. It works nicely for me. Take a look on it: www.ibatis.com
Cheers, Daniel Silva. --- Grassi Fabio <[EMAIL PROTECTED]> wrote: > 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. __________________________________ Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage! http://promotions.yahoo.com/new_mail --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
