2

I am trying to output the error message if the query had an error, here's my code:

class app {

    private $db;
    private $con;
    private $err;
    ...

public function addNewRelative($parentId, $name, $relationCode, $relation, $phone, $email) {

    $sql = "BEGIN XXDM_DSQUARE_PKG.CREATE_CONTACTS(
                :P_SUBJECT_PARTY_ID,
                :P_OBJECT_PARTY_NAME',
                :P_RELATIONSHIP_CODE,
                :P_RELATIONSHIP_TYPE,
                :P_MOBILE_NUMBER,
                :P_EMAIL_ADDRESS,
                :X_ERROR_MSG
            ); END;";            

    //Statement does not change
    $stmt = oci_parse($this->con, $sql);

    oci_bind_by_name($stmt, ':P_SUBJECT_PARTY_ID', $parentId);
    oci_bind_by_name($stmt, ':P_OBJECT_PARTY_NAME', $name);
    oci_bind_by_name($stmt, ':P_RELATIONSHIP_CODE', $relationCode);
    oci_bind_by_name($stmt, ':P_RELATIONSHIP_TYPE', $relation);
    oci_bind_by_name($stmt, ':P_MOBILE_NUMBER', $phone);
    oci_bind_by_name($stmt, ':P_EMAIL_ADDRESS', $email);
    oci_bind_by_name($stmt, ':X_ERROR_MSG', $this->err);

    // Execute the statement as in your first try
    if (oci_execute($stmt)) {
        return json_encode(["response"=>1]);
    } else {
        return json_encode(["response"=>0]);
    }
}

But I get the following error:

Warning: oci_execute(): ORA-06550: line 8, column 6: PLS-00363: expression '<null>' cannot be used as an assignment target ORA-06550: line 1, column 7: PL/SQL: Statement ignored in D:\Server\wamp\www\dmg-dsquares-bk\app.php on line 94

The problem is with ‘X_ERROR_MSG’ parameter, as it’s OUT parameter, and I don’t know how to bind it.

CairoCoder
  • 3,091
  • 11
  • 46
  • 68
  • Is that really the correct way of passing variables to a stored procedure in Oracle? Shouldn't it just be: `BEGIN xxxxx('first', 'second', ....); END;`? Or better yet: `BEGIN xxxx(:first, :second, ...); END;` and bind the values. At least [according to Oracles docs](https://www.oracle.com/technetwork/articles/fuecks-sps-095636.html). – M. Eriksson Sep 20 '18 at 14:54
  • @MagnusEriksson, both would work, but yours is more secure, and is what I did in second try. – CairoCoder Sep 20 '18 at 14:56
  • But now you're doing `P_SUBJECT_PARTY_ID=>{$parentId}` (which looks weird) instead of either just `{$parentId}` or `:parentId`. Also, since your title is "error in binding", you should probably show the code where you use parameter bindings. The posted code has nothing to do with bindings. Please update the question to show the binding attempts. – M. Eriksson Sep 20 '18 at 15:02
  • Where do you set `$this->err`? Shouldn't it be `oci_error($this->con)`? – Barmar Sep 20 '18 at 15:07
  • @MagnusEriksson, just updated my answer. – CairoCoder Sep 20 '18 at 15:11
  • @Barmar, see the updates. – CairoCoder Sep 20 '18 at 15:11
  • Googling this error message indicates that it happens when the procedure tries to assign to a parameter variable that's declared `IN` rather than `OUT`. – Barmar Sep 20 '18 at 15:16
  • See https://stackoverflow.com/questions/9977229/oracle-pls-00363-expression-cannot-be-used-as-an-assignment-target for example – Barmar Sep 20 '18 at 15:17
  • @Barmar it’s OUT parameter, for error handling. – CairoCoder Sep 20 '18 at 15:20
  • If it's an out parameter, you need to provide an Oracle variable to be filled in, you can't use a bound parameter. – Barmar Sep 20 '18 at 15:21
  • Are you trying to get it to store the result into the PHP variable `$this->err`? – Barmar Sep 20 '18 at 15:22
  • @Barmar Yes, exactly. – CairoCoder Sep 20 '18 at 15:23
  • The example on https://www.oracle.com/technetwork/articles/fuecks-sps-095636.html implies that this should work. Does it work if you use an ordinary variable instead of a class property? – Barmar Sep 20 '18 at 15:26
  • @Barmar No, same error. – CairoCoder Sep 20 '18 at 15:28
  • Does the procedure work if you call it by hand rather than from PHP? – Barmar Sep 20 '18 at 15:29
  • @Barmar Yes, and working also from PHP but without error parameter. – CairoCoder Sep 20 '18 at 15:31

0 Answers0