TO_DATE问题与Oracle DBMS_SQL.EXECUTE函数 [英] TO_DATE issue with Oracle DBMS_SQL.EXECUTE function
问题描述
我试图使用DBMS_SQL.EXECUTE函数调用Oracle存储过程。我将PL / SQL块作为输入参数传递给使用DBMS_SQL.EXECUTE函数调用DB过程的函数。该过程有一个字符串参数，我使用TO_DATE函数以字符串格式传递当前日期。请参阅下面的关于我用来调用过程的语法。调用该过程时，传递的日期将作为DATE数据类型保存在数据库中。我面临的问题是日期的时间部分被截断，时间变为12:00 A.M，而不考虑传递给函数的时间。有人可以让我知道我做错了什么吗？
用于调用函数的SQL块：
plsql_block = BEGIN P_USER_TIME（to_date（'21 -Jul-2012 03:30:30'，'DD-MON-YYYY HH24：MI：SS'））;返回;结束;
调用过程的函数的SQL语法：
ret_int：= DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE（ret_int，plsql_block，DBMS_SQL.NATIVE）;
ret_int_execute：= DBMS_SQL.EXECUTE（ln_dbms_cur）;
PFB的oracle代码块
I我正在使用一个将date作为varchar输入的函数，并且n将它传递给另一个过程。我可以看到与日期关联的时间在某些场景中没有插入。请在下面找到我的功能
iv_plsql4：='10-08-2012 07:30:30';
ln_dbms_cur：= DBMS_SQL.OPEN_CURSOR;
iv_plsql2：= BEGIN PKG_PRADEEP.P_INSERTDATE（to_date（iv_plsql4，'DD-MM-YYYY HH24：MI：SS'））;
DBMS_OUTPUT.put_line（iv_plsql）;
DBMS_SQL.PARSE（ln_dbms_cur，iv_plsql，DBMS_SQL.NATIVE）;
ln_cur_execute：= DBMS_SQL.EXECUTE（ln_dbms_cur）;
此代码将日期插入到数据库中，但时间为12:00 A.M.
但是如果我将字符串iv_plsql2更改为下面给出的日期插入时间字段。 p>
iv_plsql2：= BEGIN PKG_PRADEEP.P_INSERTDATE（to_date（'10 -AUG-2012 07:30:30'，'DD-MM- YYYY HH24：MI：SS'））;
有人可以解释为什么会发生这种情况吗？
你所说的话似乎有矛盾，但流程并不十分清楚。您有一个从函数（未命名）调用的过程（ P_USER_TIME ？）。你的过程（至少看看它是如何声明的）会接受一个字符串参数，但是你传给它一个 date ，而不是 varchar2 值。由于我们没有实际的过程，所以我们来创建一个：
$ b
创建或替换过程p_user_time（p_time varchar2）是
l_time date;
begin
dbms_output.put_line（'Parameter p_time：'|| p_time）;
l_time：= to_date（p_time，'DD-MON-YYYY HH24：MI：SS'）;
dbms_output.put_line（'Converted l_time：'||
to_char（l_time，'DD-MON-YYYY HH24：MI：SS'））;
end;
/
如果我使用字符串 plsql_block 我得到：
$ b
alter session set nls_date_format =' DD-MON-YYYY';
set serveroutput on
exec P_USER_TIME（to_date（'21 -Jul-2012 03:30:30'，'DD-MON-YYYY HH24：MI：SS'））;
会议改变了。
参数p_time：2012年7月21日
转换的l_time：2012年7月21日00:00:00
PL / SQL过程已成功完成。
所以时间部分会丢失。您将表示日期的字符串转换为 date 对象，当它传递给过程时，它将使用默认的NLS_DATE_FORMAT掩码隐式转换回字符串，其中I已猜到可能是 DD-MON-YYYY ;所以这相当于：
$ b
exec P_USER_TIME（to_char（to_date（'21 -JUL- 2012 03:30:30'，'DD-MON-YYYY HH24：MI：SS'）））;
执行 to_char（to_date（...））看起来多余，但是因为你有一种明确的数据掩码而另一种是隐含的，所以它可能没有达到你期望的水平。
假设 P_USER_TIME 过程期待您传递的特定格式的日期/时间字符串，您应该传递字符串，而不是尝试自己转换它：
exec P_USER_TIME（'21 -Jul-2012 03:30:30'）;
参数p_time：21-JUL-2012 03:30:30
转换的l_time：2012年7月21日03:30:30
PL / SQL过程成功完成。
您还有一个函数可以动态调用过程。再次，让我们做一个：
创建或替换函数f_foo返回数字是
ln_dbms_cur数字;
ret_int编号;
plsql_block varchar2（256）;
begin
plsql_block：''BEGIN P_USER_TIME（to_date（'''21-JUL-2012 03:30:30''，''DD-MON-YYYY HH24：MI：SS''））;结束;';
ln_dbms_cur：= DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE（ln_dbms_cur，plsql_block，DBMS_SQL.NATIVE）;
ret_int：= DBMS_SQL.EXECUTE（ln_dbms_cur）;
DBMS_SQL.CLOSE_CURSOR（ln_dbms_cur）;
返回ret_int;
end;
/
var rc number;
exec：rc：= f_foo;
参数p_time：2012年7月21日
转换的l_time：2012年7月21日00:00:00
PL / SQL过程已成功完成。
所以同样的事情发生。如果将 plsql_block 的构造简化为：
plsql_block：='BEGIN P_USER_TIME（''21-JUL-2012 03:30:30''）;结束;';
然后您会得到：
参数p_time：21-JUL-2012 03:30:30
转换的l_time：2012年7月21日03:30:30
PL / SQL过程成功完成。
再次阅读这个问题，它实际上可能是一个更简单的潜在问题。
您说过'...我使用to_date函数以字符串格式传递当前日期的字符串参数'。
如果这是从字面上解释的，则表明您应该在 to_char 时使用 to_date ;
如果你真的想让当前的时间使得函数中的行变得像这样：
plsql_block：='BEGIN P_USER_TIME（to_char（sysdate，''DD-MON-YYYY HH24：MI：SS''））;结束;';
或者使用直接调用过程：
exec P_USER_TIME（to_char（sysdate，'DD-MON-YYYY HH24：MI：SS'））;
参数p_time：31-JUL-2012 09:38:43
转换的l_time：31-JUL-2012 09:38:43
PL / SQL过程成功完成。
编辑查看Java代码发布为注释
现在，您的函数似乎需要两个参数，其中之一是您要执行的块;并返回一个游标。我将再次猜测游标正在返回已插入的内容，所以我已经更改了我的虚拟过程以将日期/时间插入到表中，并使用我的函数来检索该过程。当然，如果您发布了一组完整的代码来演示您遇到的问题，那么这样做会容易得多。
$ b 创建或替换过程p_user_time（p_time varchar2）是
l_time date;
begin
dbms_output.put_line（'Parameter p_time：'|| p_time）;
l_time：= to_date（p_time，'DD-MON-YYYY HH24：MI：SS'）;
dbms_output.put_line（'Converted l_time：'||
to_char（l_time，'DD-MON-YYYY HH24：MI：SS'））;
插入cooldude值（l_time）;
end;
/
创建或替换函数f_foo（pNumber号，varchar2中的p_plsql_block）
return sys_refcursor是
ln_dbms_cur number;
ret_int编号;
plsql_block varchar2（256）;
rc sys_refcursor;
begin
ln_dbms_cur：= DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE（ln_dbms_cur，p_plsql_block，DBMS_SQL.NATIVE）;
ret_int：= DBMS_SQL.EXECUTE（ln_dbms_cur）;
DBMS_SQL.CLOSE_CURSOR（ln_dbms_cur）;
从cooldude打开选择*的rc;
return rc;
end;
/
我仍然可以从SQL * Plus中调用，没有任何问题。我可以让Java程序执行它：
$ b
import java.sql。*;
import java.text。*;
导入oracle.jdbc。*;
导入oracle.jdbc.pool.OracleDataSource;
public class Cooldude
{
public static void main（String args []）throws SQLException
{
String plSqlBlk =BEGIN P_USER_TIME（to_char（sysdate，'DD-MON-YYYY HH24：MI：SS'））; END;;
SimpleDateFormat sdf = new SimpleDateFormat（yyyy-MM-dd HH：mm：ss）;
连接conn;
OracleDataSource ds = new OracleDataSource（）;
ds.setURL（jdbc：oracle：thin：scott/oracle@127.0.0.1：1521：orcl）;
conn = ds.getConnection（）;
CallableStatement cstmt = null;
ResultSet rs = null;
String output =;
System.out.println（****** calling SP *******）;
cstmt = conn.prepareCall（{？= call f_foo（？，？）}）;
cstmt.setFetchSize（10000）;
cstmt.registerOutParameter（1，OracleTypes.CURSOR）;
cstmt.setInt（2，204149885）;
cstmt.setString（3，plSqlBlk）;
cstmt.executeQuery（）;
rs =（ResultSet）cstmt.getObject（1）;
while（rs.next（））
{
Timestamp ts = rs.getTimestamp（1）;
System.out.println（sdf.format（ts））;
}
if（conn！= null）
{
try {conn.close（）; } catch（Exception ex）{}
conn = null;
}
}
}
javac Cooldude.java&& java Cooldude
******调用SP *******
2012-08-11 09:45:07
2012-08-11 09： 46:04
2012-08-11 09:54:33
好像是精细;这有三个Java程序调用的输出。
你没有说出你为什么认为时间在你的Java代码中被截断的原因。我会更进一步的肢体......你是否将它从Java显示器上显示为 00:00:00 ;如果是这样，你使用 rs.getDate（）而不是 rs.getTimestamp ？ java.sql.Date 没有时间部分，不像 java.util.Date 。 （例如，请参阅此问题）。
I am trying to invoke an Oracle stored procedure using the DBMS_SQL.EXECUTE function. I am passing the PL/SQL block as the input argument to the function which invokes the DB procedure using the DBMS_SQL.EXECUTE function. The procedure is having a string argument which I pass the current date in string format using the TO_DATE function. See below for the syntax I used to invoke the procedure. When the procedure is invoked the date passed is saved in the database as DATE data type. The issue I am facing is the time part of the date is truncated and the time becomes 12:00 A.M irrespective of the time that is passed to the function. Can someone please let me know what I am doing something wrong?
SQL block used to invoke the function:
plsql_block =BEGIN P_USER_TIME(to_date('21-JUL-2012 03:30:30','DD-MON-YYYY HH24:MI:SS')); Return; End;
SQL syntax of the function invoking the procedure :
ret_int := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(ret_int,plsql_block,DBMS_SQL.NATIVE);
ret_int_execute := DBMS_SQL.EXECUTE(ln_dbms_cur);
PFB the oracle code block
I am using a function that takes date as varchar input and the n passes it to another procedure.I could see that the time past associated with the date is not getting inserted at some scenario's. please find below my function
iv_plsql4 :='10-08-2012 07:30:30';
ln_dbms_cur := DBMS_SQL.OPEN_CURSOR;
iv_plsql2 := BEGIN PKG_PRADEEP.P_INSERTDATE(to_date(iv_plsql4,'DD-MM-YYYY HH24:MI:SS'));
DBMS_OUTPUT.put_line(iv_plsql);
DBMS_SQL.PARSE(ln_dbms_cur,iv_plsql,DBMS_SQL.NATIVE);
ln_cur_execute := DBMS_SQL.EXECUTE(ln_dbms_cur);
This code inserts the date in to the database but the time comes as 12:00 A.M.
but if I change the string iv_plsql2 as given below the date gets inserted with the TIME field.
iv_plsql2 := BEGIN PKG_PRADEEP.P_INSERTDATE(to_date('10-AUG-2012 07:30:30','DD-MM-YYYY HH24:MI:SS'));
Can someone explain why this happens?
There seems to be a contradiction in what you've said, but the flow isn't very clear. You have a procedure (P_USER_TIME?) which is called from a function (unnamed). Your procedure (and it would be helpful to at least see how that's declared) takes a string argument, but you are passing it a date, not a varchar2 value. Since we don't have your actual procedure, let's make one up:
create or replace procedure p_user_time(p_time varchar2) is
l_time date;
begin
dbms_output.put_line('Parameter p_time: ' || p_time);
l_time := to_date(p_time, 'DD-MON-YYYY HH24:MI:SS');
dbms_output.put_line('Converted l_time: ' ||
to_char(l_time, 'DD-MON-YYYY HH24:MI:SS'));
end;
/
If I call that with the string you're passing as plsql_block I get:
alter session set nls_date_format = 'DD-MON-YYYY';
set serveroutput on
exec P_USER_TIME(to_date('21-JUL-2012 03:30:30','DD-MON-YYYY HH24:MI:SS'));
Session altered.
Parameter p_time: 21-JUL-2012
Converted l_time: 21-JUL-2012 00:00:00
PL/SQL procedure successfully completed.
So the time portion is lost. You're converting a string representing the date into a date object, and when it's passed to the procedure it's being implicitly converted back to a string using your default NLS_DATE_FORMAT mask, which I've guessed is probably DD-MON-YYYY; so this is equivalent to doing:
exec P_USER_TIME(to_char(to_date('21-JUL-2012 03:30:30','DD-MON-YYYY HH24:MI:SS')));
Doing to_char(to_date(...)) looks redundant, but because you have an explicit data mask one way and an implicit one the other, it's probably not doing what you expect.
Assuming the P_USER_TIME procedure is expecting the date/time string in the specific format you're passing, you should just pass the string, not try to convert it yourself:
exec P_USER_TIME('21-JUL-2012 03:30:30');
Parameter p_time: 21-JUL-2012 03:30:30
Converted l_time: 21-JUL-2012 03:30:30
PL/SQL procedure successfully completed.
You also have a function that's calling the procedure dynamically. Again, let's make one up:
create or replace function f_foo return number is
ln_dbms_cur number;
ret_int number;
plsql_block varchar2(256);
begin
plsql_block := 'BEGIN P_USER_TIME(to_date(''21-JUL-2012 03:30:30'',''DD-MON-YYYY HH24:MI:SS'')); END;';
ln_dbms_cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(ln_dbms_cur, plsql_block, DBMS_SQL.NATIVE);
ret_int := DBMS_SQL.EXECUTE(ln_dbms_cur);
DBMS_SQL.CLOSE_CURSOR(ln_dbms_cur);
return ret_int;
end;
/
var rc number;
exec :rc := f_foo;
Parameter p_time: 21-JUL-2012
Converted l_time: 21-JUL-2012 00:00:00
PL/SQL procedure successfully completed.
So the same thing happens. If the construction of plsql_block is simplified to:
plsql_block := 'BEGIN P_USER_TIME(''21-JUL-2012 03:30:30''); END;';
then you get:
Parameter p_time: 21-JUL-2012 03:30:30
Converted l_time: 21-JUL-2012 03:30:30
PL/SQL procedure successfully completed.
Reading the question again, it may actually be a much simpler underlying problem.
You said '... string argument to which I pass the current date in string format using the to_date function'.
If that's interpreted literally, it suggests you might just be using to_date when you should have to_char;
if you really want the current time that would make the line in the function something like:
plsql_block := 'BEGIN P_USER_TIME(to_char(sysdate, ''DD-MON-YYYY HH24:MI:SS'')); END;';
Or using the direct call to the procedure:
exec P_USER_TIME(to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
Parameter p_time: 31-JUL-2012 09:38:43
Converted l_time: 31-JUL-2012 09:38:43
PL/SQL procedure successfully completed.
Edited to look at the Java code posted as a comment
Your function now seems to take two arguments, one of which is the block you want to execute; and return a cursor. I'm going to guess (again) that the cursor is returning what's been inserted, so I've changed my dummy procedure to insert the date/time into a table, and my function to retrieve that. This would be a lot easier if you posted a complete set of code that demonstrates the problems you're seeing, of course.
create or replace procedure p_user_time(p_time varchar2) is
l_time date;
begin
dbms_output.put_line('Parameter p_time: ' || p_time);
l_time := to_date(p_time, 'DD-MON-YYYY HH24:MI:SS');
dbms_output.put_line('Converted l_time: ' ||
to_char(l_time, 'DD-MON-YYYY HH24:MI:SS'));
insert into cooldude values(l_time);
end;
/
create or replace function f_foo(pNumber number, p_plsql_block in varchar2)
return sys_refcursor is
ln_dbms_cur number;
ret_int number;
plsql_block varchar2(256);
rc sys_refcursor;
begin
ln_dbms_cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(ln_dbms_cur, p_plsql_block, DBMS_SQL.NATIVE);
ret_int := DBMS_SQL.EXECUTE(ln_dbms_cur);
DBMS_SQL.CLOSE_CURSOR(ln_dbms_cur);
open rc for select * from cooldude;
return rc;
end;
/
I can still call that from SQL*Plus with no problems. And I can have a Java program execute it:
import java.sql.*;
import java.text.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;
public class Cooldude
{
public static void main(String args[]) throws SQLException
{
String plSqlBlk = "BEGIN P_USER_TIME(to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS')); END;";
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Connection conn;
OracleDataSource ds = new OracleDataSource();
ds.setURL("jdbc:oracle:thin:scott/oracle@127.0.0.1:1521:orcl");
conn = ds.getConnection();
CallableStatement cstmt = null;
ResultSet rs = null;
String output = "";
System.out.println("******calling SP *******");
cstmt = conn.prepareCall("{? = call f_foo(?,?)}");
cstmt.setFetchSize(10000);
cstmt.registerOutParameter(1, OracleTypes.CURSOR);
cstmt.setInt(2, 204149885);
cstmt.setString(3, plSqlBlk);
cstmt.executeQuery();
rs = (ResultSet) cstmt.getObject(1);
while (rs.next())
{
Timestamp ts = rs.getTimestamp(1);
System.out.println(sdf.format(ts));
}
if ( conn != null )
{
try { conn.close(); } catch ( Exception ex ) {}
conn = null;
}
}
}
javac Cooldude.java && java Cooldude
******calling SP *******
2012-08-11 09:45:07
2012-08-11 09:46:04
2012-08-11 09:54:33
Which seems to be fine; this has the output from three calls to the Java program.
You haven't said quite why you think the time is being truncated in your Java code. I'll go even further out on a limb... are you basing that on the display from your Java showing it as 00:00:00; and if so, are you using rs.getDate() instead of rs.getTimestamp? java.sql.Date doesn't have a time portion, unlike java.util.Date. (See this question for example).
这篇关于TO_DATE问题与Oracle DBMS_SQL.EXECUTE函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
