Hi Ciara,
I have had the same issue.
The SQLExecutor can not handle Oracle PL/SQL out parameters to populate an attribute.
I have solved this by creating a Stored Function instead of a stored Procedure.
The Stored Function can be called inside a SQL statement and the resulting value can be used as an attribute.
Hope this helps.
Erik
@erik_jan Thanks so much for your quick response. I was going through multiple methods that usually work for me but getting nowhere. That sounds like a good solution for me
Ciara
Hi Ciara,
I have had the same issue.
The SQLExecutor can not handle Oracle PL/SQL out parameters to populate an attribute.
I have solved this by creating a Stored Function instead of a stored Procedure.
The Stored Function can be called inside a SQL statement and the resulting value can be used as an attribute.
Hope this helps.
Erik
can you please some piece of a code as an example? I having the same issue and not getting anywhere.
can you please some piece of a code as an example? I having the same issue and not getting anywhere.
Here is a test I set up for you Gauripearl:
ORACLE Setup:
CREATE TABLE test_table_for_function(
id NUMBER PRIMARY KEY NOT NULL
,txt_column VARCHAR2(20)
,num_column NUMBER
);
INSERT INTO test_table_for_function(id, txt_column, num_column)
VALUES (1, 'test text value 1', 123456);
INSERT INTO test_table_for_function(id, txt_column, num_column)
VALUES (2, 'test text value 2', 123457);
INSERT INTO test_table_for_function(id, txt_column, num_column)
VALUES (3, 'test text value 3', 123458);
Create a Function:
CREATE OR REPLACE FUNCTION fn_get_val(p_id IN NUMBER)
RETURN NUMBER IS
v_num_value NUMBER;
BEGIN
SELECT num_column
INTO v_num_value
FROM test_table_for_function a
WHERE a.id = p_id;
RETURN (v_num_value);
END;
In your SQL Executor:
FME_SQL_DELIMITER ;
select fn_get_val(1) return_value
from dual;
This will return your function result which I have aliased to an attribute called RETURN_VALUE
