CODEWITHSUNDEEP
phporacle-databasestored-proceduresplsql
T mon
T mon

Reputation: 33

PLSQL custom data type when calling procedures in php

I am trying to call an PLSQL PROCEDURE with php, but i am having issues with custom data types.

One of the columns in my table has the custom data type below.

create or replace TYPE CUST_ADDRESS_TYP 
    AS OBJECT 
    (   STREET_ADDRESS VARCHAR2 (40) , 
        CITY VARCHAR2 (30) , 
        STATE_PROVINCE VARCHAR2 (10) ,
        POSTAL_CODE VARCHAR2 (10) ,  
        COUNTRY_ID CHAR (2) 
    )  
;

Below is how i have the procedure

create or replace PROCEDURE process_order
(
    p_customer_address in  CUSTOMER.CUST_ADDRESS%TYPE
)
IS

to insert into this colum in PLSQL this is a sample of what i use

CUST_ADDRESS_TYP('77 Old Street','San Diego','CA','94567','US')

and it works just fine

However when i do this in PHP i get

$p_customer_address = "CUST_ADDRESS_TYP('45 High Street','Toronto','CO','94567','CN')";

$stid = oci_parse($conn, 'begin process_order(:p_customer_address); end;');

oci_bind_by_name($stid, ':p_customer_address', $p_customer_address);

Warning: oci_execute(): ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'PROCESS_ORDER' ORA-06550

Thank you.

Upvotes: 2

Views: 727

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

You're passing a single string argument, not an instance of your object type. The content of the string is not evaluated, it's just treated purely as a string - which is one of the advantages of bind variables and how they help avoid SQL injection.

You can put the type constructor inside the procedure call and bind each field of the object directly:

$stid = oci_parse($conn,
  'begin process_order(CUST_ADDRESS_TYP(:street, :city, :state, :post_code, :country)); end;');

oci_bind_by_name($stid, ':street', "45 High Street");
oci_bind_by_name($stid, ':city', "Toronto");
oci_bind_by_name($stid, ':state', "CO");
oci_bind_by_name($stid, ':post_code', "94567");
oci_bind_by_name($stid, ':country', "CN");

You could also declare a customer address object as a local PL/SQL variable and populate that in the same way with the bind variables, and then pass that to the procedure - all within the same anonymous PL/SQL block - but that's only useful if you want to call any object methods first really.

Upvotes: 1

Related Questions