Comma separated column output into one plsql sproc












0















Code of my stored procedure..



create or replace procedure test_1(p_table_owner in varchar2, p_table_name in varchar2, p_result out sys_refcursor)
AS
BEGIN
OPEN p_result FOR
select
di.index_name as "INDEX_NAME", case when di.index_type='IOT - TOP' then 'clustered' else 'nonclustered' end ||
case when di.uniqueness='UNIQUE' and substr(di.index_name, 1, 3) = 'PK_' then ', unique, primary key'
when di.uniqueness='UNIQUE' then ', unique' end ||
case when di.uniqueness='NONUNIQUE' then '' else '' end || ' located on PRIMARY'
AS "INDEX_DESCRIPTION",ci.column_name as "INDEX_KEYS",null as "INCLUDE_COLS",null as "INDEX_FILTER",null as "DATA_COMPRESSION",null as "ALLOW_PAGE_LOCKS",di.table_name as "TABLE_NAME",
case when di.index_type='IOT - TOP' then 'CLUSTERED' else 'NONCLUSTERED' end AS "INDEX_TYPE"
from dba_indexes di
join dba_ind_columns ci on ci.index_owner = di.owner
and ci.index_name = di.index_name

where di.table_owner = p_table_owner
and di.table_name = p_table_name
and partitioned = 'NO'

union all
select
di.index_name as "INDEX_NAME", case when di.index_type='IOT - TOP' then 'clustered' else 'nonclustered' end ||
case when di.uniqueness='UNIQUE' and substr(di.index_name, 1, 3) = 'PK_' then', unique, primary key'
when di.uniqueness='UNIQUE' then ', unique' end ||
case when di.uniqueness='NONUNIQUE' then '' else '' end || ' located on PRIMARY'
AS "INDEX_DESCRIPTION",ci.column_name as "INDEX_KEYS",null as "INCLUDE_COLS",null as "INDEX_FILTER",null as "DATA_COMPRESSION",null as "ALLOW_PAGE_LOCKS",di.table_name as "TABLE_NAME",
case when di.index_type='IOT - TOP' then 'CLUSTERED' else 'NONCLUSTERED' end AS "INDEX_TYPE"
from dba_indexes di
join dba_ind_partitions dip on dip.index_owner = di.owner
and dip.index_name =
di.index_name
join dba_ind_columns ci on ci.index_owner = di.owner
and ci.index_name = di.index_name
where di.table_owner = p_table_owner
and di.table_name = p_table_name
and di.partitioned = 'YES'

and dip.composite != 'YES'
union all
select
di.index_name as "INDEX_NAME", case when di.index_type='IOT - TOP' then 'clustered' else 'nonclustered' end ||
case when di.uniqueness='UNIQUE' and substr(di.index_name, 1, 3) = 'PK_' then', unique, primary key'
when di.uniqueness='UNIQUE' then ', unique' end ||
case when di.uniqueness='NONUNIQUE' then '' else '' end || ' located on PRIMARY'
AS "INDEX_DESCRIPTION",ci.column_name as "INDEX_KEYS",null as "INCLUDE_COLS",null as "INDEX_FILTER",null as "DATA_COMPRESSION",null as "ALLOW_PAGE_LOCKS",di.table_name as "TABLE_NAME",
case when di.index_type='IOT - TOP' then 'CLUSTERED' else 'NONCLUSTERED' end AS "INDEX_TYPE"
from dba_indexes di
join dba_ind_partitions dip on dip.index_owner = di.owner
and dip.index_name =
di.index_name
join dba_ind_subpartitions dis on dis.index_owner = di.owner
and dis.index_name =
di.index_name
and dis.partition_name =
dip.partition_name
join dba_ind_columns ci on ci.index_owner = di.owner
and ci.index_name = di.index_name
where di.table_owner = p_table_owner
and di.table_name = p_table_name
and di.partitioned = 'YES'
and dip.composite = 'YES';
END;


Currently if i execute this on table(to get indexes information)..it displays me 2 rows for indexes(multi columns) like in below output..i.e it returns 2 seperate rows for index 24290...but i would like it to return only one row per index..In case of multiple columns, the INDEX_KEYS column should reflect all the columns as comma separated list. In this particular this column should appear as:
TESTSMALLINT, BIGINTTEST



SPROC should be compatible with versions lesser than 11g too..



Multi column indexes









