Methods of reading and writing struct and varray of racle

Oracle's struct and Varray read-write methodsQiao Qiao Computer network 2010-02-25 qqread Anon Collection of this small and medium-sized share to QQ space Renren net Bean Micro bo Tencent Weibo more in this paper, read and write Oracle Special data types struct and Varray operation methods. First, create type creation type Struct_test as OBJECT (s VARCHAR, I number); CREATE TYPE Number_varray as Varray of number (12, 2); And then.

This paper presents the operation methods of Struct and varray of special data types for Oracle reading and writing.

First, create the type

CREATE TYPE Struct_test as OBJECT (s VARCHAR, I number);

CREATE TYPE Number_varray as Varray of number (12, 2);

Then, create the table

CREATE TABLE Test (i number, obj struct_test);

reate TABLE varray_table (Col_number_array number_varray)

Then, insert the data

inserting struct data

Object[] values = new object[]{"str", new BigDecimal (123)};

　　Oracle. SQL. Structdescriptor Structdesc = Oracle. SQL. Structdescriptor.createdescriptor ("Struct_test", CN);

Oracle.sql.STRUCT oraclestruct = new Oracle.sql.STRUCT (Structdesc, CN, values);

Create a prepared statement for insertion into test

PreparedStatement PS = connection.preparestatement ("INSERT into Test VALUES (?,?)");

Set the values to insert

Ps.setint (1, 123);

Ps.setobject (2, oraclestruct);

Insert the new row

Ps.execute ();

Inserting Varray data

Create an Oracle.sql.ARRAY object to hold the values

Oracle.sql.ArrayDescriptor Arraydesc = Oracle.sql.ArrayDescriptor.createDescriptor ("Number_varray", connection);

int arrayvalues[] = {123, 234};

Oracle.sql.ARRAY ARRAY = new Oracle.sql.ARRAY (Arraydesc, CN, arrayvalues);

Create a prepared statement for insertion into varray_table

PreparedStatement PS = cn.preparestatement ("INSERT into Varray_table VALUES (?)");

Set the values to insert

((oracle.jdbc.driver.OraclePreparedStatement) PS). SetArray (1, array);

Insert the new row

Ps.execute ();

Finally, read the data

Reading struct data

ResultSet ResultSet = Stmt.executequery ("SELECT * from Test");

while (Resultset.next ()) {

Get the Struct_test value from the second column obj

Oracle.sql.STRUCT oraclestruct = (Oracle.sql.STRUCT) resultset.getobject (2);

Get the Object1 values from each row

Object[] values = Oraclestruct.getattributes ();

Get the ' the ' of Object1, which is a string

String str = (string) values[0];

BigDecimal i = (BigDecimal) values[1];

}

Reading Varray data

Create a statement

Statement stmt = Cn.createstatement ();

Select rows from varray_table

ResultSet ResultSet = Stmt.executequery ("SELECT * from varray_table");

Get the Varray values from each row

while (Resultset.next ()) {

Get the Varray value in the

Oracle.sql.ARRAY ARRAY = ((Oracle.jdbc.driver.OracleResultSet) resultSet). GetArray (1);

Get the varray elements; Values.length is the number of values in the Varray

Java.math.bigdecimal[] values = (java.math.bigdecimal[]) Array.getarray ();

}

Note: Garbled problem, depends on Nls_charset12.jar, otherwise string type of data is garbled, insert failed, read out is null

Please put the Nls_charset12.jar in the classpath.

Related articles: 100 Questions that Oracle beginners must know

Initial password After Oracle installation is complete. UPDATE AAA SET bns_snm= (SELECT bns_snm from BBB WHERE AAA. DPT_NO=BBB. DPT_NO) W here BBB. Dpt_no is not NULL; P4 Computer installation method will be SYMCJIT.DLL to Sysmcjit.old 24. What query server is not OPS. SELECT * from V$option.

===============

  Use Oracle's Varray through JDBC This code snippet demonstrates you to use Oracle ' s varray through JDBC. The code:creates varrays of different lengths. Contains a table to store data of above array type. Stores the objects in an array. Inserts them into the database table in two different ways (with SQL and Arraydescriptor). </li itxtharvested= "0" Itxtnodeid = "459" >

