>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
Michael,
1) Your proc is incorrect (that's what the error message is complaining
about). You need to fix a few things:
- don't use both AS and IS in the same declaration (just AS)
- no output prameters (the proc needs to get something back to
you)
- the select needs to be INTO an output param (or you need to
OPEN a cursor variable AS this select)
- no semi-colon at the end of the select
2) once you've fixed these, try running the proc using <cfstoredproc>
iso <cfquery>
/t
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:226581
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
