Not to mention that since there is no declared cursor you are relying on an implicit cursor but;
1) this looks like it will return more than one row, which is a no-no with an implicit cursor and 2) there is no INTO clause to store the result, so the procedure won't run anyway. Start with a procedure known to work properly and go from there. If you want this example to be a procedure, declare a cursor and use cfstoredproc. On 12/8/05, Deanna Schneider <[EMAIL PROTECTED]> wrote: > Does that even work for you in sql plus? It compiles with errors for me. I > think you'd need this: > CREATE OR REPLACE PROCEDURE selRandomQuestionIDs AS > BEGIN > SELECT quizQuestionID, RANDOM_ORDER > FROM random_number > WHERE rownum <= 20; > END; > > Though, to be honest, I"m not sure why you're bothering making a stored proc > for this - it's just a simple select. > > > On 12/7/05, Michael Bramwell <[EMAIL PROTECTED]> wrote: > > > > Hi > > > > I am having troubles getting even basic Oracle sp's to call from my > > coldfusion pages. > > > > Heres the procedure: > > > > CREATE OR REPLACE PROCEDURE selRandomQuestionIDs() AS > > IS > > BEGIN > > SELECT quizQuestionID, RANDOM_ORDER > > FROM random_number > > WHERE rownum <= 20 > > END; > > > > Heres the call: > > > > <cfquery name="selRandomQuestionIDs" datasource="#datasourceName#"> > > BEGIN selRandomQuestionIDs(); END; > > </cfquery> > > > > The error being returned is as follows: > > > > [Macromedia][Oracle JDBC Driver][Oracle]ORA-06550: line 1, column 7: > > PLS-00905: object SYSTEM.SELRANDOMQUESTIONIDS is invalid ORA-06550: line > > 1, column 7: PL/SQL: Statement ignored > > > > Any help will be most appreciated. > > > > Cheers, > > > > Michaelb. > > > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:226547 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
