CODEWITHSUNDEEP
exceloracle-databaseoracle-sqldeveloperexport-to-excel
xGen
xGen

Reputation: 554

return column names along with result in oracle select query- to export to excel

I need to get the column names in the result set in oracle select query. When i copy the result set in Oracle SQL Developer to excel only the data gets copied. not column names.

I know i can use dba_tab_columns to get the column information of a table but i want a way to include it in the row format with the result. Also i do not want to hard code the column names with a union query as there are many tables i need to take and lot of columns.

Export option also doesnt work for me as i am connecting to a remote server using Citrix. so it saves to the remote machine path. not the local path

Upvotes: 3

Views: 2709

Answers (3)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

SQL Developer provides a lot of options. And you might be missing something, else the Export option to save the query result as Excel does the job.

  1. Run statement ( Ctrl + Enter)
  2. Right click on the Query Result and select Export

enter image description here

  1. In the Export Wizard, select excel 2003 +(xlsx) and make sure the check box of Header is ticked.

enter image description here

Upvotes: 3

OddDev
OddDev

Reputation: 3734

There's an awesomely easy way to achieve what you want. If I got you right, you copy/paste the results to excel, right?

However, if you want to have the full data (including the column names), you just have to execute your query as a script.

There's a button "Execute script" next to the "normal" "play button".

enter image description here

You'll get something like this as the result:

C1                      C2                            C3                        C4     C5
---------------------- ------------------------------ ------------------------- ----- ------ 
8                      SOMETHING                       1337                      A     1337

You are able to copy this output completely in your clipboard ans paste it in excel.

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172408

You can use the UNION trick:

SELECT 'column1',
       'column2',
       'column3',
       1 AS x
  FROM yourTable
UNION
SELECT column1,
       column2,
       column3,
       2 AS x
  FROM yourTable
ORDER BY x;

If the number of columns is huge then you can use the dba_tab_columns to get the column names from your table and do the UNION logic.

Upvotes: 2

Related Questions