Using DBMS_METADATA in Procedure to reference another schema's object

This is a question already asked by many and I thought I should give an example here. You want to call DBMS_METADATA in PL/SQL created on your schema, but referencing another users object and use the the error like:

*
ERROR at line 1:
ORA-31603: object "EMP" of type TABLE not found in schema "SCOTT"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 2805
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1
ORA-06512: at "TEST.TEST_META", line 5
ORA-06512: at line 1


It is an expected behviour which is stated in the security Model for dbms_metadata in the Oracle® Database PL/SQL Packages and Types Reference
.

The SELECT_CATALOG_ROLE alone does not give you privilege to use it in a package/procedure for another schema. But it allows you to use it outside.

So you need to use the invokers-right by adding authid current_user

Example:

SQL> connect test/test
Connected.
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMP', 'SCOTT') from dual;

DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
--------------------------------------------------------------------------------

CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),

SQL> create or replace procedure test_meta
2 as
3 v_str varchar2(32767);
4 begin
5 SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMP', 'SCOTT') into v_str from dual;
6 dbms_output.put_line (v_str);
7 end;
8 /

Procedure created.

SQL> exec test_meta;
BEGIN test_meta; END;

*
ERROR at line 1:
ORA-31603: object "EMP" of type TABLE not found in schema "SCOTT"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 2805
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1
ORA-06512: at "TEST.TEST_META", line 5
ORA-06512: at line 1

SQL> create or replace procedure test_meta authid current_user
2 as
3 v_str varchar2(32767);
4 begin
5 SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMP', 'SCOTT') into v_str from dual;
6 dbms_output.put_line (v_str);
7 end;
8 /

Procedure created.

SQL> exec test_meta;

CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),

"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL"
NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT
"BIN$Bj5vCCIBT6uv5ZCEA/Zm0A==$0" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10
INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
DEFAULT)
TABLESPACE "USERS" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1
MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS
1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE ROW MOVEMENT


PL/SQL procedure successfully completed.

SQL>

Oracle® 11g Database Hints

I have finally installed Oracle® Database 11g Release 1 (11.1) for my testing. I will be sharing my experience with this post as I test and try to break it.


Platform: Windows XP SP2
Dell Latitude D810 Laptop
Intel Pentium M 2.0 GHz Processor
1GB RAM
60GB Hard Drive

Installation
My first install is for Windows. As usual, the Installation Guides from the Oracle® 11g Documentation Library was very helpul.

Installation Components
There are installation changes you may want to take note of. These include the addition of the following components: Oracle Application Express, Oracle Configuration Manager,Oracle Database Vault,Oracle Real Application Testing,Oracle SQL Developer and Oracle Warehouse Builder.

Pre installation checks - DHCP
The Pre installation check now includes checking and reporting if your machine has DHCP assigned IP Address. My System has a DHCP assigned IP Address. I got the warning, but ignored it because the IP assigned to me via DHCP is Reserved for my PC on our network. Reserved is not the same as Static.

Hardware Requirements

Physical memory: (RAM) 1 GB minimum
Virtual memory: Double the amount of RAM
Disk space: Basic Installation Type total: 4.55 GB
Disk space: Advanced Installation Types total: 4.92 GB
Video adapter: 256 colors
Processor: 550 MHz minimum (On Windows Vista, 800 MHz minimum)

Take note of the following hardware requirements. This is important because if you plan to installation with preconfigured database, you System might be so slow that you cannot start anything after the install because it will automatically start the database.

SPFILE

Below is some contents of my SPFILETEST11DB.ORA immediately after install, before I started changing it. So, looking at it, it is important that you have at least the 1GB RAM as recommended.

test11db.__db_cache_size=180355072
test11db.__java_pool_size=12582912
test11db.__large_pool_size=4194304
test11db.__oracle_base='C:\oracle'#ORACLE_BASE set from environment
test11db.__pga_aggregate_target=104857600
test11db.__sga_target=327155712
test11db.__shared_io_pool_size=0
test11db.__shared_pool_size=125829120
test11db.__streams_pool_size=0
*.audit_file_dest='C:\oracle\admin\test11db\adump'
*.audit_trail='db'
*.compatible='11.1.0.0.0'
*.db_block_size=8192
*.db_domain=''
*.db_name='test11db'
*.db_recovery_file_dest='C:\oracle\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.diagnostic_dest='C:\oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=test11dbXDB)'
*.memory_target=428867584
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'


Deprecated Parameters

#background_dump_dest
#user_dump_dest

Deprecated Components

SQL*Plus Gui and iSQL*Plus is not included in the release
Enterprise Manager Java Console is not included in the release

Stanby Database

Oracle 11g allows you to use RMAN to change the db_unique_name of a Standby database using the CHANGE command CHANGE DB_UNIQUE_NAME FROM old_name to new_name. You can use this command after you have changed it in init parameter to update the recovery catalog.


Enterprise Manager Database Control
Also, the new look and feel of Enterprise Manager Console is an interesting. It is now grouped into the following tabs: Home, Performance, Availability, Server, Schema, Data Movement, Software and Support.

Why can I find my Database Tool in the EM Console?

Click my test images below.




EM Proxy and Browser Issues

I have Proxy settings on my Explorer. The bad news is that this is reset each time I log on to our Network. So, I have to change it not to proxy for my localhost, my servername and servername.domain.com. If I do not remember to do this, I keep getting Page Not found errors.

Note that this issue is not because of Oracle 11g. It is normal when you have to access any webserver related stuff on your PC and the browser has to go to the Proxy Server and back to yur PC. So whether it is 11g or 10g, you will get the same error.

Secured DB Console

I did a standard install using preconfigured setting. So by default, DB Console is secured. When I accessed with http, I kept getting a blank screen with squarea. I then checked my logs, changed the URL to use https and it worked.

Oracle® Demos for Scott Tiger

Ever thought of where to find those Oracle® demos for Scott Tiger ? I am posting it here because a lot of new Oracle Users can hardly find it. So you can pick it up here.

--
-- Copyright (c) Oracle Corporation 1988, 2000. All Rights Reserved.
--
-- NAME
-- demobld.sql
--
-- DESCRIPTION
-- This script creates the SQL*Plus demonstration tables in the
-- current schema. It should be STARTed by each user wishing to
-- access the tables. To remove the tables use the demodrop.sql
-- script.
--
-- USAGE
-- From within SQL*Plus, enter:
-- START demobld.sql

SET TERMOUT ON
PROMPT Building demonstration tables. Please wait.
SET TERMOUT OFF

DROP TABLE EMP;
DROP TABLE DEPT;
DROP TABLE BONUS;
DROP TABLE SALGRADE;
DROP TABLE DUMMY;

CREATE TABLE EMP
(EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2));

INSERT INTO EMP VALUES
(7369, 'SMITH', 'CLERK', 7902,
TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO EMP VALUES
(7499, 'ALLEN', 'SALESMAN', 7698,
TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO EMP VALUES
(7521, 'WARD', 'SALESMAN', 7698,
TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO EMP VALUES
(7566, 'JONES', 'MANAGER', 7839,
TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO EMP VALUES
(7654, 'MARTIN', 'SALESMAN', 7698,
TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES
(7698, 'BLAKE', 'MANAGER', 7839,
TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO EMP VALUES
(7782, 'CLARK', 'MANAGER', 7839,
TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO EMP VALUES
(7788, 'SCOTT', 'ANALYST', 7566,
TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
(7839, 'KING', 'PRESIDENT', NULL,
TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES
(7844, 'TURNER', 'SALESMAN', 7698,
TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
INSERT INTO EMP VALUES
(7876, 'ADAMS', 'CLERK', 7788,
TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES
(7900, 'JAMES', 'CLERK', 7698,
TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
INSERT INTO EMP VALUES
(7902, 'FORD', 'ANALYST', 7566,
TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
(7934, 'MILLER', 'CLERK', 7782,
TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

CREATE TABLE DEPT
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13) );

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

CREATE TABLE BONUS
(ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER,
COMM NUMBER);

CREATE TABLE SALGRADE
(GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER);

INSERT INTO SALGRADE VALUES (1, 700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);

CREATE TABLE DUMMY
(DUMMY NUMBER);

INSERT INTO DUMMY VALUES (0);

COMMIT;

SET TERMOUT ON
PROMPT Demonstration table build is complete.

EXIT

DBMS Direct at Oracle® Technology Network

I thought I should provide you with a link to find answers to some of the questions I have already answered or contributed to Oracle® Technology Network Forums.

Search My Oracle® Technology Network Contributions

If you cannot find the required answer, then feel free to ask me.

All the best