How to call an Oracle Function in Symfony 2

Hi All,

How are you doing? Seems like my fingers do not want me to stay away from Symfony2 despite the Spring Bank Holiday. πŸ™‚

Today, I am going to talk about how to call an Oracle Function in Symfony2. A function (aka stored function) is a collection of SQL statements which is used to perform various different activities within the Database. The good thing about the function is you can write your chunks of SQL inside the function and call it using a name to initiate. I will be focusing on how to call an Oracle Function in Symfony2 and if you would like to know more about Oracle functions please click here.

First, let’s take a look at a sample function which I have created and is saved in Oracle 12c Database.

create or replace 
FUNCTION testFunc123(old_bname IN VARCHAR2) 
   RETURN VARCHAR2 IS 
   BEGIN 
      IF old_bname = 'TEST' THEN
          RETURN 'Y';
      ELSE
        RETURN 'N';
      END IF;  
    END;

The role of the above created Oracle function is to check whether the value of ‘old_name’ is equal to ‘TEST’ or not. If it is ‘TEST’, then the function will return ‘Y’ otherwise ‘N’. So, now what we going to do is call this function inside a Symfony2 controller. This is really easy, thankfully to wonderful Doctrine2.

public function runOracleFunctionAction()
    {
        $conn = $this->getDoctrine()->getConnection();

        $sql = "SELECT testFunc123(?) AS result FROM dual";
        $params = array(
            'abc'
        );
        $paramsTypes = array(
            \PDO::PARAM_STR
        );
        
        $stmt = $conn->executeQuery($sql, $params, $paramsTypes);

        $res = $stmt->fetchAll(\PDO::FETCH_ASSOC);
        print_r($res);
        die;
        
        return $formName;        
    } 

The above function will call the Oracle procedure and get the returning value for you. All you want to know is the name of the function (i.e. testFunct123) and the parameter(s). Just make sure that you are giving correct type(s) of the parameter(s) when binding. I presume the function itself is self-explanatory and do not need further clarifications. But, in case if you need further clarifications, the comment section below is for you πŸ™‚

When you run above function, you will see the following output.

Array ( [0] => Array ( [RESULT] => N ) )

So, now you may use this output to continue rest of your functionality. Ok all, thanks for reading this article. Looking forward to seeing you again in another post. Meantime, please share if you know any other ways to call an Oracle Function in Syfmoyn2 in the comments section below. Ta ta !

Share

Leave a Reply

Your email address will not be published. Required fields are marked *