Sqlplus

Hi,I previously used sqldeveloper and run sql and extract data in a readable format (csv,xls).

Now i am using sqlplus where the output is not formatted.

I need a way where i can place the sql in one file and get the output as i get in sqldeveloper.

Is it possible in a better ways?

DB:oracle 9

Thanks
LVL 5
magentoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
CSV is easy:
set pages 0
set lines 1000
set trimspool on
set feedback off
spool myfile.csv
select col1 || ',' || col2 || ',' || col3 from some_table;
spool off
magentoAuthor Commented:
Hi,

My sql is very complex around 70 columns and 200 lines of conditions.

Is there any generic way other than set columsep "," ?
slightwv (䄆 Netminder) Commented:
>>Is there any generic way other than set columsep "," ?

Using colsep is very problematic and I would suggest never using it.  For example: if the column widths aren't set up right and a column wraps.

There may be some XML tricks but straight forward string concatenation is normally the easiest.

Can you add the concatenation in a wrapper and make it easier to deal with?

select col1 || ',' || col2 || ',' || col3 from
(
      select ...  -- your complex SQL
)
/
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

MikeOM_DBACommented:
You could generate HTML reports from SQL*Plus which are easily loaded into Excel.
Or you can use this procedure from Ask Tom
Mark GeerlingsDatabase AdministratorCommented:
The number of lines of conditions in your "where" clause do not make any difference for creating CSV output from a query in SQL*Plus.  (They may make your query run slowly, but that is a different problem.)

The number of columns in your query: "around 70 columns" does add some complexity in SQL*Plus.  This value that slightwv suggested may not be large enough:

set lines 1000

That may need to be set quite a bit larger.  That "lines" value is actually for "linesize" (the number of characters per line) and not the number of "lines" in the report.  You will need that value set high enough to accommodate all of the characters you expect in those 70 columns, plus the separator characters.

Also, if any of your character columns include a comma in the data, you will need to wrap those columns with double quotes, more like this (for example, if "col2" is a character column that includes commas in the data):

select col1 || ',"' || col2 || '",' || col3 from some_table;

Note that number and date columns should *NOT* be wrapped with double quotes.  Onlv CHAR or VARCHAR2 columns need this.

(I started writing this a couple hours ago, but got interrupted before I posted it.  Maybe the suggestions from MikeOM will be easier for you.)
MikeOM_DBACommented:
Another option: use an open-source ETL tool like CloverETL or Pentaho both can query a table and generate csv, xls, and multiple other output formats.
And NO NEED TO CODE!!!
Geert GOracle dbaCommented:
why can't you use sql developer any more ?
if you can use sqlplus, you can use sql developer
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
If you need scripting, .NET languages including PowerShell scripts are better suited.
slightwv (䄆 Netminder) Commented:
>>if you can use sqlplus, you can use sql developer

Likely needed for automation/scripting.

>>If you need scripting, .NET languages including PowerShell scripts are better suited.

For a CSV file, I believe sqlplus is better suited.

Now if you want to generate native XLSX files and not txt files, then I would completely agree.  I use .Net, OpenXML and LINQ to work with native XLSX and DOCX files quite nicely!

I will also add that at times I've been able to perform ETL faster with .Net than I have sqlplus scripts.
MikeOM_DBACommented:
For a CSV file, I believe sqlplus is better suited.
Wrong, the ETL programs I suggested will do almost anything without having to write a line of code.
:p
slightwv (䄆 Netminder) Commented:
>>will do almost anything without having to write a line of code.

Free compared to a purchased product?  No comparison...

Even if the tool was purchased, going through a series of clicks to set up some job that magically extracts table data to a csv compared to a pretty straight forward select statement and some set commands also isn't a true comparison to me.

I really don't consider a sqplus script as 'code'.
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
The advantage with e.g. PowerShell is that you just stuff in a query and get the resiult as intended. No coding but the generic one.
MikeOM_DBACommented:
@slightwv
Both CloverETL and Pentaho have community editions (Free).

PS; I use Pentaho and I find it a great tool! For example, this would be the procedure to create csv file from a database table:

1) open new transformation (click on the icon)
2) Click table input and drag to canvas: select the connection and click on "Get Select statement" (Generates a select with all columns -- remove unwanted columns).
3) Click on text file output and drag into canvas, set the delimiters,  enclosure character and file name.
4) Connect both with a "Hop": click on one and position arrow in the other.
5) Click on "Run"
6) Magic occurs! -- You don't have to press any key on the keyboard.
Pentaho.JPG
magentoAuthor Commented:
Hi ,

First my sincere thanks to all the experts who took their time and guiding me.

I am working on live data so i cant use opensource tools.

Can u explain/direct to any source  on how i can write a sql code in powershell so i can get the csv/xlsx output?

