Saturday, May 24, 2014

Oracle PL/SQL script for reading a Long data column,

CREATE OR REPLACE FUNCTION  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_buflen    NUMBER := 4000;
     l_long_len  NUMBER;
       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 getlong;
/


No comments: