1 Reply Latest reply: Jul 31, 2008 1:36 PM by 650952 RSS

    ROWTOCOL function

    650952
      HI,
      I am trying use this rowtocol function posted in oracle tech doc's I am trying pass a query to this function but it is not working can some one please look into this:

      I am trying to get output from the following:
      select distinct user_id "Account ID",
      username "Correlation Key (PPM User ID)",
      (select environment_name||'-'||server_name from kenv_environments where environment_name = 'KINTANA_PROD') as "End Point",
      (select database_type from kenv_environments where environment_name = 'KINTANA_PROD') as "Domain",
      full_name "Comments",
      (select 0 from dual) as "Suspended",
      (select 0 from dual) as "Locked",
      rowtocol('select ksg.security_group_name
                     from knta_users ku,knta_user_security kus,knta_security_groups ksg
                     where ku.user_id = ' ||kus.user_id|| '
                     and kus.security_group_id = ' ||ksg.security_group_id ||
                     and ku.end_date is null') As Sample
           from knta_users
           where end_date is null
           order by 1;

      Original Query:
      select user_id "Account ID",

      username "Correlation Key (PPM User ID)",

      (select environment_name||'-'||server_name from kenv_environments where environment_name = 'KINTANA_PROD') as "End Point",

      (select database_type from kenv_environments where environment_name = 'KINTANA_PROD') as "Domain",

      full_name "Comments",

      (select 0 from dual) as "Suspended",

      (select 0 from dual) as "Locked"

      from knta_users

      where end_date is null

      order by 1

      And I need to add these rows as columns (well, only the Security Group Name row) to the above query



      --Security Groups for Active Users

      select ku.user_id,

      ksg.security_group_name "Security Group Name"

      from knta_users ku,

      knta_user_security kus,

      knta_security_groups ksg

      where ku.user_id = kus.user_id

      and kus.security_group_id = ksg.security_group_id

      and ku.end_date is null

      order by 1, 2
        • 1. Re: ROWTOCOL function
          650952
          oops...forgot to paste ROWTOCOL function
          ---------------------------------------------------------------
          CREATE OR REPLACE 

               FUNCTION rowtocol( p_slct IN VARCHAR2,

               p_dlmtr IN VARCHAR2 DEFAULT ',' ) RETURN VARCHAR2

               AUTHID CURRENT_USER AS

               /*



          1) Column should be character type.
          2) If it is non-character type, column has to be converted into character type.
          3) If the returned rows should in a specified order, put that ORDER BY CLASS in the SELECT statement argument.
          4) If the SQL statement happened to return duplicate values, and if you don't want that to happen, put DISTINCT in the SELECT statement argument.





               TYPE c_refcur IS REF CURSOR;

               lc_str VARCHAR2(4000);

               lc_colval VARCHAR2(4000);

               c_dummy c_refcur;

               l number;


               BEGIN


               OPEN c_dummy FOR p_slct;



               LOOP

               FETCH c_dummy INTO lc_colval;

               EXIT WHEN c_dummy%NOTFOUND;

               lc_str := lc_str || p_dlmtr || lc_colval;

               END LOOP;

               CLOSE c_dummy;



               RETURN SUBSTR(lc_str,2);

               /*

               EXCEPTION

               WHEN OTHERS THEN



               lc_str := SQLERRM;

               IF c_dummy%ISOPEN THEN

               CLOSE c_dummy;

               END IF;

               RETURN lc_str;

               */



               END;

               /
          http://www.oracle.com/technology/oramag/code/tips2004/050304.html