Skip to Main Content

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Issue with XMLType to string conversion

Hello Team,

We have below plsql Types & procedure,

CREATE OR REPLACE EDITIONABLE TYPE BASELINE_OBJECT AS OBJECT (
entry_name VARCHAR2(256),
entryid VARCHAR2(256),

                               xml\_content                 XMLTYPE  

);

/
--------------------------------------------------------
-- DDL for Type BASELINE_OBJECTS_TABLE_TYPE
--------------------------------------------------------

CREATE OR REPLACE EDITIONABLE TYPE BASELINE_OBJECTS_TABLE_TYPE
AS TABLE OF baseline_object;

We have a procedure(id IN, p_baseline_objects_table_type OUT baseline_objects_table_type) to populate the object (BASELINE_OBJECT ) and then add the object to table(BASELINE_OBJECTS_TABLE_TYPE ) and return the baseline_objects_table_type.

We are able to get the data from the procedure,

Array baselineObjs = cs.getArray(2);

for (Object obj : (Object[])baselineObjs.getArray()) {

java.sql.Struct arr = (java.sql.Struct) obj;

Object[] orset = arr.getAttributes();

try {

BaselineObject object = new BaselineObject();
object.setEntryName(orset[0]!= null ?orset[0].toString() : null);

}

So far good…

Now the issue is, we need to create a files using the contents of XMLType column.

String filePath = "C:\\TEMP\\filename.txt" ;

File aTargetFile = new File(filePath);

String encoding = "UTF-8";

InputStream returnInputStreamofFile = null;

String xmlObjectString = ( (XMLType) orset[2].getXmlContent() ).getString();

returnInputStreamofFile = new ByteArrayInputStream(xmlObjectString.getBytes(encoding));

/** file not exists */

if (!aTargetFile.exists()) {

try (OutputStream outputStream = new FileOutputStream(aTargetFile)) {

IOUtils.copy(returnInputStreamofFile, outputStream);

}

We are getting below exception at String xmlObjectString = ( (XMLType) orset[2]).getString();

It would be great if you can suggest any clue.

Thanks

PLS-00201: identifier 'DBMS_XDBRESOURCE.XDBRESOURCE' must be declared

ORA-06550: line 0, column 0:

PL/SQL: Compilation unit analysis terminated

at oracle.xml.binxml.DBBinXMLMetadataProviderImpl.getResource(DBBinXMLMetadataProviderImpl.java:450)

at oracle.xml.binxml.BinXMLDecodeReader.getNext(BinXMLDecodeReader.java:2511)

at oracle.xml.binxml.BinXMLDecodeReader.getNextEvent(BinXMLDecodeReader.java:2020)

at oracle.xml.binxml.BinXMLDecodeReader.decode(BinXMLDecodeReader.java:1829)

at oracle.xml.binxml.BinXMLDecoderImpl.decode(BinXMLDecoderImpl.java:85)

at oracle.xdb.XMLType.writeBinXMLStream(XMLType.java:4693)

at oracle.xdb.XMLType.getStringFromBinXMLStream(XMLType.java:4649)

at oracle.xdb.XMLType.getStringVal(XMLType.java:1676)

at oracle.xdb.XMLType.getString(XMLType.java:4973)

at com.oracle.doceng.acms.uimain.BaselineObjectsTest.downloadFileWithoutFileSystem(BaselineObjectsTest.java:234)

at com.oracle.doceng.acms.uimain.BaselineObjectsTest.main(BaselineObjectsTest.java:150)

Caused by: java.sql.SQLException: ORA-06550: line 2, column 7:

PLS-00201: identifier 'DBMS_XDBRESOURCE.XDBRESOURCE' must be declared

ORA-06550: line 0, column 0:

PL/SQL: Compilation unit analysis terminated

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)

at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)

at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:553)

at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269)

at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)

at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:265)

at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:86)

at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:965)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1205)

at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3666)

at oracle.jdbc.driver.T4CCallableStatement.executeInternal(T4CCallableStatement.java:1358)

at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3778)

at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4251)

at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1081)

at oracle.xml.binxml.DBBinXMLMetadataProviderImpl.getResource(DBBinXMLMetadataProviderImpl.java:442)

... 10 more

Caused by: Error : 6550, Position : 14, Sql = declare

res DBMS_XDBResource.XDBResource ;

begin

res := xdb.DBMS_XDB.getResource( abspath => :1 ) ;

:2 := DBMS_XDBResource.getContentVarchar2( res ) ;

end ;

, OriginalSql = declare

res DBMS_XDBResource.XDBResource ;

begin

res := xdb.DBMS_XDB.getResource( abspath => ? ) ;

? := DBMS_XDBResource.getContentVarchar2( res ) ;

end ;

, Error Msg = ORA-06550: line 2, column 7:

PLS-00201: identifier 'DBMS_XDBRESOURCE.XDBRESOURCE' must be declared

ORA-06550: line 0, column 0:

PL/SQL: Compilation unit analysis terminated

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513)

... 25 more

java.sql.SQLException: java.sql.SQLException: ORA-06550: line 2, column 7:

PLS-00201: identifier 'DBMS_XDBRESOURCE.XDBRESOURCE' must be declared

ORA-06550: line 0, column 0:

PL/SQL: Compilation unit analysis terminated

at oracle.xdb.XMLType.writeBinXMLStream(XMLType.java:4699)

at oracle.xdb.XMLType.getStringFromBinXMLStream(XMLType.java:4649)

at oracle.xdb.XMLType.getStringVal(XMLType.java:1676)

at oracle.xdb.XMLType.getString(XMLType.java:4973)

at com.oracle.doceng.acms.uimain.BaselineObjectsTest.downloadFileWithoutFileSystem(BaselineObjectsTest.java:234)

at com.oracle.doceng.acms.uimain.BaselineObjectsTest.main(BaselineObjectsTest.java:150)

Comments
Post Details
Added on May 12 2023
0 comments
157 views