6 Replies Latest reply: Jun 8, 2011 2:19 PM by fac586 RSS

    Apex call stored procedure from webpage Q

    673017
      Dear all,

      I am following the "Handling the Verification Link (Pg 114)" from the book Pro Oracle Application Express and have problems with permission error. I have followed all the steps from the book including granting execute permission to the package as per user in the DAD (APEX_PUBLIC_USER). The error is as below. What else should I check? Thanks.


      Forbidden
      You don't have permission to access /pls/apex/myapp.pkg_auth.verify_user on this server.


      Regards,
      Kueh.
        • 1. Re: Apex call stored procedure from webpage Q
          673017
          Dear all,

          Ok, I found how it is done. Below are the steps. Thanks.

          To run stored procedure in APEX via a webpage do the following

          1)     Login to the database as user SYS that store the APEX application, in my case MYAPP.
          2)     Locate the function called wwv.flow_epg_include_mod_local that belongs to user FLOWS_030100. I use TOAD so it is easy.
          3)     Modify the function.
          a.     Remarked the ‘return false’ statement.
          b.     Add the function name that need to be exposed to the web ‘MYAPP.PKG_AUTH.VERIFY_USER'.

          CREATE OR REPLACE function FLOWS_030100.wwv_flow_epg_include_mod_local(
          procedure_name in varchar2)
          return boolean
          is
          begin
          -- The next statement below (one Line) is remarked by Kueh 24/Dec/2008
          return false; remove this statement when you modify this function
          --
          -- Administrator note: the procedure_name input parameter may be in the format:
          --
          -- procedure
          -- schema.procedure
          -- package.procedure
          -- schema.package.procedure
          --
          -- If the expected input parameter is a procedure name only, the IN list code shown below
          -- can be modified to itemize the expected procedure names. Otherwise you must parse the
          -- procedure_name parameter and replace the simple code below with code that will evaluate
          -- all of the cases listed above.
          --
          if upper(procedure_name) in (
          'MYAPP.PKG_AUTH.VERIFY_USER') then
          return TRUE;
          else
          return FALSE;
          end if;
          end wwv_flow_epg_include_mod_local;
          • 2. Re: Apex call stored procedure from webpage Q
            Sachin.Singh
            Good Stuff!! Thanks
            • 3. Re: Apex call stored procedure from webpage Q
              Martin1
              Hi,

              that's exactly what i need - it works fine :-)

              Thanks and regards,
              Martin
              • 4. Re: Apex call stored procedure from webpage Q
                721224
                Dear all,
                thanks al lot for the interesting topic and useful reply.
                My question is how can I send parameter to stored procedure? or better
                Can I send a parameter to stored procedure?
                Thanks

                Regards

                Paolo
                • 5. Re: Apex call stored procedure from webpage Q
                  865512
                  I am trying to execute a stored procedure using db link can any one help me with that .

                  DECLARE
                  v_statement varchar2(255);
                  BEGIN
                  v_statement := 'begin
                  DBA_BATCH.ACCOUNT_UNLOCK@DBNAME(''' || :INSTANCES || ''',''' || :USERNAME || ''');
                  end;';
                  EXECUTE IMMEDIATE v_statement;
                  END;

                  Regards,
                  Nischit.P
                  • 6. Re: Apex call stored procedure from webpage Q
                    fac586
                    Stop posting follow-ups to closed/old threads.

                    <li>Other users may ignore the thread as it appears to be closed
                    <li>Your assumption that the questions are related may be incorrect, leading to confusion about the nature of the problem and potential solutions.
                    <li>Watches on the thread may have expired, so the original participants may be unaware of the new post, or they may no longer be active on the forum
                    <li>APEX Phone Test
                    <li>You have no ability to mark posts as helpful or correct

                    Post your question as a new thread, including the following information:

                    <li>APEX version
                    <li>DB version and edition
                    <li>Web server architecture (EPG, OHS or APEX listener)
                    <li>Browser(s) used
                    <li>Theme
                    <li>Templates
                    <li>Region type
                    <li>Links to related posts and threads using the methods in the FAQ.

                    Your question has NOTHING to do with this thread.

                    And update your forum profile with a better handle than "862509".