Troy, Are you getting an error when you try and run the procedure? What version of CF/Oracle and what happens when the IN parameter is used? I've been successful using CF5/Oracle 8.1.6 to pass in parameters.
dave At 10:38 AM 12/14/2001 -0500, you wrote: >All, > >I should have been more clear on this part. >I am not interested in limiting the number of rows that are returned. >I am interested limiting the rows based on a criteria. > >For example: >1. Select * from emp where id = 23; >2. Return the one row. > >Not: >1. Select * from emp; >2. Return the first 10 rows. > >This example is simple, but the real query is much more complex. > >Troy > > >Is it possible to pass a parameter into an Oracle Stored Procedure to > > >limit the record set that is returned but the procedure? If I remove > > >the "IN" Parameter from the Oracle Stored Procedure, it works fine. But > > >I do not want all the records. I just want a return a set of records. > > >I appears that it is not possible to supply additional values to > > ><CFstoredproc> when trying to retrieve a record set. > > > > > >I have the <cfstoredproc> tag defined as follows: > > > > > ><cfstoredproc > > > procedure="Portfolio.Get_Branch" > > > datasource="#Application.DSN#" > > > dbtype="#Application.DSNDBType#"> > > > > > > <cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="p_ObjPK" > > >value="34" null="No"> > > > <cfprocparam type="Out" cfsqltype="CF_SQL_REFCURSOR" > > >variable="p_Branch" null="Yes"> > > > <cfprocresult name= rs> > > ></cfstoredproc> > > > > > >Here is the definitions in my Oracle Package Header and Body: > > > > > >***In the Header:*** > > > > > >Type Blank_CurType Is Ref Cursor; > > > > > >Procedure Get_Branch > > >( > > > p_ObjPK in Number, > > > p_Branch out Blank_CurType > > > ); > > > > > >***In the Body:*** > > > > > > Procedure Get_Branch > > > ( > > > p_ObjPK in Number, > > > p_Branch out Blank_CurType > > > ) > > > Is > > > Begin > > > Open p_Branch For Select * from cobj; > > > -- I want to do this: Open p_Branch For Select * from cobj where > > >objpk = p_objpk; > > > End Get_Branch; > > > > > >I hope that I have explained it well enough. > > > > > >Thanks, > > >Troy > > > > > >-- > > >Troy Simpson | North Carolina State University > > >NCSU Libraries | Campus Box 7111 | Raleigh | North Carolina > > >ph.919.515.3855 | fax.919.513.3330 > > > > > >It is better to be hated for what you are > > >than to be loved for what you are not. > > > -- Andre Gide > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
