ARRAYs as *real* objects?

   Hello,

	   I'm trying to get a "generic" way to handle ARRAYs (nested
	   tables in fact) from a table. Considering this code :


   create type attribute as object (
	  name varchar2(256),
	  attr varchar2(256)
	  );

   create type attribute_list as table of attribute;
/

   create type node as object (

	   name		varchar2(256),
	   attributes	attribute_list,
	   attributes_private	attribute_list,
	   childlist	ref node,
	   parent		ref node,
	   previous	ref node,
	   next		ref node,
	   childnum	number,
	   content		clob       
	  );

   create table arbo of node

	  nested table attributes store as attr_table
	  nested table attributes_private store as attrpriv_table;

	   This example works fine, but the problem arises when I want
	   to do some dml on nested tables "attributes" and
	   "attributes_private".

	   I must declare particular methods for *each* nested tables,
	   and I haven't found a way to manage both nested table the
	   same way, with the same code (neither with jdbc nor with pl/sql).

	   I thought that the "table_collection_expression" as
	   specified in the SQL reference can take an ARRAY as
	   argument, but if I don't set this *explicitly*, I'll get
	   syntax error in pl/sql or invalid table name with
	   jdbc. Please see the following code.

   PL/SQL Code :

   CREATE OR REPLACE PROCEDURE insertAttr(attrlist ATTRIBUTE_LIST, key CHAR, value CHAR) is    BEGIN
           INSERT INTO TABLE(attrlist) VALUES (key, value);    END;
/

   Error Code :
   Erreurs pour PROCEDURE INSERTATTR :

   LINE/COL ERROR

• ----------------------------------------------------------------- 3/2 PLS-00801: erreur interne [22016] 3/2 PL/SQL: SQL Statement ignored

   I could do something as :

   CREATE OR REPLACE PROCEDURE insertAttr(attrlist ATTRIBUTE_LIST, key CHAR, value CHAR) is    BEGIN

	   attrlist.EXTEND;
	   attrlist(attrlist.LAST) := attribute(key, value);

   but it is illegal too, because argument are passed by value et I    haven't found a way to "REFerencing" a nested table, REF    ATTRIBUTE_LISTE is simply refused by pl/sql engine.

   *This* works :

   CREATE OR REPLACE PROCEDURE insertAttr(anode REF NODE, key CHAR, value CHAR) is    BEGIN
           INSERT INTO TABLE(SELECT attributes FROM arbo a WHERE REF(a) = anode) VALUES (key, value);    END;
/

   Same problem with JDBC Code :

	   OracleConnection conn;
	   StructDescriptor structdesc;
	   ArrayDescriptor arraydesc;
	   OracleCallableStatement createNodeStatement;
	   OracleCallableStatement insertNodeStatement;
	   OracleCallableStatement createAttributeListStatement;
	   OraclePreparedStatement pstmt;
	   Statement stmt;
	   STRUCT anode;
	   REF refnode = null;
	   ARRAY anarray;

	   Class.forName("oracle.jdbc.driver.OracleDriver");
	   conn = (OracleConnection) DriverManager.getConnection("jdbc:oracle:oci8:_at_",<snipped>,<snipped>);

	   structdesc = 
	       StructDescriptor.createDescriptor("NODE", conn);

	   arraydesc =
	       ArrayDescriptor.createDescriptor("ATTRIBUTE_LIST", conn);

	   createNodeStatement = 
	       (OracleCallableStatement) conn.prepareCall("begin ? := node(NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL); end;");
	   createNodeStatement.registerOutParameter(1, Types.STRUCT, "LIVRET.NODE");

	   insertNodeStatement =
	       (OracleCallableStatement) conn.prepareCall("begin ? := insertNode(?); end;");
	   insertNodeStatement.registerOutParameter(1, Types.REF, "LIVRET.NODE");

	   createAttributeListStatement = 
	       (OracleCallableStatement) conn.prepareCall("begin ? := attribute_list(); end;");
	   createAttributeListStatement.registerOutParameter(1, Types.ARRAY, "LIVRET.ATTRIBUTE_LIST");

	   stmt = conn.createStatement();
	   stmt.execute("DELETE FROM arbo");

	   createNodeStatement.execute();
	   anode = createNodeStatement.getSTRUCT(1);

	   stmt.execute("CREATE OR REPLACE FUNCTION insertNode(anode NODE) "+
			"   RETURN REF node AS "+
			"   node_ref REF node; "+
			"BEGIN "+
			"   INSERT INTO arbo a VALUES (anode) RETURNING REF(a) INTO node_ref; "+
			"   RETURN node_ref; "+
			"END;");

	   insertNodeStatement.setSTRUCT(2, anode);
	   insertNodeStatement.execute();
	   refnode = insertNodeStatement.getREF(1);

	   pstmt =
	       (OraclePreparedStatement) conn.prepareStatement("UPDATE arbo a SET attributes = attribute_list() WHERE REF(a) = ?");
	   pstmt.setREF(1, refnode);
	   pstmt.execute();
	   pstmt.close();

	   pstmt =
	       (OraclePreparedStatement) conn.prepareStatement("SELECT attributes FROM arbo a WHERE REF(a) = ?");
	   pstmt.setREF(1, refnode);
	   rs = pstmt.executeQuery();
	   rs.next();
	   anarray = ((OracleResultSet) rs).getARRAY(1);
	   rs.close();
	   pstmt.close();



   /* This works */


	   pstmt = (OraclePreparedStatement) conn.prepareStatement("INSERT INTO TABLE(SELECT attributes FROM arbo a WHERE REF(a) = ?) VALUES (?, ?)");
	   pstmt.setREF(1, anode);
	   pstmt.setString(2, "bouh");
	   pstmt.setString(3, "haha");
	   pstmt.execute();
	   pstmt.close();



   /* This doesn't work and throws SQLException ORA-00903: Invalid table name */


	   pstmt = (OraclePreparedStatement) conn.prepareStatement("INSERT INTO TABLE(?) VALUES (?, ?)");
	   pstmt.setARRAY(1, anarray);
	   pstmt.setString(2, "blah");
	   pstmt.setString(3, "blih");
	   pstmt.execute();
	   pstmt.close();


	   createNodeStatement.close();
	   createAttributeListStatement.close();
	   stmt.close();
	   conn.close();

       }

   In conclusion, ARRAYs *aren't* real objects, or I've missed something.

[Quoted]    Many thanks for any help or references you can provide on that    topic.

   --
   François-David Collin
   Institut Curie/Section Recherche
   remove the "nospam" in adress for email. Received on Mon Jan 22 2001 - 19:50:18 CET