4 Replies Latest reply on Oct 16, 2018 9:00 PM by Nigel-M

    PLSQL Table and ORDS




      Does anyone know how  we use PLSQL table with ORDS. when using 11gR2 database. I tried creating a REST service PL/SQL block based and wrote a package procedure with PL/SQL table (array) one parameter.when i call it in REST handler i get error  wrong type argument.


      Following is the code. Help me to find the error. I have read that PLSQL index by integer table are supported.

      REST handler



      java.sql.SQLException: ORA-06550: line 3, column 4:

      PLS-00306: wrong number or types of arguments in call to 'INS_C'

      ORA-06550: line 3, column 4:

      PL/SQL: Statement ignored


        • 1. Re: PLSQL Table and ORDS

          Works for me, here's an example using our AUTO PLSQL feature


          • 2. Re: PLSQL Table and ORDS

            Hi Jeff,


            Thanks for you reply. I have 2 questions.

            1. It worked for Auto PLSQL will it work with rest handler using PL/SQL Block as i did it.

            2. As i see that it works, You are passing JSON. Is it Oracle 11gR2 or 12c database. I am using 11gR2

            • 3. Re: PLSQL Table and ORDS

              Thank Jeff, It worked for me but with 11gR2. but in AUTO PLSQL. Its not working with REST handler i am not sure what error i am doing. following is my rest handler code.

              But i have some questions:

              1. This works in AUTO PLSQL mode with INS_C in uppercase


              2. BUT below does not work - Notice pkg1/ins_c  in lower case

              My Package









              • 4. Re: PLSQL Table and ORDS

                I'm not sure if this is advisable, but you could always qualify the procedure name inside the package with double quotes, e.g.:


                procedure "ins_c"
                end "ins_c";


                ...which gives you a lower case url mapping.


                There is a p_object_alias parameter in the ORDS.ENABLE_OBJECT procedure - but as far as I can tell that can only alias the package name, not the procedure names inside.