Stored Procedures with Date data types and Oracle

Report · May 09, 2006

This should be easy.... But i keep getting the error:

[Macromedia][Oracle JDBC Driver][Oracle]ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'RETRIEVE_TS' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

And I got it worked out to where i know it is a problem with the way i am using the date data types in a stored procedure....

In the past i usually avoided calling procedures in Oracle with date as the in type.... It is always easiest to let oracle convert the string to a date.... Unfortunately now i am stuck with having a date type in the procedure call.... So the question is:

WHAT IS THE PROPER WAY TO SUBMIT DATE/TIME STAMP IN A STORED PROCEDURE?

The Oracle Procedure looks like this:

PROCEDURE retrieve_ts (
p_at_tsv_rc IN OUT sys_refcursor,
p_units IN OUT VARCHAR2,
p_officeid IN VARCHAR2,
p_timeseries_desc IN VARCHAR2,
p_start_time IN DATE,
p_end_time IN DATE,
p_timezone IN VARCHAR2 DEFAULT 'GMT',
p_trim IN NUMBER DEFAULT false_num,
p_inclusive IN NUMBER DEFAULT NULL,
p_versiondate IN DATE DEFAULT NULL,
p_max_version IN NUMBER DEFAULT true_num
)

AND the stored procedure call looks like this:

<cfset ed = Now()>
<cfset sd = #DateAdd("d",-lbt,Now())#>

<cfstoredproc datasource="CWMS"
procedure="cwms.cwms_ts.retrieve_ts"
returncode="no">
<cfprocparam type="inout" variable="unit" value="#unit#"
dbvarname="@p_units"
cfsqltype="cf_sql_varchar">

<cfprocparam type="in" value="MVS"
dbvarname="@p_officeid"
cfsqltype="cf_sql_varchar">

<cfprocparam type="in" value=#id.cwms_ts_id#
dbvarname="@p_timeseries_desc"
cfsqltype="cf_sql_varchar">

<cfprocparam type="in" value="#sd#"
dbvarname="@p_start_time"
cfsqltype="cf_sql_date">

<cfprocparam type="in" value="#ed#"
dbvarname="@p_end_time"
cfsqltype="cf_sql_date">

<cfprocparam type="in" value="#tz#"
dbvarname="@p_time_zone"
cfsqltype="cf_sql_varchar">

<cfprocparam type="in" value="0"
dbvarname="@p_trim"
cfsqltype="cf_sql_numeric">

<cfprocparam type="in" value=""
null = "yes"
dbvarname="@p_inclusive"
cfsqltype="cf_sql_numeric">

<cfprocparam type="in" value=""
null="yes"
dbvarname="@p_versiondate"
cfsqltype="cf_sql_date">

<cfprocparam type="in" value="1"
dbvarname="@p_max_version"
cfsqltype="cf_sql_numeric">

<cfprocresult name="ts_dat">

</cfstoredproc>

Text

Report · May 09, 2006

First of all, you can get rid of the dbvarname parameters, as they have been deprecated in MX and serves absolutely no purpose. You must still match up each cfprocparam with its stored procedure parameter in number, type, and order.

the dbvarname attribute behavior:...is now ignored for all drivers. ColdFusion MX uses JDBC 2.2 and does not support named parameters.

What version of Oracle are you on? There have been some issues with recent versions of Oracle and date/time verses timestamp data types (10g for sure, later 9i as well), but assuming that this isn't the case, try using cfsqltype="CF_SQL_TIMESTAMP" instead of CF_SQL_DATE for Oracle date/time data types.

Phil

Report · May 09, 2006

Phil -

We are running Oracle 9.2.0.6.....

Tried timestamp... same error....

Report · May 09, 2006

