updating xml stored in oracle tables

REM **********************************************************************
REM Set Environment parameters
REM **********************************************************************

SET scan off;

set serveroutput on size 100000;

select ‘START TIME: ‘||to_char(sysdate,’mm/dd/yyyy hh24:mi:ss’) from dual;

DECLARE
xml_text1 clob;
xml_text2 clob;
xml_text3 clob;
xml_text4 clob;
xml_column_name VARCHAR2(50) := ‘demo.xml’;
sql_stmt1 VARCHAR2(2000);
sql_stmt2 VARCHAR2(2000);
sql_stmt3 VARCHAR2(2000);
sql_stmt4 VARCHAR2(2000);
BEGIN
xml_text1 :=’ –….. put your xml data here…

‘;

xml_text2 :=’ –….. put your xml data here…

‘;

xml_text3 :=’ –….. put your xml data here…

‘;

xml_text4 :=’ –….. put your xml data here…

‘;

sql_stmt1 := ‘update XML_TABLE set XML = :1, SYS_UPDATE_DATE = SYSDATE where OBJECT_NAME = :2 and SCHEMA = :3’;

sql_stmt2 := ‘update XML_TABLE  set XML = XML||:1, SYS_UPDATE_DATE = SYSDATE where OBJECT_NAME = :2 and SCHEMA = :3’;

sql_stmt3 := ‘update XML_TABLE set XML = XML||:1, SYS_UPDATE_DATE = SYSDATE where OBJECT_NAME = :2 and SCHEMA = :3’;

sql_stmt4 := ‘update XML_TABLE set XML = XML||:1, SYS_UPDATE_DATE = SYSDATE where OBJECT_NAME = :2 and SCHEMA = :3’;

EXECUTE IMMEDIATE sql_stmt1 USING xml_text1, object_name, schema_name;
EXECUTE IMMEDIATE sql_stmt2 USING xml_text2, object_name, schema_name;
EXECUTE IMMEDIATE sql_stmt3 USING xml_text3, object_name, schema_name;
EXECUTE IMMEDIATE sql_stmt4 USING xml_text4, object_name, schema_name;

commit;

END;
/
SET scan on
REM **********************************************************************
REM Get time
REM **********************************************************************
select ‘END TIME: ‘||to_char(sysdate,’mm/dd/yyyy hh24:mi:ss’) from dual;

Advertisements

Writing your first JDBC Application?

Ok, after a long time, here is core technical blog for all of u.
If u r wondering how to access MySQL database using JAVA program. The answer is JDBC application!

Before going to coding, u must understand certain aspects of JDBC programming.

1. You must include Jconnector in the classpath of ur machine so that Java program can interact with ur database schema. Further, you must import packages to use built-in SQL execution functions in Java.

2. You have to register the JDBC driver so that u can open a communication channel to database.

3. Then, you have open the Connection using getConnection() Method.It’s use is demonstrated later in the code.

4. After, the Connection is established, you need to execute a query. For that purpose, it requires using an object of type Statement for building and submitting an SQL
statement to the database.

5. After the execution of query, the result is stored in ResultSet. You will often use the appropriate ResultSet.getXXX() method to retrieve the data from the result set.

6. Atlast, it is always safe to close all the resources instead of relying on JVM’s garbage collection.

Here is the sample code!
//STEP 1. Import packages

import java.io.*;
import java.sql.*;
public class cleaning
{
public static void main(String[] args)
{

Connection con=null;
Statement stmt =null;
ResultSet rs=null;
try
{
//STEP 2: Register JDBC driver
Class.forName(“com.mysql.jdbc.Driver”);

//STEP 3: Open a connection con=DriverManager.getConnection(“jdbc:mysql://localhost:3306/student?user=root&password=it”);

//STEP 4: Execute a query
stmt=con.createStatement();
String sql;
sql = “SELECT RollNo, FirstName, LastName, FirstSem FROM Finalyr”;
rs = stmt.executeQuery(sql);

//STEP 5: Extract data from result set
while(rs.next()){
String rollno= rs.getString(“rollno”);
String firstname = rs.getString(“firstname”);
String lastname = rs.getString(3);
int firstsem = rs.getInt(4);
System.out.println(“Roll no.: ” + rollno +”, “+ firstname+ ” “+lastname+”,Ist Sem:”+firstsem);
}

//STEP 6: Clean−up environment
rs.close();
stmt.close();
conn.close();
}
catch(SQLException se){
//Handle errors for JDBC
se.printStackTrace();
}
catch(Exception e){
//Handle errors for Class.forName
e.printStackTrace();
}finally{
//finally block used to close resources
try
{
if(con!=null)
con.close();
}
catch(SQLException se){
se.printStackTrace();
}
}
}
}

nJoy!