Forum Stats

  • 3,751,310 Users
  • 2,250,340 Discussions
  • 7,867,379 Comments

Discussions

PLSQL Table and ORDS

VikasSh
VikasSh Member Posts: 42 Bronze Badge

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

pastedImage_0.png

Package

pastedImage_1.png

pastedImage_2.png

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:

pastedImage_8.png

Kiran Pawar

Answers

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 7,916 Employee
    edited Oct 15, 2018 3:55PM

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

    pastedImage_0.png

    Kiran Pawar
  • VikasSh
    VikasSh Member Posts: 42 Bronze Badge
    edited Oct 16, 2018 6:31AM

    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

  • VikasSh
    VikasSh Member Posts: 42 Bronze Badge
    edited Oct 16, 2018 3:49PM

    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

    pastedImage_12.png

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

    pastedImage_19.png

    My Package

    pastedImage_23.png

    pastedImage_27.png

    begin

       pkg1.ins_c(:p_name);

    end; 

    begin
       pkg1.ins_c(:p_name);
    end;

  • Nigel-M
    Nigel-M Member Posts: 9
    edited Oct 16, 2018 5:00PM

    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.