Stored procedure and data export automation

Question

I've created a stored procedure in Oracle SQL Developer that includes a set of queries. Every query returns a table that contains a large amount of data (over 2 million rows per table => the manual export already causes problems due to the size of data).

I want to automate the stored procedure and the export of every result table to a separate Excel sheet in one batch.

Thank you

If there is a particular piece of code you need help with please post that code and we can try help you with that problem. — Rene, Jan 15 '20 at 13:41
Sure, if you want i'll post the procedure but it's just a simple procedure fild with a lot of SELECT queries. The 2 problemes i have is how to automate the lunch and export in one batsh and how to include in that batsh a solution to be able to export a huge amount from oracle to excel @Rene — koutheir, Jan 15 '20 at 13:46
both your OS and your DB offer scheduling systems, you could use sqlcl/sqlplpus and a bash script plus crontab or you could use a DBMS_SCHEDULER job in the database to run your stored procedure — thatjeffsmith, Jan 15 '20 at 14:04
it's not only a problem a procedure scheduling @thatjeffsmith — koutheir, Jan 15 '20 at 14:10
@koutheir right, so i've given you half your answer. also you'll get better answers if you share your code/work with specific code questions — thatjeffsmith, Jan 15 '20 at 14:23
@thatjeffsmith; yes i know and thank you ;). as for the code would you like that i post the procedure? — koutheir, Jan 15 '20 at 14:29

score 1 · Answer 1 · answered Jan 15 '20 at 14:12

1

To export a huge amount of data to an XLS file you can use:

https://www.oracle.com/webfolder/community/oracle_database/3784064.html

Also explained in

Create an Excel File (.xlsx) using PL/SQL

answered Jan 15 '20 at 14:12

Thomas Strub

1,275
7
20

score 1 · Accepted Answer · answered Jan 16 '20 at 11:32

I created a powershell script that exports all the tables of the specified user into separate csv files.

You can specify the desired list of tables in the query. select TNAME from tab where tabtype='TABLE' and tname not like 'BIN$%' If the table has 0 rows, then the csv file is not created. In the script, you can specify the date format and code page for the strings.

$NLS_NUMERIC_CHARACTERS=".,"
$NLS_DATE_FORMAT="DD.MM.YYYY HH24:MI:SS"
$NLS_LANG="AMERICAN_AMERICA.UTF8"

The script exports all user tables to separate CSV files.

.\run_export_all_tables.ps1 -username SCOTT -password tiger -connect_string ORCL -csv_dir_path C:\upwork\powershell_sqlplus_export_csv\csv\  -log_file log_file.log


<#
    .SYNOPSIS
     The script exports all user tables to separate CSV files.
     Author: Dmitry Demin dmitrydemin1973@gmail.com


    .DESCRIPTION
     In the script, the format for displaying the date and decimal separator is configured.

    .PARAMETER username
    Specify the username  for example SCOTT

    .PARAMETER password
    Specify the password  for example TIGER

    .PARAMETER connect_string
    Specify the connect_string(TNS alias)  for connect to database from $ORACLE_HOME/network/admin/tnsnames.ora.  

    .PARAMETER csv_dir_path
    Specify the csv directory for csv files

    .PARAMETER  log_file
    Specify the  log file for this script.  


    .EXAMPLE
      The script exports all user tables to separate CSV files.
    .\run_export_all_tables.ps1 -username SCOTT -password tiger -connect_string ORCL -csv_dir_path C:\upwork\powershell_sqlplus_export_csv\csv\  -log_file log_file.log
#>


param(
[string]$username = "scott", 
[string]$password = "tiger",
[string]$connect_string = "esmd",
[string]$csv_dir_path = "C:\upwork\powershell_sqlplus_export_csv\csv\",
[string]$log_file = "C:\upwork\powershell_sqlplus_export_csv\log_file.log"
)
# Column separator for csv file 
$COLSEP=";"
# NLS_NUMERIC_CHARACTERS
$NLS_NUMERIC_CHARACTERS=".,"
$NLS_DATE_FORMAT="DD.MM.YYYY HH24:MI:SS"
# Log file 
$full_log_path=$log_file
# CSV directory
$full_csv_path=$csv_dir_path
#csv file extension
$csv_ext=".csv"
#Set NLS_LANG for session sqlplus 
#"RUSSIAN_CIS.UTF8"
#"RUSSIAN_CIS.CL8MSWIN1251"
#"AMERICAN_AMERICA.UTF8"
#$NLS_LANG="RUSSIAN_CIS.CL8MSWIN1251"
$NLS_LANG="AMERICAN_AMERICA.UTF8"

