CODEWITHSUNDEEP
javaoracle-databaseprepared-statementresultset
seckinozden
seckinozden

Reputation: 73

Prepared Statement does not retrieve the exact result of the sql

I am trying read the Oracle table information from my application. To get the table description, I execute this query on my application: 

SELECT DBMS_METADATA.GET_DDL('TABLE', 'CONTRACT_TABLE' ,'SCHEMA_NAME') FROM DUAL

Here is the code block that executes query:

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        String sql = "SELECT DBMS_METADATA.GET_DDL('TABLE', 'CONTRACT_TABLE' ,'SCHEMA_NAME') FROM DUAL";
        preparedStatement = connection.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();

        if (resultSet.next()) {
            Clob clob = resultSet.getClob(1);
            String str = clob.getSubString(1, (int)clob.length());
            return str;             
        } else {
            throw new DBPlatformException("Object not available [Schema: "
                    + schema + "].[Name: " + objectName + "]!");
        }

When I run the sql directly on the database I get this result: 

  CREATE TABLE "SCHEMA_NAME"."CONTRACT_TABLE" 
   (  "CONTRACT_ID" NUMBER, 
  "START_DATE" DATE, 
  "END_DATE" DATE
   ) PCTFREE 0 PCTUSED 0 INITRANS 1 MAXTRANS 255 
 COMPRESS FOR QUERY HIGH  NOLOGGING 
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TMP_SPACE" 
  PARTITION BY HASH ("CONTRACT_ID") 
 (PARTITION "P01" SEGMENT CREATION DEFERRED 
  TABLESPACE "TMP_SPACE" 
 COMPRESS FOR QUERY HIGH , 
 PARTITION "P02" SEGMENT CREATION DEFERRED 
  TABLESPACE "TMP_SPACE" 
 COMPRESS FOR QUERY HIGH , 
 PARTITION "P03" SEGMENT CREATION DEFERRED 
  TABLESPACE "TMP_SPACE" 
 COMPRESS FOR QUERY HIGH , 
 PARTITION "P04" SEGMENT CREATION DEFERRED 
  TABLESPACE "TMP_SPACE" 
 COMPRESS FOR QUERY HIGH , 
 PARTITION "P05" SEGMENT CREATION DEFERRED 
  TABLESPACE "TMP_SPACE" 
 COMPRESS FOR QUERY HIGH ) 
  PARALLEL 32

When I run my application, it returns the result like this: 

  CREATE TABLE "SCHEMA_NAME"."CONTRACT_TABLE" 
   (  "CONTRACT_ID" NUMBER, 
  "START_DATE" DATE, 
  "END_DATE" DATE
   ) 
  PARTITION BY HASH ("CONTRACT_ID") 
 (PARTITION "P01" , 
 PARTITION "P02" , 
 PARTITION "P03" , 
 PARTITION "P04" , 
 PARTITION "P05" )

The result that my application returns has fewer information. I need to know if table is parallel or not but, my application can not give me that information.

I am trying to figure out why the results of the query differs and How can I retrieve parallel information.

Upvotes: 5

Views: 211

Answers (1)

Evgeniy K.
Evgeniy K.

Reputation: 1137

Try execute

begin
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',TRUE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE',TRUE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',TRUE);
end;
/

before you statement in java and see if results change.

Upvotes: 1

Related Questions