share



























    0















    Code of my stored procedure..



    create or replace procedure test_1(p_table_owner in varchar2, p_table_name in varchar2, p_result out sys_refcursor)
    AS
    BEGIN
    OPEN p_result FOR
    select
    di.index_name as "INDEX_NAME", case when di.index_type='IOT - TOP' then 'clustered' else 'nonclustered' end ||
    case when di.uniqueness='UNIQUE' and substr(di.index_name, 1, 3) = 'PK_' then ', unique, primary key'
    when di.uniqueness='UNIQUE' then ', unique' end ||
    case when di.uniqueness='NONUNIQUE' then '' else '' end || ' located on PRIMARY'
    AS "INDEX_DESCRIPTION",ci.column_name as "INDEX_KEYS",null as "INCLUDE_COLS",null as "INDEX_FILTER",null as "DATA_COMPRESSION",null as "ALLOW_PAGE_LOCKS",di.table_name as "TABLE_NAME",
    case when di.index_type='IOT - TOP' then 'CLUSTERED' else 'NONCLUSTERED' end AS "INDEX_TYPE"
    from dba_indexes di
    join dba_ind_columns ci on ci.index_owner = di.owner
    and ci.index_name = di.index_name

    where di.table_owner = p_table_owner
    and di.table_name = p_table_name
    and partitioned = 'NO'

    union all
    select
    di.index_name as "INDEX_NAME", case when di.index_type='IOT - TOP' then 'clustered' else 'nonclustered' end ||
    case when di.uniqueness='UNIQUE' and substr(di.index_name, 1, 3) = 'PK_' then', unique, primary key'
    when di.uniqueness='UNIQUE' then ', unique' end ||
    case when di.uniqueness='NONUNIQUE' then '' else '' end || ' located on PRIMARY'
    AS "INDEX_DESCRIPTION",ci.column_name as "INDEX_KEYS",null as "INCLUDE_COLS",null as "INDEX_FILTER",null as "DATA_COMPRESSION",null as "ALLOW_PAGE_LOCKS",di.table_name as "TABLE_NAME",
    case when di.index_type='IOT - TOP' then 'CLUSTERED' else 'NONCLUSTERED' end AS "INDEX_TYPE"
    from dba_indexes di
    join dba_ind_partitions dip on dip.index_owner = di.owner
    and dip.index_name =
    di.index_name
    join dba_ind_columns ci on ci.index_owner = di.owner
    and ci.index_name = di.index_name
    where di.table_owner = p_table_owner
    and di.table_name = p_table_name
    and di.partitioned = 'YES'

    and dip.composite != 'YES'
    union all
    select
    di.index_name as "INDEX_NAME", case when di.index_type='IOT - TOP' then 'clustered' else 'nonclustered' end ||
    case when di.uniqueness='UNIQUE' and substr(di.index_name, 1, 3) = 'PK_' then', unique, primary key'
    when di.uniqueness='UNIQUE' then ', unique' end ||
    case when di.uniqueness='NONUNIQUE' then '' else '' end || ' located on PRIMARY'
    AS "INDEX_DESCRIPTION",ci.column_name as "INDEX_KEYS",null as "INCLUDE_COLS",null as "INDEX_FILTER",null as "DATA_COMPRESSION",null as "ALLOW_PAGE_LOCKS",di.table_name as "TABLE_NAME",
    case when di.index_type='IOT - TOP' then 'CLUSTERED' else 'NONCLUSTERED' end AS "INDEX_TYPE"
    from dba_indexes di
    join dba_ind_partitions dip on dip.index_owner = di.owner
    and dip.index_name =
    di.index_name
    join dba_ind_subpartitions dis on dis.index_owner = di.owner
    and dis.index_name =
    di.index_name
    and dis.partition_name =
    dip.partition_name
    join dba_ind_columns ci on ci.index_owner = di.owner
    and ci.index_name = di.index_name
    where di.table_owner = p_table_owner
    and di.table_name = p_table_name
    and di.partitioned = 'YES'
    and dip.composite = 'YES';
    END;


    Currently if i execute this on table(to get indexes information)..it displays me 2 rows for indexes(multi columns) like in below output..i.e it returns 2 seperate rows for index 24290...but i would like it to return only one row per index..In case of multiple columns, the INDEX_KEYS column should reflect all the columns as comma separated list. In this particular this column should appear as:
    TESTSMALLINT, BIGINTTEST



    SPROC should be compatible with versions lesser than 11g too..



    Multi column indexes









    share

























      0












      0








      0








      Code of my stored procedure..



      create or replace procedure test_1(p_table_owner in varchar2, p_table_name in varchar2, p_result out sys_refcursor)
      AS
      BEGIN
      OPEN p_result FOR
      select
      di.index_name as "INDEX_NAME", case when di.index_type='IOT - TOP' then 'clustered' else 'nonclustered' end ||
      case when di.uniqueness='UNIQUE' and substr(di.index_name, 1, 3) = 'PK_' then ', unique, primary key'
      when di.uniqueness='UNIQUE' then ', unique' end ||
      case when di.uniqueness='NONUNIQUE' then '' else '' end || ' located on PRIMARY'
      AS "INDEX_DESCRIPTION",ci.column_name as "INDEX_KEYS",null as "INCLUDE_COLS",null as "INDEX_FILTER",null as "DATA_COMPRESSION",null as "ALLOW_PAGE_LOCKS",di.table_name as "TABLE_NAME",
      case when di.index_type='IOT - TOP' then 'CLUSTERED' else 'NONCLUSTERED' end AS "INDEX_TYPE"
      from dba_indexes di
      join dba_ind_columns ci on ci.index_owner = di.owner
      and ci.index_name = di.index_name

      where di.table_owner = p_table_owner
      and di.table_name = p_table_name
      and partitioned = 'NO'

      union all
      select
      di.index_name as "INDEX_NAME", case when di.index_type='IOT - TOP' then 'clustered' else 'nonclustered' end ||
      case when di.uniqueness='UNIQUE' and substr(di.index_name, 1, 3) = 'PK_' then', unique, primary key'
      when di.uniqueness='UNIQUE' then ', unique' end ||
      case when di.uniqueness='NONUNIQUE' then '' else '' end || ' located on PRIMARY'
      AS "INDEX_DESCRIPTION",ci.column_name as "INDEX_KEYS",null as "INCLUDE_COLS",null as "INDEX_FILTER",null as "DATA_COMPRESSION",null as "ALLOW_PAGE_LOCKS",di.table_name as "TABLE_NAME",
      case when di.index_type='IOT - TOP' then 'CLUSTERED' else 'NONCLUSTERED' end AS "INDEX_TYPE"
      from dba_indexes di
      join dba_ind_partitions dip on dip.index_owner = di.owner
      and dip.index_name =
      di.index_name
      join dba_ind_columns ci on ci.index_owner = di.owner
      and ci.index_name = di.index_name
      where di.table_owner = p_table_owner
      and di.table_name = p_table_name
      and di.partitioned = 'YES'

      and dip.composite != 'YES'
      union all
      select
      di.index_name as "INDEX_NAME", case when di.index_type='IOT - TOP' then 'clustered' else 'nonclustered' end ||
      case when di.uniqueness='UNIQUE' and substr(di.index_name, 1, 3) = 'PK_' then', unique, primary key'
      when di.uniqueness='UNIQUE' then ', unique' end ||
      case when di.uniqueness='NONUNIQUE' then '' else '' end || ' located on PRIMARY'
      AS "INDEX_DESCRIPTION",ci.column_name as "INDEX_KEYS",null as "INCLUDE_COLS",null as "INDEX_FILTER",null as "DATA_COMPRESSION",null as "ALLOW_PAGE_LOCKS",di.table_name as "TABLE_NAME",
      case when di.index_type='IOT - TOP' then 'CLUSTERED' else 'NONCLUSTERED' end AS "INDEX_TYPE"
      from dba_indexes di
      join dba_ind_partitions dip on dip.index_owner = di.owner
      and dip.index_name =
      di.index_name
      join dba_ind_subpartitions dis on dis.index_owner = di.owner
      and dis.index_name =
      di.index_name
      and dis.partition_name =
      dip.partition_name
      join dba_ind_columns ci on ci.index_owner = di.owner
      and ci.index_name = di.index_name
      where di.table_owner = p_table_owner
      and di.table_name = p_table_name
      and di.partitioned = 'YES'
      and dip.composite = 'YES';
      END;


      Currently if i execute this on table(to get indexes information)..it displays me 2 rows for indexes(multi columns) like in below output..i.e it returns 2 seperate rows for index 24290...but i would like it to return only one row per index..In case of multiple columns, the INDEX_KEYS column should reflect all the columns as comma separated list. In this particular this column should appear as:
      TESTSMALLINT, BIGINTTEST



      SPROC should be compatible with versions lesser than 11g too..



      Multi column indexes









      share














      Code of my stored procedure..



      create or replace procedure test_1(p_table_owner in varchar2, p_table_name in varchar2, p_result out sys_refcursor)
      AS
      BEGIN
      OPEN p_result FOR
      select
      di.index_name as "INDEX_NAME", case when di.index_type='IOT - TOP' then 'clustered' else 'nonclustered' end ||
      case when di.uniqueness='UNIQUE' and substr(di.index_name, 1, 3) = 'PK_' then ', unique, primary key'
      when di.uniqueness='UNIQUE' then ', unique' end ||
      case when di.uniqueness='NONUNIQUE' then '' else '' end || ' located on PRIMARY'
      AS "INDEX_DESCRIPTION",ci.column_name as "INDEX_KEYS",null as "INCLUDE_COLS",null as "INDEX_FILTER",null as "DATA_COMPRESSION",null as "ALLOW_PAGE_LOCKS",di.table_name as "TABLE_NAME",
      case when di.index_type='IOT - TOP' then 'CLUSTERED' else 'NONCLUSTERED' end AS "INDEX_TYPE"
      from dba_indexes di
      join dba_ind_columns ci on ci.index_owner = di.owner
      and ci.index_name = di.index_name

      where di.table_owner = p_table_owner
      and di.table_name = p_table_name
      and partitioned = 'NO'

      union all
      select
      di.index_name as "INDEX_NAME", case when di.index_type='IOT - TOP' then 'clustered' else 'nonclustered' end ||
      case when di.uniqueness='UNIQUE' and substr(di.index_name, 1, 3) = 'PK_' then', unique, primary key'
      when di.uniqueness='UNIQUE' then ', unique' end ||
      case when di.uniqueness='NONUNIQUE' then '' else '' end || ' located on PRIMARY'
      AS "INDEX_DESCRIPTION",ci.column_name as "INDEX_KEYS",null as "INCLUDE_COLS",null as "INDEX_FILTER",null as "DATA_COMPRESSION",null as "ALLOW_PAGE_LOCKS",di.table_name as "TABLE_NAME",
      case when di.index_type='IOT - TOP' then 'CLUSTERED' else 'NONCLUSTERED' end AS "INDEX_TYPE"
      from dba_indexes di
      join dba_ind_partitions dip on dip.index_owner = di.owner
      and dip.index_name =
      di.index_name
      join dba_ind_columns ci on ci.index_owner = di.owner
      and ci.index_name = di.index_name
      where di.table_owner = p_table_owner
      and di.table_name = p_table_name
      and di.partitioned = 'YES'

      and dip.composite != 'YES'
      union all
      select
      di.index_name as "INDEX_NAME", case when di.index_type='IOT - TOP' then 'clustered' else 'nonclustered' end ||
      case when di.uniqueness='UNIQUE' and substr(di.index_name, 1, 3) = 'PK_' then', unique, primary key'
      when di.uniqueness='UNIQUE' then ', unique' end ||
      case when di.uniqueness='NONUNIQUE' then '' else '' end || ' located on PRIMARY'
      AS "INDEX_DESCRIPTION",ci.column_name as "INDEX_KEYS",null as "INCLUDE_COLS",null as "INDEX_FILTER",null as "DATA_COMPRESSION",null as "ALLOW_PAGE_LOCKS",di.table_name as "TABLE_NAME",
      case when di.index_type='IOT - TOP' then 'CLUSTERED' else 'NONCLUSTERED' end AS "INDEX_TYPE"
      from dba_indexes di
      join dba_ind_partitions dip on dip.index_owner = di.owner
      and dip.index_name =
      di.index_name
      join dba_ind_subpartitions dis on dis.index_owner = di.owner
      and dis.index_name =
      di.index_name
      and dis.partition_name =
      dip.partition_name
      join dba_ind_columns ci on ci.index_owner = di.owner
      and ci.index_name = di.index_name
      where di.table_owner = p_table_owner
      and di.table_name = p_table_name
      and di.partitioned = 'YES'
      and dip.composite = 'YES';
      END;


      Currently if i execute this on table(to get indexes information)..it displays me 2 rows for indexes(multi columns) like in below output..i.e it returns 2 seperate rows for index 24290...but i would like it to return only one row per index..In case of multiple columns, the INDEX_KEYS column should reflect all the columns as comma separated list. In this particular this column should appear as:
      TESTSMALLINT, BIGINTTEST



      SPROC should be compatible with versions lesser than 11g too..



      Multi column indexes







      oracle plsql plsql-developer





      share












      share










      share



      share










      asked 8 mins ago









      rock_techierock_techie

      14




      14






















          0






          active

          oldest

          votes











          Your Answer








          StackExchange.ready(function() {
          var channelOptions = {
          tags: "".split(" "),
          id: "182"
          };
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function() {
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled) {
          StackExchange.using("snippets", function() {
          createEditor();
          });
          }
          else {
          createEditor();
          }
          });

          function createEditor() {
          StackExchange.prepareEditor({
          heartbeatType: 'answer',
          autoActivateHeartbeat: false,
          convertImagesToLinks: false,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: null,
          bindNavPrevention: true,
          postfix: "",
          imageUploader: {
          brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
          contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
          allowUrls: true
          },
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          });


          }
          });














          draft saved

          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230112%2fcomma-separated-column-output-into-one-plsql-sproc%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown

























          0






          active

          oldest

          votes








          0






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes
















          draft saved

          draft discarded




















































          Thanks for contributing an answer to Database Administrators Stack Exchange!


          • Please be sure to answer the question. Provide details and share your research!

          But avoid



          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.


          To learn more, see our tips on writing great answers.




          draft saved


          draft discarded














          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230112%2fcomma-separated-column-output-into-one-plsql-sproc%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown





















































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown

































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown







          Popular posts from this blog

          Liste der Baudenkmale in Friedland (Mecklenburg)

          Single-Malt-Whisky

          Czorneboh