2 Replies Latest reply: Apr 5, 2012 4:23 AM by Sudheendra-Oracle RSS

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

    927515
      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!