CODEWITHSUNDEEP
oracle11gphalcon
Zahid Khowaja
Zahid Khowaja

Reputation: 66

How to execute oracle procedure in phalcon

I have to call an oracle procedure in phalcon framework. Does anyone know how to call it in phalcon model.

I have tried but it doesn't work.

Please help!

Upvotes: 0

Views: 349

Answers (1)

Nikolaos Dimopoulos
Nikolaos Dimopoulos

Reputation: 11485

There are two ways to perform this task and none of them are Phalcon related.

To call a stored procedure you will need to use PDO prepared statements. Since Phalcon implements PDO you will be able to do this using the db service and not the models. Information on how to do this is here:

http://php.net/manual/en/pdo.prepared-statements.php

This also depends on whether you have installed the oci PDO related extension.

The second way is to use the oracle supplied methods such as:

oci_connect
oci_parse (sql statement here)
oci_bind_by_name(bind each parameter to a php variable)
oci_execute
oci_free_statement

You could potentially create models of your own that will encapsulate the above and call the relevant stored procedure. Upon receiving the data back you can instantiate a resultset object and populate it with the returned data.

This will offer the normal resultset back to your application but you won't be able to do much with it since you rely on stored procedures and not a straight up model->table relationship.

There are long discussions and sometimes heated ones on why one should or should not use stored procedures and even more why one should or should not use Oracle. One thing is clear, stored procedures with all their benefits do offer a level of complexity and restrictions to the developers. In Oracle's case and with the notorious cursors, those restrictions are a bit more acute.

One last thing to note is that if you create your own models (nothing to do with Phalcon) you can have the stored procedure variables as properties in each model. That way you will be able to set them, make your call to the stored procedure (see the oci_* functions above) with a say call() function in that model, and then update the object's properties again with the returned variables from the stored procedure. This model will be able to serve you with the basic CRUD operations by calling relevant stored procedures that will allow this CRUD but expose methods that are a bit friendlier to you say insert(), get(), delete() etc.

Upvotes: 1

Related Questions