How can I use query with array

Question

I made a procedure using array parameter in Oracle:

create or replace PACKAGE my_pkg AS

TYPE USER_ID_TYPE IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;

create or replace procedure my_proc (my_id varchar2, ids USER_ID_TYPE)
is
  xxxx xxxx xxxx ....
end;

And I want to test that in sqldeveloper.

how can i do this?

I tried it like this:

exec('1010', ('111', '222')) -> fail 

exec('1010', ['111', '222']) -> fail

exec('1010' ARRAY('111', '222')) -> fail

I just want to use procedure in sqldeveloper with array.

You'll have to create a pl/sql block like: Declare arr user_id_type begin exec my_proc... End;. Oracle is very restrict to user's types. — Leonardo Gabriel
– Leonardo Gabriel, Commented Apr 11, 2018 at 13:10
@a_horse_with_no_name You can initialise a collection (TABLE OF) by just using the constructor (sort of) like that but not a PL/SQL associative array (TABLE OF ... INDEX BY). — MT0
– MT0, Commented Apr 11, 2018 at 13:25

Steven Feuerstein · Accepted Answer · 2018-04-11 13:42:23Z

6

In the recently released 18.1 version of Oracle Database (available for free exploration at livesql.oracle.com and also on the Oracle Cloud), you can use a qualified expression to do just what you'd like: construct an associative array "on the fly" in the invocation of the procedure:

CREATE OR REPLACE PACKAGE my_pkg
AS
   TYPE user_id_type IS TABLE OF VARCHAR2 (50)
      INDEX BY BINARY_INTEGER;
END;
/

CREATE OR REPLACE PROCEDURE my_proc (
   my_id VARCHAR2, ids my_pkg.user_id_type)
IS
BEGIN
   DBMS_OUTPUT.put_line ('COUNT = ' || ids.COUNT);
END;
/

BEGIN
   my_proc ('steven', 
            my_pkg.user_id_type (1 => '111', 2=> '222'));
END;
/

Package created.
Procedure created.
COUNT = 2

Check out my LiveSQL script exploring qualified expressions for arrays in more detail.

answered Apr 11, 2018 at 13:42

Steven Feuerstein

2,0041 gold badge12 silver badges16 bronze badges

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

Comments

Community · Accepted Answer · 2020-06-20 09:12:55Z

If you really do want an associative array...

Write an anonymous PL/SQL block to call it:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE test (
  my_id VARCHAR2(10),
  id    VARCHAR2(50),
  key   NUMBER(8,0)
)
/

CREATE PACKAGE my_pkg AS
  TYPE USER_ID_TYPE IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;

  PROCEDURE my_proc(my_id varchar2, ids USER_ID_TYPE);
END;
/

CREATE PACKAGE BODY my_pkg AS
  PROCEDURE my_proc(my_id varchar2, ids USER_ID_TYPE)
  IS
    i BINARY_INTEGER;
  BEGIN
    i := ids.FIRST;
    WHILE i IS NOT NULL LOOP
      INSERT INTO test VALUES ( my_id, ids(i), i );
      i := ids.NEXT(i);
    END LOOP;
  END;
END;
/

Query 1:

DECLARE
  ids MY_PKG.USER_ID_TYPE;
BEGIN
  ids(111) := '222';
  ids(42) := 'The Meaning of Life, The Universe and Everything.';
  MY_PKG.MY_PROC( '1010', ids );
END;

Query 2:

SELECT * FROM TEST

Results:

| MY_ID |                                                ID | KEY |
|-------|---------------------------------------------------|-----|
|  1010 | The Meaning of Life, The Universe and Everything. |  42 |
|  1010 |                                               222 | 111 |

However, you probably just want a collection (array) not an associative array...

... then you can create the array and populate it in the constructor.

So, remove the INDEX BY BINARY_INTEGER from your type declaration:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE test (
  my_id VARCHAR2(10),
  id    VARCHAR2(50),
  key   NUMBER(8,0)
)
/

CREATE PACKAGE my_pkg AS
  TYPE USER_ID_TYPE IS TABLE OF VARCHAR2(50);

  PROCEDURE my_proc(my_id varchar2, ids USER_ID_TYPE);
END;
/

CREATE PACKAGE BODY my_pkg AS
  PROCEDURE my_proc(my_id varchar2, ids USER_ID_TYPE)
  IS
  BEGIN
    FOR i IN 1 .. ids.COUNT LOOP
      INSERT INTO test VALUES ( my_id, ids(i), i );
    END LOOP;
  END;
END;
/

Query 1:

BEGIN
  MY_PKG.MY_PROC( '1010', MY_PKG.USER_ID_TYPE( '111', '222' ) );
END;

Query 2:

SELECT * FROM TEST

Results:

| MY_ID |  ID | KEY |
|-------|-----|-----|
|  1010 | 111 |   1 |
|  1010 | 222 |   2 |

Collectives™ on Stack Overflow

How can I use query with array

2 Answers 2

Comments

If you really do want an associative array...

However, you probably just want a collection (array) not an associative array...

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

If you really do want an associative array...

However, you probably just want a collection (array) not an associative array...

Comments

Your Answer

Sign up or log in

Post as a guest

Related