ORACLE: Using Column Name returned by function in SELECT statement

Question

I want to automatically assign column aliases to multiple columns. My tables usually have min 20 columns. I am trying to avoid writing something like "column1 AS XXX_column1, column2 AS XXX_column2 ... x20".

I am new to PLSQL but I was hoping to use output from multiplied “column AS XXX_column” returned by function directly in standard SELECT clause.

So I have written a function (RETURN data type is VARCHAR2) to generate a column name. I want to use it directly in my SELECT statement. Below is simplified example of this:

--- Function

CREATE OR REPLACE FUNCTION simple RETURN varchar2 IS
BEGIN
  RETURN ‘my_column’;
END simple;

--- Select

SELECT simple FROM my_table;

This does not work. It seems that output from function is passed as a quotation i.e.

SELECT ‘my_column’ FROM my_table;

So the output from SELECT is a list of rows populated with values my_table:

COLUMN  simple
ROW1    my_column
ROW2    my_column
ROW3    my_column

Can please someone help me with this?

Instead of posting some obscure code and expecting us to guess what you're trying to achieve why don't you just explain the scenario? — APC
– APC, Commented Feb 17, 2013 at 15:21
I want to automatically assign column aliases to multiple columns. My tables usually have min 20 columns. Writing something like "column1 AS XXX_column1, column2 AS XXX_column2 ... x20" is what I am trying to avoid. I am new to PLSQL but I was hoping to use output from multiplied “column AS XXX_column” returned by function directly in standard SELECT clause. — zwornik
– zwornik, Commented Feb 17, 2013 at 22:25
So your explanation reveals a question worth answering. Unfortunately the question has already been closed so we can't answer it unless people vote to reopen. The teaching here is, always explain what you're trying to do. Bad or unclear questions have a short lifespan. — APC
– APC, Commented Feb 18, 2013 at 6:59

Sebas · Accepted Answer · 2013-02-17 13:50:41Z

2

CREATE OR REPLACE FUNCTION simple RETURN varchar2 IS
BEGIN
  RETURN ‘my_column’;
END simple;


--- Select
DECLARE
    myCol VARCHAR2(128);
BEGIN
    myCol := simple;
    EXECUTE IMMEDIATE 'SELECT ' || myCol || ' FROM my_table';
END;

answered Feb 17, 2013 at 13:50

Sebas

21.6k9 gold badges60 silver badges109 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

zwornik Over a year ago

Thanks for quick reply. SELECT statement that you provided is inside PLSQL block. What I am looking for is to use my function in standard SQL (SELECT simple FROM my_table). Is it possible to do this? Maybe use different data type returned by function. I am just guessing.

Sebas Over a year ago

I'm afraid dynamic sql is not standard sql.

Sebas Over a year ago

what is the context? There's always a solution

zwornik Over a year ago

I want to modify all column names in given table. Using aliases it’s not handy when there are lots of columns.

Plouf Over a year ago

@zwornik Use a view with column names you like then.

|

Collectives™ on Stack Overflow

ORACLE: Using Column Name returned by function in SELECT statement

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related