Forum Stats

  • 3,827,131 Users
  • 2,260,743 Discussions
  • 7,897,181 Comments

Discussions

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

cookie01
cookie01 Member Posts: 2
edited Jun 13, 2016 9:43PM in Node.js

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;

Best Answer

Answers

  • Christopher Jones-Oracle
    Christopher Jones-Oracle Member Posts: 1,648 Employee
    edited May 30, 2016 8:11PM Answer ✓

    Two thoughts are that it's a data locking issue of some kind (though the information above doesn't support this), or you are locking all Node threads somehow (but not enough info was given to determine this).  Check the behavior if you increase the connection pool size and/or change UV_THREADPOOL_SIZE (https://github.com/oracle/node-oracledb/blob/v1.9.3/doc/api.md#connpoolmonitor).  Check how you are committing.  Check what is happening in parallel. Check how you are handling concurrent user requests, e.g with pool queueing https://github.com/oracle/node-oracledb/blob/v1.9.3/doc/api.md#propdbqueuerequests

    cookie01
  • cookie01
    cookie01 Member Posts: 2
    edited Jun 2, 2016 10:28AM

    Hi CJ,

    You were correct, it was a data locking issue, and probably, I should admit, a stupid user problem on my part? I had my PL/SQL developer IDE open and connected to tables that my stored procedure was using to interact with. Yesterday I decided to close my IDE to see if that was the source of the issue. When I did that my REST service could connect to oracle and execute the stored procedure no problems at all. When I opened up my IDE and tried to run the service it hung until I closed my IDE then it completed successfully.

    I had it open so that I could ensure that the data was properly being inserted into the DB (make my workflow a little smoother). I totally didn't think that by doing this I was locking the tables my stored procedure needed to have access too.

    Your comments on locking helped steer me in the right direction (i.e. try closing my IDE to see if that was the source of the problem). My DB admin could see there was a session that had a lock on some tables that I needed to access but we always thought it was an orphaned session not closed by my REST service, even though I thought I had properly closed all connections when not needed.

    Thanks for replying.

    Dave

  • Christopher Jones-Oracle
    Christopher Jones-Oracle Member Posts: 1,648 Employee
    edited Jun 13, 2016 9:43PM

    Easily done.  I'm glad you were able to resolve it.