This discussion is archived
2 Replies Latest reply: Apr 5, 2012 2:23 AM by 423410 RSS

Bind a date with OCIBindByName for a stored procedure in C/Objective-C

927515 Newbie
Currently Being Moderated
Hello all.

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[0], "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);
     [formatter release];
...
//Call another function which will perform the OCI SQL Call
//[stringFromDate UTF8String] is for t_now
     iRet = ExecPROCEDURE_NAME(..., [stringFromDate UTF8String], sqlcmd, ...);
...
     //Inside ExecPROCEDURE_NAME
sprintf(tnow, "TO_DATE('%s', 'YYYY/MM/DD HH24:MI:SS')", t_now);
...
     rc = (int)OCIBindByName(p_sql,
                         &bndtnow,
                         p_err,
                         (text *) ":tnow",
                         strlen(":tnow"),
                         (ub1 *) tnow,
                         8,
                         SQLT_DAT,
                         (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'

Please help. Thank you so much!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points