CODEWITHSUNDEEP
oracle-databaseplsql
Cyber
Cyber

Reputation: 15

error ORA-00998: must name this expression with a column alias

I tried to create table from all_tab_columns but it throws me an error. like error ORA-00998: must name this expression with a column alias. i tried to figure it out but not working.

Declare

CURSOR c1 IS  

SELECT COLUMN_NAME FROM (select 'standard_hash(MY_NAME) AS MY_NAME' COLUMN_NAME from DUAL
UNION 
SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE TABLE_NAME='TABLE1' AND COLUMN_NAME<>'MY_NAME');

cols  c1%ROWTYPE;
sqlstmt  VARCHAR2(4000);

BEGIN
   OPEN c1;
     LOOP
         FETCH c1 into cols;
         EXIT WHEN c1%NOTFOUND;
         sqlstmt := sqlstmt ||cols.column_name||',';
      END LOOP;
   CLOSE c1;
   sqlstmt := 'CREATE TABLE TABLE2  AS SELECT '||substr(sqlstmt, 1, length(sqlstmt)-1)||' FROM TABLE1';
   DBMS_OUTPUT.PUT_LINE(sqlstmt);
   EXECUTE IMMEDIATE sqlstmt;
   EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('error '||sqlerrm);
END;
/

Upvotes: 0

Views: 607

Answers (2)

William Robertson
William Robertson

Reputation: 15991

I think this can be simplified somewhat.

You don't need to declare an explicit cursor and a record type and fetch each row into it, as this is already built into the language as the Cursor FOR loop construction:

declare
    sqlStmt long := 'create table table2 as select ';
begin
    for r in (
        select 'standard_hash(my_name) as my_name' column_name
        from   dual
        union
        select column_name
        from   all_tab_columns
        where  table_name = 'TABLE1'
        and    column_name <> 'MY_NAME'
    )
    loop
        sqlStmt := sqlStmt || r.column_name || ', ';
    end loop;

    sqlStmt := sqlStmt || rtrim(sqlStmt,', ') || ' from table1';

    dbms_output.put_line(sqlStmt);
    execute immediate sqlStmt;
end;
/

But you can do this without any loop, as the listagg function can already build a comma-separated list for you. It also makes it easier to retain the order of columns in the original table.

declare
    selectList  long;
    sqlStmt     long;
begin
    select listagg(
               case column_name
                   when 'MY_NAME' then 'standard_hash(my_name) as my_name'
                   else lower(column_name)
               end, ', '
           ) within group (order by column_id) as select_list
    into   selectList
    from   user_tab_columns c
    where  c.table_name = 'TABLE1';
    
    sqlStmt :=
        'create table table2 pctfree 0 nologging parallel as '||chr(10)||
        'select '||selectList||chr(10)||
        'from table1';

    dbms_output.put_line(sqlStmt||';');
    execute immediate sqlstmt;
end;

Upvotes: 0

Popeye
Popeye

Reputation: 35900

You just have to give the name to the expression as follows:

sqlstmt := 'CREATE TABLE TABLE2  AS SELECT '
           ||substr(sqlstmt, 1, length(sqlstmt)-1)
           ||' as column_name FROM TABLE1'; -- column_name will bre name of the column of new table

As it will become the name of the column of newly created table.

Upvotes: 1

Related Questions