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

    PLSQL Table and ORDS

    VikasSh
    VikasSh Oct 15, 2018 5:43 PM

      Hi,

       

      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

      Package

      Error

      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

      Table:

      • 269 Views
      • Tags:
        • 1. Re: PLSQL Table and ORDS
          thatJeffSmith-Oracle
          thatJeffSmith-Oracle Oct 15, 2018 7:55 PM (in response to VikasSh)

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

           

          • 2. Re: PLSQL Table and ORDS
            VikasSh
            VikasSh Oct 16, 2018 10:31 AM (in response to thatJeffSmith-Oracle)

            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
              VikasSh
              VikasSh Oct 16, 2018 7:49 PM (in response to thatJeffSmith-Oracle)

              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

               

               

              begin

               

                 pkg1.ins_c(:p_name);

               

              end; 

              begin
                 pkg1.ins_c(:p_name);
              end;

              • 4. Re: PLSQL Table and ORDS
                Nigel-M
                Nigel-M Oct 16, 2018 9:00 PM (in response to VikasSh)

                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.