Need help executing Oracle insert procedure with PHP

Question

I'm trying to execute a stored procedure in PHP that inserts data into a table and keep getting the following error message.

oci_execute() [function.oci-execute]: ORA-01843: not a valid month ORA-06512: at line 1

I've ran my insert query by itself, and don't receive this error. Here is the procedure code.

CREATE OR REPLACE PACKAGE trans_data AS 
  PROCEDURE INSERT_TRANSACTION_INFO(
    var_FName IN TRANSACTION.FIRST_NAME%type, 
    var_LName IN TRANSACTION.LAST_NAME%type, 
    var_DOB IN TRANSACTION.DOB%type, 
    var_InvoiceDate IN TRANSACTION.INVOICE_DATE%type, 
    var_ServiceCode IN TRANSACTION.SERVICE_CODE%type,
    var_BilledAmt IN TRANSACTION.BILLED_AMT%type,
    var_SSN IN TRANSACTION.SSN%type,
    var_ServiceDate IN TRANSACTION.SERVICE_DATE%type,
    var_VendorCode IN TRANSACTION.VENDOR_CODE%type,
    var_TransStatus IN TRANSACTION.TRANS_STATUS%type,
    var_Comp IN TRANSACTION.COMPANY%type,
    var_State IN TRANSACTION.STATE%type,
    var_Return OUT VARCHAR2
  );
END trans_data;

CREATE OR REPLACE PACKAGE BODY trans_data AS 
  PROCEDURE INSERT_TRANSACTION_INFO(
    var_FName IN TRANSACTION.FIRST_NAME%type, 
    var_LName IN TRANSACTION.LAST_NAME%type, 
    var_DOB IN TRANSACTION.DOB%type, 
    var_InvoiceDate IN TRANSACTION.INVOICE_DATE%type, 
    var_ServiceCode IN TRANSACTION.SERVICE_CODE%type,
    var_BilledAmt IN TRANSACTION.BILLED_AMT%type,
    var_SSN IN TRANSACTION.SSN%type,
    var_ServiceDate IN TRANSACTION.SERVICE_DATE%type,
    var_VendorCode IN TRANSACTION.VENDOR_CODE%type,
    var_TransStatus IN TRANSACTION.TRANS_STATUS%type,
    var_Comp IN TRANSACTION.COMPANY%type,
    var_State IN TRANSACTION.STATE%type,
    var_Return OUT VARCHAR2)
  IS
  BEGIN
    INSERT INTO TRANSACTION
      (FIRST_NAME, LAST_NAME, DOB, INVOICE_DATE, SERVICE_CODE, BILLED_AMT, 
        SSN, SERVICE_DATE, VENDOR_CODE, TRANSACTION_ID, TRANS_STATUS, COMPANY, 
        STATE)
    VALUES
      (var_FName, var_LName, to_date(var_DOB, 'MM/DD/YY'), 
        to_date(var_InvoiceDate, 'MM/DD/YY'), var_ServiceCode, var_BilledAmt, 
        var_SSN, to_date(var_ServiceDate, 'MM/DD/YY'), var_VendorCode, 
        SEQ_TRANSACTION_ID.nextval, var_TransStatus, var_Comp, var_State);
    var_return := 'PASS';
  EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    var_return := 'DUPE';
  WHEN OTHERS THEN
    var_return := 'FAIL';
  END INSERT_TRANSACTION_INFO;
END trans_data;

My PHP code receives a POST from the previous page, which I verified all the fields are passing correctly, and binds them to the variables.

//setup insert statement
$stmts = OCI_Parse($c,"BEGIN ucs.trans_data.INSERT_TRANSACTION_INFO(
            :var_FName, :var_LName, :var_DOB, :var_InvoiceDate, :var_ServiceCode, :var_BilledAmt, :var_SSN, 
            :var_ServiceDate, :var_VendorCode, :var_TransStatus, :var_Comp, :var_State, :var_return); END;");

//bind input and output
OCI_Bind_By_Name($stmts, ":var_FName", $FName);
OCI_Bind_By_Name($stmts, ":var_LName", $LName);
OCI_Bind_By_Name($stmts, ":var_DOB", $DOB);
OCI_Bind_By_Name($stmts, ":var_InvoiceDate", $InvoiceDate);
OCI_Bind_By_Name($stmts, ":var_ServiceCode", $ServiceCode);
OCI_Bind_By_Name($stmts, ":var_BilledAmt", $BilledAmt);
OCI_Bind_By_Name($stmts, ":var_SSN", $SSN);
OCI_Bind_By_Name($stmts, ":var_ServiceDate", $ServiceDate);
OCI_Bind_By_Name($stmts, ":var_VendorCode", $VendorCode);
OCI_Bind_By_Name($stmts, ":var_TransStatus", $TransStatus);
OCI_Bind_By_Name($stmts, ":var_Comp", $ComCode);
OCI_Bind_By_Name($stmts, ":var_State", $State);
OCI_Bind_By_Name($stmts, ":var_return", $Return, 4);

//execute statement and add message to Return
oci_execute($stmts);
oci_commit($c);

Any ideas why using a date of 01/01/11 would return this message? Let me know if you need me to post any other information.

socha23 socha23 · Accepted Answer · 2011-12-20T16:18:19

You declared wrong types for DATE arguments in your procedure.

Your procedure uses to_date to parse dates, so each should be passed as VARCHAR. But you declared your arguments as being of type DATE:

var_DOB IN TRANSACTION.DOB%type, 
var_InvoiceDate IN TRANSACTION.INVOICE_DATE%type, 
var_ServiceDate IN TRANSACTION.SERVICE_DATE%type,

Try changing it to:

var_DOB IN VARCHAR2, 
var_InvoiceDate IN VARCHAR2, 
var_ServiceDate IN VARCHAR2,

and see if it works then.

Need help executing Oracle insert procedure with PHP

1 Answers