Stack Overflow
Stack Overflow

Questions tagged [dbms-metadata]

An Oracle package that enables the retrieval of an objects metadata.

DBMS_METADATA enables the retrieval of an objects metadata as either XML or the creation DDL then to submit the XML to re-create the object.

Questions tagged DBMS_METADATA should always be tagged .

37 questions
15
votes
2 answers

How to make dbms_metadata.get_ddl more pretty/useful

I am creating a package to generate the DDL of objects in my schema (you parse the object name, and return a clob with DDL), so I can generate files, and put them straight into SVN. I am using dbms_metadata.get_ddl, and it works great for all…
Patryk Turowicz
  • 151
  • 1
  • 1
  • 6
15
votes
2 answers

Get VIEW ddl using query

For database re-architecture I need to get DDL of each table and view in the database(Oracle). I don't want to go to property of each table/view and get SQL out of it in SQL Developer. I successfully got DDL for table using- select…
Mithun Khatri
  • 636
  • 3
  • 9
  • 22
10
votes
3 answers

Generating DDL script for object without schema name baked in using DBMS_METADATA.GET_DDL?

How can I generate the DDL script for my object with DBMS_METADATA.GET_DDL without the schema name baked in? With DBMS_METADATA.GET_DDL: CREATE TABLE "MYSCHEMA"."MYTABLE" ( "COL1" NUMBER(10,0) ) SQL Developer can do that, and I think it's also…
Palesz
  • 2,104
  • 18
  • 20
6
votes
4 answers

Oracle's dbms_metadata.get_ddl for object_type JOB

I'd like to create ddl scripts for most of my database objects. dbms_metadata.get_ddl works for most of the object types. For instance the following creates the ddl for a view: select dbms_metadata.get_ddl ( 'VIEW', 'SAMPLE_VIEW') from dual On the…
asalamon74
  • 6,120
  • 9
  • 46
  • 60
5
votes
1 answer

How can i ignore create portioned script from create table script using get_ddl in oracle?

I am working on java project in which we are getting create table ddl using get_ddl method in Oracle 12c. For example my table name is PARENT_EMP and schema is SECONDARYUSER then I am using get_ddl method. select…
santosh
  • 435
  • 1
  • 7
  • 24
4
votes
2 answers

Oracle 12c interprets SQL in a strange way (Inner Query)

We've recently migrated our Oracle database from 10g to 12c (12.1.0.1.0). After considering an issue with some queries we deceided to further clean up the database and drop all unneeded objects. Therefore I wrote a query that searches the database…
skyfrog
  • 117
  • 10
3
votes
1 answer

make DBMS_METADATA.GET_DDL Pretty with identation

I'm trying to generate DDL with JAVA Calling: DBMS_METADATA.GET_DDL; DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',TRUE); It works Fine but it gives me something like: CREATE TABLE "DEPARTMENTS" ( "DEPARTMENT_ID"…
maryam
  • 147
  • 3
  • 11
3
votes
2 answers

what's the reason of dbms_metadata.get_granted_ddl('SYSTEM_GRANT', 'AQ_ADMINISTRATOR_ROLE') ora-31608 error?

I'd like to get ddls of all roles in the database using dbms_metadata package. Unfortunately dbms_metadata.get_granted_ddl fails with error when there are no grant (object, system or role type) for the role. That's why I have to check the presence…
hotmori
  • 91
  • 2
  • 5
2
votes
0 answers

How to remove Primary Key from DBMS_METADATA?

I'm running Oracle 12.2 and want to copy the table with its dependent objects to a new schema and new name using DBMS_METADATA as below: h_read :=…
PiC
  • 137
  • 10
2
votes
1 answer

ORA-00904: "DBMS_METADATA"."GET_DDL": invalid identifier

I get the ORA-00904: "DBMS_METADATA"."GET_DDL": invalid identifier error when I run dbms_metadata. That error is for having the wrong column name or alias. I am not sure why I am getting it. Here is some code to show the error: I create a…
Harry L
  • 95
  • 1
  • 7
2
votes
1 answer

Get the DDL of a materialized view on a pre-built table - getting single-row subquery

I have a materialized view on a pre-built table with the same name. When attempting to use DBMS_METADATA.GET_DDL() to obtain the DDL of the materialized view I'm getting ORA-01427: single-row subquery returns more than one row from within…
Ben
  • 51,770
  • 36
  • 127
  • 149
2
votes
2 answers

Get complete ddl for index in oracle

I am using oracle 11g/12c. I want to get ddl of indexes in my database. For this I used the query - SELECT DBMS_METADATA.GET_DDL('INDEX','SYS_IL0000091971C00001$$','CCEEXPERTS') FROM dual Here 'SYS_IL0000091971C00001$$' is my index name and…
parita porwal
  • 662
  • 1
  • 10
  • 32
2
votes
2 answers

Which Oracle view contains all constraints together?

I'm trying to get CONSTRAINTS from user_objects table like this: select CASE object_type WHEN 'DATABASE LINK' then 'dblinks' WHEN 'FUNCTION' then 'functions' WHEN 'INDEX' then 'indexes' WHEN 'PACKAGE' then 'packages' …
Ronaldus
  • 29
  • 2
  • 7
1
vote
0 answers

ORA-31607 on DBMS_METADATA.PUT(CLOB): Either (1) FETCH_XML was called

I try to alter DB structure via DBMS_METADATA package, but get an error. This is function inside a package I use: FUNCTION putxml( alterxml IN CLOB, sub_res OUT NOCOPY sys.ku$_SubmitResults ) RETURN BOOLEAN IS ret_val …
Olga Pshenichnikova
  • 1,509
  • 4
  • 22
  • 47
1
vote
3 answers

How to i read the contents of Oracle stored procedures using Python

I am trying to read the contents/code of a stored procedure using python. i used cx_Oracle function to establish the connection with oracle database. here is the code import cx_Oracle as co import pandas as pd dsn_tsn =…
Ashwin Kumar Nagarajan
  • 13
  • 4
1
2 3