CODEWITHSUNDEEP
sqloracle-database
John Doe
John Doe

Reputation: 3233

Dynamic column alias in PLSQL

Is there a way to dynamically create an alias in pl/sql?

I have a table ABC

Field1    Field2   Field3  Field4
Joe       Doe      $45.00  XXX
Jane      Doe      $77.00  XXX

I created a new table MY_REF

TableName   FieldName    DisplayValue
ABC         Field1       First Name
ABC         Field2       Last Name
ABC         Field3       Tax Amount

I would like to create a proc that would take a table name (as incoming param) and return the data with the custom aliases.

So if I passed in 'ABC' my end result would be:

First Name   Last Name  Tax Amount
Joe          Doe        $45.00
Jane         Doe        $77.00

EDIT: (Attempt based on Metthew's suggestion)

PROCEDURE GET_CUSTOM_DATA (
                             i_TableName  IN  VARCHAR2,
                             o_Refcur OUT SYS_REFCURSOR
                        )
        IS
        query_string VARCHAR2(500);
        BEGIN                       
            SELECT 'SELECT ' || listagg(fieldname || ' AS "' || displayvalue || '"',',') within group (order by null) || ' FROM ' || i_TableName
            INTO query_string
            FROM ABC
            WHERE tablename = i_TableName

            OPEN :o_Refcur FOR query_string;

        END GET_CUSTOM_DATA_DATA;

Upvotes: 2

Views: 4881

Answers (1)

Matthew McPeak
Matthew McPeak

Reputation: 17934

In Oracle, the number and name of each column in your query must be defined when the query is parsed. Since you want these to be dynamic, then you must build dynamic SQL. Here is an example of how to do that that uses your data model.

The VARIABLE and PRINT commands are SQL*Plus commands, used here only as a convenient way to print the results. You don't need to use SQL*Plus to accomplish this. You could open a PL/SQL SYS_REFCURSOR variable instead.

VARIABLE rc REFCURSOR

DECLARE
  p_table_name VARCHAR2(30) := 'ABC';  -- This is the parameter you pass in
  l_sql VARCHAR2(4000);
BEGIN

SELECT 'SELECT ' || listagg(fieldname || ' AS "' || displayvalue || '"',',') within group (order by null) || ' FROM ' || p_table_name
INTO   l_sql
FROM   my_ref
WHERE  tablename = p_table_name;

OPEN :rc FOR l_sql;

END;
/


PRINT rc

Results:

PL/SQL procedure successfully completed.

First Name                     Last Name                      Tax Amount
------------------------------ ------------------------------ ------------------------------
Joe                            Doe                            $45.00
Jane                           Doe                            $77.00

Response to error

Sorry I seem to have confused things by involving SQL*Plus. To do it in PL/SQL, you don't use the SQL*Plus bind variables. See my commented version of your code, below.

PROCEDURE GET_CUSTOM_DATA (
                         i_TableName  IN  VARCHAR2,
                         o_Refcur OUT SYS_REFCURSOR
                    )
    IS
    query_string VARCHAR2(500);
    l_rc SYS_REFCURSOR;  -- This is in place of the SQL*Plus bind variable
    BEGIN                       
        SELECT 'SELECT ' || listagg(fieldname || ' AS "' || displayvalue || '"',',') within group (order by null) || ' FROM ' || i_TableName
        INTO query_string
        FROM my_ref  -- You had this as ABC.  ABC should be the value of i_TableName
        WHERE tablename = i_TableName

        -- Open into the PL/SQL ref cursor l_rc instead of the SQL*Plus 
        -- bind variable.
        OPEN l_rc FOR query_string;

        -- NOTE: you need to do something with l_rc: pass it back to the
        -- caller or process it yourself.

    END GET_CUSTOM_DATA_DATA;

Upvotes: 3

Related Questions