Calling Oracle Procedures from PHP

Using Oracle Functions or Procedures can be used to insert /update/delete data from oracle database.

While creating function ADD_INVOICE to insert data in AR_HEADER and AR_LINES,
I found when calling it from TOAD it works fine and insert data in the two levels (HEADER and LINES).

When using PHP it only insert data in one level (HEADER) only.

After some search , I got the concept of calling stored procedure using PHP.
So, I converted the preceding function (ADD_INVOICE ) to procedure which holds an OUT parameter.

Then  when calling it, the insert was successfully in the two levels (HEADER and LINES).

here's the PHP get code :


<?php
    //Connect to oracle database 
    $db=oci_connect('pos','pos','prod', 'AL32UTF8');
    
    $query = " DECLARE  BEGIN ADD_INVOICE(:message); END;";     
    $stmt =oci_parse ($db , $query);  
    // Bind the output parameter
    oci_bind_by_name($stmt,':message',$message,32);    
    // execute query
    oci_execute($stmt); 
print "$message\n";
UPDATES:
 The preceding  $query in green , must be written in one line , to avoid erros!
Also when receiving JSON  using $_POST methods , it's automatically add back slash to every character.

I send json_text : [{ "product_id": "60402", "price": "20.0", "tax_rate": "0.0" , "qty": "1", }{ "product_id": "60302", "price": "60.0", "tax_rate": "5.0" , "qty": "1", }]

It's converted to: [{ \"product_id\": \"60402\", \"price\": \"20.0\", \"tax_rate\": \"0.0\" , \"qty\": \"1\", }{ \"product_id\": \"60302\", \"price\": \"60.0\", \"tax_rate\": \"5.0\" , \"qty\": \"1\", }]

So We use Oracle REPLACE function to remove back slash  ,then parse json.

No comments:

Post a Comment

Remove unused Layouts in Oracle Apex

 Tables used : APEX_XXXXXXX.WWV_FLOW_REPORT_LAYOUTS APEX_XXXXXXX.WWV_FLOW_SHARED_QUERIES use the following query to delete unused Layouts. ...