Current Position:Home > Generic function to display a specific row from any table

Generic function to display a specific row from any table

Update:11-30Source: network consolidation
Advertisement
Hi all,
I need some help to write a function in PL/SQL or Dynamic SQL (or both), to write a function that would take in parameters only the unique ID of the row to display and the name of the table to display (that could be any table with any format).
Actually we don't know in advance which table will be accessed, so we don't know the columns and their types until the function is called.
Once I get the row, I want to return a small table with a size of only one line containing the details of the row I looked for.
So the signature of the function would be something like: CREATE function displayDetailedRowFromAnyTable(uniqId in varchar2(25), tableName in varchar2(25)).
Hope the description is clear. I found in some forums some details about this kind of problems; however, the structure of the tables (ie. the columns name and their types) is always known in advance. In my case, I don't know those details until run time.
Any help would be greatly appreciated.

The Best Answer

Advertisement
There is a built in "function" to do this. It's called "SELECT" and it's available through the SQL engine. You give it the table name and the ID you want and it can return you all the columns from that table...
e.g.
SELECT *
FROM <table_name>
WHERE id = <required_id>If you truly want a generic dynamic SQL then you'll have to code it as an ANYTYPE (as already mentioned above). Although this will make your code very complex and difficult to maintain.
e.g. of defining your own dynamic pipelined function using anytype ...
create or replace type NColPipe as object
  l_parm varchar2(10),   -- The parameter given to the table function
  rows_requested number, -- The parameter given to the table function
  ret_type anytype,      -- The return type of the table function
  rows_returned number,  -- The number of rows currently returned by the table function
  static function ODCITableDescribe( rtype out anytype, p_parm in varchar2, p_rows_req in number := 1 )
  return number,
  static function ODCITablePrepare( sctx out NColPipe, ti in sys.ODCITabFuncInfo, p_parm in varchar2, p_rows_req in number := 1 )
  return number,
  static function ODCITableStart( sctx in out NColPipe, p_parm in varchar2, p_rows_req in number := 1 )
  return number,
  member function ODCITableFetch( self in out NColPipe, nrows in number, outset out anydataset )
  return number,
  member function ODCITableClose( self in NColPipe )
  return number,
  static function show( p_parm in varchar2, p_rows_req in number := 1 )
  return anydataset pipelined using NColPipe
create or replace type body NColPipe as
  static function ODCITableDescribe( rtype out anytype, p_parm in varchar2, p_rows_req in number := 1 )
  return number
  is
    atyp anytype;
  begin
    anytype.begincreate( dbms_types.typecode_object, atyp );
    atyp.addattr( to_char(to_date(p_parm,'MONYYYY'),'MONYY')
                , dbms_types.typecode_varchar2
                , null
                , null
                , 10
                , null
                , null
    atyp.endcreate;
    anytype.begincreate( dbms_types.typecode_table, rtype );
    rtype.SetInfo( null, null, null, null, null, atyp, dbms_types.typecode_object, 0 );
    rtype.endcreate();
    return odciconst.success;
  exception
    when others then
      return odciconst.error;
  end;  
  static function ODCITablePrepare( sctx out NColPipe, ti in sys.ODCITabFuncInfo, p_parm in varchar2, p_rows_req in number := 1 )
  return number
  is
    elem_typ sys.anytype;
    prec pls_integer;
    scale pls_integer;
    len pls_integer;
    csid pls_integer;
    csfrm pls_integer;
    tc pls_integer;
    aname varchar2(30);
  begin
    tc := ti.RetType.GetAttrElemInfo( 1, prec, scale, len, csid, csfrm, elem_typ, aname );
    sctx := NColPipe( p_parm, p_rows_req, elem_typ, 0 );
    return odciconst.success;
  end;
  static function ODCITableStart( sctx in out NColPipe, p_parm in varchar2, p_rows_req in number := 1 )
  return number
  is
  begin
    return odciconst.success;
  end;
  member function ODCITableFetch( self in out NColPipe, nrows in number, outset out anydataset )
  return number
  is
  begin
    anydataset.begincreate( dbms_types.typecode_object, self.ret_type, outset );
    for i in self.rows_returned + 1 .. self.rows_requested
    loop
      outset.addinstance;
      outset.piecewise();
      outset.setvarchar2( self.l_parm );
      self.rows_returned := self.rows_returned + 1;
    end loop;
    outset.endcreate;
    return odciconst.success;
  end;
  member function ODCITableClose( self in NColPipe )
  return number
  is
  begin
    return odciconst.success;
  end;
end;
And to use it
SQL> select * from table( NColPipe.show( 'JAN2008' ) );
JAN08
JAN2008
  • Generic function to display a specific row from any table Update:11-30

    Hi all, I need some help to write a function in PL/SQL or Dynamic SQL (or both), to write a function that would take in parameters only the unique ID of the row to display and the name of the table to display (that could be any table with any format)

  • How to identify one specific row in the table STPO? Update:11-30

    Hi! How can I identify one specific row in the table STOP? As an input for my ABAP function I have MatNr, Plant, Alternative, BomUsage and Bom Position (0010, 0020...). With these values I can ask the table MAST to get the STLNR. But how can I get al

  • To color a specific row in a Table UI element Update:10-11

    Hello Experts, We have a requirement to highlight (with distinguish color) a specific row of a table UI based on column data. Suppose we have a table with 5 column and 10 row, now if the data on 5th column greater than some XYZ value then we need to

  • Display all the rows in the table with Varray Update:11-30

    I created two Varrays CREATE TYPE phone_varray AS VARRAY(3) OF NUMBER(10); CREATE TYPE email_varray AS VARRAY(3) OF varchar2(30); CREATE TABLE ee (id number(2), phone phone_varray); now i inserted some rows into ee table and displayed it as: SELECT e

  • Getting values from a specific row in a table Update:10-11

    hi all, i have a table that has the following data WITH sec AS   SELECT 111 id,  'XPE' cid,  10 alias, 'TLC' Description, 'PEYU' lvl_1, 'IES' lvl_2, ' BAN' lvl_3 FROM dual UNION ALL   SELECT 111 id,  'XPE' cid,  78 alias, 'TLC2' Description, 'SUN' lv

  • How to color specific row in a table.... Update:10-11

    Hi all, I created a table out of an Array[]. The table is successfully filled up by array's elements. Each element is a row. I want to be able to color specific row; instead of every row. How do I do that? I was looking rendering to color a row or a

  • Display of selected row no in table footer. Update:10-11

    Hi, Row no of first row is generally displayed in the footer of the table as Row <first row rowno> of <total no of rows>.Eg Row 1 of 10. Can this be changed to display row no of currently selected row insted of first row? Thanks in advance, Ap

  • How to: Display Last n Rows in a table Update:10-11

    I am currently running Crystal Reports XI and have been tasked with developing a report that displays only the last 30 rows in a table in ascending order (the records are used for showing data trends over time in a line graph). I have created a Comma

  • How to display All the rows in a Table Update:11-30

    Hi All, Our requirement is, we need to display all the records of an VO in a table. We should not have navigation bar with previous and next. Runtime only will come to know how many records the VO has. Is it possible to achieve our requirement using

  • How to display multiple selected rows in a table inside a popup? Update:10-11

    Hi, I have a table on which multiple selection is enabled. I am able to get hold of multiple selected rows i.e. i am able to iterate over the selected row keys and print their values. Now, my problem is how to display the contents of all the selected