Using SQL Macro Package Vs "Traditional" package

From: Zahir Mohideen <zahir.dba_at_gmail.com>
Date: Wed, 6 Nov 2024 14:13:15 -0500
Message-ID: <CAM5KiKrwAY_LvjvGqJ8CeABF4FAZbT5tGqX2M=VzcFGED611OA_at_mail.gmail.com>

I am trying to use Macro in a package and granted execute privs to another user ( in my case , rptuser).

DBUSER has a table , regular package , SQL macro package. Both are with DEFINER rights .
Also , learn that , we can't have a SQL macro package with invoker rights.

When I execute the "regular" package from another user, I get the results as expected.
When I execute the "SQL Macro" package from another , I get "invalid permissions" ( ORA-00942).

Here is my test case below.
Am I misunderstanding about the usage of "SQL Macro" package ?

Note: I posted this question to asktom website this morning as well.

DB Version

---

SQL> select banner from v$version;

BANNER

---

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

DBUSER

---

Here , I am creating tables and packages under "DBUSER".

create table emp ( eid int , ename varchar2(50) )

insert into emp values ( 100 , 'Zahir Mohideen') ; insert into emp values ( 101 , 'Abdul Hameed') ;

commit;

create or replace package pkg_emp
as

    procedure listemp( p_eid int , rfc_emp OUT SYS_REFCURSOR ); end pkg_emp;
/

CREATE OR REPLACE PACKAGE BODY pkg_emp AS

    PROCEDURE listemp (

        p_eid   INT,
        rfc_emp OUT SYS_REFCURSOR

    ) AS
    BEGIN

        OPEN rfc_emp FOR SELECT
                                              ename
                                          FROM
                                              dbuser.emp
                         WHERE
                             eid = p_eid;

    END listemp;
END pkg_emp;
/

create or replace package pkg_emp_mac
as

    FUNCTION listemp ( p_eid int ) RETURN VARCHAR2 SQL_MACRO ; end pkg_emp_mac;
/

CREATE OR REPLACE PACKAGE BODY pkg_emp_mac AS

      FUNCTION listemp ( p_eid  int  )   RETURN VARCHAR2 SQL_MACRO
       IS
      BEGIN

          RETURN q'{
          Select  ename from emp where eid = p_eid
      }';
     END;

END pkg_emp_mac
;
/

grant execute on pkg_emp_mac to rptuser; grant execute on pkg_emp to rptuser;

Here , I am executing package calls as "RPTUSER"

SQL> show user
USER is "RPTUSER"
SQL> var x refcursor;

SQL> exec dbuser.pkg_emp.listemp( 100 , :x);

PL/SQL procedure successfully completed.

SQL> print :x;

ENAME

---

Zahir Mohideen

SQL> select * from dbuser.pkg_emp_mac.listemp(100); select * from dbuser.pkg_emp_mac.listemp(100)

                                            *

ERROR at line 1:
ORA-00942: table or view does not exist

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Nov 06 2024 - 20:13:15 CET