Thanks
MikeOM_DBACommented:
I am working on live data so i cant use opensource tools.
BS, Who says you cannot use opensource tools? Just let them know they waste time (manhours) and $$ not taking advantage of ready-made free utilities.

Since I discovered Pentaho, the time for migration projects has been reduced from a couple of months down to one week!
:p
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
PowerShell script to run a SQL in Oracle (or MsSql) and export the result to a new Excel workbook:
<# Sql2Excel - generate Excel worksheet from SQL results #>

param(
  [String] $constr,   <# connection string;  ex. "server=Srv; database=master; Integrated Security=sspi"  #>
                      <#                         "server=OraServer/instance; User=username; Password=pwd" #>
  [String] $sqlCmd,   <# sql command;        ex. "SELECT top 10 name, type from sysobjects"               #>
  [String] $sqlFile,  <# sql cmd file;       ex. "C:\temp\mySql.sql"                                      #>
  [String] $xlsFile   <# XLS file to create; ex. "c:\temp\test"                                           #>
)
<# Note: The sqlFile parameter, if provided, will overrule sqlCmd parameter #>
if ($sqlFile)
{
  $sqlCmd = (Get-Content $sqlFile) -join " "
}

# $dbms = "MsSql"
$dbms = "Oracle"

if ($dbms -eq "Oracle") { [System.Reflection.Assembly]::LoadWithPartialName(“System.Data.OracleClient”) | Out-Null }

<# Excel initialization stuff #>
$excel = New-Object -ComObject excel.application
$excel.visible=$true
$wb = $excel.Workbooks.Add()            # empty, unnamed workbook

#   Delete all but one work sheet
$excel.DisplayAlerts = $false
for ($i = $wb.Worksheets.Count; $i -ge 2; --$i) {$wb.Worksheets.Item($i).Delete()}
$excel.DisplayAlerts = $true
$ws = $wb.Worksheets.Item(1)

<# SQL initialization stuff #>
$set = new-object system.data.dataset

<# now doing the work #>
if ($dbms -eq "MsSql" ) { (new-object System.Data.SqlClient.SqlDataAdapter ($sqlCmd, $constr)).Fill($set) | out-null }
if ($dbms -eq "Oracle") { (new-object System.Data.OracleClient.OracleDataAdapter ($sqlcmd, $constr)).Fill($set) | out-null }

$row = 1
$set.Tables[0] |
  % { 
    $col = 1
    foreach ($colname in $set.Tables[0].columns) {
      if ($row -eq 1) { 
        $ws.Cells.Item($row, $col).value2 = "$colname"
        $ws.Cells.Item($row, $col).Font.Bold = $true
      }
      $ws.Cells.Item($row+1, $col++).value2 = $_.$colname
    }
    $row++
  }

$ws.usedRange.EntireColumn.AutoFit() | Out-Null
$wb.SaveAs($xlsFile)
$excel.Quit()

Open in new window

If you call the script Sql2Excel, you run it as e.g.:
.\Sql2Excel.ps1 -conStr "server=OraServer/instance; User=username; Password=pwd" -SqlCmd "select * from dual" -xlsfile "C:\Temp\result.xlsx"

Open in new window

It can also make use of a SQL command file instead of the SQL string, but takes only the first result set for the export.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Exporting to CSV should be much more simple (untested code).
<# Sql2CSV - generate CSV file from SQL results, with full delimiters #>
param(
  [String] $constr,   <# connection string;  ex. "server=Srv; database=master; Integrated Security=sspi"  #>
                      <#                         "server=OraServer/instance; User=username; Password=pwd" #>
  [String] $sqlCmd,   <# sql command;        ex. "SELECT top 10 name, type from sysobjects"               #>
  [String] $sqlFile,  <# sql cmd file;       ex. "C:\temp\mySql.sql"                                      #>
  [String] $csvFile
)
<# Note: The sqlFile parameter, if provided, will overrule sqlCmd parameter #>
if ($sqlFile)
{
  $sqlCmd = (Get-Content $sqlFile) -join " "
}

# $dbms = "MsSql"
$dbms = "Oracle"

if ($dbms -eq "Oracle") { [System.Reflection.Assembly]::LoadWithPartialName(“System.Data.OracleClient”) | Out-Null }

<# SQL initialization stuff #>
$set = new-object system.data.dataset

<# now doing the work #>
if ($dbms -eq "MsSql" ) { (new-object System.Data.SqlClient.SqlDataAdapter ($sqlCmd, $constr)).Fill($set) | out-null }
if ($dbms -eq "Oracle") { (new-object System.Data.OracleClient.OracleDataAdapter ($sqlcmd, $constr)).Fill($set) | out-null }

$row = 1
$set.Tables[0] | Export-CSV -NoType $csvFile

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.