Skip to Main Content

Strange behavior when I connect to my Oracle database via my Node v6.2.0 based web service not sure

cookie01May 25 2016 — edited Jun 13 2016

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;

Comments
Post Details
Added on May 25 2016
3 comments
1,528 views