6 Replies Latest reply: Jun 8, 2011 9:19 AM by fac586 RSS

    Apex call stored procedure from webpage Q

      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.

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

        • 1. Re: Apex call stored procedure from webpage Q
          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
          -- 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;
          return FALSE;
          end if;
          end wwv_flow_epg_include_mod_local;
          • 2. Re: Apex call stored procedure from webpage Q
            Good Stuff!! Thanks
            • 3. Re: Apex call stored procedure from webpage Q

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

              Thanks and regards,
              • 4. Re: Apex call stored procedure from webpage Q
                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?


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

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

                  • 6. Re: Apex call stored procedure from webpage Q
                    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>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".