How can i get data from db table without specific column name on PLSQL

I have a table like;

when i try "select * from my_table" query takes long time because of huge pl_out column.

i dont prefer "select ID,Name,Year from my_table" way. Maybe sometimes my table will have many colums.

so how can i get data from db table without specific column name ? is there any way ?

like "select * from my_table where column_name!='pl_out'"
it dosent work ([Error] Execution (9: 67): ORA-00904: "COLUMN_NAME": invalid identifier)

thanks !

I try get data from db table without specific column name

> How can I get data from db table without specific column name?

Make the column INVISIBLE then it will be excluded from SELECT * FROM table_name; but you can still select it if you explicitly name it.

For example:

CREATE TABLE table_name (ID, Name, Year, pl_out) AS
SELECT 1, 'Alice', 1900, 'A big column you do not want.' FROM DUAL;

ALTER TABLE table_name MODIFY pl_out INVISIBLE;

Then:

SELECT * FROM table_name;

Outputs:

And:

SELECT t.*, pl_out FROM table_name t;

Outputs:

fiddle

You may create a Polymorphic Table Function available since 18c that will skip columns you specify.

Note:

This approach should be used carefully for very limited cases, because explicit is better than implicit. Either specify star (*) to designate that you want everything or specify columns you want to specify what is expected to be.

Everything except some may be okay as intermediate steps of data transformation where you define those extra columns right in the transformation (for example, union of two CTEs with adjustment of few columns like in unpivot). Otherwise you cannot know the future and cannot guarantee that new columns in the table are those you really want/expect: all except one means everything in the entire Universe except this particular entity.

PTF implementation is in below code:

>
> create package pkg_projection_ptf as
> function describe(
> p_table in out dbms_tf.table_t,
> p_exclude_cols dbms_tf.columns_t
> ) return dbms_tf.describe_t;
> end pkg_projection_ptf;/
>
>
> create package body pkg_projection_ptf as
> function describe(
> /*Table identifier*/
> p_table in out dbms_tf.table_t,
> /*List of column identifiers*/
> p_exclude_cols dbms_tf.columns_t
> ) return dbms_tf.describe_t
> as
> begin
> for i in 1..p_table.column.count loop
> for j in 1..p_exclude_cols.count loop
> /*pass_through = visible for subsequent processing.
> Is TRUE by default, so we need to make it FALSE when required*/
> p_table.column(i).pass_through := p_exclude_cols(j) != p_table.column(i).description.name;
> exit when p_table.column(i).pass_through = false;
> end loop;
> end loop;
>
> return null;
> end;
> end pkg_projection_ptf;/
>

>
> /*Create PTF that is implemented by the package*/
> create function f_projection_exclude(
> p_table in out table,
> p_exclude_cols columns
> )
> return table
> pipelined row polymorphic
> using pkg_projection_ptf;/
>

Test the function:

>
> create table sample_table (col1, col2, col3) as
> select 1, 2, 3 from dual
>

>
> select *
> from f_projection_exclude(
> /*Table name and column names are identifiers here, not text literals*/
> p_table => sample_table,
> p_exclude_cols => columns(col2, col3)
> )
>
> | COL1 |
> | -----|
> | 1 |
>
>
> select *
> from f_projection_exclude(
> p_table => sample_table,
> p_exclude_cols => columns(col1)
> )
>
> | COL2 | COL3 |
> | -----|------|
> | 2 | 3 |
>

fiddle

Or the same with SQL Macro available since 19.7 (seems that DBMS_TF parameters are available since 21c, but I have no 19.7 to test this).

>
> with function f(
> p_tab dbms_tf.table_t,
> p_cols dbms_tf.columns_t
> )
> return varchar2
> sql_macro(table)
> as
> l_keep_col boolean;
> l_cols varchar2(1000);
> l_stmt varchar2(1000);
> begin
> for i in 1..p_tab.column.count loop
> l_keep_col := true;
> for j in 1..p_cols.count loop
> if p_tab.column(i).description.name = p_cols(j) then
> l_keep_col := false;
> exit;
> end if;
> end loop;
>
> if l_keep_col then
> l_cols := l_cols || ',' || p_tab.column(i).description.name;
> end if;
> end loop;
>
> l_stmt := 'select ' || trim( both ',' from l_cols) || ' from p_tab';
> dbms_output.put_line(l_stmt);
> return l_stmt;
> end;
>
> select *
> from f(sample_table, columns(col2))
>
> | COL1 | COL3 |
> | ----:|----:|
> | 1 | 3 |
>
> status
>
> dbms_output:
> select "COL1","COL3" from p_tab
>

fiddle

As you have many tables that need a kind of exclusion of certain column(s) maybe you could define and store those tables and exclusions in a table. Below is an example of such a table (I named it SQL_EXEC) showing three different types of excluding a column. The SQL_SELECT column will be updated later using a function. Here is the SQL_EXEC table:

The function that generates the SQL Select statement for certain exclusion could be like below:

Create or Replace 
FUNCTION Get_SQL(p_owner VarChar2, p_table VarChar2, p_exclude_type VarChar2, p_exclude_value VarChar2) RETURN VarChar2 AS 
BEGIN
    Declare
        mRet         VarChar2(1000) := '';
        mExclusion   VarChar2(64);
        mSQL         VarChar2(1000);
        mCursor      SYS_REFCURSOR;
        mColumn      VarChar2(32);
        sq           VarChar2(1) := '''';
    Begin
        mExclusion := p_exclude_type || ' != ' || sq || p_exclude_value || sq;
        mSQL := 'Select COLUMN_NAME From all_tab_columns Where OWNER = ' || sq || p_owner || sq || ' And TABLE_NAME = ' || sq || p_table || sq || ' And ' || mExclusion;
        Open mCursor For mSQL;
        Loop
            Fetch mCursor Into mColumn;
            Exit When mCursor%NOTFOUND;
            mRet := mRet || mColumn || ', ';
        End Loop;
        If Length(mRet) > 0 Then
            mRet := 'Select ' || SubStr(mRet, 1, Length(mRet) - 2)  || ' From ' || p_table;
        End If;
        RETURN mRet;
    End;
END Get_SQL;

After having your data in SQL_EXEC table you can update the SQL_SELECT column using the function. As a sample I used it on the TEST table.

UPDATE SQL_EXEC
    SET SQL_SELECT = Get_SQL(OWNER_NAME, TABLE_NAME, EXCL_TYPE, EXCL_VAL)

This will update all three rows with the same select statement derived from three different exclusions of PL_OUT column.

Select ID, A_NAME, A_YEAR From TEST

This could further be used either one by one or looped through in a dynamic way.