I’m trying to call a PL/SQL procedure which takes a Date for input. I’ve failed to get passing dates into my adapter to work.
I’ve used a map step with pub.date:getCurrentDate to get the current date as a java.util.Date. Passing this into the adapter resulted in a ClassCastException, so I changed the adapter input type from java.sql.Date to java.util.Date.
This however results in an ORA-01008: Not all variables bound. The Oracle documentation states that this involves substitution variables, and the adapter shouldn’t be using those at all.
Just to test, I modified the PL/SQL to not take in the date parameter but use sysdate instead. When there was no dates involved everything went fine. Unfortunately, the date is needed…
Maybe using pub.date:currentDate would work. This gets the date as string, not a Java object. This service accepts a pattern for the date string. Use a pattern that is right for your Oracle instance.
Hmm… As my organization has a bunch of Oracle databases, and I don’t think they all use the same date formats, that’s not necessarily the best option. It’s a workaround though.
But I thought the adapter would HAVE to support date parameters, and that I’m using it the wrong way… Would the deprecated pub.db:call or pub.db:execSQL perhaps be better? I’ve not tried these, but I’ve seen there are a lot of posts about them.
Oracle is very tricky on what dates it will accept and what format the date is in. The easiest way around this is to use the Oracle to_date function as part of the SQL call. For example the SQL Server query:
Select * from table where dateField > ‘2/13/2003 15:12:00’
In Oracle would be written
Select * from table where dateField > to_date(‘2/13/2003 15:12:00’, ‘DD-Mon-YYYY hh24:mi:ss’)
Actually I think that would be ‘dd/mm/yyyy’ and onwards… ‘dd-mon-yyyy’ expects dashes instead of slashes and textly month (like jan or feb).
That’s what I’d be doing, programming in java. And I expected the adapter to do the same thing, but obviously it’s not. I unfortunately don’t have control over the SQL generated by the adapter. Sending a string to the adapter, I suspect it would put my TO_DATE inside 's and thus avoid the conversion inside the DBMS and fail.
I can make the JDBC call in a java service created myself, if I only knew how to get the connection information stored. If I have a connection org.personell.conn:personell, how do I go about extracting this?
I’ve tried all the different ways of putting dates in the adapter now, including putting java.sql.Date, java.util.Date, String into a Date input, and putting java.sql.Timestamp, java.util.Date, String into a Timestamp input, and String into a Varchar input. Nothing works.
The kinds of pipeline objects I’ve tried to pass are both kinds. The java.util.Date, java.sql.Date and java.sql.Timestamp are objects (and depicted with a nice watch picture in the results tab), while the string is, naturally, a string.
The services I use are as follows:
pub.date:getCurrentDate - returns a java.util.Date. This will be replaced with a date input coming from a published document later.
util:utilDateToSqlDate - A very simple java service I made to convert java.util.Date to java.sql.Date.
util:utilDateToTimestamp - Ditto, to java.sql.Timestamp.
The stored procedure adapter I created, which takes in 5 varchar parameters and 1 date parameter. As mentioned in my previous post, I’ve changed the expected input type for the date parameter in the adapter as I’ve changed the objects I try to pass in.
I am also having issues with a web service which inturn calls a stored procedure written for SQL Server. This Stored Proc takes and returns date.
Do you have a JAVA code handy for util:utilDateToSqlDate and util:utilDateToTimestamp which you wrote? Can you upload it here ?
i wanna try something real quick 2morrow morning.