This discussion is archived
2 Replies Latest reply: May 17, 2011 3:03 PM by gaverill RSS

Any tricks to use PL/SQL types in object attributes?

516765 Newbie
Currently Being Moderated
I guess this is a bit of a newbie-question, but I haven't been able to find any workarounds elsewhere, so please bear with me... I'm far from new to object orientation, but I'm rather new to Oracle's object features.

I was wondering if there's some trick you can use to keep references to attributes of PL/SQL types even though they are not allowed in object types (as these are "SQL", yes I do think I understand). I was thinking there might be some way you could cast them to some data type that is supported in SQL and then get them back by the reverse process when you need them in the PL/SQL inside the methods?

In the concrete case, I would like to keep a reference to a utl_smtp connection in my object. It doesn't matter that the reference would be meaningless in other sessions etc. (actually I may not even want to store the objects in any persistent table - it's the polymorphism I'm after):

CREATE OR REPLACE TYPE o_test AS OBJECT (
att1 NUMBER,
att2 sys.utl_smtp.connection
);

- which of course give me:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0     PL/SQL: Compilation unit analysis terminated
3/12     PLS-00329: schema-level type has illegal reference to
     SYS.UTL_SMTP

The problem becomes rather dull since I can't pass the connection record as a parameter to methods either.

The only workaround I could think of was to keep the connection as a global variable in a PL/SQL package and then get it from there inside the methods. Of course this can be refined using an index by table and some object unique id to support multiple objects with their separate connections. But it still seems rather clumbsy - especially given that what I was looking for was the elegance of polymorphism.

Any tricks I don't know of?

I'm working in Oracle 10gR2.

best regards,
Jakob

