Home » SQL & PL/SQL » SQL & PL/SQL » Now you see it. Now you don't.
Now you see it. Now you don't. [message #690303] Mon, 10 March 2025 18:25 Go to next message
Darth Waiter
Messages: 84
Registered: October 2020
Member
When I run this query on a blank installation of Oracle 18c, it returns 1 row:

select t.OBJECT_ID, t.OWNER, t.OBJECT_NAME, t.OBJECT_TYPE, i.PROCEDURE_NAME
from ALL_OBJECTS t
inner join all_procedures i on i.OWNER = t.OWNER and i.PROCEDURE_NAME = t.OBJECT_NAME
where t.OBJECT_TYPE = 'PROCEDURE'
OBJECT_ID	OWNER	OBJECT_NAME	OBJECT_TYPE	PROCEDURE_NAME
7,101		SYS	SUBPTXT		PROCEDURE	SUBPTXT
When I plug values from the result into this query, I get an error:

select DBMS_METADATA.GET_DDL ('PROCEDURE', 'SUBPTXT', 'SYS') from dual;
Quote:
Error : 31603, Position : 7, SQL = select DBMS_METADATA.GET_DDL ('PROCEDURE', 'SUBPTXT', 'SYS') from dual
, Original SQL = select DBMS_METADATA.GET_DDL ('PROCEDURE', 'SUBPTXT', 'SYS') from dual
, Error Message = ORA-31603: object "SUBPTXT" of type PROCEDURE not found in schema "SYS"
ORA-06512: at "SYS.DBMS_METADATA", line 6681
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 6668
ORA-06512: at "SYS.DBMS_METADATA", line 9672
ORA-06512: at line 1

Position: 70
So is SUBPTXT a procedure that the 1st query found, or is it not?

[Updated on: Mon, 10 March 2025 18:26]

Report message to a moderator

Re: Now you see it. Now you don't. [message #690304 is a reply to message #690303] Tue, 11 March 2025 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68753
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Which user did you use for both queries?

Report message to a moderator

Re: Now you see it. Now you don't. [message #690305 is a reply to message #690303] Tue, 11 March 2025 02:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68753
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Also are you connected in CDB$ROOT or in a PDB?

Report message to a moderator

Re: Now you see it. Now you don't. [message #690306 is a reply to message #690303] Tue, 11 March 2025 02:28 Go to previous messageGo to next message
John Watson
Messages: 8972
Registered: January 2010
Location: Global Village
Senior Member
It works for me Smile
orclz> select DBMS_METADATA.GET_DDL ('PROCEDURE', 'SUBPTXT', 'SYS') from dual;

DBMS_METADATA.GET_DDL('PROCEDURE','SUBPTXT','SYS')
--------------------------------------------------------------------------------

  CREATE OR REPLACE NONEDITIONABLE PROCEDURE "SYS"."SUBPTXT" (name varchar2, su


orclz> sho user
USER is "SYS"
orclz> select cdb from v$database;

CDB
---
NO

orclz> select * from v$version;

BANNER
--------------------------------------------------------------------------------
BANNER_FULL
------------------------------------------------------------------------------------------------------------------------
BANNER_LEGACY                                                                             CON_ID
-------------------------------------------------------------------------------- ---------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production                         0


orclz>

Report message to a moderator

Re: Now you see it. Now you don't. [message #690307 is a reply to message #690306] Tue, 11 March 2025 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68753
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Yes, it works with SYS on non-CDB or CDB$ROOT but not if you are in a PDB or with a non-privileged account thus my questions.

Report message to a moderator

Re: Now you see it. Now you don't. [message #690308 is a reply to message #690307] Tue, 11 March 2025 03:49 Go to previous messageGo to next message
John Watson
Messages: 8972
Registered: January 2010
Location: Global Village
Senior Member
sys in a PDB no problem:
[oracle@sh8 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Mar 11 04:43:37 2025
Version 19.25.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.25.0.0.0

SQL> alter session et container=atest;
alter session et container=atest
              *
ERROR at line 1:
ORA-00922: missing or invalid option


SQL> c/et/set
  1* alter session set container=atest
SQL> /

Session altered.

SQL> select DBMS_METADATA.GET_DDL ('PROCEDURE', 'SUBPTXT', 'SYS') from dual;                             
DBMS_METADATA.GET_DDL('PROCEDURE','SUBPTXT','SYS')
--------------------------------------------------------------------------------

  CREATE OR REPLACE NONEDITIONABLE PROCEDURE "SYS"."SUBPTXT"


SQL>
Darth, looking at your original post, are you running some sort of pl/sql procedure? If so it will need to be either owner by sys and compiled with owner's rights, or with invoker's rights and run by sys.

Report message to a moderator

Re: Now you see it. Now you don't. [message #690310 is a reply to message #690308] Tue, 11 March 2025 06:56 Go to previous message
Michel Cadot
Messages: 68753
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

This is what you call no problem? Smile

You only have the header of the function, not the body.
Compare your both results.

Yes, you have no error but the error depends on your privileges:
SQL> create procedure p is begin null; end;
  2  /

Procedure created.

SQL> grant execute on p to scott;

Grant succeeded.

SQL> select DBMS_METADATA.GET_DDL ('PROCEDURE', 'P','MICHEL') from dual;
DBMS_METADATA.GET_DDL('PROCEDURE','P','MICHEL')
----------------------------------------------------------------------------------

  CREATE OR REPLACE PROCEDURE "MICHEL"."P" is begin null; end;

1 row selected.

SQL> connect scott/TIGER;
Connected.
SQL> execute michel.p

PL/SQL procedure successfully completed.

SQL> select owner, object_name, object_type from all_objects where owner='MICHEL' and object_name='P';
OWNER                          OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------ -------------------
MICHEL                         P                              PROCEDURE

1 row selected.
SQL> select DBMS_METADATA.GET_DDL ('PROCEDURE', 'P','MICHEL') from dual;
ERROR:
ORA-31603: object "P" of type PROCEDURE not found in schema "MICHEL"
ORA-06512: at "SYS.DBMS_METADATA", line 5805
ORA-06512: at "SYS.DBMS_METADATA", line 8344
ORA-06512: at line 1

Report message to a moderator

Previous Topic: Could you please explain how SYS_DBURIGEN() works?
Next Topic: Row generator
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Mar 15 22:56:39 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.