CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
bright
bright

Reputation: 7

Oracle sql developer| export from query to a table

Good day, In DBeaver db manager we are able to transfer data directly from query to a table created by us. We right click>execute>export to a table. Can anyone help me to do the same in oracle developer environment. Here I have created a table in the same connection,and want to store query results into table. Thank you

Upvotes: 0

Views: 324

Answers (1)

thatjeffsmith
thatjeffsmith

Reputation: 22457

Right click on your table.

Choose Table - Copy.

enter image description here

Give it a name, and check 'Include Data'

enter image description here

You can see the code we're about to run by clicking on the SQL panel.

declare
  l_sql varchar2(32767);
  c_tab_comment varchar2(32767);
  procedure run(p_sql varchar2) as
  begin 
     execute immediate p_sql;
     
  end; 
begin
run('create table "HR".BEERS_COPY as select * from "HR"."BEERS" where '||11||' = 11');
  begin
  select comments into c_tab_comment from sys.all_TAB_comments where owner = 'HR' and table_name = 'BEERS' and comments is not null;
  run('comment on table HR.BEERS_COPY is '||''''||REPLACE(c_tab_comment, q'[']', q'['']')||'''');

  for tc in (select column_name from sys.all_tab_cols where owner = 'HR' and table_name = 'BEERS')
      loop
     for c in (select comments from sys.all_col_comments where owner = 'HR' and table_name = 'BEERS' and column_name=tc.column_name) 
     loop 
     run ('comment on column HR.BEERS_COPY.'||tc.column_name||' is '||''''||REPLACE(c.comments, q'[']', q'['']')||'''');
   end loop;
  end loop;
  EXCEPTION
    WHEN OTHERS THEN NULL; 
  end;
end;

So basically a CTAS + we grab the comments for you.

Disclaimer: I'm the product manager for SQL Developer and a Oracle employee.

Upvotes: 1

Related Questions