0

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?

axle_h
  • 553
  • 1
  • 5
  • 16

2 Answers2

0

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;
Pugzly
  • 844
  • 3
  • 14
  • I can't get write permissions to the source database unfortunately, if I could then I think simply calling `dbms_metadata.get_ddl` on all objects in `user_objects` (no PLSQL needed) would work fine. e.g. https://stackoverflow.com/a/10886633/1970286 – axle_h Apr 29 '22 at 08:44
0

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';
Brian Leach
  • 2,025
  • 1
  • 11
  • 14
  • Nice, that covers types, views and packages although `all_source` does not include `CREATE` statements I 'think' that could be fixed without parsing the SQL. As for tables, indexes, sequences etc, maybe they're building them up from meta like in `user_tables`, `user_tab_cols`? I just hoped there'd be an open source thing that handled this already. – axle_h Apr 29 '22 at 08:37
  • Generally speaking open source enthusiasts look to fill in the gaps in existing functionality rather than duplicating built-in libraries. So as Oracle already provided DBMS_METADATA it is highly unlikely anybody will have bothered to roll their own equivalent. Essentially what you have here is a **political** problem - not having access to either a source control repository or an accessible database - rather than a programming issue. – APC Apr 29 '22 at 13:22
  • Having said which, it is likely there are open source libraries which work for generic SQL over generic data dictionaries. I'm guessing that's what Jetbrains does. But finding such a library is not a programming question either. – APC Apr 29 '22 at 13:24