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

      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,
                               (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'

      Please help. Thank you so much!