Skip to Main Content
Forums

ORDS, SODA & JSON in the Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

PLSQL Table and ORDS

VikasShOct 15 2018 — edited Oct 16 2018

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

Comments

thatJeffSmith-Oracle

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

pastedImage_0.png

1 person found this helpful
VikasSh

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

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

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.

1 - 4

Post Details

Added on Oct 15 2018
4 comments
432 views