CODEWITHSUNDEEP
oracle-databaseplsql
SASPYTHON
SASPYTHON

Reputation: 1621

How to use Variable in Alias Oracle PL/SQL

I try to use variable in the alias.

Is it possible not to change to dynamic SQL???

For example

get_this_year --> this is the function call this year, so 2018.

CREATE OR REPLACE PROCEDURE do_activity
v_cur_year    VARCHAR2(11);

BEGIN
v_cur_year := get_this_year;


select t.1 AS v_cur_year -- I wanna use in here
FROM table1

END do_activity

how can I use Variable as alias.

Thanks

Upvotes: 0

Views: 1621

Answers (1)

Hilarion
Hilarion

Reputation: 870

What you are asking for is not possible.

The column list, names, structure, etc. has to be known, when the query is parsed. Queries within PL/SQL are parsed, when PL/SQL code is parsed/compiled, so in your case, on procedure creation. (This obviously excludes dynamic queries, which are constructed in run-time, hence can't be parsed on PL/SQL compilation.)

You would have to use the dynamic SQL, to get the column name defined by the function result, but you already stated, that you do not want dynamic SQL.

There's a second issue with your code, although it may be a result of you simplifying the code. You are not capturing the query result in your procedure, which is obligatory in Oracle PL/SQL. You can't just run a query, and expect its result to be returned by running the procedure - it's not Transact-SQL. To return a data set from PL/SQL, you would have to write a tabular function (still, this would require stable data set structure, so no dynamic column naming) or you would have to use an OUT parameter of ref-cursor type.

Upvotes: 1

Related Questions