Edited by: schmidt on Mar 21, 2011 10:52 PM
  • 1. Re: Any tricks to use PL/SQL types in object attributes?
    gaverill Journeyer
    Currently Being Moderated
    I'm not familiar with the UTL_SMTP package or its Connection object type, but if there is both a unique attribute for a Connection and a way to instantiate a Connection object for an existing connection (without initiating a new, actual connection), you could use that identifier as your object attribute instead, and re-create the Connection instance on-the-fly in your object methods...

    Or, you could encapsulate all your connection-related behavior in your own Connection class and have your object make calls against that instead, leaving you free to implement it as you see fit (or extend it beyond SMTP)...

    Or, you could use the package-based session-caching to cache that... session attribute...

    I don't know, doesn't seem clumsy to me (then again, I've found it useful enough that I've written my own supporting SessionCache object types...)

    Gerard
  • 2. Re: Any tricks to use PL/SQL types in object attributes?
    gaverill Journeyer
    Currently Being Moderated
    The UTL_SMTP Connection record is not too complicated, and can be easily translated into SQL object types. Add a package to aid in conversion between SQL and PLSQL, and voila!
    create or replace type o_utl_tcp_connection is object (
         remote_host     VARCHAR2(255),
         remote_port     INTEGER,
         local_host     VARCHAR2(255),
         local_port     INTEGER,
         charset          VARCHAR2(30), 
         newline          VARCHAR2(2),
         tx_timeout     INTEGER,
         private_sd     INTEGER
    )
    /
    
    define     typeOf_SQL_BOOLEAN     = 'number'
    define     SQL_BOOLEAN          = '&typeOf_SQL_BOOLEAN(1)'
    define     SQL_TRUE          = 1
    define     SQL_FALSE          = 0
    
    create or replace type o_utl_smtp_connection is object (
         host          VARCHAR2(255),          -- remote host name
         port          INTEGER,          -- remote port number
         tx_timeout     INTEGER,          -- Transfer time out (in seconds) 
         private_tcp_con o_utl_tcp_connection,     -- private, for implementation use
         private_state     INTEGER,          -- private, for implementation use
    
         --
         -- Optionally, encapsulate all UTL_SMTP package calls behind object methods
         --
    
         member procedure open(
              self                    IN OUT NOCOPY     o_utl_smtp_connection,
              host                    IN          VARCHAR2, 
              port                    IN          INTEGER DEFAULT 25, 
              tx_timeout               IN          INTEGER DEFAULT NULL,
              wallet_path               IN          VARCHAR2 DEFAULT NULL,
              wallet_password               IN          VARCHAR2 DEFAULT NULL, 
              secure_connection_before_smtp     IN          &typeOf_SQL_BOOLEAN DEFAULT &SQL_FALSE
         ),
    
         --
         -- ...
         --
    
         member procedure writeData(
              self                    IN OUT NOCOPY     o_utl_smtp_connection,
              data                    IN          VARCHAR2 CHARACTER SET ANY_CS
         )
    
    )
    /
    
    create or replace type body o_utl_smtp_connection is
         member procedure open(
              self                    IN OUT NOCOPY     o_utl_smtp_connection,
              host                    IN          VARCHAR2, 
              port                    IN          INTEGER DEFAULT 25, 
              tx_timeout               IN          INTEGER DEFAULT NULL,
              wallet_path               IN          VARCHAR2 DEFAULT NULL,
              wallet_password               IN          VARCHAR2 DEFAULT NULL, 
              secure_connection_before_smtp     IN          &typeOf_SQL_BOOLEAN DEFAULT &SQL_FALSE
         )
         is
         begin
              self := SMTP_UTILS.toSqlConnection(SYS.UTL_SMTP.Open_Connection(
                        host
                   ,     port
                   ,     tx_timeout
                   ,     wallet_path
                   ,     wallet_password
                   ,     nvl(secure_connection_before_smtp = &SQL_TRUE, false)
                   ));
         end;
    
         --
         -- ...
         --
    
         member procedure writeData(
              self                    IN OUT NOCOPY     o_utl_smtp_connection,
              data                    IN          VARCHAR2 CHARACTER SET ANY_CS
         )
         is
              conn     SYS.UTL_SMTP.Connection          := SMTP_UTILS.toPlSqlConnection(self);
         begin
              begin
                   SYS.UTL_SMTP.Write_Data(conn, data);
    
                   self := SMTP_UTILS.toSqlConnection(conn);
              exception
              when others then
                   self := SMTP_UTILS.toSqlConnection(conn);
    
                   raise;
              end;
         end;
    
    end;
    /
    
    create or replace type o_test is object (
         attr1          number,
         attr2          o_utl_smtp_connection,
    
         member procedure doSomethingWithConnection
    )
    /
    
    create or replace package SMTP_UTILS
    is
         function toPLSQLConnection(aConnection in o_utl_smtp_connection)
         return SYS.UTL_SMTP.Connection;
    
         function toSQLConnection(aConnection in SYS.UTL_SMTP.Connection)
         return o_utl_smtp_connection;
    
    end;
    /
    
    create or replace package body SMTP_UTILS
    is
         function toPLSQLConnection(aConnection in o_utl_smtp_connection)
         return SYS.UTL_SMTP.Connection
         is
              result     SYS.UTL_SMTP.Connection;
         begin
              result.host                    := aConnection.host;
              result.port                    := aConnection.port;
              result.tx_timeout               := aConnection.tx_timeout;
              result.private_state               := aConnection.private_state;
    
              result.private_tcp_con.remote_host     := aConnection.private_tcp_con.remote_host;
              result.private_tcp_con.remote_port     := aConnection.private_tcp_con.remote_port;
              result.private_tcp_con.local_host     := aConnection.private_tcp_con.local_host;
              result.private_tcp_con.local_port     := aConnection.private_tcp_con.local_port;
              result.private_tcp_con.charset          := aConnection.private_tcp_con.charset;
              result.private_tcp_con.newline          := aConnection.private_tcp_con.newline;
              result.private_tcp_con.tx_timeout     := aConnection.private_tcp_con.tx_timeout;
              result.private_tcp_con.private_sd     := aConnection.private_tcp_con.private_sd;
    
              return     result;
         end;
    
         function toSQLConnection(aConnection in SYS.UTL_SMTP.Connection)
         return o_utl_smtp_connection
         is
              result     o_utl_smtp_connection;
         begin
              result.host                    := aConnection.host;
              result.port                    := aConnection.port;
              result.tx_timeout               := aConnection.tx_timeout;
              result.private_state               := aConnection.private_state;
    
              result.private_tcp_con.remote_host     := aConnection.private_tcp_con.remote_host;
              result.private_tcp_con.remote_port     := aConnection.private_tcp_con.remote_port;
              result.private_tcp_con.local_host     := aConnection.private_tcp_con.local_host;
              result.private_tcp_con.local_port     := aConnection.private_tcp_con.local_port;
              result.private_tcp_con.charset          := aConnection.private_tcp_con.charset;
              result.private_tcp_con.newline          := aConnection.private_tcp_con.newline;
              result.private_tcp_con.tx_timeout     := aConnection.private_tcp_con.tx_timeout;
              result.private_tcp_con.private_sd     := aConnection.private_tcp_con.private_sd;
    
              return     result;
         end;
    
    end;
    /
    
    create or replace type body o_test is
         member procedure doSomethingWithConnection
         is
         begin
              -- Make SMTP calls thru connection object methods
    
              self.attr2.open();
         end;
    
    end;
    /
    Hope it helps.

    Gerard

    Edited by: gaverill on May 17, 2011 3:02 PM - formatted code

Legend

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