4 Replies Latest reply: Jun 6, 2014 4:35 AM by user6549382 RSS

    Column XMLTYPE is SECURE or BASIC FILE BINARY?

    Cherif bh

      Hi all,

       

      I am working on tables having XMLTYPE columns, I need to identify tables having XMLType column and its storage type is  basicfile or secure file binary?

       

      Below an example :

       

      drop table CHB_SECURE ;

      drop table CHB_BASIC_FILE;

      CREATE TABLE CHB_SECURE_FILE

         (

         ID NUMBER,

          XMLDATA XMLTYPE

         )

      XMLTYPE COLUMN "XMLDATA" STORE AS SECUREFILE BINARY XML ;

       

       

      CREATE TABLE CHB_BASIC_FILE

         (

         ID NUMBER,

          XMLDATA XMLTYPE

         )

      XMLTYPE COLUMN "XMLDATA" STORE AS BASICFILE BINARY XML ;

       

       

      When I check the view user_tab_cols , I found that oracle create new columns with blob types,

      SELECT TABLE_NAME, COLUMN_NAME,DATA_TYPE FROM USER_TAB_COLS WHERE TABLE_NAME IN ('CHB_SECURE_FILE','CHB_BASIC_FILE');

       

      CHB_BASIC_FILEIDNUMBER
      CHB_BASIC_FILEXMLDATAXMLTYPE
      CHB_BASIC_FILESYS_NC00003$BLOB
      CHB_SECURE_FILEIDNUMBER
      CHB_SECURE_FILEXMLDATAXMLTYPE
      CHB_SECURE_FILESYS_NC00003$BLOB

       

      select  table_name,column_name,segment_name,a.securefile from user_lobs a where TABLE_NAME IN ('CHB_SECURE_FILE','CHB_BASIC_FILE');

       

      TABLE_NAMECOLUMN_NAMESEGMENT_NAMESECUREFILE
      CHB_BASIC_FILESYS_NC00003$SYS_LOB0000768291C00003$$NO
      CHB_SECURE_FILESYS_NC00003$SYS_LOB0000768288C00003$$YES

       

      In view user_lobs, I found that these new added columns, I can identify that is securefile or not.

       

      But I can not get this information for xmltype columns.

      I think there is a relation between column SYS_NC00003$ and my xmltype column XMLDATA but I can not get this information

       

      Could you please help me?

       

      Thanks,

      Cherif

        • 1. Re: Column XMLTYPE is SECURE or BASIC FILE BINARY?
          odie_63

          I think there is a relation between column SYS_NC00003$ and my xmltype column XMLDATA but I can not get this information

          Yes, there's a relation.

          The XMLType column is a virtual column that points to the hidden BLOB where the data is actually stored in binary XML format.

           

          There ought to be something more straightforward, but you can use DBMS_METADATA API to retrieve the information you need :

          SQL> select t.table_name, x.*

            2  from user_tables t

            3     , xmltable(xmlnamespaces(default 'http://xmlns.oracle.com/ku'),

            4         '/TABLE/RELATIONAL_TABLE/TABLE_PROPERTIES/COLUMN_PROPERTIES/COL_LIST/COL_LIST_ITEM[DATATYPE="XMLTYPE"]'

            5       passing xmlparse(document dbms_metadata.get_sxml('TABLE', t.table_name))

            6       columns column_name   varchar2(30) path 'NAME'

            7            , is_securefile  varchar2(5)  path 'exists(XMLTYPE_PROPERTIES/XMLTYPE_STORAGE/LOB_PROPERTIES/STORAGE_TABLE/SECUREFILE)'

            8       ) x

            9  where t.table_name in ('CHB_SECURE_FILE', 'CHB_BASIC_FILE')

          10  ;

           

          TABLE_NAME                     COLUMN_NAME                    IS_SECUREFILE

          ------------------------------ ------------------------------ -------------

          CHB_BASIC_FILE                 XMLDATA                        false

          CHB_SECURE_FILE                XMLDATA                        true

           

           

          • 2. Re: Column XMLTYPE is SECURE or BASIC FILE BINARY?
            Cherif bh

            Hi odie_63,

            Thanks for your help.

            I try to get this information oracle views.

             

            Thanks,

            Cherif.

            • 3. Re: Column XMLTYPE is SECURE or BASIC FILE BINARY?
              odie_63

              I've been digging a little...

               

              For the record, here's a more "low-level" approach tested on 11.2.0.2, but since it queries the dictionary base tables, there's no guarantee it'll work in all versions :

              SQL> select o.name  as "TABLE_NAME"

                2       , cx.name as "XMLTYPE_COLUMN"

                3       , cl.name as "BLOB_COLUMN"

                4       , decode(bitand(l.property, 2048),2048,'YES','NO') as "SECUREFILE"

                5  from sys.obj$ o

                6       join sys.col$ cx on cx.obj# = o.obj#

                7       join sys.opqtype$ opq on opq.obj# = o.obj#

                8                            and opq.intcol# = cx.intcol#

                9       join sys.col$ cl on cl.obj# = o.obj#

              10                       and cl.intcol# = opq.lobcol

              11       join sys.lob$ l on l.obj# = o.obj#

              12                      and l.intcol# = cl.intcol#

              13  where o.name in ('CHB_SECURE_FILE', 'CHB_BASIC_FILE')

              14  and cx.type# = 58

              15  and bitand(opq.flags, 4) = 4 ;

               

              TABLE_NAME                     XMLTYPE_COLUMN                 BLOB_COLUMN                    SECUREFILE

              ------------------------------ ------------------------------ ------------------------------ ----------

              CHB_BASIC_FILE                 XMLDATA                        SYS_NC00003$                   NO

              CHB_SECURE_FILE                XMLDATA                        SYS_NC00003$                   YES

               

              • 4. Re: Column XMLTYPE is SECURE or BASIC FILE BINARY?
                user6549382

                Hello,

                 

                You can check the column "COLUMN_ID" from user_tab_columns. The xmltype and the hidden clob column have the same value.

                 

                Hope it helps

                Ovidiu