3 Replies Latest reply on Jul 8, 2019 11:22 AM by martv

    run SODA in package problem

    martv

      Hi,

       

      I’ve some problems running SODA code in a package.

       

      DB Version:

      Oracle Database 18c EE High Perf Release 18.0.0.0.0 - Production

      Version 18.5.0.0.0

       

      If i run the SODA code in anonymous block it works

      Declare

      Begin

      ....

      End;

      /

       

      But if i put the code in a package it doesn’t work

      I allready did

      GRANT SODA_APP TO user;

       

      Begin

      1. Package.procedure(par..);

      End;

      /

       

      ORA-06550: Regel 1, kolom 8:

      PLS-00201: Identificatiesymbool 'DBMS_SODA_ADMIN' moet worden gedeclareerd..

      ORA-06550: Regel 1, kolom 8:

      PL/SQL: Statement ignored.

      ORA-06512: in "SYS.DBMS_SODA", regel 65

      ORA-06512: in "RDFUSER.PKG_VO_APEX_LD", regel 309

       

          -- Open the collection

      309:     collection := DBMS_SODA.open_collection('soda_lidia_applications');

       

      describe           doesn’t give any result.

       

       

      Any suggestions what to do?

       

      Greetings

        • 1. Re: run SODA in package problem
          MaxOrgiyan-Oracle

          Right, that has to do with PLSQL language itself.

           

          The workaround is to grant SODA_APP to the procedure from your admin user (the one that can grant SODA_APP):

           

          grant SODA_APP to procedure mySchemaNameHere.myProcedureNameHere;

           

          You'd substitute "mySchemaNameHere" for the schema where you have your procedure defined.

           

          In your case, since you your procedure(s) in a package, you can grant SODA_APP to the package from your admin user:

           

          grant SODA_APP to package mySchemaNameHere.myPackageNameHere;

           

          Similarly, you'd substitute "mySchemaNameHere" for the schema where you have your package.

           

          Quote from our doc:

           

          If a PL/SQL subprogram that you write invokes subprograms that are in package DBMS_SODA, and if your subprogram has definer (owner) rights, then a database administrator (DBA) must grant role SODA_APP to your subprogram. For example, this code grants role SODA_APP to procedure my_soda_proc, which is owned by database schema (user) my_db_schema:

          GRANT SODA_APP TO PROCEDURE my_db_schema.my_soda_proc;
          • 2. Re: run SODA in package problem
            martv

            Hi,

             

            doesn't seem to work.

            grant SODA_APP to pkg_vo_apex_ld rdfuser.pkg_vo_apex_ld;

             

            Error starting at line : 3 in command -

            grant SODA_APP to pkg_vo_apex_ld rdfuser.pkg_vo_apex_ld

            Error report -

            ORA-00933: SQL-opdracht is niet juist beëindigd.

            00933. 00000 -  "SQL command not properly ended"

            *Cause:   

            *Action:

            • 3. Re: run SODA in package problem
              martv

              Sorry,

               

              My fault.

              this works: grant SODA_APP to package rdfuser.pkg_vo_apex_ld;

               

              thanks