Load a long datatype from an Oracle DB

Load a long datatype from an Oracle DB

 

When a field is stored as a "long" datatype in an Oracle DB the OLE DB that Verivo uses in the Oracle Plugin does not convert this field to a varchar and truncate the data. To get around this issue, in Oracle you can use a function to first call the long datatype, return it as varchar  then call it in the view's SQL creation statement.

The sample Code for this function is shown below:

CREATE OR REPLACE FUNCTION fn_getlong (
p_tname IN VARCHAR2, 
p_cname IN VARCHAR2,
p_rowid IN ROWID)
RETURN VARCHAR2

AS
l_cursor INTEGER DEFAULT DBMS_SQL.open_cursor;
l_n NUMBER;
l_long_val VARCHAR2 (4000);
l_long_len NUMBER;
l_buflen NUMBER := 4000;
l_curpos NUMBER := 0;

BEGIN
DBMS_SQL.parse (l_cursor,
'select ' || p_cname || ' from ' || p_tname || ' where rowid = :x',
DBMS_SQL.native);
DBMS_SQL.bind_variable (l_cursor, ':x', p_rowid);
DBMS_SQL.define_column_long (l_cursor, 1);
l_n := DBMS_SQL.execute (l_cursor);
IF (DBMS_SQL.fetch_rows (l_cursor) > 0) THEN
DBMS_SQL.column_value_long (l_cursor, 1, l_buflen, l_curpos, l_long_val,l_long_len);
END IF;

DBMS_SQL.close_cursor (l_cursor);

RETURN l_long_val;

END fn_getlong;


-- Here is how you would call it in the select statement:

-- fn_getlong ('S_NOTE_ACCNT', 'NOTE', T24.ROWID) as MissionStatement

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk