Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2006
    Posts
    5

    Unanswered: Dynamic object structure

    Hi,

    I'm using OCCI (although could move over to OCI if necessary) to call a stored procedure that returns an object with a specific type name, however the attributes of that object may change over time.

    My application is required to navigate through the object tree and process the attributes. All the examples that I can find use the OTT which is not really suitable for this task, as that would require recompilation each time a change is made.

    Is there a way to use the Metadata for the type to decode an object in a generic manner?

    Thanks, Alex

    Edit: Using Oracle 10g, by the way

  2. #2
    Join Date
    Aug 2006
    Posts
    5
    Is there really no way to do this directly with OCCI?

    I think that OCIObjectGetAttr should be able to do what I want, but this doesn't seem to be available via OCCI, so I'll probably have to either move over to OCI or mix APIs.

    It seems an obvious piece of missing functionality when you are able to get such a detailed runtime description of the attributes of an object in a ResultSet and yet only be able to access them if you knew the object structure at compile time.

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    you can query the table user_tab_columns anytime if you want to get the attributes of a given table (i'm assuming you're talking about tables when you say object).

    anyway, it is _strange_ to have a table change its attributes frequently.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I have no idea what you are asking about.
    >navigate through the object tree and process the attributes.
    Exactly what "object tree" are you asking about?
    OTT is a TLA, AFAIK & IMO is not common in the Oracle realm.
    Simply put, what problem (in plain English) are you trying to solve?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Aug 2006
    Posts
    5
    I'm talking about objects (user defined datatypes). OCCI lets you see a description of the objects (the datatype of each attribute, etc.), but then not actually access the values in them.

    As an example, imagine that you were writing a generic DB management application that let you execute any query and display the result. I don't think that this is currently possible with OCCI if the result is an object, without falling back to OCI.

  6. #6
    Join Date
    Aug 2006
    Posts
    5
    Quote Originally Posted by anacedent
    I have no idea what you are asking about.
    >navigate through the object tree and process the attributes.
    Exactly what "object tree" are you asking about?
    OTT is a TLA, AFAIK & IMO is not common in the Oracle realm.
    Simply put, what problem (in plain English) are you trying to solve?
    Sorry, I thought that the OTT (Object Type Translator) was more widely known. It is very common if you are developing with user defined datatypes in OCI (Oracle Call Interface) or OCCI (Oracle C++ Call Interface). It is a utility that comes with the Oracle 10g (and 9i, I believe) SDK (Software Development Kit) and reads metadata from the database and converts it to classes/structures that can be compiled.

    As they have to be compiled, if the definition of the object changes in the database (or is not known when the application is written), they are not useful. For this reason, I was hoping to use OCCI to be able to access the attributes of an object without knowing what the object looks like.

    As I'm sure you know, if you do a select statement with basic types, you can easily get the values of the columns in the result set without having to know the data types when the application is written. I was hoping that this would also be possible with user defined datatypes.

  7. #7
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    We have begun using Oracle Advanced Queueing with OCCI a few weeks ago and faced the same issue, since Oracle-structured messages in AQ are Objects. We didn't find any dynamic solution with OCCI when dealing solely with objects, so we used a workaround that may help you. The tip is to create a type that is a NESTED TABLE of your object type, then a RECORD TYPE of the same structure as the object, then a REF CURSOR TYPE returning this record type (so that you can read its definition dynamically with the Metadata class), and finally have a stored procedure that don't return the object but a REF CURSOR of the preceding type.

    For example :

    Code:
    CREATE TYPE obj_Test IS OBJECT(
        Id NUMBER(30),
        Description VARCHAR2(200),
        CONSTRUCTOR FUNCTION obj_Test(
        Id IN NUMBER,
        Description IN VARCHAR2 DEFAULT NULL) RETURN SELF AS RESULT);
    /
    
    CREATE TYPE BODY obj_Test AS
        
        CONSTRUCTOR FUNCTION obj_Test(
        Id IN NUMBER,
        Description IN VARCHAR2 DEFAULT NULL) RETURN SELF AS RESULT IS
        BEGIN
            SELF.Id := Id;
            SELF.Description := Description;
            RETURN;
        END obj_Test;
    
    END;
    /
    
    CREATE TYPE obj_Test_Table AS TABLE OF obj_Test;
    /
    
    CREATE OR REPLACE PACKAGE pkg_TestObjects_Cursors AS
        
        TYPE Rec_Test IS RECORD(
            Id NUMBER(30),
            Description VARCHAR2(200));
    
        TYPE Cur_Test IS REF CURSOR RETURN Rec_Test;
    
    END pkg_TestObjects_Cursors;
    /
    
    CREATE OR REPLACE PACKAGE pkg_TestObjects AS
    
        PROCEDURE selectTestObject(
            objTst IN Obj_Test,
            rcTestObj OUT pkg_TestObjects_Cursors.Cur_Test);
    
    END pkg_TestObjects;
    /
    
    CREATE OR REPLACE PACKAGE BODY pkg_TestObjects AS
    
        PROCEDURE selectTestObject(
            objTst IN Obj_Test,
            rcTestObj OUT pkg_TestObjects_Cursors.Cur_Test) IS
    
        objTstTbl obj_Test_Table := obj_Test_Table(objTst);
    
        BEGIN
    
            OPEN rcTestObj FOR
                SELECT Id, 
                    Description
                FROM TABLE(CAST(objTstTbl AS obj_Test_Table));
    
        END selectTestObject;
    
    END pkg_TestObjects;
    /
    Code:
    rbaraer@Ora10g> var rc REFCURSOR
    rbaraer@Ora10g> declare
      2  obj Obj_Test := Obj_Test(1, 'Description 1');
      3  begin
      4      pkg_TestObjects.selectTestObject(obj, :rc);
      5  end;
      6  /
    
    PL/SQL procedure successfully completed.
    
    rbaraer@Ora10g> PRINT rc
    
            ID
    ----------
    DESCRIPTION
    -----------------------------------------------------------------------------------------------------------------------------------
             1
    Description 1
    
    
    rbaraer@Ora10g>
    Hope it is clear enough .

    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  8. #8
    Join Date
    Aug 2006
    Posts
    5
    Thanks for the idea, but unfortunately I'm not in a position to change the way this is handled.

    I've moved over to OCI and have successfully retrieved an attribute from my object with OCIObjectGetAttr().

    This has given me an OCIType* representing the type of the attribute.

    I can retrieve a string form of this type by using OCIDescribeAny(), then getting the Param handle, then the OCI_ATTR_NAME attribute (which returns "VARCHAR2").

    This isn't very convenient for programmatic use though, and I would prefer either the OCI_TYPECODE_xx typecode or the SQLT_xx data type.

    When I request the OCI_ATTR_TYPECODE, I get "ORA-22307: operation must be on a user-defined type".

    When I request OCI_ATTR_DATA_TYPE, I get " ORA-24328: illegal attribute value".

    There is an OCITypeTypeCode() function which does just what I want, but it's not documented as it has been made obsolete by OCIDescribeAny(), so I would rather use this technique if possible.

    Could someone point me in the right direction, please? I'm confused as to why I can't get the data type or typecode in a numeric form.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •