5 Replies Latest reply on Nov 29, 2015 6:25 PM by rareddy

    call stored procedure in oracleDB from Teiid

    prashanthi1
    prashanthi1 Nov 23, 2015 6:57 AM

      Hi,

       

      we have couple of stored procedures in oracle databases, that we want to expose from DV as vdbs.

      How can we import these stored procedures?

      Please provide documentation links that would help me achieve this.

       

      Thanks,

      Prashanthi.

        • 1. Re: call stored procedure in oracleDB from Teiid
          shawkins
          shawkins Nov 23, 2015 3:14 PM (in response to prashanthi1)

          > How can we import these stored procedures?

           

          Are you using Designer or just Teiid?  Designer should provide you with an option in the import wizard to import procedures.  For Teiid, the import property importer.importProcedures must be set - see JDBC Translators - Teiid 9.0 (draft) - Project Documentation Editor

           

          There are some cases however in which procedures that have a cursor/result set are not properly imported due to deficiencies in the standard JDBC DatabaseMetaData.  If that seems to be the case you can open an issue or simply model the desired procedure by hand or add the appropriate DDL - DDL Metadata - Teiid 9.0 (draft) - Project Documentation Editor

            • Actions
          • 2. Re: call stored procedure in oracleDB from Teiid
            prashanthi1
            prashanthi1 Nov 24, 2015 8:40 AM (in response to shawkins)

            Hi Steven,

             

            Thanks for the response.

            I am using teiid designer. I am able to import the stored procedure and created a view procedure also.

            when i create a vdb I am not able to see the procedure in the odata output.

             

            How do i access this procedure?

             

            thanks in advance,

            Prashanthi.

              • Actions
            • 3. Re: call stored procedure in oracleDB from Teiid
              rareddy
              rareddy Nov 24, 2015 11:57 AM (in response to prashanthi1)

              Do you see the Function definition for the procedure in $metadata?

                • Actions
              • 4. Re: call stored procedure in oracleDB from Teiid
                prashanthi1
                prashanthi1 Nov 25, 2015 3:27 PM (in response to rareddy)

                Ramesh, my stored procedure is having 2 in and 2 out parameters. when i imported the procedure in to Teiid designer the datatype was BigDecimal by default.

                when i execute the vdb, it prompts for in parameters.

                i observed that when i given any number of length 4 or less then my procedure is executed successfully, but when i give a number of 5 digits it gives a runtime exception saying data not found.

                i changed the datatype to BigInteger and long, still no change in behaviour.

                the input parameters are sequence generated number and can be any length.

                 

                what should be the datatype of the variables to execute the stored procedure successfully?

                 

                Thanks in advance,

                Prashanthi.

                  • Actions
                • 5. Re: call stored procedure in oracleDB from Teiid
                  rareddy
                  rareddy Nov 29, 2015 6:25 PM (in response to prashanthi1)

                  Prashanthi,

                   

                  What is error you see when executing with OData? Do you see the FunctionImport in $metadata

                   

                  As far executing outside of OData, execute using tool like SquirreL and tell us exact error, giving us just incomplete information we can not really see what is going on. If possible show us a example VDB with procedure exhibiting issue.

                   

                  Ramesh..

                    • Actions