How to execute procedure in APEX SQL script?

Question

I am trying to understand how to use multiple procedures in APEX SQL script. First I don't really need stored procedure, but not sure how to declare simple procedure in APEX SQL script. So this is my attempt:

create or replace procedure test1 as
begin
    DBMS_OUTPUT.ENABLE;
    dbms_output.put_line('test1');
end;

execute test1;

This gives me an error:

Error at line 7: PLS-00103: Encountered the symbol "EXECUTE"

So questions - how to create regular/not stored/ procedures in one SQL script and then call them. What is the entry point of execution in APEX SQL script?

Just to clarify. I mean SQL Scripts in SQL Workshop of APEX. — Pablo
– Pablo, Commented Nov 4, 2014 at 17:46
Why would you want to do this? Sounds like a strange way to go about things, unless I misunderstand the question — Scott
– Scott, Commented Nov 5, 2014 at 3:16

Dmitriy · Accepted Answer · 2014-11-05 10:02:03Z

1

UPD (At the first time I understood question totally wrong)

Correct version of a script:

create or replace procedure test1 as
begin
    DBMS_OUTPUT.ENABLE;
    dbms_output.put_line('test1');
end;
/
begin
  test1;
end;
/

Documentation says, that script can contain inly SQL and PL/SQL commands. Commands of sqlplus will be ignored.

OLD VERSION (Let stay here)

In APEX pages you can use PL/SQL anonymous blocks. For example, you can create process (APEX has some types of them) or PL/SQL region, and use following:

declare
   ...
begin
   some_proc(:P_MY_ITEM);
end;

Here you can invoke any procedure and do anything else that allowed by PL/SQL. Also you can use parameters like :P_ITEM_NAME to get and set values of page and application items.

edited Nov 5, 2014 at 10:02

answered Nov 4, 2014 at 15:54

Dmitriy

5,58512 gold badges27 silver badges39 bronze badges

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

3 Comments

Pablo Over a year ago

Yes, but my question is about APEX SQL script particularly, not pages. I want to use procedures in APEX SQL script. Writing anonymous block in APEX SQL script will run it, but then adding procedure leads to errors and I don't know how to call them.

Dmitriy Over a year ago

Ohhh... I had made a little mistake ;) You mean SQL Scripts in SQL Workshop? As I understood, that scripts has to be like scripts for sqlplus.

Pablo Over a year ago

No worries :) Exactly, I mean SQL Scripts in SQL workshop. So there is any entry point for that sqlplus scripts and ability to define/call procedures?

Collectives™ on Stack Overflow

How to execute procedure in APEX SQL script?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related