Oracle procedure does't executes calling from PHP

Question

I'm facing with problem executing Oracle Procedure from PhP. Actually, I am using Doctrine to execute it.

What is interesting is the fact of any other Queries can be executed/fetched, but procedures.

Below, you can find the codes I managed to use, this Select works wonderfully:

$connection = $this->getApplication()->getDataSourceManager()->getEntityConnection();
$stmt = $connection->prepare("SELECT SYSDATE FROM DUAL"); //or any other select works nice
$stmt->execute();

However, any procedure wont work, this is one of them:

$connection = $this->getApplication()->getDataSourceManager()->getEntityConnection();
$stmt = $connection->prepare("call prc_nutr_values('$cdfil', '$cdserice', '001', '0000000036', 'S', '$selectdt', '$selectdt')");
$stmt->execute();

The procedure above, doesn't surge any changes in DB. It doesn't his procedure

What error do you get? If the code is being called, the procedure will either run or an error will be thrown. It sounds like you are saying that the procedure isn't doing what you expect it to do. That implies that there is a bug in the procedure or that you are not passing in what your procedure expects. — Justin Cave, Apr 17 '14 at 22:09
What happens if you try an SP that contains a select query? Does it return results? — Dan Bracuk, Apr 17 '14 at 22:09
@JustinCave, I don't get any error. That's the thing! It seems to be executed, but it doesn't. The procedure executes just fine in the Oracle Sql Developer. — meetnick, Apr 17 '14 at 22:20
If the line of code you posted is being executed and no errors are thrown, there is a bug in your procedure and/or you are not passing exactly the same arguments that you are when you call it from SQL Developer or there is some difference in the environment (perhaps there are uncommitted changes in one session that cause a behavior change). — Justin Cave, Apr 17 '14 at 22:22
Try to execute with with prepared statements `call blah(?);$stmt->bindParam(1, $var, PDO::PARAM_STR, 4000);$stmt->execute` — Mihai, Apr 17 '14 at 22:42
@JustinCave, What kind of bug do you mean? Because the procedures executes just fine in SQL Developer. There are no errors thrown by SQL neither from PHP server. Don't you think if there are any uncommited changes, on SQL Developer shouldn't work neither? — meetnick, Apr 17 '14 at 22:43
Also http://stackoverflow.com/questions/13382922/calling-stored-procedure-with-out-parameter-using-pdo — Mihai, Apr 17 '14 at 22:43
Without seeing the procedure and knowing what it's expected to do, it's impossible to guess what sort of bug might cause it not to do whatever you expect it to do. My first guess, though, is that the real problem is that you aren't passing the same values from your PHP code that you are in SQL Developer. — Justin Cave, Apr 17 '14 at 22:45
@JustinCave, I've forced fixed values previously tested on SQL Developer and don't work either. So, they have the same values I just don't understand why this procedure works in other applications (like writen on Delphi), but mine. — meetnick, Apr 17 '14 at 22:51
@Mihai, I tried as following, same behaviour: $stmt->bindValue(':param1', $cdfil, PDO::PARAM_STR, 4000); $stmt->bindValue(':param2', $cdservice,PDO::PARAM_STR, 4000); $stmt->bindValue(':param3', '001', PDO::PARAM_STR, 4000); $stmt->bindValue(':param4', '0000000036', PDO::PARAM_STR, 4000); $stmt->bindValue(':param5', 'S', PDO::PARAM_STR, 4000); $stmt->bindValue(':param6', $selectdt, PDO::PARAM_STR, 4000); $stmt->bindValue(':param7', $selectdt, PDO::PARAM_STR, 4000); $stmt->execute(); — meetnick, Apr 17 '14 at 22:54
Trace your procedure. If what you are saying is correct, the procedure is being called, it's just not doing what you expect. If your procedure is not doing what you expect, you need to trace it to find out why. You may not be passing in the same values (particularly if you are every relying on implicit data type conversion). Your procedure may have a bug. Or you may be seeing different data in different sessions. — Justin Cave, Apr 17 '14 at 22:54
@JustinCave, you were absolutely right! It was an date format error. I just set TO_DATE('$variable', 'DD/MM/YYYY) and it worked. — meetnick, Apr 25 '14 at 16:11

score 0 · Answer 1 · answered Apr 25 '14 at 16:19

Error found!

The main problem was that PhP doesn't throw any error (I don't know why. However, I made the transaction in a different way, creating a clean new connection and it threw the error, as following:

$entitiesPath = realpath(__DIR__."/../src/");
        $isDevMode = true;
        $entity_metadata_config = \Doctrine\ORM\Tools\Setup::createAnnotationMetadataConfiguration(array($entitiesPath), $isDevMode);
        $connection_params = array(
            'driver'    => 'oci8',
            'user'      => 'user',
            'password'  => 'pwd',
            'host'      => 'IP',
            'port'      => 'port',
            'dbname'    => 'ORCL',
        );
        $entity_manager = \Doctrine\ORM\EntityManager::create($connection_params, $entity_metadata_config);
        $conn=$entity_manager->getConnection();

        $sql ="CALL FUNCTION_NAME('$param1', '$param2', '$param3', '$param4', '$param5', $param6, $param7)";

        $outputMeta = $conn->exec($sql);

Connecting through this way, I could receive the error detailed: it was $param6 and $param7 (non-formatted dates)

to solve this... I just replace $param6 and $param7 with TO_DATE('$param6', 'DATE-FORMAT') and TO_DATE('$param7', 'DATE-FORMAT')

where 'DATE-FORMAT' is the used date format ('MM/DD/YYYY', or other used date formats)

Oracle procedure does't executes calling from PHP

1 Answers1