Quick PS Util - Get Record Description

This function displays Record Definition under Property for a PS record, the function can be expanded to display LONG field values for any PS record.

Create Or replace Function  gl( rec In varchar2, long_fld In varchar2, key_fld In varchar2 )
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 ' || long_fld || ' from ' || rec ||' where recname=upper(:x)', dbms_sql.native );
  dbms_sql.bind_variable( l_cursor, ':x', key_fld );

  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 gl;
/

EX:
Select gl('psrecdefn','descrlong','vendor') From dual


**** Nice utility to parse PS SQL Trace files : http://devwfb.blogspot.com/2009/11/script-analyzing-tracesql-file-and.html


Get View Definition Text: -----------------------------------------------------------------------
select * from user_views where view_name=upper('')

select DBMS_METADATA.GET_DDL('VIEW',upper('___')) from dual

select * from PSSQLTEXTDEFN where sqlid=upper('___')