Hmmm, try moving p_at_tsv_rc IN OUT sys_refcursor to being the last parameter in your stored procedure declaration (don't forget to move it in the package spec as well as in the body!) Plus, if you are using ThinClient JDBC drivers for your Oracle datasource, you can NOT use ref cursors to return result sets.

Here are two posts that I found regarding the date/timestamp issue.

CFMX and Oracle 10G JDBC no longer retrieves timestamp with date
Oracle Date/Timestamp issue

Phil

Report · May 10, 2006

I will try moving the refference cursor to the end, but am afraid that isn't going to work either.... Let me try to clarify why i know it is a date datatype problem....

What i did was right a similar pl/sql procedure that accepts all the same variable types as the package cwms_ts.retrieve_ts with one exception.... I send the start and endtimes as varchar to the stored procedure and convert them to dates inside Oracle.... It worked fine....

Hence it is something with sending dates through the stored procedure....

I think the links you show is the right direction to try.....

Report · May 10, 2006

OK, so the solution (for most) appears to be add this to the CF Admin JVM settings:
-Doracle.jdbc.V8Compatible=true

I did this.... It still doesn't work..... used both cfsqltype = cf_sql_date and cf_sql_timestamp....

Any other ideas?

Report · May 10, 2006

Wow, I don't know what to tell you. I'm running Oracle 9.2.0.5 and MX 7 (7,0,1,116466) and have not experienced the same problems that you are having. I created a test procedure with a date/timestamp IN parameter, a VARCHAR2 OUT parameter, and a ref cursor result set. The setup is listed below, and mine works like a champ.

<<cfstoredproc procedure="test_pkg.test" datasource="myDSN" returncode="no">
<cfprocparam type="IN" cfsqltype="cf_sql_timestamp" value="15-JUN-2005">
<cfprocparam type="OUT" cfsqltype="cf_sql_varchar" variable="v_out">
<cfprocresult name="rs1">
</cfstoredproc>

PROCEDURE test(
v1_in IN date default null,
v2_out OUT varchar2,
v3_out OUT ref_cur_type)

IS
BEGIN

v2_out := TO_CHAR(v1_in, 'mm/dd/yyyy');

OPEN v3_out
FOR
SELECT *
FROM user
WHERE activation_date >= v1_in;

END test;

NOTE: I do get the same error message that you get if I change the cf_sql_type of my IN parameter to something like cf_sql_integer. Very strange!

Phil

Report · May 10, 2006

What about the number of arguments? There are 11 of them In the stored procedure and only 10 in thecalling page.

Report · May 10, 2006

Yeah.... One is a type INOUT ref cursor.... which is denoted by the cfprocresult....

By the way:

Phil - the code example with a little tweaking worked fine on my machine....

<<cfstoredproc procedure="test_pkg.test" datasource="myDSN" returncode="no">
<cfprocparam type="IN" cfsqltype="cf_sql_timestamp" value="15-JUN-2005">
<cfprocparam type="OUT" cfsqltype="cf_sql_varchar" variable="v_out">
<cfprocresult name="rs1">
</cfstoredproc>

PROCEDURE test(
v3_out OUT ref_cur_type,
v1_in IN date default null,
v2_out OUT varchar2
)

IS
BEGIN

v2_out := TO_CHAR(v1_in, 'mm/dd/yyyy');

OPEN v3_out
FOR
SELECT *
FROM user
WHERE activation_date >= v1_in;

END test;

Why do i still get this error......

Here is what it looks like now:

PROCEDURE retrieve_ts (
p_at_tsv_rc IN OUT sys_refcursor,
p_units IN OUT VARCHAR2,
p_officeid IN VARCHAR2,
p_timeseries_desc IN VARCHAR2,
p_start_time IN DATE,
p_end_time IN DATE,
p_timezone IN VARCHAR2 DEFAULT 'GMT',
p_trim IN NUMBER DEFAULT false_num,
p_inclusive IN NUMBER DEFAULT NULL,
p_versiondate IN DATE DEFAULT NULL,
p_max_version IN NUMBER DEFAULT true_num
)

<cfstoredproc datasource="CWMS"
procedure="cwms.cwms_ts.retrieve_ts"
returncode="no">

<cfprocparam type="INOUT" variable="p_units" value="#unit#" 
cfsqltype="cf_sql_varchar">

<cfprocparam type="IN" value="MVS" 
cfsqltype="cf_sql_varchar">

<cfprocparam type="IN" value=#id.cwms_ts_id# 
cfsqltype="cf_sql_varchar">

<cfprocparam type="IN" value="#sd#" 
cfsqltype="cf_sql_timestamp">

<cfprocparam type="IN" value="#ed#" 
cfsqltype="cf_sql_timestamp">

<cfprocparam type="IN" value="#tz#" 
cfsqltype="cf_sql_varchar">

<cfprocparam type="IN" value="0" 
cfsqltype="cf_sql_integer">

<cfprocparam type="IN" value="" 
null = "yes"
cfsqltype="cf_sql_numeric">

<cfprocparam type="IN" value="" 
null="yes"
cfsqltype="cf_sql_timestamp">

<cfprocparam type="IN" value="1" 
cfsqltype="cf_sql_integer">

<cfprocresult name="ts_dat"> 

</cfstoredproc>

If I truly am short a parameter, How do you specify the INOUT sys_refcursor?

Report · May 10, 2006

Jason, check this discussion on similar topic - it could help:
http://www.adobe.com/cfusion/webforums/forum/messageview.cfm?catid=6&threadid=1010746&messageid=3618...

Report · May 10, 2006

You are NOT short a parameter, as you would not include one for your refcursor since your CFPROCRESULT tag takes care of your refcursor, because that is how you are returning your result set. However, you should probably declare it as OUT only in your PL/SQL, as you would never be able to pass a refcursor parameter from ColdFusion to PL/SQL. Having said that, I modified my test procedure by making the refcursor an IN OUT, and also moving that parameter to be the first parameter in the PL/SQL declaration, and I did not get any error. So, I'm at a loss. This procedure isn't overloaded, is it?

Also, what happens if you change the CFSQL type for your NUMBER parameters to CF_SQL_DECIMAL? The LiveDocs page for the CFQUERYPARAM tag has a table with the mapping of ColdFusion SQL data types with JDBC SQL types, and it shows the Oracle NUMBER type mapped to CF_SQL_DECIMAL and CF_SQL_FLOAT.....very very interesting.

Phil

Report · Jun 08, 2006

I have finally had some time to work on this again and I am still at a loss:

Again its gotta be something with the date type:

I get this to work:

PL/SQL:

CREATE OR REPLACE PROCEDURE "RET_TS2"(
p_at_tsv_rc IN OUT sys_refcursor,
p_units IN OUT VARCHAR2,
p_officeid IN VARCHAR2,
p_timeseries_desc IN VARCHAR2,
p_start_time IN VARCHAR2,
p_end_time IN VARCHAR2,
p_timezone IN VARCHAR2 DEFAULT 'GMT',
p_trim IN NUMBER DEFAULT cwms.cwms_ts.FALSE_NUM,
p_inclusive IN NUMBER DEFAULT NULL,
p_versiondate IN DATE DEFAULT NULL,
p_max_version IN NUMBER DEFAULT cwms.cwms_ts.TRUE_NUM
)

IS

st DATE := TO_DATE(p_start_time, 'MM-DD-YYYY HH24:MI');
et DATE := TO_DATE(p_end_time, 'MM-DD-YYYY HH24:MI');

BEGIN

cwms.cwms_ts.retrieve_ts(p_at_tsv_rc, p_units, p_officeid, p_timeseries_desc, st, et, p_timezone, p_trim, p_inclusive, p_versiondate, p_max_version);

END;
/

Coldfusion:

<cfset ed = #DateFormat(Now(), "mm-dd-yyyy")#&" "&#TimeFormat(Now(), "HH:mm")#>

<cfset temp_sd = #DateAdd("d",-lbt,ed)#>
<cfset sd = #DateFormat(temp_sd, "mm-dd-yyyy")#&" "&#TimeFormat(temp_sd, "HH:mm")#>

<cfstoredproc datasource="CF" result="test1"
procedure="ret_ts2"
debug="yes">

<cfprocparam type="INOUT" variable="p_units" value="#unit#" 
cfsqltype="cf_sql_varchar">

<cfprocparam type="IN" value="MVS" 
cfsqltype="cf_sql_varchar">

<cfprocparam type="IN" value=#id.cwms_ts_id# 
cfsqltype="cf_sql_varchar">

<cfprocparam type="IN" value="#sd#" 
cfsqltype="cf_sql_date">

<cfprocparam type="IN" value="#ed#" 
cfsqltype="cf_sql_date">

<cfprocparam type="IN" value="US/Central" 
cfsqltype="cf_sql_varchar">

<cfprocparam type="IN" value="0" 
cfsqltype="cf_sql_numeric">

<cfprocparam type="IN" value="" 
null = "yes"
cfsqltype="cf_sql_numeric">

<cfprocparam type="IN" value="" 
null="yes"
cfsqltype="cf_sql_date">

<cfprocparam type="IN" value="1" 
cfsqltype="cf_sql_numeric">

<cfprocresult name="ts_dat1"> 

</cfstoredproc>

Still stuck..... Any more advice????

Report · Jun 08, 2006

By any chance, is this procedure overloaded? Also, what are the actual values of "#sd#" and "#ed#" that you are attempting to pass to your stored procedure? Are they actual date/tine objects or "character" strings representing date values? In other words, did you use one of the ColdFusion functions such as CreateDateTime() or Now() to generate an actual date/time object?

Per livedocs:

Date-time functions and queries when ODBC is not supported
Many CFML functions, including the Now, CreateDate, CreateTime, and CreateDateTime functions, return date-time objects. ColdFusion creates Open Database Connectivity (ODBC) timestamp values when it converts date-time objects to strings. As a result, you might get unexpected results when using dates with a database driver that does not support ODBC escape sequences, or when you use SQL in a query of queries.

If you use SQL to insert data into a database or in a WHERE clause to select data from a database, and the database driver does not support ODBC-formatted dates, use the DateFormat function to convert the date-time value to a valid format for the driver. This rule also applies to queries of queries.

Phil

Report · Jun 08, 2006

Ok... Overloaded - yes i think?.... I had a function pipelined called retrieve_ts... I renamed it to retrieve_ts_pipe.... Ran the stuff again... Same error....

[Macromedia][Oracle JDBC Driver][Oracle]ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'RETRIEVE_TS' ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'RETRIEVE_TS' ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'RETRIEVE_TS' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

sd and ed are created using the now and dateadd functions

ed {ts '2006-06-08 13:16:25'}
sd {ts '2006-06-04 13:16:25'}

I am using Oracle 9i and I get the same error when i do:

<cfprocparam type="IN" value="#DateFormat(DateAdd("d",-lbt,Now()), "yyyy-mm-dd")#" 
cfsqltype="cf_sql_date">

<cfprocparam type="IN" value="#DateFormat(Now(), "yyyy-mm-dd")#" 
cfsqltype="cf_sql_date">

If that isn't a valid date format for the Oracle driver, do you know where i can find it?

Report · Jun 08, 2006

Overloading a procedure means that you would have two or more procedures with the same name, but different datatypes, and would be declared more than once in a package. An example of this would be the Oracle TO_CHAR() function, which can take numbers or dates as parameters, and return VARCHAR, etc. If your procedures have different names, then they aren't overloaded.

What happens if you "hard code" some date values like below?

<cfprocparam type="IN" value="1-JUN-2006" cfsqltype="cf_sql_date">
<cfprocparam type="IN" value="8-JUN-2006" cfsqltype="cf_sql_date">

Phil

Report · Jun 08, 2006

As suggested:

<cfstoredproc datasource="CWMS"
procedure="cwms.cwms_ts.retrieve_ts"
debug="yes">

<cfprocparam type="INOUT" variable="p_units" value="#unit#" 
cfsqltype="cf_sql_varchar">

<cfprocparam type="IN" value="MVS" 
cfsqltype="cf_sql_varchar">

<cfprocparam type="IN" value=#id.cwms_ts_id# 
cfsqltype="cf_sql_varchar">

<cfprocparam type="IN" value="1-JUN-2006" 
cfsqltype="cf_sql_date">

<cfprocparam type="IN" value="8-JUN-2006" 
cfsqltype="cf_sql_date">

<cfprocparam type="IN" value="US/Central" 
cfsqltype="cf_sql_varchar">

<cfprocparam type="IN" value="0" 
cfsqltype="cf_sql_integer">

<cfprocparam type="IN" value="" 
null = "yes"
cfsqltype="cf_sql_numeric">

<cfprocparam type="IN" value="" 
null="yes"
cfsqltype="cf_sql_date">

<cfprocparam type="IN" value="1" 
cfsqltype="cf_sql_integer">

<cfprocresult name="ts_dat_ini"> 

</cfstoredproc>
Yields

Error Executing Database Query.
[Macromedia][Oracle JDBC Driver][Oracle]ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'RETRIEVE_TS' ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'RETRIEVE_TS' ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'RETRIEVE_TS' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

The error occurred in /var/apache/htdocs/cf/realtime/api/retrieve_ts_api.cfm: line 220

218 : cfsqltype="cf_sql_integer">
219 :
220 : <cfprocresult name="ts_dat_ini"> 
221 :
222 : </cfstoredproc>

--------------------------------------------------------------------------------

SQL {call cwms.cwms_ts.retrieve_ts( (param 1) , (param 2) , (param 3) , (param 4) , (param 5) , (param 6) , (param 7) , (param 8) , (param 9) , (param 10) )}
DATASOURCE CWMS
VENDORERRORCODE 6550
SQLSTATE HY000

Report · Jun 08, 2006

Well, you sure got me stumped! I use lots of Oracle stored procedures, in fact my Oracle applications use them 100% of the time and I return lots of ref cursors, and I've never had this problem! And, I can't duplicate your problem either. Funny that the test procedure that you tried back on 5/10 worked OK. I must be missing something, but sure as heck don't see it.

Sorry,
Phil