Skip to main content

Hi all,

I am calling a stored procedure in oracle. The procedure returns a "ref cursor" data type.

When calling with SQL Developer I have this result:

select bddoc_mgr.get_file_status_fme('w:\rwa005\energis\data\dwg\bp\geo','l144152-3.dwg') from dual

{<FILE_ID=1,S_FILE_STATUS=2,S_STATUS=4,N_VERSION=1000,L_USERID=IAA543    >,}

When calling with SQLCreator I have this error:

Could not understand type information: type=116, size=8, scale=0, precision=0ORACLE Reader: Executing SQL Statement `select bddoc_mgr.get_file_status_fme('w:\rwa005\energis\data\dwg\bp\geo','l144152-3.dwg') from dual' to obtain features for feature type `QueryFactory_SQLExecute'Unable to define column number 1: error was `ORA-01024: invalid datatype in OCI call'. (serverType=`ORACLE8i', serverName=****, userName=****, password=`***', dbname=`')

When calling with python I can parse the result easily:

Is there a way to reach similar result using an SQLCreator?

import fme
import fmeobjects
import cx_Oracle
# Template Function interface:
def processFeature(feature):
    pass
# Template Class Interface:
class FeatureProcessor(object):
    def __init__(self):
        pass
    def input(self,feature):
        try:
            # Connect to database.
            db_conn = *****
            # Get file path and name.
            f_path = feature.getAttribute("L_PATH")
            f_name = feature.getAttribute("L_NAME")
            # Call function.
            db_cur = db_conn.cursor()
            values = db_cur.callfunc("bddoc_mgr.get_file_status_fme", cx_Oracle.CURSOR, \
                    if_path, f_name])
            # Convert values.
            for value in values:
                feature.setAttribute("S_STATUS", valuev2])
                feature.setAttribute("N_VERSION", valuer3])
                feature.setAttribute("L_USERID", value_4])
                break
        finally:
            # Disconnect.
            try:
                db_conn.close()
            except:
                pass
            self.pyoutput(feature)
    def close(self):
        pass 

Hi JP

Somebody from Safe will have to confirm, but I do not think the SQLExecutor will accept a cursor reference as a return value, I suspect it expects row data.

Is there a particular reason for wanting to use your Python script?

David


Hi David,

Thanks for your answer. I was suspecting something like this.

The python script is my current workaround to parse the ref_cursor datatype into fme attributes. I'll use it if SQLCreator can't make use of ref_cursor.

Would you have a suggestion on how I could develop a stored procedure in Oracle in such a way that all "db_model" logic would be hidden in the SQL procedure and I could call easily with FME? That's all I want to do here...

.JP


Hi David,

Thanks for your answer. I was suspecting something like this.

The python script is my current workaround to parse the ref_cursor datatype into fme attributes. I'll use it if SQLCreator can't make use of ref_cursor.

Would you have a suggestion on how I could develop a stored procedure in Oracle in such a way that all "db_model" logic would be hidden in the SQL procedure and I could call easily with FME? That's all I want to do here...

.JP

Could a view replace your stored procedre?

You could try casting your ref cursor as a table, as described here.

Here's also an alternative solution.


Besides that: A stored procedure in Oracle can only return a value in an out parameter, never as a return value. To use a return value a stored function is used.

The stored function can be used in a view in the database and be called from a SQL statement.


Reply