title: In Apex, a package called from SQL Workshop succeeds but from the Application Builder it fails
body: <p>Question from an Apex newby . . .</p>
  
  <p><strong>Background</strong></p>
  
  <p>I’m using Oracle Apex to generate reports for a 3rd party SIS (student information system) PowerSchool which resides in its own schema, PS. In PS, I have abstracted key business logic into a collection of custom functions in a single custom package – CKSD_Students.</p>
  
  <p><strong>Problem</strong></p>
  
  <p>I have the following simple Oracle query. </p>
  
  <pre><code>Select   S.ID                       As Student_ID
         , S.Student_Number           As Student_Number
         , S.LastFirst                As LastFirst
         , ps.cksd_student.get_student_enroll_status(S.Enroll_Status) As Enroll_Status
         , S.Grade_Level              As Grade_Level
         , S.SchoolID                 As SchoolID
         , Sch.Name                   As School_Name
  From     Students S
           Inner Join Schools Sch
                 On   Sch.School_Number = S.SchoolID
  </code></pre>
  
  <p>It is saved in Oracle Application Express in SQL Workshop as "Saved SQL".</p>
  
  <p>When I open the query and run it, It works and I get the expected results.</p>
  
  <p>Note: "ps.cksd_student" is a custom package has been created in s data base / schema associated with a third party application, PowerSchool.</p>
  
  <p>When I create an Apex report using this query as the SQL Query in the body and press run I get the following error:</p>
  
  <pre><code>    is_internal_error: true
      apex_error_code: APEX.AUTHORIZATION.ACCESS_DENIED
      component.type: APEX_APPLICATION_AUTHORIZATION
      component.id: 8270293315071117
      component.name: User still logged into PowerSchool
      error_backtrace:
  
      ----- PL/SQL Call Stack -----
        object      line  object
        handle    number  name
      00007FFBF623C890       642  package body APEX_050000.WWV_FLOW_ERROR
      00007FFBF623C890       710  package body APEX_050000.WWV_FLOW_ERROR
      00007FFBF623C890      1014  package body APEX_050000.WWV_FLOW_ERROR
      00007FFBF7EE0960       793  package body APEX_050000.WWV_FLOW_AUTHORIZATION
      00007FFC1790FE50      4518  package body APEX_050000.WWV_FLOW
      00007FFB9D79BF88       173  procedure APEX_050000.F
      00007FFB8554A498         2  anonymous block
  </code></pre>
  
  <p>When I remove the column generated by ps.cksd_student then the report runs fine.</p>
  
  <p>In Oracle, in the PS Schema, I have granted ps.cksd_student execute to the parsing schema and all of the schemas that seem to be connected to Apex, I even granted execute to public and I still get the same error.</p>
  
  <ul>
  <li>EXECUTE    APEX_050000</li>
  <li>EXECUTE    APEX_ADMINISTRATOR_ROLE</li>
  <li>EXECUTE    APEX_LISTENER</li>
  <li>EXECUTE    APEX_PUBLIC_USER</li>
  <li>EXECUTE    APEX_REST_PUBLIC_USER</li>
  <li>EXECUTE    FLOWS_FILES</li>
  <li>EXECUTE    NAVIGATOR_ROLE</li>
  <li>EXECUTE    PSNAVIGATOR</li>
  <li>EXECUTE    PS_APEX (my Parsing Schema)</li>
  <li>EXECUTE    PS_MGMT</li>
  <li>EXECUTE    PUBLIC</li>
  </ul>
  
  <p>One note. This package does log errors into a table in the PS database. I have granted Insert / Update / Select to:</p>
  
  <ul>
  <li>APEX_050000</li>
  <li>APEX_ADMINISTRATOR_ROLE</li>
  <li>APEX_LISTENER</li>
  <li>APEX_PUBLIC_USER</li>
  <li>APEX_REST_PUBLIC_USER</li>
  <li>PS_APEX (my Parsing Schema)</li>
  <li>PS_MGMT</li>
  </ul>
  
  <p>Two questions:</p>
  
  <p>What I find odd is the package is available from SQL Workshop but not an Apex Application.</p>
  
  <p>1) Now that I'm sure the Parsing Schema is covered is there something what else have I missed. Other packages from PS work with Apex so I know this is possible.</p>
  
  <p>2) I know this is throwing an Oracle error somewhere. Can someone help me find it? (probable disk / folder / name)</p>
  
  <p>Thanks,</p>
  
  <p>Eric.</p>