I have been struggling for this for almost 1 day and have been googling a lot but still can't get the solution. :)
Anyway, here is what I want to do.
My Objective-C program needs to call a procedure PROCEDURE_NAME(p1 in varchar2, p2 in varchar2, p3 in varchar2, tnow in date)
Below are the main part of my program which calls this procedure, i.e.
sprintf(sqlcmd, "BEGIN\nPROCEDURE_NAME(:p1, :p2, :p3, :tnow);\nEND;");
//My client needs tnow to be in "YYYY/MM/DD HH24:MI:SS" format
NSDateFormatter *formatter = [[NSDateFormatter alloc] init];
[formatter setDateFormat:@"yyyy/MM/dd HH:mm:ss"];
NSString *stringFromDate = [formatter stringFromDate:[NSDate date]];
NSLog(@"stringFromDate = %@", stringFromDate);
//Call another function which will perform the OCI SQL Call
//[stringFromDate UTF8String] is for t_now
iRet = ExecPROCEDURE_NAME(..., [stringFromDate UTF8String], sqlcmd, ...);
sprintf(tnow, "TO_DATE('%s', 'YYYY/MM/DD HH24:MI:SS')", t_now);
rc = (int)OCIBindByName(p_sql,
(text *) ":tnow",
(ub1 *) tnow,
(dvoid *) 0, (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
Execution gives me ORA-01483: invalid length for DATE or NUMBER bind variable
. I tried many different ways like changing SQLT_DAT to SQLT_STR or supplying tnow with different values but I can't solve this issue.
The program works when I give tnow with something like "30-MAR-12" but my client wants 'YYYY/MM/DD HH24:MI:SS'
You will have to allocate a DATE descriptor, OCIDescriptorAlloc() with OCI_DTYPE_DATE
convert to date from text using OCIDateFromText()
bind this date as SQLT_DATE.