Announcement

Collapse
No announcement yet.

Oracle stored procedure call does not work

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Oracle stored procedure call does not work

    I am trying to fetch value from oracle database. it always give me an error . some body will guide me on right . Is that some thing related to out parameter ,if yes how we can handle it . Even i tried diffrent link in mirth forum like ..
    --https://www.mirthcorp.com/community/forums/showthread.php?t=1677
    --http://www.mirthproject.org/community/forums/showthread.php?t=4075

    ---------------------------------------------------------------------------------
    var result=0;
    var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('oracle.jdbc.driver.OracleDriver','jdbcracle:thin:@192.168.0.226:1521:dbdb25','Dbname','X XX');
    var sql = 'exec MIRTH_TEST_PROCEDURE';
    var str = '';

    try {
    objresult = dbConn.executeCachedQuery(sql);
    while (objresult.next())
    {
    str = objresult.getString('Channename');
    }
    }

    catch (e) {
    logger.info('call to db failed: ' + e);
    }
    ---------------------------------------------------------------------------------


    My oracle stored procedure ---


    create or replace PROCEDURE MIRTH_TEST_PROCEDURE (
    --p_MODE in number,
    P_CURSOR OUT SYS_REFCURSOR)

    AS
    BEGIN
    OPEN p_CURSOR FOR
    select * from HL7_CLIENT_CHANNEL;

    END;

    -------------- Error ---------------------------------------------
    [2018-08-10 12:06:54,020] INFO (js-connector:?): call to db failed: JavaException: java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

  • #2
    try

    Code:
    var sql = 'exec  <schema-name>.MIRTH_TEST_PROCEDURE';
    HL7v2.7 Certified Control Specialist!

    Comment


    • #3
      ORA-00900: invalid SQL statement and tried different way

      I am getting same error even if edit the script .

      [2018-08-14 18:44:45,050] INFO (js-connector:?): call to db failed: JavaException: java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

      and I tried different way . it returns false no error
      --------------------------------------------------------
      importPackage(Packages.ca.uhn.hl7v2.model.primitiv e);
      importPackage(Packages.oracle.jdbc.driver);
      importPackage(java.sql);
      java.sql.DriverManager.registerDriver (new OracleDriver());

      // Change connection info here !!!
      // jdbcracle:thin:@<host>/<service_name>
      // jdbcracle:thin:@<host>:<sid>
      var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('oracle.jdbc.driver.OracleDriver','jdbcracle:thin:@192.168.0.226:1521:XXX','CCCCC','XXX') ;
      conn = dbConn.getConnection();
      //var spstmt = dbConn.prepareCall("{ call store_adt_event(?, ?, ?, ?, ?, ?, ?, ?) }");
      var spstmt = conn.prepareCall("{ call MIRTH_TEST_PROCEDURE(?) }");

      spstmt.registerOutParameter(1, OracleTypes.CURSOR);
      logger.debug(spstmt.execute()); /// it returns false.

      //var result=spstmt.executeQuery();

      //var result = spstmt.getResultSet();//getInt("pr_key");
      //logger.debug("size=="+result.size());

      //logger.info("new key=" + newnewKey);


      dbConn.close();
      ----------------------------------------------------------
      Last edited by shuhaibk54; 08-14-2018, 05:58 AM.

      Comment


      • #4
        You need to specify the schema name where the SP is located for oracle. something like dbo.storedProcedure where dbo is the schema.

        I don't see that anywhere on your posted code. BTW, You should wrap your code in code tags.
        HL7v2.7 Certified Control Specialist!

        Comment


        • #5
          I tired as you advised . Does anybody has a reference for oracle stored procedure call . I Searched entire forum and internet . Found Sql server related and it works well . Oracle related nothing found .

          Comment


          • #6
            Hope this helps ...

            Example Oracle function:

            CREATE OR REPLACE FUNCTION GETPATIENTNAMEFORID
            (
            P_PATIENTID IN VARCHAR2
            ) RETURN VARCHAR2
            IS
            cursor V_C_PATIENT IS select PAT_LAST_NAME || ' ' || PAT_FIRST_NAME from PATIENT where PAT_PRIMARY_ID = P_PATIENTID;
            V_PATIENTNAME VARCHAR2(1000);
            BEGIN
            open V_C_PATIENT;
            fetch V_C_PATIENT into V_PATIENTNAME;

            if V_C_PATIENT%notfound then
            V_PATIENTNAME := 'Unknown';
            end if;

            close V_C_PATIENT;
            RETURN V_PATIENTNAME;
            END GETPATIENTNAMEFORID;



            // Example javascript transformer code


            var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('oracle.jdbc.driver.OracleDriver',
            'jdbcracle:thin:@192.168.2.29:1521:XE', 'adt', 'adt');

            var jdbcNativeConn = dbConn.getConnection();

            var spstmt = jdbcNativeConn.prepareCall("{? = call GETPATIENTNAMEFORID (?) }");
            spstmt.registerOutParameter (1, java.sql.Types.VARCHAR);
            // 0000324550 is a sample value
            spstmt.setString(2, '0000324550');

            spstmt.execute();

            var patientFullName = spstmt.getString(1);

            logger.info("PatientName = " + patientFullName);

            dbConn.close();
            Nico Vannieuwenhuyze

            Amaron.be

            Comment


            • #7
              Thanks for advice . as per your advice function works well. Similarly I tried calling stored procedure . Yous simple push may help to finish this task .

              I tried stored procedure for return entire table as result data .It return null .

              below is sample stored procedure which I can fetch from c# code .
              -------------------------------------------Stored procedure -----------
              create or replace PROCEDURE MIRTH_TEST_PROCEDURE (
              --p_MODE in number,
              P_CURSOR OUT SYS_REFCURSOR)

              AS
              BEGIN
              OPEN p_CURSOR FOR
              select * from HL7_CLIENT_CHANNEL;

              END;


              -------------------------------------------------Mirth code ---------------
              var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('oracle.jdbc.driver.OracleDriver','jdbcracle:thin:@fun-host:1521:dbtest','hell','pwd');
              conn = dbConn.getConnection();
              var spstmt = conn.prepareCall("{call MIRTH_TEST_PROCEDURE(?) }");
              spstmt.registerOutParameter(1, OracleTypes.CURSOR);
              spstmt.execute();
              var result = spstmt.getResultSet();
              logger.debug("size=="+result.size()) // Error it return null : TypeError: Cannot call method "size" of null
              -------------------------------------------------------------------------------
              Error------------------
              // Error it return null : TypeError: Cannot call method "size" of null

              // OrcaleDBtestCONNECTOR:
              SourceSOURCE CODE:
              135: //logger.info("Channelname = " + Channelname);136: //var result=spstmt.executeUpdate();137: //var result=spstmt.executeQuery(); 138: 139: var result = spstmt.getResultSet();//getInt("pr_key");140: logger.debug("size=="+result.size());141: 142: //logger.info("new key=" + newnewKey);143: 144: LINE NUMBER:
              140DETAILS:
              TypeError: Cannot call method "size" of null

              ---------------------------------------------------------------------------

              Comment


              • #8
                This should do the trick ...

                // database reader javascript source code starts here
                var dbConn;

                try {
                dbConn = DatabaseConnectionFactory.createDatabaseConnection ('oracle.jdbc.driver.OracleDriver',
                'jdbcracle:thin:@oracleserver:1521:XE', 'amaron', 'proctest');

                var jdbcNativeConn = dbConn.getConnection();

                var spstmt = jdbcNativeConn.prepareCall("{ call MIRTH_TEST_PROCEDURE (?) }");
                spstmt.registerOutParameter (1, Packages.oracle.jdbc.OracleTypes.CURSOR);

                // call the stored procedure
                spstmt.execute();

                // get the resultset object from the output parameter
                var result = spstmt.getObject(1, java.sql.ResultSet);

                // copy the resultset into a cachedrowset (required for mirth)
                var cachedRowSet = new Packages.com.sun.rowset.CachedRowSetImpl();
                cachedRowSet.populate(result);

                // close the original resultset
                result.close();

                logger.debug("hoera! got the records");

                return cachedRowSet;

                } finally {
                if (dbConn) {
                dbConn.close();
                }
                }

                // end of code


                Best Regards

                Nico
                Nico Vannieuwenhuyze

                Amaron.be

                Comment

                Working...
                X