1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Dba_ind_expressions.column_expression as text?

Discussion in 'SQL PL/SQL' started by Schnitzel, Jun 6, 2018.

  1. Schnitzel

    Schnitzel Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Winnipeg, MB
    When I select from dba_ind_expressions, column_expression displays as a string; however, if I try to use a string function against column_expression, like this
    Code (SQL):
    SELECT LISTAGG(column_expression, ', ') WITHIN GROUP (ORDER BY column_expression)
      FROM dba_ind_expressions
    WHERE table_owner = 'USRUNTMST'
       AND index_name = 'UQUNTMSTOPTIONLIST_FEATURE_CI';
    I get an error saying
    inconsistent datatypes: expected NUMBER got LONG

    How can I manipulate the column_expression as text and capture it as text?

    Thanks.
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    773
    Likes Received:
    147
    Trophy Points:
    830
    Location:
    Russian Federation
    Code (SQL):

    SELECT listagg(column_expression, ', ') WITHIN GROUP (ORDER BY column_position)
    FROM
    xmltable( '/ROWSET/ROW'
    passing dbms_xmlgen.getXMLType(q'{
    select
    i.COLUMN_POSITION,
    column_expression
    from dba_ind_expressions i
    where table_owner = '
    USRUNTMST'
       AND index_name = '
    UQUNTMSTOPTIONLIST_FEATURE_CI'
    }'

    )
    COLUMNS
    COLUMN_POSITION NUMBER
    ,column_expression varchar2(255)
    )
     
     
    Schnitzel likes this.
  3. Schnitzel

    Schnitzel Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Winnipeg, MB
    Wow, this works.

    Is it possible to use varchar2 variables in a code block in place of the hard-coded values inside the getXMLType call? So instead of using 'USRUNTMST' and 'UQUNTMSTOPTIONLIST_FEATURE_CI' we'd see something like
    Code (SQL):

    SET SERVEROUTPUT ON

    DECLARE
       cOwner       VARCHAR2(255) := 'USRUNTMST';
       cIndexName   VARCHAR2(255) := 'UQUNTMSTOPTIONLIST_FEATURE_CI';
       cListAgg     VARCHAR2(4000);
    BEGIN
       SELECT LISTAGG(column_expression, ', ') WITHIN GROUP (ORDER BY column_position)
         INTO cListAgg
         FROM XMLTABLE('/ROWSET/ROW'
                       PASSING DBMS_XMLGEN.getXMLType(q'{
     select
     i.COLUMN_POSITION,
     column_expression
     from dba_ind_expressions i
     where table_owner = cOwner
        AND index_name = cIndexName
     }'
    )
                       COLUMNS COLUMN_POSITION NUMBER, column_expression VARCHAR2(255));
       DBMS_OUTPUT.PUT_LINE(cListAgg);
    END;
     
    (The above does not work. Says "error occurred in XML processing".)

    Thanks, Wayne
     
    Last edited: Jun 7, 2018