I have a problem trying to call a stored procedure in
Oracle 8i using CFSTOREDPROC. My code for the sp is
below (followed by my cf code):
CREATE OR REPLACE PACKAGE WebInterface
AS
PROCEDURE IsBaseCaseComplete (p_CaseID IN NUMBER,
p_Complete OUT VARCHAR2);
PRAGMA RESTRICT_REFERENCES (IsBaseCaseComplete,
WNDS);
PRAGMA RESTRICT_REFERENCES (WebInterface, WNDS,
WNPS, RNDS,
RNPS);
END WebInterface;
/
CREATE OR REPLACE PACKAGE BODY WebInterface
AS
NegAnswer VARCHAR2(3) := 'no';
PosAnswer VARCHAR2(3) := 'yes';
PROCEDURE IsBaseCaseComplete (p_CaseID IN NUMBER,
p_Complete OUT VARCHAR2)
IS
ReturnCase NUMBER := 0;
BEGIN
p_Complete := PosAnswer;
SELECT Ca.CaseID
INTO ReturnCase
FROM dla.Synopsis,
dla.Offender,
dla.Offense,
"DLA"."CASE" ca
WHERE Synopsis.CaseID = Ca.CaseID
AND Offense.OffenseID LIKE Ca.CaseID || '__'
AND Offender.OffenderID LIKE Ca.CaseID || '__'
AND Ca.CaseID = p_CaseID
AND rownum < 2;
IF SQL%NOTFOUND
THEN
p_Complete := NegAnswer;
END IF; -- SQL%NOTFOUND
EXCEPTION
WHEN OTHERS THEN
p_Complete := NegAnswer;
END IsBaseCaseComplete;
END WebInterface;
/
And here is the cf code:
<CFSTOREDPROC procedure="WebInterface"
datasource="#request.DATASOURCE#"
username="#Client.Username#"
password="#Client.Password#">
<CFPROCPARAM cfsqltype="CF_SQL_NUMERIC" type="In"
value="#attributes.caseid#">
<CFPROCPARAM cfsqltype="CF_SQL_VARCHAR" type="Out"
variable="isitok">
</CFSTOREDPROC>
The only descriptive errors I have managed to get (by
commenting the 2nd procparam) are that either the
input variable is not of the right precision (but
adding TO_NUMBER function doesn't fix it) or that the
procedure is undefined. What is weird is that I also
have code to test this in SQL Plus and I don't get any
errors when trying to pass in the caseid, it returns
the output variable(yes/no) just fine. Here is the
test sp code:
VARIABLE ret_val VARCHAR2(20);
BEGIN
WebInterface.IsBaseCaseComplete(20010005, :ret_val);
END;
/
PRINT :ret_val;
This is a brain bust for me at this point, I am not a
PL/SQL guru and it's the first time I have used this
tag with Oracle (SQL 7 is no prob for me). Any help
would be greatly appreciated.
Andrew
=====
Andrew Middleton
"Consistency is contrary to nature, contrary to life. The
only completely consistent people are the dead."
--Aldous Huxley
__________________________________________________
Do You Yahoo!?
Yahoo! Auctions - buy the things you want at great prices
http://auctions.yahoo.com/
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
