An Oracle stored procedure accept array(table) parameter in package example needed

Question

This question is a part of my question how to pass javascript array to oracle store procedure by ado parameter object

I think divide it to 3 small parts will get answer faster.

For this question.

I know we can declare a table type and use

select last_name 
from employees in (select * from table(cast(my_table_type_var as my_table_type));

but I always get error from create new package which include a procedure has this. and I read some thread said I need to declear a sql(in schema) type because sql and pl/slq are not using same engine. I try create type in schema by spl*plus, but still get same errors so I need a working package example for this first please.

Vincent Malgrat · Accepted Answer · 2012-07-11 14:53:41Z

1

You need a SQL object to call the TABLE operator from a SELECT. Here's a small example (9iR2):

SQL> CREATE TYPE table_number is TABLE OF NUMBER;
  2  /

Type created.

SQL> SELECT * FROM TABLE(table_number(1,2,3));

COLUMN_VALUE
------------
           1
           2
           3

You can use a function here instead (note that my TYPE is still defined outside the package):

SQL> CREATE OR REPLACE PACKAGE pkg AS
  2     FUNCTION f RETURN table_number;
  3  END;
  4  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY pkg AS
  2     FUNCTION f RETURN table_number IS
  3     BEGIN
  4        RETURN table_number(4,5,6);
  5     END;
  6  END;
  7  /

Package body created.

SQL> SELECT * FROM table(pkg.f);

COLUMN_VALUE
------------
           4
           5
           6

answered Jul 11, 2012 at 14:53

Vincent Malgrat

67.9k9 gold badges122 silver badges176 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Simon Sheng Over a year ago

Thank you very much, after your example I read table function description, I understand most of this. but I am debugging my own package store procedur, there is no error for creating it, but I got error for calling it. I am new, I don't know how to formate my code in comment. I add all my coed by the next comment. hope you can help me.

Simon Sheng Over a year ago

CREATE OR REPLACE TYPE t_table IS TABLE of VARCHAR2(255); / CREATE OR REPLACE PACKAGE simon_pkg IS TYPE t_table IS TABLE of VARCHAR2(255); TYPE c_cursor IS ref CURSOR; PROCEDURE f (t_input in t_table,c_out out c_cursor); END; / CREATE OR REPLACE PACKAGE BODY simon_pkg IS PROCEDURE f (t_input in t_table,c_out out c_cursor) IS v_cursor c_cursor; BEGIN OPEN v_cursor FOR SELECT last_name FROM employees WHERE last_name IN (SELECT * FROM TABLE(t_input)); c_out := v_cursor; END f; END; /

Simon Sheng Over a year ago

I will create a new question.I can formate my code in comment.

Collectives™ on Stack Overflow

An Oracle stored procedure accept array(table) parameter in package example needed

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related