procedimientos - Pasar una matriz de datos como un parámetro de entrada a un procedimiento de Oracle

llamada a procedimiento oracle desde java

Question

Estoy tratando de pasar una matriz de datos ( varchar ) en un procedimiento de Oracle. El procedimiento de Oracle sería llamado desde SQL * Plus o desde otro procedimiento PL / SQL como ese:

BEGIN
pr_perform_task(''1'',''2'',''3'',''4'');
END;

pr_perform_task leerá cada uno de los parámetros de entrada y realizará las tareas.

No estoy seguro de cómo puedo lograr esto. Lo primero que pensé fue utilizar un parámetro de entrada de tipo varray pero estoy obteniendo un Error: PLS-00201: identifier ''VARRAY'' must be declared error, cuando la definición del procedimiento se ve así:

CREATE OR REPLACE PROCEDURE PR_DELETE_RECORD_VARRAY(P_ID VARRAY) IS

Para resumir, ¿cómo puedo pasar los datos como una matriz, dejar que el SP pase por cada uno de los parámetros y realice la tarea?

Estoy usando Oracle 10gR2 como mi base de datos.

Answer 1

Esta es un forma de hacerlo:

SQL> set serveroutput on
SQL> CREATE OR REPLACE TYPE MyType AS VARRAY(200) OF VARCHAR2(50);
2 /
Type created
SQL> CREATE OR REPLACE PROCEDURE testing (t_in MyType) IS
2 BEGIN
3 FOR i IN 1..t_in.count LOOP
4 dbms_output.put_line(t_in(i));
5 END LOOP;
6 END;
7 /
Procedure created
SQL> DECLARE
2 v_t MyType;
3 BEGIN
4 v_t := MyType();
5 v_t.EXTEND(10);
6 v_t(1) := ''this is a test'';
7 v_t(2) := ''A second test line'';
8 testing(v_t);
9 END;
10 /
this is a test
A second test line

Para ampliar mi comentario a la respuesta de @dcp, aquí se explica cómo podría implementar la solución propuesta allí si quisiera usar una matriz asociativa:

SQL> CREATE OR REPLACE PACKAGE p IS
2 TYPE p_type IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
3
4 PROCEDURE pp (inp p_type);
5 END p;
6 /
Package created
SQL> CREATE OR REPLACE PACKAGE BODY p IS
2 PROCEDURE pp (inp p_type) IS
3 BEGIN
4 FOR i IN 1..inp.count LOOP
5 dbms_output.put_line(inp(i));
6 END LOOP;
7 END pp;
8 END p;
9 /
Package body created
SQL> DECLARE
2 v_t p.p_type;
3 BEGIN
4 v_t(1) := ''this is a test of p'';
5 v_t(2) := ''A second test line for p'';
6 p.pp(v_t);
7 END;
8 /
this is a test of p
A second test line for p
PL/SQL procedure successfully completed
SQL>

Esta operación crea un Oracle TYPE independiente (que no puede ser una matriz asociativa) que requiere la definición de un paquete que todos puedan ver para que el TIPO que define pueda ser utilizado por todos.

Answer 2

Si los tipos de los parámetros son todos iguales ( varchar2 por ejemplo), puede tener un paquete como este que hará lo siguiente:

CREATE OR REPLACE PACKAGE testuser.test_pkg IS
TYPE assoc_array_varchar2_t IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
PROCEDURE your_proc(p_parm IN assoc_array_varchar2_t);
END test_pkg;
CREATE OR REPLACE PACKAGE BODY testuser.test_pkg IS
PROCEDURE your_proc(p_parm IN assoc_array_varchar2_t) AS
BEGIN
FOR i IN p_parm.first .. p_parm.last
LOOP
dbms_output.put_line(p_parm(i));
END LOOP;
END;
END test_pkg;

Luego, para llamarlo, necesitaría configurar la matriz y pasarla:

DECLARE
l_array testuser.test_pkg.assoc_array_varchar2_t;
BEGIN
l_array(0) := ''hello'';
l_array(1) := ''there'';
testuser.test_pkg.your_proc(l_array);
END;
/