Generate Oracle DDL without using dbms

I need to automate DDL generation on Oracle where I have read only access. When attempting to do this via dbms_metadata e.g. dbms_metadata.get_ddl('TABLE', 'SOME_TABLE') I get:

ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database or pluggable database open for read-only access

Which is surprising as I am not asking to write to the database.

It is not possible for me to get more access to fix this.

However, I can successfully use Intellij or DataGrip to generate DDL https://www.jetbrains.com/datagrip/features/generation.html and the output is sufficient.

Any idea how the IDEs are doing this? Or what (if any) open source package Intellij are using?

CodePudding user response：

I don't have a pluggable database but I used the following on livesql and my database and it works well.

The error indicates a read only database do you have a read/write db to test on?

Note if you are trying my example change the value of owner to meet your requirements


CREATE  OR REPLACE PROCEDURE get_metadata(
p_object_type VARCHAR2, 
p_object_name VARCHAR2,
p_owner VARCHAR2) authid current_user is
      x clob;
    begin
      x := 
dbms_metadata.get_ddl(
p_object_type,
p_object_name, 
p_owner);
dbms_output.put_line(x);
    end;   
/


BEGIN 
    FOR cur_r IN(
SELECT OBJECT_TYPE,
OBJECT_NAME,
OWNER
FROM ALL_OBJECTS 
WHERE OWNER like 'SQL%'
AND OBJECT_TYPE IN ('TABLE', 'INDEX', 'FUNCTION', 'PROCEDURE', 'TYPE','PACKAGE', 'SEQUENCE') order by OBJECT_NAME 
)
   LOOP
  
get_metadata
  (cur_r.OBJECT_TYPE,
  cur_r.OBJECT_NAME,
   cur_r.OWNER);
  
  END LOOP;
END;

CodePudding user response：

They might be selecting from all_source:

  SELECT text
    FROM all_source
   WHERE owner = 'CEAADMIN'
     AND name = 'APPLICATION_LIST'
     AND TYPE IN ('PACKAGE', 'PACKAGE BODY')
ORDER BY TYPE,  line;

View source can be retrieved from all_views. The difference is that the view is kept in a WIDEMEMO type.

SELECT *
  FROM all_views
 WHERE owner = 'CEAADMIN' AND view_name = 'MYLINKS_VW';