If I run the SP code through SQL+ it will return records almost instantly (we're talking only 350 records returned here), but via the CF code the server (web) will either hang FOREVER or return the statement that the server is busy.
SERVER:
- Compaq Proliant (not sure of the model I don't have access to the server)
- WIN 2000
- Over 1gig memory
- dual processor
- CF5 Ent
Database:
- Unix
- Oracle 9i
- not sure about the rest but I can find out if necessary (again I don't have access to the room containing the servers)
Here's the CF Code:
<cfstoredproc procedure="pkg_analysis.getGridAnswers" datasource="#mysession.dcname#" blockfactor=20>
<cfprocparam type="In" maxlength="10" cfsqltype="CF_SQL_INTEGER" value="#session.currentbase#">
<cfprocparam type="In" maxlength="10" cfsqltype="CF_SQL_INTEGER" value="#bq2.questionid#">
<cfprocparam type="In" maxlength="10" cfsqltype="CF_SQL_INTEGER" value="#URL.afid#">
<!--- CF5 adds an ascii value of 0 (not the digit 0, but the ascii value of 0) to string parameters. It needs to be stripped off inside Oracle. --->
<cfprocparam type="In" cfsqltype="CF_SQL_LONGVARCHAR" value="#URL.searchvalue#" maxlength="4000">
<!--- this CF_SQL_REFCURSOR only works for cf5 and causes errors in MX --->
<cfif left(mysession.cfProductVersion,1) LT "6">
<!--- had to pull this out of because it doesn't compile on CF/MX --->
<cfprocparam type="Out" cfsqltype="CF_SQL_REFCURSOR" variable="Yo2" maxrows="-1">
</cfif>
<cfprocresult name="analysisgridanswers">
</cfstoredproc>
And here's the SP code:
CREATE OR REPLACE
PACKAGE BODY pkg_analysis
AS
PROCEDURE getGridAnswers
(pi_OrgId IN safiebsec.org.orgid%TYPE
,pi_Questionid IN question.questionid%TYPE
,pi_AnswerfieldId IN answerfield.answerfieldid%TYPE
,pi_Answer IN baseanswerfield.answer%TYPE
,pio_arg_Cursor IN OUT analysis_ref_cursor
)
IS
v_answer baseanswerfield.answer%TYPE := RTRIM(pi_answer,CHR(0));
BEGIN
OPEN pio_arg_Cursor FOR
SELECT bq.orgid
,ba.baseanswerid
,af.answerfieldorder
,baf.gridrowid
,baf.gridrow
,SUBSTR(baf.answer,1,128) answer
,baf.baseanswerfieldid
,DECODE(LENGTH(SUBSTR(baf.ANSWER,1,129)),129, 1, NULL) overflow
,sc.statuscolor
,sc_cmdlevel.statusname cmdlevel_statusname
,sc_cmdlevel.statuscolor cmdlevel_statuscolor
-- ,PKG_ANALYSIS.ROW_HAS_THIS_VALUE(baf.baseanswerid,pi_answerfieldid,baf.gridrowid,pi_answer) hasvalue
-- ,length(pi_answer) lenans
-- ,ascii(substr(pi_answer,5,1)) asciians
-- ,LPAD(LTRIM(TO_CHAR(bq.orgid)),10,'0') || '.' || baf.gridrow sortvalue
FROM basequestion bq
,baseanswer ba
,baseanswerfield baf
,answerfield af
,statuscode sc
,statuscode sc_cmdlevel
,basestatus bs
WHERE bq.orgid IN (SELECT orgid FROM orgvw START WITH orgid = pi_orgid CONNECT BY PRIOR orgid = parentorgid)
AND bq.questionid = pi_questionid
AND bq.basequestionid = ba.basequestionid
AND ba.baseanswerid = PKG_VALGRID.GET_LATEST_BA(bq.basequestionid)
AND PKG_ANALYSIS.ROW_HAS_THIS_VALUE(baf.baseanswerid,pi_answerfieldid,baf.gridrowid,v_answer) = 'Y'
AND ba.baseanswerid = baf.baseanswerid
AND baf.answerfieldid = af.answerfieldid
AND bq.statuscodeid = sc.statuscodeid
AND bq.orgid = bs.orgid
AND bq.datacall = bs.datacall
AND 'CMDLEVEL' = sc_cmdlevel.usedby
AND bs.cmdlevel = sc_cmdlevel.statusname
UNION ALL
SELECT bq.orgid
,TO_NUMBER(NULL)
,af.answerfieldorder
,TO_NUMBER(NULL)
,TO_CHAR(NULL)
,' '
,TO_NUMBER(NULL)
,NULL
,sc.statuscolor
,sc_cmdlevel.statusname cmdlevel_statusname
,sc_cmdlevel.statuscolor cmdlevel_statuscolor
FROM basequestion bq
,answerfield af
,statuscode sc
,org o
,statuscode sc_cmdlevel
,basestatus bs
WHERE bq.orgid IN (SELECT orgid FROM orgvw START WITH orgid = pi_orgid CONNECT BY PRIOR orgid = parentorgid)
AND bq.questionid = pi_questionid
AND bq.orgid = o.orgid
AND bq.orglevelname = 'Base'
AND PKG_VALGRID.GET_LATEST_BA(bq.basequestionid) IS NULL
AND bq.questionid = af.questionid
AND bq.statuscodeid = sc.statuscodeid
AND bq.orgid = bs.orgid
AND bq.datacall = bs.datacall
AND 'CMDLEVEL' = sc_cmdlevel.usedby
AND bs.cmdlevel = sc_cmdlevel.statusname
ORDER BY 1,5,4,3
;
END;
FUNCTION row_has_this_value
(pi_baseanswerid IN baseanswer.baseanswerid%TYPE
,pi_answerfieldid IN answerfield.answerfieldid%TYPE
,pi_gridrowid IN baseanswerfield.gridrowid%TYPE
,pi_answer IN baseanswerfield.answer%TYPE
) RETURN VARCHAR2 -- Y or N
IS
return_value VARCHAR2(1) := 'N';
CURSOR chk_value IS
SELECT 'Y'
FROM baseanswerfield baf
WHERE baf.baseanswerid = pi_baseanswerid
AND baf.answerfieldid = pi_answerfieldid
AND (baf.gridrowid = pi_gridrowid OR (baf.gridrowid IS NULL AND pi_gridrowid IS NULL))
AND (baf.answer LIKE pi_answer OR (baf.answer IS NULL AND pi_answer IS NULL))
;
BEGIN
IF pi_answerfieldid IS NULL
OR pi_answerfieldid = 0
THEN
return_value := 'Y';
ELSE
OPEN chk_value;
FETCH chk_value INTO return_value;
CLOSE chk_value;
END IF;
RETURN return_value;
END;
END;
/
Thanks in advance,
Steve
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
