Styjun

Why does the Saturn V have standalone inter-stage rings?

What size of powerbank will I need to power a phone and DSLR for 2 weeks?

Loss of power when I remove item from the outlet

Can humans ever directly see a few photons at a time? Can a human see a single photon?

Employer wants to use my work email account after I quit

Why does Linux list NVMe drives as /dev/nvme0 instead of /dev/sda?

Can there be an UN resolution to remove a country from the UNSC?

Do I have any obligations to my PhD supervisor's requests after I have graduated?

How to make clear to people I don't want to answer their "Where are you from?" question?

Is it damaging to turn off a small fridge for two days every week?

Non-flat partitions of a set

What exactly is the 'online' in OLAP and OLTP?

"How can you guarantee that you won't change/quit job after just couple of months?" How to respond?

How many children?

Interaction between Leyline of Anticipation and Teferi, Time Raveler

What could exist inside and between the walls of a Dyson Sphere?

Array initialization optimization

Can you find x?

How to model a twisted cylinder like this

Can Ogre clerics use Purify Food and Drink on humanoid characters?

Java TreeMap.floorKey() equivalent for std::map

Is "Busen" just the area between the breasts?

Are all instances of trolls turning to stone ultimately references back to Tolkien?

Does having had a visa for a country mean I used to be a citizen/national of that country?

Extract FULL DDL of XMLINDEX in Oracle

Get list of all tables in Oracle?How do I limit the number of rows returned by an Oracle query after ordering?Difference between a user and a schema in Oracle?What is DDL and DML?SQL Group By On Output From User Defined FunctionOracle xmltype column, create xmlindex with include below pathSQL Query - Indirect joining of two tablesJoining tables with common columns in Oracle SQLDiffing 2 rows of 2 tables based on column values (without prior knowledge of columns names)Oracle Grouping by data value in a column

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

1

I want to extract DDL of XMLINDEX but when I run the below code I got incorrect or incomplete code.

execute dbms_output.put_line(DBMS_METADATA.GET_DDL('INDEX','IDX_XML_AD','SCHEMAXX'));

This is the output coming from the above command:

 CREATE INDEX "IDX_XML_AD" ON "AD_TABLE" ("AD_COL1") 
 INDEXTYPE IS "XDB"."XMLINDEX" 

I want output as below:

CREATE INDEX IDX_XML_AD ON AD_TABLE ( "AD_COL1" )
 INDEXTYPE IS "XDB"."XMLINDEX" PARAMETERS 
 (
 q'
 [

 PATH TABLE "X_AD_PATHTAB_FULLDATA" 

 GROUP GRP_1 
 XMLTABLE X_AD_1 '/ABC' 
 columns 
 "COL1" VARCHAR2(30) PATH 'col1', 
 "COL2" VARCHAR2(30) PATH 'col2' 

 GROUP GRP_2 
 XMLTABLE X_AD_2 '/ABC/def/ghi/COL3' 
 columns 
 "COL3" VARCHAR2(30) PATH '/' 
 ]'
 );

I tried DBMS_METADATA package. Also I tried SQL Developer export tool. But not getting expected code in output.

oracle ddl xmlindex

share|improve this question

edited Mar 25 at 10:04

swet

asked Mar 25 at 8:34

swetswet

488

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Have you done set long 32767, and possibly set longchunk 32767 too, from SQL*Plus or SQL Developer (running your query as a statement or script)? What is wrong with the version from the SQL Developer export tool?
  
  – Alex Poole
  Mar 25 at 9:30
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I did check with set long 32767 and set longchunk 32767 in sql developer. Nothing wrong with SQL developer version or export tool. I think DBMS_METADATA is incapable of extracting XMLINDEX DDL. If yes then how to do it?
  
  – swet
  Mar 25 at 10:07
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I'm not sure what you mean, you seem to be saying it does work and that it doesn't work at the same time. I just tried creating a dummy table and your index, then retrieving it with dbms_metadata.get_ddl, and it was fine (SQL Developer 18.4, Oracle 11gR2, as a simple query or via execute). The only difference I can see is that it comes back with escaped single quotes rather than the q'[...]' alternate syntax. You can see the same thing in db<>fiddle.
  
  – Alex Poole
  Mar 25 at 10:11
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  what you have given in the attachment, I tried and got the result as expected, thank. But the actual XMLINDEX is >1000 chars in length and I think this is the problem because of which sqlplus/sql developer are not printing the whole code. Can you find some way to print XMLINDEX code which has PARAMETERS >1000 chars?
  
  – swet
  Mar 25 at 10:34
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  How are you creating it in the first place - if I try I get ORA-29896: Length of PARAMETER string longer than 1000 characters. Which version of Oracle are you using? (It fails in 18c too...)
  
  – Alex Poole
  Mar 25 at 10:38
  
  

  
  
  
  

 | 
show 5 more comments

1

I want to extract DDL of XMLINDEX but when I run the below code I got incorrect or incomplete code.

execute dbms_output.put_line(DBMS_METADATA.GET_DDL('INDEX','IDX_XML_AD','SCHEMAXX'));

This is the output coming from the above command:

 CREATE INDEX "IDX_XML_AD" ON "AD_TABLE" ("AD_COL1") 
 INDEXTYPE IS "XDB"."XMLINDEX" 

I want output as below:

