Oracle Date literals / literals

[Steve James]

Hi, I have an Oracle stored procedure that has a date IN parameter.

How can I pass a literal to a Stored Procedure IN parameter for Oracle?

The Oracle jdbc documentation states that a date literal needs to be {d 'yyyy-mm-dd'}
http://docs.oracle.com/cd/E18283_01/jav ... m#i1005144

I guess the other option is I could pass a literal of to_date('01/01/2016','dd/mm/yyyy') but cannot establish how to pass &&{VAR} as per http://dev-docs.verj.io/ufs/doc/Databas ... c407616147

Code: Select all

fields.DATESUMBITTED.value = new Date();

Due to Ebase date format (?) results in 

Setting in parameter 4(DATESUMBITTED) = 14/03/16 11:39:47

SQL error executing stored procedure ORACLE_STOREDPROC ORA-01843: not a valid month
ORA-06512: at line 1

Any sort of formatting of the date results in a string and therefore an ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at line 1

Any ideas as to how I can pass a literal into a Stored Procedure? I want to use the core Stored Procedure resources rather than direct access from a script (at this stage).


Thanks

[Hovik]

Steve,

I must be missing something. Using a simple Oracle Stored Procedure which takes 2 date IN parameters, I am not getting the error you report.

Stored prodeure

Code: Select all

CREATE OR REPLACE PROCEDURE HOURS_BETWEEN
(start_date in DATE, end_date in DATE, no_hours out integer) AS
-- returns an integer of the number of hours between two date times rounded up to the next hour (i.e. 1.5=2)
BEGIN

  SELECT  ceil(abs((end_date - start_date) * 1440 / 60)) into  no_hours  from dual ;

END;

Importing this into Ebase creates resource with 2 date and one integer resource fields.

Ebase script

Code: Select all

fields.START_DATE.value = new Date();
resources.hours_between.exec();

With the resource debug checked, execution log shows:

Code: Select all

10:21:31.590 Info : Executing Javascript script calc_hrs_between
10:21:31.626 Debug: Start execution of command - exec: 10:21:31.626
10:21:31.626 Debug: Debug for stored procedure resource hours_between - exec statement
10:21:31.626 Debug: {call EBASE_SAMPLES.HOURS_BETWEEN(?,?,?)}
10:21:31.626 Debug: Debug for stored procedure resource hours_between
10:21:31.626 Debug: Setting in parameter 2(END_DATE) = 03/26/2016
10:21:31.626 Debug: Debug for stored procedure resource hours_between
10:21:31.626 Debug: Setting in parameter 1(START_DATE) = 03/15/2016
10:21:31.626 Debug: End execution of command - exec  : 10:21:31.626

Are you trying to pass a datestamp in the parameter?

[Steve James]

Thanks Hovik, slightly odd.

Importing this into Ebase creates resource with 2 date and one integer resource fields.

When I import my stored procedure into Ebase the DATE fields import as TIMESTAMP.

I missed that in the stored procedure definition in Ebase. I've manually overridden them and the proc works as expected.

Thanks