方法:
protected void initPreparedParamer(PreparedStatement stmt, KeyValue paramter) throws SQLException
的主要代码:
TransVO vo = (TransVO) paramter.getValue();
Connection conn=getCon(stmt);//从stmt获取链接
stmt.setInt(2, vo.getLineID());
stmt.setInt(3, vo.getDirection());
stmt.setString(4, vo.getTransTime());
stmt.setInt(5, vo.getWeightTotal());
stmt.setInt(6, vo.getVolumeTotal());
String carStr=vo.getCars();
String[] cars=carStr.split("--");
ArrayDescriptor desc = ArrayDescriptor.createDescriptor("CARLIST",conn);//CARLIST是在Oracle中的自定义类型,原来这还一定要大写...不大写会报错:java.sql.SQLException: 无效的名称模式:oracle.carlist
ARRAY array = new ARRAY(desc, conn , cars);
((OracleCallableStatement)getStmt(stmt)).setARRAY(1, array);//这里一定要把stmt转成OracleCallableStatement才能使用setARRAY方法
---------------------------------------------------------------------------------------------
不单这里麻烦这种方式在JDBC链接方式下是没有问题的。。。项目提交需要转成JNDI连接方式。。。
结果晕掉了。。。郁闷了一阵子。。。不知道大家有没有注意到getStmt(stmt)方法。。。来看看下面这段。。。更详细点
@Override
protected void initPreparedParamer(PreparedStatement stmt, KeyValue paramter) throws SQLException {
String key = paramter.getKey();
if(key.equals("insert")){
CarVO vo =(CarVO)paramter.getValue();
stmt.setString(1,vo.getCarType());
stmt.setInt(2,vo.getWeight());
stmt.setInt(3, vo.getVolume());
stmt.setInt(4, vo.getState());
}else if(key.equals("update")){
CarVO vo =(CarVO)paramter.getValue();
stmt.setString(1,vo.getCarType());
stmt.setInt(2,vo.getWeight());
stmt.setInt(3,vo.getVolume());
stmt.setInt(4, vo.getState());
stmt.setString(5,vo.getCarId());
}else if(key.equals("delete")){
logger.debug("in init delete...");
String[] carids =(String[])paramter.getValue();
logger.debug("carids length:"+carids.length);
Connection conn=getCon(stmt);//从代理获取链接...代理是什么???一会看getCon方法的代码
ArrayDescriptor desc = ArrayDescriptor.createDescriptor("CARLIST", conn);
ARRAY array = new oracle.sql.ARRAY(desc, conn , carids);
((OracleCallableStatement)getStmt(stmt)).setARRAY(1, array);
}
}
getCon方法和getStmt方法是从BaseDAO类实现过来的...省事啊...大家都要用...呵呵
------------------------------------------------------------------------------------------------------------------
两个方法的代码:
public Connection getCon(PreparedStatement stmt){//换服务器需改这个方法
Connection con;
try{
if(DBConfig.isDebug())//jdbc
con=stmt.getConnection();
else//jndi
con=((org.apache.commons.dbcp.PoolableConnection)stmt.getConnection()).getInnermostDelegate();
//其实从stmt中获得连接非常简单...stmt.getConnection就行啦...但是得到的Connection并不是Java.sql.Connection...而是PoolableConnection...所以...惨了...必须转换成org.apache.commons.dbcp.PoolableConnection才能使用
return con;
}catch(SQLException e){
e.printStackTrace();
return null;
}
}
public PreparedStatement getStmt(PreparedStatement stmt){//换服务器需改这个方法
PreparedStatement statement;//传进来的是stmt...但是返回的还是PreparedStatement...不是白做吗...非也
if(DBConfig.isDebug())//jdbc
statement=stmt;
else//jndi
statement=((org.apache.commons.dbcp.DelegatingCallableStatement)stmt).getDelegate();//结果的stmt和传进来的stmt是不同的...具体有什么不同也不知道...但是不转换就会报错...老师说不清...网上没资料
//不过要注意的是需要加载apache的dbcp包...因为要用到DelegatingCallableStatement嘛...呵呵...我的服务器是Tomcat...数据库是Oracle...所以这个就郁闷了...如果要换服务器的话...就要修改这里...还没有想到比较好的方法...
return statement;
}
————————————————————————————————————————————
这个方法在网上找了很久。。。大概花了2天时间才解决。。。呵呵。。。最后还是从一篇文章中得到的灵感。。。呵呵。。。谢谢文章的作者和热情恢复的网友们。。。谢谢网络。。。谢谢百度。。。文章如下。。。:::::::::::::::::
===========================================================================================
第一灵感来源
Hibernate 1.2.3 has built-in support for blobs. Hibernate natively maps blob columns to java.sql.Blob. However, it's sometimes useful to read the whole blob into memory and deal with it as a byte array.
One approach for doing this to create a new UserType as follows.
package mypackage;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Types;import java.sql.Blob;import cirrus.hibernate.Hibernate;import cirrus.hibernate.HibernateException;import cirrus.hibernate.UserType;public class BinaryBlobType implements UserType{public int[] sqlTypes(){return new int[] { Types.BLOB };}public Class returnedClass(){return byte[].class;}public boolean equals(Object x, Object y){return (x == y)|| (x != null&& y != null&& java.util.Arrays.equals((byte[]) x, (byte[]) y));}public Object nullSafeGet(ResultSet rs, String[] names, Object owner)throws HibernateException, SQLException{Blob blob = rs.getBlob(names[0]);return blob.getBytes(1, (int) blob.length());}public void nullSafeSet(PreparedStatement st, Object value, int index)throws HibernateException, SQLException{st.setBlob(index, Hibernate.createBlob((byte[]) value));}public Object deepCopy(Object value){if (value == null) return null;byte[] bytes = (byte[]) value;byte[] result = new byte[bytes.length];System.arraycopy(bytes, 0, result, 0, bytes.length);return result;}public boolean isMutable(){return true;}}The BinaryBlobType will convert a blob into a byte array and back again.
Here's how to use it. First, define an entity that contains a byte[] property:
public class ImageValue{private long id;private image byte[];public long getId() { return id; }public void setId(long id) { this.id = id; }public byte[] getImage() { return image; }public void setImage(byte[] image) { this.image = image; }}Then map a blob column onto the byte[] property:
<class name="ImageValue" table="IMAGE_VALUE"><id name="id/><property name="image" column="IMAGE" type="mypackage.BinaryBlobType"/></class>Notes:
1) Blobs aren't cachable. By converting the blob into a byte array, you can now cache the entity.
2) This approach reads the whole blob into memory at once.
3) The above type is known to work for reading blobs out of the db. Other usage patterns might also work.
Comments (GK)
I changed isMutable() to return true, since an array is a mutable object.
The use of setBlob() will work on some drivers, but not all. I think its more portable to use setBytes() or even setBinaryStream().
comments (db)
db's comment above was right, setBlob() didn't work on Oracle, I used setBytes().
comments (Chad Woolley)
Below is a modified nullsafeset() that i needed to use to get it to work with tomcat 4.1.27 & oracle 8/9i - the normal calls don't work through the tomcat/dbcp connection pool wrapper objects... (this caused me great pain)
pls note that the setBytes() doesn't seem to work with oracle driver & hibernate
d.birch@eclipsegroup.com.au
public void nullSafeSet(PreparedStatement st, Object value, int index)throws HibernateException, SQLException{if(st instanceof org.apache.commons.dbcp.DelegatingPreparedStatement &&((org.apache.commons.dbcp.DelegatingPreparedStatement)st).getDelegate()instanceof oracle.jdbc.OraclePreparedStatement){oracle.sql.BLOB blob = oracle.sql.BLOB.createTemporary(((org.apache.commons.dbcp.PoolableConnection)st.getConnection()).getDelegate(), false, oracle.sql.BLOB.DURATION_SESSION);blob.open(BLOB.MODE_READWRITE);OutputStream out = blob.getBinaryOutputStream();try{out.write((byte[])value);out.flush();out.close();}catch(IOException e){throw new SQLException("failed write to blob" + e.getMessage());}blob.close();((oracle.jdbc.OraclePreparedStatement)((org.apache.commons.dbcp.DelegatingPreparedStatement)st).getDelegate()).setBLOB(index, blob);}else{st.setBlob(index, Hibernate.createBlob((byte[]) value));}}//and.. note the null check, oracle drivers return a null blob...public Object nullSafeGet(ResultSet rs, String[] names, Object owner)throws HibernateException, SQLException{final Blob blob = rs.getBlob(names[0]);return blob != null?blob.getBytes(1, (int)blob.length()):null;}/ comments Vanitha
I had to use the user type to save pdfs as oraBLOBs in oracle 91 database. nullsafeSet
needed a sligh modification , or else ther was a classcastexception. Used oracle Blob instead of Hibernate Blob type and it works.
public void nullSafeSet(PreparedStatement st, Object value, int index)throws HibernateException, SQLException{oracle.sql.BLOB t_blob = oracle.sql.BLOB.createTemporary(((org.jboss.resource.adapter.jdbc.WrappedConnection) st.getConnection()).getUnderlyingConnection(),false, oracle.sql.BLOB.DURATION_SESSION);OutputStream t_out = null;t_blob.open(BLOB.MODE_READWRITE);t_out = t_blob.getBinaryOutputStream();try{t_out.write((byte[]) value);t_out.flush();t_out.close();}catch (IOException e){throw new SQLException("failed write to blob" + e.getMessage());}t_blob.close();st.setBlob(index, t_blob);}</code>
===========================================================================================
其他灵感来源。。。资源嘛。。。没人嫌多的。。。呵呵
今天在改公司管理系统的时候,因为某个功能需要使用数组类型作为IN参数调用存储过程,看了看文档发现有Varray、nested table,但Varray有个最多数量的限制,也只好用nested table了,于是引发一连串的问题。
环境:
java version "1.6.0_05"
Java(TM) SE Runtime Environment (build 1.6.0_05-b13)
Java HotSpot(TM) Client VM (build 10.0-b19, mixed mode, sharing)
apache-tomcat-6.0.16
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
本来对Java这种据说很先进的东西就懵懵懂懂,索性就真的以为他非常牛X。使用几维数组作为参数调用存储过程还不是跟Set个String一样那么简单,但其实我错了,所以我也对java很失望,他远不如想象中那么XX。
Object arrInt[] = {0,1,2,3,4,5,6};
callStmt.SetObject(1, arrInt, Types.ARRAY);
要是想像上面这样操作他就会抛个java.lang.ClassCastException: [Ljava.lang.Object; cannot be cast to oracle.sql.ARRAY,于是我知道java他不会自己转,非得人工干预。但我突然发现自己很愚蠢,我都没告诉他procedure参数的类型,即使可以转过去又有个P用,百度了一下才知道得用下面的法子。
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
Connnection conn = DBConnManager.getConnection();
callStmt = conn.prepareCall(sql);
ArrayDescriptor arrDesc = ArrayDescriptor.createDescriptor("dbms_sql.Varchar2_Table", conn);
ARRAY arr = new ARRAY(arrDesc, conn, arrInt);
执行一下,结果异常。我靠数据库里能用dbms_sql.Varchar2_Table声明,他居然 java.sql.SQLException: 无效的名称模式: DBMS_SQL.VARCHAR2_TABLE。我心想是不是得写成SYS.dbms_sql.Varchar2_Table,结果还是一样。再百度,人们说这样不行,原因...不知道,但必须得自己定义类型才可以。于是我不得不
create type numbertable as table of number;
ArrayDescriptor arrDesc = ArrayDescriptor.createDescriptor("numbertable", conn);
ARRAY arr = new ARRAY(arrDesc, conn, arrInt);
结果又来了个java.sql.SQLException: 无效的名称模式: baby.numbertable。我无语还得百度!@#¥%....N久无果!但我发别人的码的代码里这个类型都是大写,于是我也写成NUMBERTABLE。哈哈,果然不出那个异常了。但他NND又蹦个java.lang.ClassCastException: org.apache.tomcat.dbcp.dbcp.PoolingDataSource$PoolGuardConnectionWrapper cannot be cast to oracle.jdbc.OracleConnection出来。这下郁闷了,莫非从DBCP拿出来的Connection跟OracleConnection不一样。晕了,别介呀,我对java不懂!又search了半天,发现了一个UnWrapper,本以为能把这个Wrapper给干掉的,但搞了半天没搞明白。过了XX时间,不知道是在哪国网站上看到有人
ArrayDescriptor arrDesc = ArrayDescriptor.createDescriptor("NUMBERTABLE", ((DelegatingConnection)conn).getDelegate()));
他们用着都好用,到我这((DelegatingConnection)conn).getDelegate()出来的Connection是个null,很郁闷。后来又看到
public static Connection getNativeConnection(Connection con) throws SQLException {
if (con instanceof DelegatingConnection) {
Connection nativeCon = ((DelegatingConnection) con).getInnermostDelegate();
// For some reason, the innermost delegate can be null: not for a
// Statement's Connection but for the Connection handle returned by the pool.
// We'll fall back to the MetaData's Connection in this case, which is
// a native unwrapped Connection with Commons DBCP 1.1.
return (nativeCon != null ? nativeCon : con.getMetaData().getConnection());
}
return con;
}
可((DelegatingConnection) con).getInnermostDelegate();依然是null但con.getMetaData().getConnection());得到了一个OracleConnection,debug时看着eclipse variables的窗口心中一阵暗喜,该OK了吧!
哎,事实上最近一段时间总是事与愿违,执行-又异常了!郁闷,一次比一次郁闷,一次比一次怪异!
java.lang.ClassCastException: oracle.jdbc.driver.OracleConnection cannot be cast to oracle.jdbc.OracleConnection 由于字符串太长搜都搜不到,想了好久,尝试着各种各样的方法!终于有一个次把tomcat/lib目录classes12.jar删掉,没有异常,一切OK!但后来把classes12.jar又仍进去了,还正常的,代码没有一点变化!很是郁闷,但既然问题没了,也就懒得看了!
最后的代码:
-----------------------------------------------------------------------------------
public static Connection getConnection() {
Connection con = null;
try {
Context ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup("java:comp/env/jdbc/yoyo");
con = ds.getConnection();
} catch (Exception e) {
System.out.println("**** error DataSource");
}
return con;
}
-----------------------------------------------------------------------------------
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import org.apache.tomcat.dbcp.dbcp.DelegatingConnection;
public class BussinessLog {
public static ArrayList<Comparable> CancelLog(String sLoginUser, Object[] arrLogID)
{
ArrayList<Comparable> arrList = new ArrayList<Comparable>();
Connection conn = null;
CallableStatement callStmt = null;
String sql = null;
ArrayDescriptor arrDesc = null;
try
{
conn = DbConnectionManager.getConnection();
sql = "{call P_CanceltLog(?,?,?,?)}";
callStmt = conn.prepareCall(sql);
arrDesc = ArrayDescriptor.createDescriptor("NUMBERTABLE", getNativeConnection(conn));
ARRAY arr = new ARRAY(arrDesc, getNativeConnection(conn), arrLogID);
callStmt.setString(1, sLoginUser);
callStmt.setObject(2, arr, Types.ARRAY);
callStmt.registerOutParameter(3, Types.VARCHAR);
callStmt.registerOutParameter(4, Types.INTEGER);
callStmt.execute();
arrList.add(callStmt.getInt(4));
arrList.add(callStmt.getString(3));
return arrList;
} catch (Exception e) {
System.out.println(e.toString());
} finally {
DbAction.clear(conn, callStmt);
}
return arrList;
}
public static Connection getNativeConnection(Connection con) throws SQLException {
if (con instanceof DelegatingConnection) {
Connection nativeCon = ((DelegatingConnection) con).getInnermostDelegate();
// For some reason, the innermost delegate can be null: not for a
// Statement's Connection but for the Connection handle returned by the pool.
// We'll fall back to the MetaData's Connection in this case, which is
// a native unwrapped Connection with Commons DBCP 1.1.
return (nativeCon != null ? nativeCon : con.getMetaData().getConnection());
}
return con;
}
}
-----------------------------------------------------------------------------------
A.在这之前我还下载了最新的commons-dbcp-1.2.2.jar,但使用里面DelegatingConnection时,con instanceof DelegatingConnection是false,看来tomcat里出来的Connection就的配Tomcat\lib\tomcat-dbcp.jar里的DelegatingConnection,还真是什么枪打什么鸟。
B.偶尔发现((DelegatingConnection) con).getInnermostDelegate();之所以返回null是因为tomcat里Context.Resource的accessToUnderlyingConnectionAllowed参数默认为false所致,被设置成true之后是可以取到OracleConnection的.
哎,技术烂,没事也就只能发发牢骚了!
创建于:2008-05-24 04:49:08,修改于:2008-05-24 05:06:24,已浏览270次,有评论5条
网友评论
网友:Experiencing this problem 时间:2008-06-12 19:15:48 IP地址:212.44.43.★
Hi Aowken,
Hopefully you can speak English. I'm sorry but I don't understand Chinese.
For what I can see of your blog entry, you are describing the problem I've got.
I'm trying to get to the embeded Oracle connection from the connection given by the DBCP pool in Tomcat 5.5 and I've used your example code here, but I'm getting the error: con instanceof DelegatingConnection: false
I tried to use the tomcat-dbcp.jar instead of commons-dbcp-1.2.2.jar, but with no luck.
Unfortunately I cannot understand your comments in Chinese. Do you think you could advise me?
Thanks a lot,
Monica
网友:aowken 时间:2008-06-13 05:12:52 IP地址:221.13.5.★
Hi Monica, I don't speak English well, but i got you.
You can't use commons-dbcp-1.2.2.jar, must be tomcat-dbcp.jar.
I downloaded Tomcat 5.5.26 and tried.
Environment: Windows XP sp2, MyEclipse 5.5.1, Tomcat 5.5.26, Java 1.5, Oracle 9.2.0.1.0
1.Create new web project in MyEclipse
2.Tomcat
Placed ojdbc14.jar/nls_charset12.zip/classes12.jar to D:\apache-tomcat-5.5.26\common\lib
server.xml----------------------------------------------------------------------------
<Context path="/demo" docBase="F:\Workspace\Eclipse\demo\WebRoot" debug="5" reloadable="true" >
<Resource name="jdbc/demo" auth="Container" type="javax.sql.DataSource"
maxActive="100" maxIdle="30" maxWait="10000"
username="system" password="system"
driverClassName="oracle.jdbc.driver.OracleDriver"
url="jdbc:oracle:thin:@127.0.0.1:1521:ora92"/>
</Context>
3.Project
WEB-INF/web.xml ----------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.4"
xmlns="http://java.sun.com/xml/ns/j2ee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee
http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
<resource-ref>
<description>DB Connection</description>
<res-ref-name>jdbc/demo</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
<welcome-file-list>
<welcome-file>index.jsp</welcome-file>
</welcome-file-list>
</web-app>
Code---------------------------------------------------------------------------------
package com.demo.database;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import org.apache.tomcat.dbcp.dbcp.DelegatingConnection;
public class dao {
public String GetCurrentTime()
{
Connection conn = null;
CallableStatement callStmt = null;
String sql = null;
ArrayDescriptor arrDesc = null;
String CurrentTime = null;
Object[] arrObj = {0,1,2,3,4,5};
try
{
Context ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup("java:comp/env/jdbc/demo");
conn = ds.getConnection();
sql = "{call SP_DEMO(?,?)}";
callStmt = conn.prepareCall(sql);
arrDesc = ArrayDescriptor.createDescriptor("NUMBERTABLE", getNativeConnection(conn));
ARRAY arr = new ARRAY(arrDesc, getNativeConnection(conn), arrObj);
callStmt.setObject(1, arr, Types.ARRAY);
callStmt.registerOutParameter(2, Types.VARCHAR);
callStmt.execute();
CurrentTime = callStmt.getString(2);
callStmt.close();
conn.close();
} catch (Exception e) {
System.out.println(e.toString());
}
return CurrentTime;
}
public Connection getNativeConnection(Connection con) throws SQLException {
if (con instanceof DelegatingConnection) {
Connection nativeCon = ((DelegatingConnection) con).getInnermostDelegate();
// For some reason, the innermost delegate can be null: not for a
// Statement's Connection but for the Connection handle returned by the pool.
// We'll fall back to the MetaData's Connection in this case, which is
// a native unwrapped Connection with Commons DBCP 1.1.
return (nativeCon != null ? nativeCon : con.getMetaData().getConnection());
}
return con;
}
}
index.jsp----------------------------------------------------------------------------
<%@ page language="java" pageEncoding="utf-8"%>
<jsp:useBean id="demo" class="com.demo.database.dao" scope="page" />
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>My JSP 'index.jsp' starting page</title>
</head>
<body>
This is my JSP page. <br>
Current time : <%=demo.GetCurrentTime() %>
</body>
</html>
Java Build Path----------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<classpath>
<classpathentry kind="src" path="src"/>
<classpathentry kind="con" path="org.eclipse.jdt.launching.JRE_CONTAINER"/>
<classpathentry kind="con" path="com.genuitec.eclipse.j2eedt.core.J2EE14_CONTAINER"/>
<classpathentry kind="lib" path="D:/apache-tomcat-5.5.26/common/lib/naming-factory-dbcp.jar"/>
<classpathentry kind="lib" path="D:/apache-tomcat-5.5.26/common/lib/servlet-api.jar"/>
<classpathentry kind="lib" path="D:/apache-tomcat-5.5.26/common/lib/commons-el.jar"/>
<classpathentry kind="lib" path="D:/apache-tomcat-5.5.26/common/lib/jasper-compiler.jar"/>
<classpathentry kind="lib" path="D:/apache-tomcat-5.5.26/common/lib/jasper-compiler-jdt.jar"/>
<classpathentry kind="lib" path="D:/apache-tomcat-5.5.26/common/lib/jasper-runtime.jar"/>
<classpathentry kind="lib" path="D:/apache-tomcat-5.5.26/common/lib/jsp-api.jar"/>
<classpathentry kind="lib" path="D:/apache-tomcat-5.5.26/common/lib/naming-factory.jar"/>
<classpathentry kind="lib" path="D:/apache-tomcat-5.5.26/common/lib/naming-resources.jar"/>
<classpathentry kind="lib" path="D:/apache-tomcat-5.5.26/common/lib/classes12.jar"/>
<classpathentry kind="lib" path="D:/apache-tomcat-5.5.26/common/lib/nls_charset12.zip"/>
<classpathentry kind="lib" path="D:/apache-tomcat-5.5.26/common/lib/ojdbc14.jar"/>
<classpathentry kind="output" path="WebRoot/WEB-INF/classes"/>
</classpath>
=================================================================================================
Hopefully can help you. Good Luck!!!
网友:aowken 时间:2008-06-13 05:15:07 IP地址:221.13.5.★
Oracle -------------------------------------------------------------------------------------
create or replace type NumberTable as table of number;
create table demo
(
n number
);
create or replace procedure SP_DEMO
(
v_IDTable NUMBERTABLE,
v_msg out varchar2
)
is
begin
for i in v_IDTable.first..v_IDTable.Last loop
insert into demo values(i);
end loop;
v_msg := to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
commit;
return;
end SP_DEMO;