Error calling Stored Procedure ORACLE by PHP passing date as parameter

Asked

Viewed 54 times

1

I have the following Stored Procedure on Oracle:

CREATE OR REPLACE PROCEDURE SP_RELATORIO(
    P_ID_SITUACAO  IN TB_PROSPECT.ID_SITUACAO%TYPE,
    P_ID_GRUPO IN TB_GRUPO.ID_GRUPO%TYPE,
    P_DT_INICIAL   IN TB_PROSPECT.DT_USUINC%TYPE --DATA DO ORACLE,,
    P_DT_FINAL     IN TB_PROSPECT.DT_USUINC%TYPE --DATA DO ORACLE,,
    P_CURSOR_DADOS OUT SYS_REFCURSOR)
IS

  BEGIN
    --CORPO DA PROCEDURE
  END;
  
END SP_RELATORIO;

The code of PHP (dates => 01-01-2020 00:00:00):

$oracle_bd = $MYTNS;

$conn = OCILogon($oracle_usuario, $oracle_senha, $oracle_bd);

$sql = 'BEGIN SP_RELATORIO(:P_ID_SITUACAO, :P_ID_GRUPO, :P_DT_INICIAL, :P_DT_FINAL, :P_CURSOR_DADOS); END;';

$idSituacao = 'PAD';
$idGrupo = '2';
$dtaInicial = '01-01-2020 00:00:00';
$dtaTermino = '31-12-2020 00:00:00';

$stmt = oci_parse($conn, $sql);
oci_bind_by_name($stmt, ':P_ID_SITUACAO', $idSituacao);
oci_bind_by_name($stmt, ':P_ID_GRUPO', $idGrupo);
oci_bind_by_name($stmt, ':P_DT_INICIAL', $dtaInicial);
oci_bind_by_name($stmt, ':P_DT_FINAL', $dtaTermino);

//But BEFORE statement, Create your cursor
$cursor = oci_new_cursor($conn);

// On your code add the latest parameter to bind the cursor resource to the Oracle argument
oci_bind_by_name($stmt, ":P_CURSOR_DADOS", $cursor, -1, OCI_B_CURSOR);

// Execute the statement as in your first try
oci_execute($stmt); 

// and now, execute the cursor
oci_execute($cursor);

// Use OCIFetchinto in the same way as you would with SELECT
while ($data = oci_fetch_assoc($cursor, OCI_RETURN_LOBS)) {
    print_r($data);
}

The above code with the respective date formats returns this error on line 1 of Oracle:

Warning: oci_execute(): ORA-01843: not a Valid Month ORA-06512: at line 1 in C: xampp htdocs teste_reportario_portability conn_oracle.php on line 47


The code of PHP (dates => 2020-01-01 00:00:00):

$oracle_bd = $MYTNS;

$conn = OCILogon($oracle_usuario, $oracle_senha, $oracle_bd);

$sql = 'BEGIN SP_RELATORIO(:P_ID_SITUACAO, :P_ID_GRUPO, :P_DT_INICIAL, :P_DT_FINAL, :P_CURSOR_DADOS); END;';

$idSituacao = 'PAD';
$idGrupo = '2';
$dtaInicial = '2020-01-01 00:00:00';
$dtaTermino = '2020-12-31 00:00:00';

$stmt = oci_parse($conn, $sql);
oci_bind_by_name($stmt, ':P_ID_SITUACAO', $idSituacao);
oci_bind_by_name($stmt, ':P_ID_GRUPO', $idGrupo);
oci_bind_by_name($stmt, ':P_DT_INICIAL', $dtaInicial);
oci_bind_by_name($stmt, ':P_DT_FINAL', $dtaTermino);

//But BEFORE statement, Create your cursor
$cursor = oci_new_cursor($conn);

// On your code add the latest parameter to bind the cursor resource to the Oracle argument
oci_bind_by_name($stmt, ":P_CURSOR_DADOS", $cursor, -1, OCI_B_CURSOR);

// Execute the statement as in your first try
oci_execute($stmt); 

// and now, execute the cursor
oci_execute($cursor);

// Use OCIFetchinto in the same way as you would with SELECT
while ($data = oci_fetch_assoc($cursor, OCI_RETURN_LOBS)) {
    print_r($data);
}

The above code with the respective date formats returns this error:

Warning: oci_execute(): ORA-01861: literal does not match format string ORA-06512: at line 1 in C: xampp htdocs teste_reportario_portability conn_oracle.php on line 47


The code of PHP (dates => 01/01/2020 00:00:00):

$oracle_bd = $MYTNS;

$conn = OCILogon($oracle_usuario, $oracle_senha, $oracle_bd);

$sql = 'BEGIN SP_RELATORIO(:P_ID_SITUACAO, :P_ID_GRUPO, :P_DT_INICIAL, :P_DT_FINAL, :P_CURSOR_DADOS); END;';

$idSituacao = 'PAD';
$idGrupo = '2';
$dtaInicial = '01/01/2020 00:00:00';
$dtaTermino = '31/12/2020 00:00:00';

$stmt = oci_parse($conn, $sql);
oci_bind_by_name($stmt, ':P_ID_SITUACAO', $idSituacao);
oci_bind_by_name($stmt, ':P_ID_GRUPO', $idGrupo);
oci_bind_by_name($stmt, ':P_DT_INICIAL', $dtaInicial);
oci_bind_by_name($stmt, ':P_DT_FINAL', $dtaTermino);

//But BEFORE statement, Create your cursor
$cursor = oci_new_cursor($conn);

// On your code add the latest parameter to bind the cursor resource to the Oracle argument
oci_bind_by_name($stmt, ":P_CURSOR_DADOS", $cursor, -1, OCI_B_CURSOR);

// Execute the statement as in your first try
oci_execute($stmt); 

// and now, execute the cursor
oci_execute($cursor);

// Use OCIFetchinto in the same way as you would with SELECT
while ($data = oci_fetch_assoc($cursor, OCI_RETURN_LOBS)) {
    print_r($data);
}

The above code with the respective date formats returns this error on line 1 of Oracle:

Warning: oci_execute(): ORA-01843: not a Valid Month ORA-06512: at line 1 in C: xampp htdocs teste_reportario_portability conn_oracle.php on line 47

I believe it is a problem in the format of dates, but I have tried several format conversions and could not. If anyone could help me out, I’d really appreciate it!?

  • Have you tried 01-JAN-2020 00:00:00? Reference: https://www.techonthenet.com/oracle/errors/ora01843.php

  • Augusto, in this format, returned the following error: Warning: oci_execute(): ORA-01830: date format picture ends before Converting entire input string ORA-06512: at line 1 in C: xampp htdocs teste_reportario_portability conn_oracle.php on line 47

  • $sql = 'BEGIN SP_RELATORIO(:P_ID_SITUACAO, :P_ID_GRUPO, to_date(:P_DT_INICIAL , 'DD/MM/YYYY HH24-MI-SS' ), to_date(:P_DT_FINAL, 'DD/MM/YYYY HH24-MI-SS' ), :P_CURSOR_DADOS); END;'; and $dtaInicial = '01-01-2020 00:00:00';
$dtaTermino = '31-12-2020 00:00:00';

  • 1

    Augusto, I removed the time from the string and worked with the following format: $dtaInicial = '01-JAN-2015'; $dtaTermino = '30-DEC-2020';

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.