Stored Procedures with Date data types and Oracle

[jasun123]

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

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

[paross1]

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

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

[jasun123]

Phil -

We are running Oracle 9.2.0.6.....

Tried timestamp... same error....

[paross1]

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!)

Phil

[jasun123]

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.....

[jasun123]

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?

[paross1]

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;

Phil

[CF_Oracle]

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

[jasun123]

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#" <!---p_units--->
cfsqltype="cf_sql_varchar">

<cfprocparam type="IN" value="MVS" <!---p_officeid--->
cfsqltype="cf_sql_varchar">

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

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

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

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

<cfprocparam type="IN" value="0" <!---p_trim--->
cfsqltype="cf_sql_integer">

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

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

<cfprocparam type="IN" value="1" <!---p_max_version--->
cfsqltype="cf_sql_integer">

<cfprocresult name="ts_dat"> <!---sys_refcursor--->

</cfstoredproc>


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

[CF_Oracle]

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

[paross1]

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?

Phil

[jasun123]

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#" <!---p_units--->
cfsqltype="cf_sql_varchar">

<cfprocparam type="IN" value="MVS" <!---p_officeid--->
cfsqltype="cf_sql_varchar">

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

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

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

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

<cfprocparam type="IN" value="0" <!---p_trim--->
cfsqltype="cf_sql_numeric">

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

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

<cfprocparam type="IN" value="1" <!---p_max_version--->
cfsqltype="cf_sql_numeric">

<cfprocresult name="ts_dat1"> <!---sys_refcursor--->

</cfstoredproc>

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

[paross1]

By any chance, is this procedure overloaded?

Phil

[jasun123]

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")#" <!---p_start_time--->
cfsqltype="cf_sql_date">

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

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

[paross1]

Overloading a procedure means that you would have two or moe 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

[jasun123]

<b>As suggested</b>:

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

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

<cfprocparam type="IN" value="MVS" <!---p_officeid--->
cfsqltype="cf_sql_varchar">

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

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

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

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

<cfprocparam type="IN" value="0" <!---p_trim--->
cfsqltype="cf_sql_integer">

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

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

<cfprocparam type="IN" value="1" <!---p_max_version--->
cfsqltype="cf_sql_integer">

<cfprocresult name="ts_dat_ini"> <!---sys_refcursor--->

</cfstoredproc>
<b>Yields</b>

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"> <!---sys_refcursor--->
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

[paross1]

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