Import java.sql.*;
Import oracle.sql.*;
Import Oracle.jdbc.oracore.Util;
Import oracle.jdbc.*; public class Varraymanipulation {public static void main (String args[]) throws Exception {Drivermanager.registerdriver (
New Oracle.jdbc.OracleDriver ());
The sample retrieves an varray of type "String_varray" and//materializes the object as an object of type ARRAY.
A new ARRAY is then inserted into the database.
String url = "<connection url>";
Connect to the database Connection conn = drivermanager.getconnection (URL, "<user>", "<password>");
Conn.setautocommit (FALSE);
Create a Statement Statement stmt = Conn.createstatement ();
try {stmt.execute ("drop TABLE sample_varray_table"); Stmt.execute ("Drop TYPE string_varray"); \ catch (SQLException e) {//exceptions would be thrown if Table and types doesnt exist.
Ignore this} stmt.execute ("CREATE TYPE String_varray as Varray (a) of VARCHAR2 (100)"); Stmt.execute ("CREATE TABLE sample_varray_table" (Acol string_Varray) ");
Insert using SQL stmt.execute ("INSERT INTO sample_varray_table VALUES (String_varray (' Test1 ', ' Test2 ')");
ResultSet rs = stmt.executequery ("Select Acol from sample_varray_table");
Printresultset (RS);
Insert using Arraydescriptor//Create a new ARRAY object String arrayelements[] = {"Test3", "Test4"};
Arraydescriptor desc = arraydescriptor.createdescriptor ("String_varray", conn);
Array newarray = new Array (DESC, conn, arrayelements);
PreparedStatement PS = conn.preparestatement ("INSERT into sample_varray_table values (?)");
((oraclepreparedstatement) PS). SetArray (1, NewArray);
Ps.execute ();
rs = Stmt.executequery ("Select Acol from sample_varray_table");
Printresultset (RS);
Close all rs.close ();
Ps.close ();
Stmt.close ();
Conn.close (); public static void Printresultset (ResultSet rs) throws SQLException {System.out.println ("********fetch starts....****
****");
int line = 0;
while (Rs.next ()) {line++; System.out.println ("********row" +line+ ":");
Array array = ((Oracleresultset) RS). GetArray (1);
System.out.println ("Array is of type" +array.getsqltypename ());
System.out.println ("Array is of length" +array.length ());
Get Array elements string[] values = (string[]) Array.getarray ();
for (int i=0; i<values.length; i++) {System.out.println ("index" +i+ "=" +values[i]);}
System.out.println ("********fetch ends....********");
 }
}

http://www.devx.com/tips/Tip/22034

===============

Previous: How to modify a variable of type collection in Oracle. |     
Next: JavaScript How to get the number of string bytes how to use struct in a Java program to pass multiple data to Oracle's stored procedures at once. To reduce the number of connections to Oracle databases, you need to pass multiple data as variables into Oracle's stored procedures one at a time. 
The method is as follows: Step one: Define the object type.
CREATE TYPE Department_type as OBJECT (DNO number (), NAME VARCHAR2 (m), LOCATION VARCHAR2 (50));
Step two: Define an Array object of an object type.
CREATE TYPE Dept_array as TABLE of Department_type;
Step three: Define a stored procedure to insert data.
CREATE OR REPLACE PACKAGE objecttype as PROCEDURE Insert_object (d dept_array);
End ObjectType; CREATE OR REPLACE PACKAGE body objecttype as PROCEDURE Insert_object (d dept_array) as BEGIN to I in D.first.
D.last LOOP INSERT into Department_teststruct VALUES (d (i). Dno,d (i) name,d (i). location);
End LOOP; 
End Insert_object;

End ObjectType;
Step four (optional steps, which you can not do): Define a Java class to map the types in the object. 

Step five: Define a Java method to invoke the stored procedure. 
Import java.sql.Connection;
Import Java.sql.DriverManager;
Import oracle.jdbc.OracleCallableStatement;
Import Oracle.sql.ARRAY;
Import Oracle.sql.ArrayDescriptor;
Import Oracle.sql.STRUCT;
Import Oracle.sql.StructDescriptor;
public class teststruct ... {public static void main (string[] args) ...
    {sendstruct (); public static void Sendstruct () ...
        {Connection dbconn = null; Try ... {object[] So1 = ... 
            {"Ten", "Accounts", "LHR"}; Object[] SO2 = ... 
            {"", "HR", "ISB"}; 
            Oraclecallablestatement callstatement = null;
            Class.forName ("Oracle.jdbc.driver.OracleDriver");
            Dbconn = Drivermanager.getconnection ("Jdbc:oracle:thin: @ServerName:P Ort:ora", "UserName", "Password");
            Structdescriptor st = new Structdescriptor ("Department_type", dbconn);
            STRUCT S1 = new STRUCT (ST,DBCONN,SO1);
            STRUCT s2 = new STRUCT (ST,DBCONN,SO2); struct[] Deptarray = ...
            {S1,S2};
            Arraydescriptor arraydept = Arraydescriptor.createdescriptor ("Dept_array", dbconn); 
            Array deptarrayobject = new Array (arraydept, Dbconn, Deptarray); Callstatement = (Oraclecallablestatement) Dbconn.preparecall ("{Call Insert_object (?)}");
            ((oraclecallablestatement) callstatement). SetArray (1, deptarrayobject); 
            Callstatement.executeupdate ();
            Dbconn.commit (); 
        Callstatement.close (); catch (Exception e) ...
        {System.out.println (e.tostring ()); }
    }
}

Http://zhidao.baidu.com/question/49553096.html