Dump a table to CSV

csv procedure dump_table_to_csv

One thing that Oracle does not do very well is CSV exports. 

Used this procedure loads. Really handy.

You need to have DUMP_DIR set up as an Oracle directory with the correct permissions.


create or replace procedure dump_table_to_csv

( p_tname in varchar2,p_dir in varchar2, p_filename in varchar2 )

is

l_output utl_file.file_type;

l_theCursor integer default dbms_sql.open_cursor;

l_columnValue varchar2(4000);

l_status integer;

l_query varchar2(1000) default 'select * from ' || p_tname;

l_colCnt number := 0;

l_separator varchar2(1);

l_descTbl dbms_sql.desc_tab;

begin

l_output := utl_file.fopen( p_dir, p_filename, 'w' );

execute immediate 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss''';

dbms_sql.parse(l_theCursor,l_query,dbms_sql.native);

dbms_sql.describe_columns(l_theCursor,l_colCnt,l_descTbl);

for i in 1 .. l_colCnt loop

utl_file.put(l_output, l_separator || '"' || l_descTbl(i).col_name || '"' );

dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000 );

l_separator := ',';

end loop;

utl_file.new_line(l_output);

l_status := dbms_sql.execute(l_theCursor);

while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop

l_separator := '';

for i in 1 .. l_colCnt loop

dbms_sql.column_value(l_theCursor, i, l_columnValue );

utl_file.put(l_output, l_separator || '"' || l_columnValue || '"' );

l_separator := ',';

end loop;

utl_file.new_line(l_output );

end loop;

dbms_sql.close_cursor(l_theCursor);

utl_file.fclose(l_output );

execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';

exception

when others then

execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';

raise;

end;


SQL> exec dump_table_to_csv('ISMA.ORGANISATION','DUMP_DIR','organisation_$NOW.csv');
 
 
Hit this issue :
 
 SQL> exec dump_table_to_csv('LEGALDBA.NWL_LEGAL_FP_MINUTES','DATA_PUMP','NWL_LEGAL_FP_MINUTES_jul26.csv');
BEGIN dump_table_to_csv('LEGALDBA.NWL_LEGAL_FP_MINUTES','DATA_PUMP','NWL_LEGAL_FP_MINUTES_jul26.csv'); END;

*
ERROR at line 1:
ORA-29285: file write error
ORA-06512: at "SYS.DUMP_TABLE_TO_CSV", line 39
ORA-06512: at line 1

To fix :
 
 l_output := utl_file.fopen( p_dir, p_filename, 'w' );
 
to 
 
 l_output := utl_file.fopen( p_dir, p_filename, 'w',5000 );

but check space too as sometiumes the filesystem can fill and give the same error.





No comments:

RMAN duplicate

Example RMAN duplicate quick instructions.

Ensure you have a successfully completed full rman backup.

We were going from ASM to non ASM so followed this

https://mohamedazar.com/2010/12/17/duplicate-database-from-asm-to-non-asm/

or use the official Oracle website.

In the init.ora file you will need.

db_file_name_convert=’+DATA’,’/prod1/db/apps_st/’
log_file_name_convert =("+FRA","/prod1/db/apps/onlinelog/")

I created a skeletion init_dup file.

Start up Auxiliary instance in NOMOUNT state

Note that the Oracle SID is set to database you want to copy into i.e. the one for prod1 – 
you just need a basic skeleton init file to start the database in mount

Where xxxx is the oracle home

SQL*Plus: Release 12.1.0.1.0 Production on Thu Apr 9 13:16:50 2015

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile=/xxxx/dbs/initPROD1_dup

ORACLE instance started.

Run the rman script to duplicate the database. The rman duplicate does all the hard work.

Set the environment and run:

$ rman auxiliary / cmdfile=commands.txt

Where commands.txt  is :

set echo on;

run {
allocate auxiliary channel d1 type disk;
allocate auxiliary channel d2 type disk;
allocate auxiliary channel d3 type disk ;
allocate auxiliary channel d4 type disk ;
duplicate database to PROD1 backup location '/backups/masking/';

release channel d1;
release channel d2;
release channel d3;
release channel d4 ;
}



Startup / shutdown the database. 

Run some integrity checks. 
No comments:

Java in the database

Bit of java to run in the database…. quick instructions

[oracle@collabn1 java]$ cat status.java

import java.sql.*;
public class status {
public void printSuccess() {
System.out.println("Success!!!")
}
public void printdatafiles() {
System.out.println("Might do something here!");
}

public static void main(String[] args)
throws ClassNotFoundException, SQLException
{
Class.forName("oracle.jdbc.driver.OracleDriver");
// jdbc:oracle:thin:@host:port/service
String url = "jdbc:oracle:thin:@collabn-cluster-scan.racattack:1521/LONDON_SVC.RACATTACK:1521/LONDON_SVC.RACATTACK";
Connection conn =
DriverManager.getConnection( "jdbc:oracle:thin:@collabn-cluster-scan.racattack:1521/LONDON_SVC.RACATTACK", "system", "racattack");
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
ResultSet rset =
stmt.executeQuery("select BANNER from SYS.V_$VERSION");
while (rset.next()) { System.out.println (rset.getString(1)); }
stmt.close();
System.out.println (" Global Name");
Statement stmt2 = conn.createStatement();
ResultSet rset2 =
stmt2.executeQuery("select * from global_name");
while (rset2.next()) { System.out.println (rset2.getString(1)); }
stmt2.close();
System.out.println (" Datafiles");
Statement stmt3 = conn.createStatement();
ResultSet rset3 =
stmt3.executeQuery("select * from dba_data_files");
while (rset3.next()) { System.out.println (rset3.getString(1)); }
stmt3.close();
status mystatus=new status();
mystatus.printSuccess();
mystatus.printdatafiles();
}
}

-tell java where the classes live, i.e. status and odbjc oracle connection class

[oracle@collabn1 java]$ env | grep CLASS

CLASSPATH=/u01/jdk1.8.0_144/jre/lib/ext:/home/oracle/java

Run java compiler to compile the java code

[oracle@collabn1 java]$ /u01/jdk1.8.0_144/bin/javac status.java

And execute it

[oracle@collabn1 java]$ /u01/jdk1.8.0_144/bin/java status

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

PL/SQL Release 12.1.0.2.0 - Production

CORE 12.1.0.2.0 Production

TNS for Linux: Version 12.1.0.2.0 - Production

NLSRTL Version 12.1.0.2.0 - Production

Global Name

LONDON

Datafiles

+FRA/MADRID/4A9C024E8375174BE055000000000001/DATAFILE/system.317.938518113
+FRA/MADRID/4A9C024E8375174BE055000000000001/DATAFILE/sysaux.316.938518113
+DATA/MADRID/4A9C024E8375174BE055000000000001/DATAFILE/users.285.939218541

Success!!!

Might do something here!

[oracle@collabn1 java]$

[oracle@collabn1 java]$ . oraenv

ORACLE_SID = [oracle] ? MADRID

The Oracle base has been set to /u01/app/oracle

[oracle@collabn1 java]$ loadjava -u system/racattack@collabn-cluster scan.racattack:1521/LONDON_SVC.RACATTACK status.java

[oracle@collabn1 java]$ sqlplus system/racattack@collabn-cluster-scan.racattack:1521/LONDON_SVC.RACATTACK

SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 7 13:47:50 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Last Successful login time: Mon Aug 07 2017 13:47:26 +01:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

SQL> create or replace procedure status -- or any name you want
as language java

name 'status.main(java.lang.String[])';

/

Procedure created.

SQL> > select text from user_source where name = 'status.java';

SQL> set head off

SQL> /

import java.sql.*;
public class status {
....
etc

80 rows selected.

SQL> exec status;

BEGIN status; END;

*

ERROR at line 1:

ORA-29532: Java call terminated by uncaught Java exception:

java.security.AccessControlException: the Permission (java.net.SocketPermission

192.168.78.253:1521 connect,resolve) has not been granted to SYSTEM. The PL/SQL
to grant this is dbms_java.grant_permission( 'SYSTEM',

'SYS:java.net.SocketPermission', '192.168.78.253:1521', 'connect,resolve' )

ORA-06512: at "SYSTEM.STATUS", line 1

ORA-06512: at line 1

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

[oracle@collabn1 ~]$ tnsping MADRID

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 07-AUG-2017 14:42:54

Copyright (c) 1997, 2014, Oracle. All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST=collabn-cluster-scan.racattack)(PORT = 1525)) (CONNECT_DATA = (SID= MADRID1)))

TNS-12541: TNS:no listener

[oracle@collabn1 ~]$ sqlplus system/racattack@collabn-cluster-scan.racattack:1521/LONDON_SVC.RACATTACK

SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 7 14:43:03 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Last Successful login time: Mon Aug 07 2017 14:19:01 +01:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

SQL> exec dbms_java.grant_permission( 'SYSTEM','SYS:java.net.SocketPermission','colabn-cluster-scan.racattack:1521', 'connect,resolve' )
PL/SQL procedure successfully completed.

NOTE from the error above:

exec dbms_java.grant_permission( 'SYSTEM', 'SYS:java.net.SocketPermission', '192.168.78.253:1521', 'connect,resolve' )

SQL> commit;
Commit complete.

SQL> exec status();

PL/SQL procedure successfully completed.

-no output so switch serveroutput on!!

SQL> set serveroutput on;

SQL> call dbms_java.set_output(50000);

Call completed.

SQL> exec status();

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

PL/SQL Release 12.1.0.2.0 - Production

CORE 12.1.0.2.0 Production

TNS for Linux: Version 12.1.0.2.0 - Production

NLSRTL Version 12.1.0.2.0 - Production

Global Name

LONDON

Datafiles

+FRA/MADRID/4A9C024E8375174BE055000000000001/DATAFILE/system.317.938518113
+FRA/MADRID/4A9C024E8375174BE055000000000001/DATAFILE/sysaux.316.938518113
+DATA/MADRID/4A9C024E8375174BE055000000000001/DATAFILE/users.285.939218541

Success!!!

Might do something here!

PL/SQL procedure successfully completed.

Et voilla….!!!!!!!
No comments:
Subscribe to: Posts (Atom)