Calling Oracle stored procedure

[Guest]

The following codes executed correctly when it was run in sqlplus.

DECLARE
P_COM_ID VARCHAR2(30);
P_STATION VARCHAR2(30);
P_TYPE VARCHAR2(99);
P_YEAR NUMBER;
P_MONTH NUMBER;
P_DAY NUMBER;
P_HOUR NUMBER;
P_MINUTE NUMBER;
P_RECEIVED DATE;

BEGIN
P_COM_ID := 'XOX';
P_STATION := '4064149';
P_TYPE := 'SA';
P_YEAR := 2006;
P_MONTH := 1;
P_DAY := 2;
P_HOUR := 0;
P_MINUTE := 0;
P_RECEIVED := to_date('2006-01-02 08:01:10','yyyy-mm-dd hh:mi:ss') ;

INGEST.REINSERT_OLD_HLY_INGEST_ROW ( P_COM_ID, P_STATION, P_TYPE, P_YEAR, P_MONTH, P_DAY, P_HOUR, P_MINUTE, P_RECEIVED );
COMMIT;
END;

However, when I called the procedure in coldfusion mx7, nothing seemed to happen.
The coldfusion codes are:

<cffunction name="f_reinsertOldHlyIngest" display="f_reinsertOldHlyIngest" access="public" returntype="string" output="false">
<cfargument name="raw_com_id" required="true" type="string">
<cfargument name="raw_station" required="true" type="string">
<cfargument name="raw_struct_type" required="true" type="string">
<cfargument name="raw_Year" required="true" type="numeric">
<cfargument name="raw_Month" required="true" type="numeric">
<cfargument name="raw_Day" required="true" type="numeric">
<cfargument name="raw_Hour" required="true" type="numeric">
<cfargument name="raw_minute" required="true" type="numeric">
<cfargument name="date_time_received" required="true" type="date">

<!--- Catch any data base error that might occure --->
<!--- Declare a bunch of variables when needed --->
<!--- Catch any data base error that might occure --->
<!--- change arguments.date_time_received to string --->
<cfset date_time_received = "to_date('" & arguments.date_time_received & "','yyyy-mm-dd hh:mi:ss')">
<cftry>

<cfset DB_ERROR_MSG="">

<!--- SQL/PL statement that inserts a name record is called --->
<cfstoredproc procedure="INGEST.reinsert_old_hly_ingest_row" datasource="#this.datasource#"
username="#this.username#" password="#this.password#" returncode="no">
<cfprocparam type="in" cfsqltype="cf_sql_varchar" maxlength="30" value="#arguments.raw_com_id#" null="yes">
<cfprocparam type="in" cfsqltype="cf_sql_varchar" maxlength="30" value="#arguments.raw_station#" null="no">
<cfprocparam type="in" cfsqltype="cf_sql_varchar" maxlength="9" value="#arguments.raw_struct_type#" null="no">
<cfprocparam type="in" cfsqltype="cf_sql_numeric" value="#arguments.raw_year#" null="no">
<cfprocparam type="in" cfsqltype="cf_sql_numeric" value="#arguments.raw_month#" null="no">
<cfprocparam type="in" cfsqltype="cf_sql_numeric" value="#arguments.raw_day#" null="no">
<cfprocparam type="in" cfsqltype="cf_sql_numeric" value="#arguments.raw_hour#" null="no">
<cfprocparam type="in" cfsqltype="cf_sql_numeric" value="#arguments.raw_minute#" null="no">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" value="#preservesinglequotes(date_time_received)#" null="no">
<!--- <cfprocparam type="In" cfsqltype="CF_SQL_DATE" value="#CreateODBCDateTime(arguments.date_time_received)#" null="no"> --->
</cfstoredproc>

<cfcatch type="DATABASE">
<cfset DB_ERROR_MSG = "#CFCATCH.DETAIL#">

</cfcatch>

</cftry>
<cfif DB_ERROR_MSG neq "">

<cfset BD_ERROR_MSG = "Update to the database failed!">


<cfelse>

</cfif>
<cfreturn DB_ERROR_MSG>
</cffunction>

Any help would be much appreciated. Thanks.

[paross1]

Try using cfsqltype="CF_SQL_TIMESTAMP" for your P_RECEIVED DATE parameter, and you may want to avoid using CreateODBCDateTime() to create your "date" unless you are actually using an ODBC connection to Oracle. Perhaps CreateDateTime().

Also, your "procedure" looks more like an anonymous block that you would call directly in SQL Plus, rather than a stored PL/SQL procedure, which would be created with a CREATE OR REPLACE PROCEDURE statement... or even within a CREATE OR REPLACE PACKAGE. I suppose what I am trying to say is, how are you executing an anonymous block from within ColdFusion?

Phil

[Guest]

Thanks Phil,

The stored procedure can be called in sqlplus with appropriate logon.

In coldfusion, the stored procedure is called by a cfc function. The values of all the parameters are passed into the stored procedure via coldfusion function arguments. The value of date comes into the function is

'2006-01-02 08:01:10'.

Since the stored procedure use the date_time_received in the where clause

where date_time_received = p_date_time

I change the value of date to contain to_date before using the value.

<cfset date_time_received = "to_date('" & arguments.date_time_received & "','yyyy-mm-dd hh:mi:ss')">

Coldfusion gave me an error when it saw the to_date when cf_sql_type equal cf_sql_timestamp was used.

[paross1]

OK, I see what you did in SQL*Plus. All that yoi did to test it there was to enclosed your stored procedure call within an anonymous block so that you could set the proc parameters, execute the proc, commit the changes, then quit. It might be interesting to see what your actual PL/SQL looks like.

This
<cfset date_time_received = "to_date('" & arguments.date_time_received & "','yyyy-mm-dd hh:mi:ss')">
makes no sense.

to_date is an Oracle function, not a ColdFusion one, so how can you possibly be setting a CF variable to that value? Even if you pass this "string" to your procedure, how do you "execute" the string within your PL/SQL without using dynamic SQL, especially since P_RECEIVED is probabl still a date datatype.

You should set date_time_received to an actual date/time object instead of a string, then you should be able to use a cfsqltype of CF_SQL_TIMESTAMP.

[Guest · Accepted solution]

Thanks Phil,

We coded to_date('" & arguments.date_time_received & "','yyyy-mm-dd hh:mi:ss')" in the pl/sql procedure and simply passed the date_time_received in CF_SQL_TIMESTAMP format and the problem is solved. Thanks.

Lisa