Advertisement
Guest User

Untitled

a guest
Oct 24th, 2014
121
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.25 KB | None | 0 0
  1. getlong ('xxx', 'YYY', :OLD.ROWID),
  2. getlong ('XXX', 'yyy', :NEW.ROWID)
  3.  
  4. CREATE OR REPLACE FUNCTION getlong (p_tname IN VARCHAR2, -- table name
  5. p_cname IN VARCHAR2, -- column name
  6. p_rowid IN ROWID)
  7. RETURN VARCHAR2
  8. AS
  9. l_cursor INTEGER DEFAULT DBMS_SQL.open_cursor;
  10. l_n NUMBER;
  11. l_long_val VARCHAR2 (4000);
  12. l_long_len NUMBER;
  13. l_buflen NUMBER := 4000;
  14. l_curpos NUMBER := 0;
  15. BEGIN
  16.  
  17. -- Usage:
  18. -- select getlong('TABLENAME', 'COLUMNNAME', rowid) from TABLENAME;
  19.  
  20. DBMS_SQL.
  21. parse (l_cursor,
  22. 'select ' || p_cname || ' from ' || p_tname || ' where rowid = :x',
  23. DBMS_SQL.native);
  24. DBMS_SQL.bind_variable (l_cursor, ':x', p_rowid);
  25.  
  26. DBMS_SQL.define_column_long (l_cursor, 1);
  27. l_n := DBMS_SQL.execute (l_cursor);
  28.  
  29. IF (DBMS_SQL.fetch_rows (l_cursor) > 0)
  30. THEN
  31. DBMS_SQL.column_value_long (l_cursor,
  32. 1,
  33. l_buflen,
  34. l_curpos,
  35. l_long_val,
  36. l_long_len);
  37. END IF;
  38.  
  39. DBMS_SQL.close_cursor (l_cursor);
  40. RETURN l_long_val;
  41. END getlong;
  42. /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement