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
1
vote
1 answer

Using python cx_Oracle, how do I call DBMS_METADATA.SET_TRANSFORM_PARAM, which has multiple declarations?

Here's the call and error I currently receive: cursor.callproc('DBMS_METADATA.SET_TRANSFORM_PARAM', ['DBMS_METADATA.SESSION_TRANSFORM', 'STORAGE', 'false']) cx_Oracle.DatabaseError: ORA-06550: line 1, column 7: PLS-00307: too many declarations of…
Beege
  • 665
  • 4
  • 18
1
vote
0 answers

How to configure dbms_metadata.get_ddl to add 'ASC' after every column of the index?

Hello I am trying to export some indexes from a schema and I am using dbms_metadata.get_ddl So far the indexes are exported like this CREATE UNIQUE INDEX "TEST" ON "TABLE" (ROUND("COLUMN1") , NVL("COLUMN2",0) , NVL("COLUMN3",0) , "YEAR" , "DAY"); …
Mihaela Ciucă
  • 11
  • 1
1
vote
0 answers

how to convert DBMS_METADATA Oracle to Postgres?

How to convert DBMS_METADATA Procedures and Functions for Submitting XML Data in oracle to postgresql For example : SET serveroutput ON; DECLARE v_handle NUMBER; v_transform_handle NUMBER; v_ddls sys.ku$_ddls; v_ddl sys.ku$_ddl; BEGIN …
Groot
  • 21
  • 3
1
vote
1 answer

is there builtins in ojdbc for constants of DBMS_METADATA package?

package Oracle DBMS_METADATA contain Object Type Constants AQ_QUEUE REF_CONSTRAINT AQ_QUEUE_TABLE REFRESH_GROUP AQ_TRANSFORM RESOURCE_COST ASSOCIATION .... I want program in java using this constants and want avoid code duplication if…
qwazer
  • 7,174
  • 7
  • 44
  • 69
1
vote
1 answer

How to get exact table definition in PlSql

ESU_1 is the Source table create table ESU_1 ( emp_id NUMBER(10), emp_name VARCHAR2(100) ); I created a table ESU_2 by using ESU_1 create table ESU_2 as select * from ESU_1 t where t.emp_id>20; When I used below query to get table…
Eswara Rao L
  • 11
  • 4
1
vote
1 answer

Why ths works in anonymous but not in a procedure?

If I do the following, everything is fine: declare l_foo clob; begin select regexp_replace( dbms_metadata.get_ddl('USER', 'SCOTT', null) || dbms_metadata.GET_GRANTED_DDL ('SYSTEM_GRANT', 'SCOTT') || dbms_metadata.GET_GRANTED_DDL…
chris
  • 89
  • 6
1
vote
0 answers

DBMS_METADATA: Get table SXML with triggers

I want to read the SXML representation of a table using this simple function: CREATE OR REPLACE FUNCTION get_table_sxml(name IN VARCHAR2) RETURN CLOB IS open_handle NUMBER; transform_handle NUMBER; doc …
rk_cpa
  • 155
  • 1
  • 9
0
votes
0 answers

How to remove Flashback Archive statements from Oracle's dbms_metadata.fetch_ddl output

When running dbms_metadata.fetch_ddl or dbms_metadata.get_ddl, the output includes ALTER TABLE "SCHEMA"."TABLE" FLASHBACK ARCHIVE "ARCHIVE_NAME". Is there any option in the dbms_metadata package to remove this line? I could use regex, but I would…
Josh Reynolds
  • 3
  • 1
0
votes
1 answer

Oracle Get DDL without extra details and sort - to use for version control

I'm trying to get the DDL for tables, views, indexes, sequences and so on in order to use for version control, and do comparisons between different db's which should have identical tables (i.e. dev vs uat). I would like to be able to sort by table…
tcm24
  • 11
  • 3
0
votes
0 answers

How can I get user creation code using DBMS_METADATA?

I need to get a DDL script of a partucular user. I'm trying to use the DBMS_METADATA.GET_DDL('USER', user) to get a ddl of current user, but I get an error: "object "user1" of type USER not found in schema "user1. I guess, this means that I don't…
stakry
  • 1
  • 1
0
votes
0 answers

Want to limit results from dbms_metadata.get_granted_ddl for object_grant in Oracle

I am trying to do an Oracle schema level transfer from one database to another. I have discovered that grants to SYS objects are NOT transferred with a schema. This is documented here: Doc ID 1911151.1 but the proposed solution script does not…
Gary G
  • 1
  • 1
0
votes
1 answer

How do i use DBMS_METADATA.SET_FILTER to exclude all TMP_ tables except TMP_BAR?

I am using dbms_metadata to extract the objects of a schema and to filter all tables whose names start with either TEMP_ or TMP_: dbms_metadata.set_filter(exp_h, 'NAME_EXPR', q'[not like 'TEMP_%' ESCAPE '\']',…
René Nyffenegger
  • 39,402
  • 33
  • 158
  • 293
0
votes
0 answers

how to access index (DDL) of the table in pl/sql using DBMS_METADATA.GET_DDL

I am trying to access index DDL of a table using user_indexes and DBMS_METADATA.GET_DDL declare V_DDL clob; Begin for rec in (select ai.owner, ai.index_name, ai.table_name from all_indexes ai, base_table til where ai.table_name =…
pawan rakesh
  • 45
  • 11
0
votes
0 answers

Oracle SQL Spool dbms_metadata.get_ddl different in 19c

When our database was running in 11g the output of >> spool post_create_users_metadata.sql select dbms_metadata.get_ddl('USER', 'PHIL') txt from dual; was >> CREATE USER "PHIL" IDENTIFIED BY VALUES 'bhbhbhjkbh' DEFAULT TABLESPACE "DATA" TEMPORARY…
scolariire
  • 11
  • 2
0
votes
1 answer

How to save Oracle DDL in an automated fashion

Oracle SQL Developer is able to export DDL through Tools -> Database Export... This works very well, but requires manual intervention. I know of DBMS_METADATA.get_ddl(), what I want to do is save the generated DDL in client machine which I use to…
Pradeepa Mendis
  • 3
  • 2
1
2
3