Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: dbms_metadata.get_ddl question - solved!
All it took was oracle to step in. Like I would have figured this out
myself. Har. Love those unpublished fixes. Maybe this can help
someone else out there now...
SR Number 5384290.992
Open Date 02-MAY-06 15:38:02
Support Identifier 3211843
Name Vince Laurent
Severity 4
Last Update 02-MAY-06 10:39:52
Product Oracle Server - Enterprise Edition
Product Version 9.2.0.4.0
Platform HP-UX PA-RISC (64-bit)
Detailed Status Modified via MetaLink
SR Reference n/a
BUG Reference n/a
Abstract
UNABLE TO GET DBMS_METADATA.GET_DDL WORKING
Resolution History
02-MAY-06 15:38:03 GMT Can you easily recover from, bypass or work around the problem? = YES Does your system or application continue normally after the problem occurs? =
YES
Are the standard features of the system or application still
available; is the
loss of service minor? = YES
### Platform and O/S version, including patch set or service pack
level? ###
HP-UX 11i
### What version and patch set level of the database are you running?
###
sapsnd is running:
sapbqa is running:
### Are you running the most recent patch set? ###
Haven't been able to get the downtime to move all the servers to
9.2.0.7
### Problem Description and its duration: ###
SQL> SELECT DBMS_METADATA.GET_DDL('INDEX','AABLG~0','SAPR3') FROM
DUAL;
ERROR:
ORA-06502: PL/SQL: numeric or value error LPX-00210: expected '<' instead of 'n' ORA-06512: at "SYS.UTL_XML", line 0 ORA-06512: at "SYS.DBMS_METADATA_INT", line 3296 ORA-06512: at "SYS.DBMS_METADATA_INT", line 4148 ORA-06512: at "SYS.DBMS_METADATA", line 458 ORA-06512: at "SYS.DBMS_METADATA", line 615 ORA-06512: at "SYS.DBMS_METADATA", line 1221 ORA-06512: at line 1
### Did the error generate a trace file? ###
No
### What was being done at time of error/any changes since this last
worked? ###
I pre-ran the initmeta.sql and the catmeta.sql on both servers.
### Error can be generated if SQL statement is run in SQL*Plus/Server
Mgr:
###
Unknown
### What is the frequency of the error? ###
Consistently
### What is the impact to your business because of this problem? ###
No impact - just trying to use the package to generate the DDL for SAP
support issues that have been popping up.
### Are you running any third-party applications? ###
SAP
### Has the third-party Vendor been contacted? ###
Does Not Apply
Contact me via : E-mail
02-MAY-06 15:42:23 GMT TAR has been assigned to an analyst -- Sending email.
02-MAY-06 16:05:12 GMT
.
Hi Vince,
Thank you for using MetaLink. We are currently reviewing/researching the situation and will update the SR / call you as soon as we have relevant information. Thank you for your patience.
Best Regards,
Bharath
Oracle Support Services
STATUS
@WIP -- Work In Progress
02-MAY-06 16:07:28 GMT
.
UPDATE
Can you refer to the Note:364214.1 and follow the workaround specified. Please let me know if you need any other clarifications on the issue.
Regards
Bharath.S
02-MAY-06 16:07:36 GMT Email Update button has been pressed
02-MAY-06 17:03:05 GMT
New info : VLAURENT : Hi,
Thanks for the quick response. When I go look for note 364214.1 I get
no hits - regardless if I use either the Quick Find or the Advanced
Search (where I put 364214.1 in the Document number).
Can you help? I must be missing something simple....
-vince
02-MAY-06 17:10:17 GMT
.
UPDATE
The Note is in unpublished.. so I am updating the SR with the contents in the Note.
In this Document
Goal
Solution
ORA-06502: PL/SQL: numeric or value error LPX-00210: expected '<' instead of 'n' ORA-06512: at "SYS.UTL_XML", line 0 ORA-06512: at "SYS.DBMS_METADATA_INT", line 3688 ORA-06512: at "SYS.DBMS_METADATA_INT", line 4544 ORA-06512: at "SYS.DBMS_METADATA", line 466 ORA-06512: at "SYS.DBMS_METADATA", line 629 ORA-06512: at "SYS.DBMS_METADATA", line 1246 ORA-06512: at line 1
Solution#1. The solution is to reload the XML API:
1. enable restricted database session
2. run:
(from $ORACLE_HOME/rdbms/admin):
catnomet.sql
rmxml.sql
to remove the xml subsystem and after that:
catxml
utlcxml.sql
prvtcxml.plb
catmet.sql
to reload it.
Please let me know if you need any other clarifications.
Regards
Bharath.S
02-MAY-06 17:10:28 GMT Email Update button has been pressed:
02-MAY-06 17:15:05 GMT New info : VLAURENT : Applies to: Oracle Server - Enterprise Edition - Version: 9.2.0.7
Guess that means I need to upgrade my databases first? Do you know if this fix will work for 9204 or 9206? If not, I will update my test database then do these steps.
Thanks
-vince
02-MAY-06 17:20:21 GMT
.
UPDATE
The Note was written when the issue was reported in 9.2.0.7.0 There is no problem applying this solution on the 9.2.0.4 or 9.2.0.6 database
Please let me know if you need any other clarifications
Regards
Bharath.S
02-MAY-06 17:20:47 GMT Email Update button has been pressed:
02-MAY-06 10:39:52 : CHANGES MADE VIA MetaLink NOT YET FORWARDED TO OUR INTERNAL SYSTEMS : New info : VLAURENT : Bingo. Worked like a charm.
SQL> SELECT DBMS_METADATA.GET_DDL('INDEX','AABLG~0','SAPR3') FROM DUAL; DBMS_METADATA.GET_DDL('INDEX','AABLG~0','SAPR3')
CREATE UNIQUE INDEX "SAPR3"."AABLG~0" ON "SAPR3"."AABLG" ("MANDT",
"BELNR", "P AGENO")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "PSAPCLUI"
Thanks! I'll close this next time around.
Vince