CREATE INDEX IDX_XML_AD ON AD_TABLE ( "AD_COL1" )
 INDEXTYPE IS "XDB"."XMLINDEX" PARAMETERS 
 (
 q'
 [

 PATH TABLE "X_AD_PATHTAB_FULLDATA" 

 GROUP GRP_1 
 XMLTABLE X_AD_1 '/ABC' 
 columns 
 "COL1" VARCHAR2(30) PATH 'col1', 
 "COL2" VARCHAR2(30) PATH 'col2' 

 GROUP GRP_2 
 XMLTABLE X_AD_2 '/ABC/def/ghi/COL3' 
 columns 
 "COL3" VARCHAR2(30) PATH '/' 
 ]'
 );

I tried DBMS_METADATA package. Also I tried SQL Developer export tool. But not getting expected code in output.

oracle ddl xmlindex

share|improve this question

edited Mar 25 at 10:04

swet

asked Mar 25 at 8:34

swetswet

488

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Have you done set long 32767, and possibly set longchunk 32767 too, from SQL*Plus or SQL Developer (running your query as a statement or script)? What is wrong with the version from the SQL Developer export tool?
  
  – Alex Poole
  Mar 25 at 9:30
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I did check with set long 32767 and set longchunk 32767 in sql developer. Nothing wrong with SQL developer version or export tool. I think DBMS_METADATA is incapable of extracting XMLINDEX DDL. If yes then how to do it?
  
  – swet
  Mar 25 at 10:07
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I'm not sure what you mean, you seem to be saying it does work and that it doesn't work at the same time. I just tried creating a dummy table and your index, then retrieving it with dbms_metadata.get_ddl, and it was fine (SQL Developer 18.4, Oracle 11gR2, as a simple query or via execute). The only difference I can see is that it comes back with escaped single quotes rather than the q'[...]' alternate syntax. You can see the same thing in db<>fiddle.
  
  – Alex Poole
  Mar 25 at 10:11
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  what you have given in the attachment, I tried and got the result as expected, thank. But the actual XMLINDEX is >1000 chars in length and I think this is the problem because of which sqlplus/sql developer are not printing the whole code. Can you find some way to print XMLINDEX code which has PARAMETERS >1000 chars?
  
  – swet
  Mar 25 at 10:34
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  How are you creating it in the first place - if I try I get ORA-29896: Length of PARAMETER string longer than 1000 characters. Which version of Oracle are you using? (It fails in 18c too...)
  
  – Alex Poole
  Mar 25 at 10:38
  
  

  
  
  
  

 | 
show 5 more comments

I want to extract DDL of XMLINDEX but when I run the below code I got incorrect or incomplete code.

execute dbms_output.put_line(DBMS_METADATA.GET_DDL('INDEX','IDX_XML_AD','SCHEMAXX'));

This is the output coming from the above command:

 CREATE INDEX "IDX_XML_AD" ON "AD_TABLE" ("AD_COL1") 
 INDEXTYPE IS "XDB"."XMLINDEX" 

I want output as below:

CREATE INDEX IDX_XML_AD ON AD_TABLE ( "AD_COL1" )
 INDEXTYPE IS "XDB"."XMLINDEX" PARAMETERS 
 (
 q'
 [

 PATH TABLE "X_AD_PATHTAB_FULLDATA" 

 GROUP GRP_1 
 XMLTABLE X_AD_1 '/ABC' 
 columns 
 "COL1" VARCHAR2(30) PATH 'col1', 
 "COL2" VARCHAR2(30) PATH 'col2' 

 GROUP GRP_2 
 XMLTABLE X_AD_2 '/ABC/def/ghi/COL3' 
 columns 
 "COL3" VARCHAR2(30) PATH '/' 
 ]'
 );

I tried DBMS_METADATA package. Also I tried SQL Developer export tool. But not getting expected code in output.

oracle ddl xmlindex

share|improve this question

edited Mar 25 at 10:04

swet

asked Mar 25 at 8:34

swetswet

488

execute dbms_output.put_line(DBMS_METADATA.GET_DDL('INDEX','IDX_XML_AD','SCHEMAXX'));

 CREATE INDEX "IDX_XML_AD" ON "AD_TABLE" ("AD_COL1") 
 INDEXTYPE IS "XDB"."XMLINDEX" 

CREATE INDEX IDX_XML_AD ON AD_TABLE ( "AD_COL1" )
 INDEXTYPE IS "XDB"."XMLINDEX" PARAMETERS 
 (
 q'
 [

 PATH TABLE "X_AD_PATHTAB_FULLDATA" 

 GROUP GRP_1 
 XMLTABLE X_AD_1 '/ABC' 
 columns 
 "COL1" VARCHAR2(30) PATH 'col1', 
 "COL2" VARCHAR2(30) PATH 'col2' 

 GROUP GRP_2 
 XMLTABLE X_AD_2 '/ABC/def/ghi/COL3' 
 columns 
 "COL3" VARCHAR2(30) PATH '/' 
 ]'
 );

share|improve this question

edited Mar 25 at 10:04

swet

asked Mar 25 at 8:34

swetswet

488

share|improve this question

edited Mar 25 at 10:04

swet

asked Mar 25 at 8:34

swetswet

488

asked Mar 25 at 8:34

swetswet

488

asked Mar 25 at 8:34

swetswet

488