3 Replies Latest reply: Nov 4, 2011 4:42 AM by Billy~Verreynne RSS

    Execute procedure on oracle apex online

    896464
      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
          Billy~Verreynne
          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
            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
              Billy~Verreynne
              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;