This discussion is archived
3 Replies Latest reply: Nov 4, 2011 2:42 AM by BillyVerreynne RSS

Execute procedure on oracle apex online

896464 Newbie
Currently Being Moderated
Hi All


CREATE OR REPLACE PROCEDURE TEST_PROC
AS
V_FNAME EMPLOYEES.FIRST_NAME%TYPE;
BEGIN
SELECT FIRST_NAME INTO V_FNAME FROM EMPLOYEES
WHERE FIRST_NAME = 'JOHN';
DBMS_OUT.PUT_LINE(V_FNAME);
END;
I have made this procedure on sql worksheet of http://apex.oracle.com/ on my account.

when i gave EXECUTE TEST_PROC; and CALL TEST_PROC; command to exeute this procedure I'm getting following error ORA-00900: invalid SQL statement.

please tell how can i run this procedure.

thanks in advance.

regards

Neeraj
  • 1. Re: Execute procedure on oracle apex online
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    PL/SQL calls need to be made using anonymous PL/SQL code blocks. E.g.
    begin
      Test_Proc;
    end;
    There are no EXECUTE server command (some client support this as a client command, wraps the content to execute into an anonymous PL/SQL block and then use that to make the database call).
  • 2. Re: Execute procedure on oracle apex online
    896464 Newbie
    Currently Being Moderated
    Hi

    begin
    TEST_PROC;
    end;

    when we execute procedure like this how to get the output on result screen using DBMS_OUT.PUT_LINE(V_FNAME); in oracle Apex because set serveroutput on
    is working here.

    thanks
  • 3. Re: Execute procedure on oracle apex online
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Some basic concepts first. Apex is a suite of PL/SQL procedures and functions and packages and database tables. Apex runs totally inside the database as stored procedural code.

    What makes it unique and different is that the procedures are called by a web browser URL. This is received by Apache and passed to a module called mod_psql. This module processes the URL from the web browser, determines what database to connect to and what stored procedure call to make.

    Inside the PL/SQL environment there is a set of web packages - and a buffer area that can be used to create dynamic web pages. Apex PL/SQL code executes and creates a dynamic web page/web content in this buffer. The code terminates.

    The mod_plsql module in Apache then reads this buffer from the Oracle session and it streams the content to the web browser.

    DBMS_OUTPUT is not specifically supported by mod_plsql as it is another buffer area (quite primitive in use and interface). It supports a basic console output display for PL/SQL code - and is typically use by session-based clients like TOAD, SQL*Plus and SQL-Developer.

    So if you want a web-enabled PL/SQL procedure to display data on the web browser, your code needs to write to the web buffer in PL/SQL and not the DBMS_OUTPUT buffer. Writing to the web buffer can be done using the HTP.prn() call.

    Here's a very basic example of outputting data to a web browser using custom PL/SQL code. Create a Apex page. On this page create a text item called something like P1_FIRSTNAME. This enables the web user to supply a parameter value to your PL/SQL procedure.

    Create a dynamic PL/SQL region on the page, that calls your procedure:
    begin
      --// if the user supplied a firstname, we call the procedure to process the data
      if :P1_FIRSTNAME is not null then
        MyTestProc( firstName => :P1_FIRSTNAME );
      end if;
    end;
    The user procedure that receives and processes the data, and supplied web output, will look something like the following:
    create or replace procedure MyTestProc( firstName varchar2 ) is
      cnt integer;
    begin
      htp.prn( 'You have entered the search criteria: '||firstName||'<br> ');
    
      select count(*) into cnt from emp where first_name like firstName;
      htp.prn( 'Number of matching employees: '||to_char(cnt)||' row(s)<br> ');
    end;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points