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 Vasques
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
– RDamazio
$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';
– Augusto Vasques
Augusto, I removed the time from the string and worked with the following format: $dtaInicial = '01-JAN-2015'; $dtaTermino = '30-DEC-2020';
– RDamazio