CODEWITHSUNDEEP
oracle-databaseoracle-apexora-06550
USSR
USSR

Reputation: 321

How to pass an array of values from Oracle Apex Page into Oracle stored procedure

I'm stuck with the passing Dates as an array parameters from the Oracle Apex page into package. Package contains one procedure with an array of type of dates. So what I want to do is to pass a simple dates into it from the Apex page, pl/sql block. Here is my code so far:

create or replace PACKAGE PK_NAME AS 

TYPE DATES_ARRAY_TYPE IS VARRAY(100) OF DATE;

PROCEDURE PASS_DATES (
      DATES DATES_ARRAY_TYPE
);

END PK_NAME;

create or replace PACKAGE BODY PK_NAME AS 

PROCEDURE PASS_DATES (
    DATES DATES_ARRAY_TYPE
) AS     
BEGIN    
for i in 1..DATES.count loop                 
   HTP.P(DATES(i));
end loop;
 END;
 END PASS_DATES;

 END PK_NAME;

Simple as that. And I call this procedure from the Apex page pl/sql block:

PK_NAME.PASS_DATES (
  DATES => '15-JAN-15', '16-JAN-15', '17-JAN-15'
);

However, it doesn't work, every time I'm trying to save it, it gives me an error:

•ORA-06550: line 3, column 25: PLS-00312: a positional parameter association may not follow a named association ORA-06550: line 2, column 1: PL/SQL: Statement ignored

What is wrong with it or what have I missed ?

Upvotes: 0

Views: 3212

Answers (1)

Анатолий Предеин
Анатолий Предеин

Reputation: 571

https://docs.oracle.com/cd/A97630_01/appdev.920/a96624/05_colls.htm

you must init constructor DATES_ARRAY_TYPE() i think it must look like this 

create TYPE DATES_ARRAY_TYPE IS VARRAY(100) OF DATE;

create or replace procedure test_case( p_dates DATES_ARRAY_TYPE) is 
begin
  dbms_output.put_line(p_dates(1));
end;

declare
 a DATES_ARRAY_TYPE;
begin
a := DATES_ARRAY_TYPE(sysdate, sysdate + 1,to_date('1.01.2016','dd.mm.yyyy'));
  test_case(a);
end;

also if you want to use TYPE in PACKAGE PK_NAME (not global) you must use object like PK_NAME.DATES_ARRAY_TYPE in your code.

ok, lets go in your case: 1. create package and body: https://gyazo.com/789b875ce47852e859c395c2021f9cd4

create or replace PACKAGE PCK AS 
  -- your type in pck
  TYPE DATES_ARRAY_TYPE IS VARRAY(100) OF DATE;
  procedure test_case(p_dates DATES_ARRAY_TYPE);      
END PCK;

create or replace PACKAGE body PCK AS 
  procedure test_case(p_dates DATES_ARRAY_TYPE) IS
   BEGIN
    --here just raise second element in array for DEMO
    raise_application_error('-20000',p_dates(2) );
   END;
END PCK;

2.create page and button and after submit process: https://gyazo.com/755f6e089db0a6a8ea058567d2b3384b

declare
  asd PCK.DATES_ARRAY_TYPE := PCK.DATES_ARRAY_TYPE('31-JUL-15', '01-AUG-15', '02-AUG-13', '03-AUG-13'); 
begin
  pck.test_case(asd);
end;
  1. after button that submit page i get this: https://gyazo.com/b894fc6b9b6dd28964ba2e6548244bc8

Upvotes: 2

Related Questions