Generate Excel and PDF in Oracle and Send Report by Email

First Install UTL_MAIL ### To install UTL_MAIL: [code language="sql"] sqlplus sys/**** @$ORACLE_HOME/rdbms/admin/utlmail.sql @$ORACLE_HOME/rdbms/admin/prvtmail.plb [/code] ### Create a Reporting User ### [code language="sql"] create user tctreport identified by tctreport; grant connect to tctreport; grant dba to tctreport; sqlplus tctreport/tctreport [/code] ## Grant TCTREPORT User permission to execute UTL_MAIL ## [code language="sql"] sqlplus "/as sysdba" grant execute … Continue reading Generate Excel and PDF in Oracle and Send Report by Email

Delete Duplicate Rows Oracle

Check for Duplicate Row using Group By function This script deletes each row in the group after the first row. DELETE FROM our_table WHERE rowid not in (SELECT MIN(rowid) FROM our_table GROUP BY column1, column2, column3... ; delete FROM TXN_TABLE WHERE rowid not in (SELECT MIN(rowid) FROM TXN_TABLE GROUP BY TRNX_UID); select COUNT(*) FROM TXN_TABLE … Continue reading Delete Duplicate Rows Oracle

Generate DDL from a Data Pump Export File

-- Get DDL from impdp -- The below command will not import the dump, but will just create a .sql file with the DDL statements impdp system/**** directory=import_dir dumpfile=IMPORT.DMP sqlfile=getddl.sql

ORA-01476: divisor is equal to zero

This error occurs when an expression is divided by zero. It is considered a logical error in Oracle. In mathematics, division by zero is division where the divisor (denominator) is zero. Such a division can be formally expressed as a/0 where a is the dividend (numerator). Whether this expression can be assigned a well-defined value … Continue reading ORA-01476: divisor is equal to zero

Generate Insert Statements Dynamically

-- Insert statements dynamically -- Select 'insert into abc values('||col1||','||col2||','||col3||','||col4||');' from xyz;    

Generating HTML Reports from SQL*Plus

This is a script to query a table, generate the result in HTML and send an email using mailx for the report.   #!/bin/ksh . $HOME/.bash_profile NOW=$(date +"%a %b %e %T %Y") MON=$(date +"%b %Y") MON2=$(date +"%b%Y") HOST=$(hostname) cd $HOME/scripts rm -rf  Report*.html sqlplus -s /NOLOG << EOF > /tmp/log.txt CONNECT abc/******** set pagesize 1000 … Continue reading Generating HTML Reports from SQL*Plus

Select Random Rows from Table in Oracle

To select 10 random records from a table in Oracle SELECT * FROM ( SELECT * FROM TABLE_NAME ORDER BY dbms_random.value ) WHERE rownum <= 10 ;

Check size of table in Oracle

SQL Query to check size of Table   -- Check Size in GB SELECT bytes/1024/1024/1024 FROM USER_SEGMENTS WHERE SEGMENT_NAME in ('TABLE1','TABLE2');   -- Check Size in MB SELECT bytes/1024/1024 FROM USER_SEGMENTS WHERE SEGMENT_NAME in ('TABLE1','TABLE2');   -- Check Size in KB SELECT bytes/1024 FROM USER_SEGMENTS WHERE SEGMENT_NAME in ('TABLE1','TABLE2');

Drop Database Oracle

SQL> conn / as sysdba Connected to an idle instance. SQL> startup mount ORACLE instance started. SQL> drop database; drop database * ERROR at line 1: ORA-12719: operation requires database is in RESTRICTED mode SQL> alter system enable restricted session; System altered. SQL> drop database; Database dropped.

Delete large number of records Oracle

When a large number of records neds to be deleted from a table. If you do a normal delete it will consume the UNDO tablespace. To avoid such a scenario in a production environment. You can use below procedure to commit frequently. In my opinion it is better to schedule the procedure as a daily … Continue reading Delete large number of records Oracle