This discussion is archived
8 Replies Latest reply: Nov 15, 2012 1:42 AM by ceving RSS

How to edit a SYS.ANYDATA column?

ceving Newbie
Currently Being Moderated
I have a table with a column of the type SYS.ANYDATA. When I try to add a row with SQL Developer it starts a new window called "Edit value" with a big multi row entry. But it seems to be read only. On the bottom there are four hyper links "Load", "Download", "Set Null" and "Editor".

What do I have to do to get a simple number into the SYS.ANYDATA column?
  • 1. Re: How to edit a SYS.ANYDATA column?
    rp0428 Guru
    Currently Being Moderated
    >
    I have a table with a column of the type SYS.ANYDATA. When I try to add a row with SQL Developer it starts a new window called "Edit value" with a big multi row entry. But it seems to be read only. On the bottom there are four hyper links "Load", "Download", "Set Null" and "Editor".

    What do I have to do to get a simple number into the SYS.ANYDATA column?
    >
    The same thing you do to put a value into a column of any other datatype: provide a value of the proper datatype. In this case you need to provide an ANYDATA datatype; Oracle does not perform any implicit conversions for you like it might if you try to put a NUMBER into a VARCHAR2.
    create table anydata_test (colA number, colB anydata);
    
    insert into anydata_test values (1, anydata.ConvertNumber(5))
    See chapt 221 ANYDATA TYPE in the PL/SQL Packages and Types doc
    http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/t_anydat.htm

    If you use sql developer those options will let you load or save an xml file. You can select 'Editor', enter '3' and save it but that is likely not what you were thinking.

    Your post raises this question: Why you are using ANYDATA if you don't know what it is or how to work with it? A simple web search for 'oracle 11g anydata' produces the doc link above as the very first item.

    You should always review and become familiar with the relevant Oracle docs before using any technology.
  • 2. Re: How to edit a SYS.ANYDATA column?
    ceving Newbie
    Currently Being Moderated
    Your post raises this question: Why you are using ANYDATA if you don't know what it is or how to work with it? A simple web search for 'oracle 11g anydata' produces the doc link above as the very first item.

    You should always review and become familiar with the relevant Oracle docs before using any technology.
    LOL. You should always read the question before you are trying to answer it.

    The question was not: give me an insert statement. The question was how to edit a sys.anydata column with SQL Developer.

    And "editing" in this context means editing with the data grid, because that is what makes SQL Developer different from SQLPlus*.

    I know how to write a conversion function to use it in a view in order to make the sys.anydata visible:
    CREATE OR REPLACE FUNCTION ANYDATA_TO_VARCHAR2 
    (
      ANYVAL IN SYS.ANYDATA  
    ) RETURN VARCHAR2
    AS 
        lv_number number;
        lv_varchar2 varchar2(4000);
    BEGIN
        case anyval.gettypeName
            when 'SYS.NUMBER' then
                if (anyval.getNumber(lv_number) = dbms_types.success)
                then
                    lv_varchar2 := lv_number;
                end if;
            when 'SYS.VARCHAR2' then
                if (anyval.getVarchar2(lv_varchar2) = dbms_types.success)
                then
                    null;
                end if;
            else
                lv_varchar2 := 'SYS.ANYDATA';
        end case;
        return lv_varchar2;
    END ANYDATA_TO_VARCHAR2;
    But this is quite some work and I am wondering why a product with a 3 at the major version does not support all of its own data types. I would expect this feature in version 1.0.
  • 3. Re: How to edit a SYS.ANYDATA column?
    Gary Graham Expert
    Currently Being Moderated
    Hi,

    Searching this forum for anydata in Date Range: All, I only got hits on 8 threads, some saying it is not supported. There is a 30 month old feature request for it on the SQL Developer Exchange, but it does not seem to have received any votes/comments:
    http://sqldeveloper.oracle.com/
    https://apex.oracle.com/pls/apex/f?p=43135:7:8166052411915::NO:RP,7:P7_ID:25161

    Even the Advanced mode of New Table from the Connections view does not list anydata as an available data type.

    Finally, Oracle Bug DB has no bug or enhancement logged against the SQL Developer tool with anydata in the subject line.

    Regards,
    Gary
    SQL Developer Team
  • 4. Re: How to edit a SYS.ANYDATA column?
    rp0428 Guru
    Currently Being Moderated
    >
    Even the Advanced mode of New Table from the Connections view does not list anydata as an available data type.
    >
    Yes it does. But it is listed as 'public.anydata' which is invalid for the create table or alter table DDL that is generated.

    Trying to add a column to an existing table generates an error message.
    alter table anydata_test add (colc public.anydata)
    That generates java.sql.SQLSyntaxErrorException: ORA-00902: invalid datatype

    The statement works for both CREATE and ALTER if the public prefix is removed.
    alter table anydata_test add (colc anydata)
    I haven't researched to see what JDeveloper does but since you share their code base I would expect the same behavior with them.

    OP has a valid point in that if the functionality is not supported it doesn't seem to make sense to have it enabled. But if that is the behavior embedded in the shared code base then, in my opinion, that would be an issue for the JDeveloper team to reconcile if appropriate.
  • 5. Re: How to edit a SYS.ANYDATA column?
    rp0428 Guru
    Currently Being Moderated
    >
    You should always read the question before you are trying to answer it.
    . . .
    The question was not: give me an insert statement. The question was how to edit a sys.anydata column with SQL Developer.
    >
    I did read it - and tried to read 'between the lines' also since most posters don't provide nearly enough information to enable a targeted response. You mentioned having a problem using sql developer to edit a table, add a new row and provide a value. But the only question in the body of the thread was this
    >
    What do I have to do to get a simple number into the SYS.ANYDATA column?
    >
    So I provided a way to do that so that it might get you over the 'hump' in case you didn't have any other way to populate the column.
    >
    And "editing" in this context means editing with the data grid, because that is what makes SQL Developer different from SQLPlus*.

    But this is quite some work and I am wondering why a product with a 3 at the major version does not support all of its own data types. I would expect this feature in version 1.0.
    >
    I'm not with Oracle or part of the team but the sql developer code base is a subset of the JDeveloper code base. So the starting premise is pretty much this: whatever JDeveloper does, or doesn't do, is what sql developer will do or not do. Sql developer is leveraging the work already done.

    The ANYDATA datatype is a rough equivalent to the Variant datatype used in Windows COM programming. Products like Delphi also had significant support for it. Because there are very significant overheads involved with using it, and because knowledge of how to work with it properly is pretty limited it requires a specific use case to use ANYDATA if there are any other viable options.

    The only use I have used or seen used is in dynamic sql that needed to process general-purpose queries and cursors and extract information about the actual metadata being used. I have also used it for pipelined functions that can return data with different projections based on the parameters that are passed.

    The reason the datatype can hold data of different types is because the metadata is stored as part of the data. That means that any tool that wants to examine or modify the actual data value has to be capable of processing the metadata in order to know what type of data is actually stored and how to manipulate it.

    So, as you likely know, that would require a 'cell' editor to determine the embedded datatype from the ANYDATA metadata and then launch an editor appropriate to the actual datatype. That is certainly doable since there are editors for the standard datatypes and if they were constructed in a way to be called and managed properly a SWITCH statement could dispatch the appropriate editor.

    It is also 'doable' to be able to configure a custom editor for user-defined datatypes.

    So what you are asking about is 'doable' but the short answer is that because JDeveloper doesn't 'do it' then neither will sql developer.
  • 6. Re: How to edit a SYS.ANYDATA column?
    Gary Graham Expert
    Currently Being Moderated
    Yes it does. But it is listed as 'public.anydata'
    Interesting -- that does not show up in my Type list. I checked on 3.2.2 and 3.0.4 against Oracle EE 11.2.0.1 and XE 10g.

    Anyway, I imagine support for anydata will improve over time, but only if there is user demand for it. Thanks for investigating.

    -Gary
  • 7. Re: How to edit a SYS.ANYDATA column?
    rp0428 Guru
    Currently Being Moderated
    >
    Interesting -- that does not show up in my Type list. I checked on 3.2.2 and 3.0.4 against Oracle EE 11.2.0.1 and XE 10g.
    >
    Sorry - should have mentioned versions.

    Oracle SQL Developer (3.2.20.09) - Windows XP SP3
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production on Oracle Enterprise Linux 5

    Shows up in Edit Table dialog - Column Properties section - Datatype - Simple in the Type dropdown between NVARCHAR2 and RAW in the alphabetized list. Same place in the advanced create table dialog.

    Also (even in 3.2.20.09) in the Edit Table dialog - Column Properties section - Datatype - Complex if you select the SYS schema then you will get a whole different set of datatypes that include ANYDATA, ANYDATASET, ANYTYPE and more. Select 'Fetch the entire list' if you really want to see some of the odd types.

    You can add a SYS.ANYTYPE column to a table without a problem. So the fact that PUBLIC.ANYDATA shows up in the 'Simple' list is probably a bug.

    The complex section is where you would normally go to select object types (VARRAYs, nested tables) to use as columns.

    The complex section works the same even in sql developer 2.1.0.63 on a 10g system.
  • 8. Re: How to edit a SYS.ANYDATA column?
    ceving Newbie
    Currently Being Moderated
    Gary Graham wrote:

    Even the Advanced mode of New Table from the Connections view does not list anydata as an available data type.
    I created the table with SQL Developer Data Modeler. The name of the logical type is SYS_ANYDATA. It can be engineered into a 10gR2 relational model, which names the type SYS.ANYDATA. The DDL created from the relational model creates the table correctly. From the engineering perspective there is no reason, why it should not be supported.

    Furthermore the reason why it is not used more is not the fact that nobody needs it. Everybody needs it but most people try their luck in string serialization to fix the lack of lately typed values. This is a work around to fix the problem on the application level. But it makes the values almost unusable on the database level, because the database does not know how to encode and decode the string serializations. The type SYS.ANYDATA is the obvious choice for lately typed values instead of using VARCHAR2 and hoping that the serialization does not exceed the 4000 byte limit.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points