#Set NLS_LANG for session sqlplus 
[Environment]::SetEnvironmentVariable("NLS_LANG",$NLS_LANG , [System.EnvironmentVariableTarget]::PROCESS)
$env_path_NLS=[Environment]::GetEnvironmentVariable("NLS_LANG", [EnvironmentVariableTarget]::PROCESS)

echo "SET session NLS_LANG: $env_path_NLS" | tee-object -Append  -filepath $full_log_path


$sqlQuery_show_user_tables = 
@"
set heading off
set termout OFF
SET FEEDBACK OFF
SET TAB OFF
set pause off
set verify off
SET UNDERLINE OFF
set trimspool on
set timing off
set echo off
set linesize 1000
set pagesize 100
select  TNAME  from tab where tabtype='TABLE' and tname not like 'BIN$%'
;
exit
"@

$SqlQueryExportTable1 = 
@"
set heading off
set termout OFF
SET FEEDBACK OFF
SET TAB OFF
set pause off
set verify off
SET UNDERLINE OFF
set trimspool on
set timing off
set echo off
set linesize 10000
set pagesize 0
SET COLSEP '$COLSEP'
ALTER SESSION SET NLS_NUMERIC_CHARACTERS='$NLS_NUMERIC_CHARACTERS';
ALTER SESSION SET NLS_DATE_FORMAT='$NLS_DATE_FORMAT'; 
select * from   
"@

$SqlQueryExportTable2 =
@"

exit
"@


function Check_File
{
     param (
          [string]$pathfile 
     )


try {
$A=Get-Content -Path $pathfile  -ErrorAction Stop
}
catch [System.UnauthorizedAccessException]
{
#Write-Host "File $pathfile  is not accessible."
echo "File $pathfile  is not accessible." | tee-object -Append  -filepath $full_log_path


exit
}

catch [System.Management.Automation.ItemNotFoundException]
{
#Write-Host "File $pathfile  is not found."
echo "File $pathfile  is not found." | tee-object -Append  -filepath $full_log_path
exit
}
catch {
Write-Host "File $pathfile.  Other type of error was found:"
#Write-Host "Exception type is $($_.Exception.GetType().Name)"
   echo "Exception type is $($_.Exception.GetType().Name)" | tee-object -Append  -filepath $full_log_path
exit
}

}


echo "===========================================================================================" | tee-object -Append  -filepath $full_log_path



$date_time_start = Get-Date -Format "yyyy-MM-dd HH:mm:ss"            
$date_time_log = Get-Date -Format "yyyyMMddHHmmss"            

Write-host "Script start time : $date_time_start "
try
{
echo "Script start time :  $date_time_start ">>$full_log_path
}
catch {
Write-Host "Log File $full_log_path.  Other type of error was found:"
Write-Host "Exception type is $($_.Exception.GetType().Name)"
exit
}


chcp 1251

$sqlQuery =  $sqlQuery_show_user_tables

$sqlOutput = $sqlQuery | sqlplus -s  $username/$password@$connect_string

$UserList =$sqlOutput| where {$_-notlike "" }
$i=1

   echo  "Found tables for export : " | tee-object -Append  -filepath $full_log_path

foreach ($user_tab in $UserList)
{
   echo " $i $user_tab"  | tee-object -Append  -filepath $full_log_path
   $i=$i+1
}


