Hi,
I recently wrote a stored procedure in our Oracle 11g database.
I have a web service that calls that stored procedure using an oracledb connection.
When I first start up my Node v6.2.0 based server that contains the web service in question AND I make a call to my stored procedure with a test case that I know will pass everything works, I get a response from oracle.
If I make calls to this stored procedures using data that I now will generate exceptions on the oracle side that also works great. I get the error messages back from oracle and handle them.
If I make an nth call to oracle using data that I know will pass the connection hangs. The connection just sits and waits for a response from oracle. Oracle doesn't seem to do anything either if I look for my data that I just added to the DB its not there.
If I go into my oracle stored procedure and simply edit it, do anything, and save it. The above connection completes and I get my positive response from oracle. The data at that point and that point only shows up in the database.
I have reviewed all the example code in the oracledb documents to see if there is a configuration parameter for my oracledb connection that I have not added that is causing this strange behaviour. I can find nothing.
I have had a couple of oracle db analysts in my office look at my stored procedure because I am VERY new to oracle PL/SQL programming. They can see nothing that would cause this behaviour.
I hope someone here can help me troubleshoot this.
I am using the latest version of oracledb.
Node v 6.2.0
Oracle 11g
My javascript query in Node
| var query2 = "BEGIN P_TCS_UPDATE(:attributes,:connector,:connectorSequenceNum,:publishDate,:sndrInstitutionID,:sndrCourseID,:levelCode,:sndrCourseNumber,:sndrSubjectID,:sndrSubjectCode,:inst_subj_code,:inst_crse_num,:creditHours,:detail,:detailComment,:startDate,:EndDate,:output);END;"; |
I am using an oracledb connection pool, right now I have limited the size of the pool to just 1, the only other connection options defined on the connection are maxRows and the outFormat oracle.OBJECT
I have err handlers in the callback but it doesn't matter as the connection callback isn't being called. When I encounter this "hanging" issue.
I think because the problem ONLY happens on subsequent successful executions of the stored procedure after the first one is run after I reboot the server I am thinking its my SP and not a missed configuration in my connection object. Since any and all connections that try to insert bad data return normally, I get the exceptions and handle them. If I restart the server and attempt a connection with "good" data that works too but first time only.
Also when I say "bad" data all I mean is that the data I try to insert already exists in the database or some parameter is poorly formatted, I am sending across all the expected parameters.
Hope someone can help.
Thanks in advance.
Dave
Stored procedure code is below.
create or replace PROCEDURE P_TCS_UPDATE
(
A_ATTRIBUTES IN VARCHAR2
, A_CONNECTOR IN VARCHAR2
, A_CONNECTOR_SEQ_NUM IN NUMBER
, A_PUBLISHED_DATE IN Date
, A_SNDR_INST_ID IN VARCHAR2
, A_SNDR_CRSE_ID IN VARCHAR2
, A_LEVEL_CODE IN VARCHAR2
, A_SNDR_CRSE_NUM IN VARCHAR2
, A_SNDR_SUBJ_ID IN VARCHAR2
, A_SNDR_SUBJ_CODE IN VARCHAR2
, A_INST_SUBJ_CODE IN VARCHAR2
, A_INST_CRSE_NUM IN VARCHAR2
, A_CREDIT_HOURS IN VARCHAR2
, A_DETAILS IN VARCHAR2
, A_DETAIL_COMMENTS IN VARCHAR2
, A_START_DATE IN VARCHAR2
, A_END_DATE IN VARCHAR2
, returnObject OUT VARCHAR2
) AS
BEGIN
-- default
returnObject := 'SUCCESS';
--global variables for this procedure.
DECLARE
var_space varchar2(5) := ' ';
var_shb_sbgi_found varchar2(6);
var_shb_subj_found varchar2(60);
var_shb_crse_found varchar2(60);
var_shb_test_select_result varchar2(300) := ' ';
var_shb_crse_num varchar2(60);
BEGIN
/**
Insert assessment into SHBTATC
**/
BEGIN
IF A_CONNECTOR_SEQ_NUM > 1 THEN
var_shb_crse_num := A_SNDR_CRSE_NUM || '/' || A_CONNECTOR_SEQ_NUM;
ELSE
var_shb_crse_num := A_SNDR_CRSE_NUM;
END IF;
INSERT INTO SATURN.SHBTATC(SHBTATC_SBGI_CODE
,SHBTATC_PROGRAM
,SHBTATC_TLVL_CODE
,SHBTATC_SUBJ_CODE_TRNS
,SHBTATC_CRSE_NUMB_TRNS
,SHBTATC_TERM_CODE_EFF_TRNS
,SHBTATC_ACTIVITY_DATE
,SHBTATC_TRNS_TITLE
,SHBTATC_TRNS_LOW_HRS
,SHBTATC_TRNS_HIGH_HRS
,SHBTATC_TRNS_REVIEW_IND
,SHBTATC_TAST_CODE
,SHBTATC_PROTECT_IND
)VALUES (A_SNDR_INST_ID
,'......'
,A_LEVEL_CODE
,A_SNDR_SUBJ_CODE
,var_shb_crse_num
,A_START_DATE
,A_PUBLISHED_DATE
,'Test Course'
,A_CREDIT_HOURS
,A_CREDIT_HOURS
,'Y' -- this must be default but should confirm
,'AC' -- default activity code
,'N' -- this will be default but should confirm.
);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
returnObject := 'FAIL -- SHBTATC';
END;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
returnObject := 'FAIL -- At INNER BEGIN.';
END;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
returnObject := 'FAIL -- At start of procedure.';
END P_TCS_UPDATE;