CODEWITHSUNDEEP
oracle-databaseoracle-apex
giorgio.pier
giorgio.pier

Reputation: 23

Apex button and procedure

Im trying to create button in Apex which execute given procedure. As input values I gave two date fields called Poczatek and Koniec. The button should execute this procedure on submit. It perfectly works in Oracle, but throw a lot of errors in Apex. 

set serveroutput on
create or replace procedure KORELACJA(:Poczatek, :Koniec)
IS
miasto VARCHAR(25);
korelacja NUMBER;
cursor c1 is
    SELECT TEMP.nazwa, corr(TEMP.temperatura, WILGOTNOSC.wilg) 
    FROM TEMP INNER JOIN WILGOTNOSC
     on TEMP.nazwa = WILGOTNOSC.nazwa 
     and TEMP.data = WILGOTNOSC.data 
     WHERE TEMP.data between to_date(:Poczatek, 'YYYY-MM-DD') and to_date(:Koniec, 'YYYY-MM-DD') 
    GROUP BY TEMP.nazwa;
BEGIN 
DBMS_OUTPUT.put_line(RPAD('Miasto',10)||RPAD('Korelacja',10));
open c1;
FOR i IN 1..6
LOOP
commit;
fetch c1 into miasto, korelacja;
DBMS_OUTPUT.put_line(RPAD(miasto,10)||RPAD(korelacja,10));
END LOOP;
close c1;
END KORELACJA;
/

Errors look like this:

1 error has occurred
ORA-06550: line 2, column 5: PL/SQL: ORA-00922: missing or invalid option 
ORA-06550: line 2, column 1: PL/SQL: SQL Statement ignored ORA-06550: line 6, 
column 11: PLS-00103: Encountered the symbol "NUMBER" when expecting one of the 
following: := . ( @ % ; ORA-06550: line 9, column 18: PLS-00103: Encountered the symbol "JOIN" when 
expecting one of the following: , ; for group having intersect minus order start 
union where connect

Anyone knows the solution?

Upvotes: 1

Views: 1898

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

I'd suggest you to leave the procedure in the database; call it from Apex.

As you said that it works OK, I'm not going to examine the code. Just modify the first line:

create or replace procedure KORELACJA(par_Poczatek in date, 
                                      par_Koniec    in date)
is ...

Then, in Apex process, call the procedure as

korelacja(:p1_poczatek, :p2_koniec);

Note that you might need to apply TO_DATE function to those items, using appropriate format mask, such as

korelacja(to_date(:p1_poczatek, 'dd.mm.yyyy',
          to_date(:p1_koniec  , 'dd.mm.yyyy');

If you insist on keeping the procedure in Apex' process (I wouldn't recommend it), the you don't need CREATE PROCEDURE but an anonymous PL/SQL block. It won't accept any parameters - use Apex items directly.

declare
  miasto    VARCHAR(25);
  korelacja NUMBER;
  cursor ...
    WHERE TEMP.data between to_date(:p1_Poczatek, 'YYYY-MM-DD') ...
begin
  ...
end;

Upvotes: 1

Related Questions