Call Oracle stored Function from Data Mapper

Question: Is it possible to call an Oracle stored Function from the mapping?

We have a Stored Function that reads two value and returns single resultset based on the query.

For Example: Below is the Create Function statement:-

CREATE OR REPLACE FUNCTION "SYSTEM"."FN_COUNT_COMMENTS" (
postId IN NUMBER,
postName IN VARCHAR2)
RETURN NUMBER
IS
commentCount NUMBER;
BEGIN
SELECT COUNT(*) INTO commentCount
FROM person
WHERE id = postId and name = postName;
RETURN( commentCount );
END;

 

 

To call the function: select SYSTEM.FN_COUNT_COMMENTS(parameter1,'parameter2') from DUAL;

 

Answer: You can call the Oracle Function by following the below high-level steps:-

1. Create a local variable like "varQuery" with value:
concat('select SYSTEM.FN_COUNT_COMMENTS(100,' ,"'Yes'",') from dual')  


2. Create another local variable like "varDbQuery" with value:
DBQuery {$varQuery, $Db_Info, 'false' }
where $Db_Info is the name of the Connection info variable.

3. Now, call the variable "$varDbQuery" in the textual rule of the target element. 

Note: The Db Query function will return a single Resultset. These steps are not for multiple ResultSet. Also, the value of 3rd parameter in step 2, is always 'false'.

 

 

Have more questions? Submit a request

0 Comments

Article is closed for comments.