foreach ($user_tab in $UserList)
{
$sqlQuery_show_table_all=""

$sqlQuery_show_table_all=$SqlQueryExportTable1+ $user_tab+ $SqlQueryExportTable2

$full_csv_path_file=$full_csv_path +  $user_tab + "_" + $date_time_log + $csv_ext

echo "-------------------------------------------------------------------------------------------"  | tee-object -Append  -filepath $full_log_path
echo "For table : $user_tab will be created new csv file: $full_csv_path_file" | tee-object -Append  -filepath $full_log_path


echo  "Script will run for table: $user_tab "  | tee-object -Append  -filepath $full_log_path

$sqlOutput_tab = $sqlQuery_show_table_all | sqlplus -s $username/$password@$connect_string
$sqlOutput_count = $sqlOutput_tab.count
 if ($sqlOutput_tab.count -gt 0) 
{
Out-File -filepath $full_csv_path_file -append -inputobject $sqlOutput_tab -encoding default
echo  "Exported rows:  $sqlOutput_count "  | tee-object -Append  -filepath $full_log_path
}
else
{
echo  "No exported rows: 0 row"  | tee-object -Append  -filepath $full_log_path
echo  "$full_csv_path_file file not created "  | tee-object -Append  -filepath $full_log_path
}

echo "-------------------------------------------------------------------------------------------"  | tee-object -Append  -filepath $full_log_path
}

For example output script

Script start time :  2020-01-16 14:25:28 
Found tables for export : 
 1 TEST_TABLE5
 2 TEST_TABLE4
 3 TEST_TABLE3
 4 TEST_TABLE2
 5 TEST_TABLE1
 6 TEST2
 7 TEST1
 8 SALGRADE
 9 EMP
 10 DEPT
 11 CL_TAB1
 12 BONUS
-------------------------------------------------------------------------------------------
For table : TEST_TABLE5 will be created new csv file: .\csv\TEST_TABLE5_20200116142528.csv
Script will run for table: TEST_TABLE5 
No exported rows: 0 row
.\csv\TEST_TABLE5_20200116142528.csv file not created 
-------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
For table : TEST_TABLE4 will be created new csv file: .\csv\TEST_TABLE4_20200116142528.csv
Script will run for table: TEST_TABLE4 
No exported rows: 0 row
.\csv\TEST_TABLE4_20200116142528.csv file not created 
-------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
For table : TEST_TABLE3 will be created new csv file: .\csv\TEST_TABLE3_20200116142528.csv
Script will run for table: TEST_TABLE3 
No exported rows: 0 row
.\csv\TEST_TABLE3_20200116142528.csv file not created 
-------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
For table : TEST_TABLE2 will be created new csv file: .\csv\TEST_TABLE2_20200116142528.csv
Script will run for table: TEST_TABLE2 
No exported rows: 0 row
.\csv\TEST_TABLE2_20200116142528.csv file not created 
-------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
For table : TEST_TABLE1 will be created new csv file: .\csv\TEST_TABLE1_20200116142528.csv
Script will run for table: TEST_TABLE1 
No exported rows: 0 row
.\csv\TEST_TABLE1_20200116142528.csv file not created 
-------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
For table : TEST2 will be created new csv file: .\csv\TEST2_20200116142528.csv
Script will run for table: TEST2 
Exported rows:  213 
-------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
For table : TEST1 will be created new csv file: .\csv\TEST1_20200116142528.csv
Script will run for table: TEST1 
Exported rows:  2 
-------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
For table : SALGRADE will be created new csv file: .\csv\SALGRADE_20200116142528.csv
Script will run for table: SALGRADE 
Exported rows:  5 
-------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
For table : EMP will be created new csv file: .\csv\EMP_20200116142528.csv
Script will run for table: EMP 
Exported rows:  14 
-------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
For table : DEPT will be created new csv file: .\csv\DEPT_20200116142528.csv
Script will run for table: DEPT 
Exported rows:  7 
-------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
For table : CL_TAB1 will be created new csv file: .\csv\CL_TAB1_20200116142528.csv
Script will run for table: CL_TAB1 
Exported rows:  4 
-------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
For table : BONUS will be created new csv file: .\csv\BONUS_20200116142528.csv
Script will run for table: BONUS 
No exported rows: 0 row
.\csv\BONUS_20200116142528.csv file not created 
-------------------------------------------------------------------------------------------

Stored procedure and data export automation

2 Answers2