Working with data in Excel can be a pain, and sometimes I have to get out of Oracle and use Excel to do certain calculations. I used to use COUNTIF in Excel a lot to sum large columns of data
How to get time from a date
Just a reminder of how to show time from a date datatype. We all know how to strip the time off of a date by using the following command: SELECT trunc(sysdate) from dual; But the following takes just the time
How to delete duplicate rows
This article contains information on how to delete duplicate rows in a table using a PL/SQL procedure or block. The duplicate row problem occurs only when primary keys are not declared on table. Procedures Create a table test with two
How to change the Oracle 8i Charset
It is possible in Oracle 8i to directly change the character set of a database. This can only be done, however, if the new character set is a strict superset of the original character set. For instance, according to 8.1.6
How to resolve Oracle error ORA-01722 invalid number error
Find the number in error when you get an ORA-01722 invalid number error. select from where trim(translate(, ‘0123456789’, ‘ ‘)) IS NOT NULL This should return the fields that cannot be converted using TO_NUMBER or where text type data won’t
How to find out which Oracle version is installed on Unix
There are actually a couple of ways to do this. For versions prior to 8i, there’s a utility in the $ORACLE_HOME/orainst called ‘inspdver’. This will list each product and version on your server. You can also inspect the file $ORACLE_HOME/orainst/unix.rgs.
How to use BTITLE and TTITLE in Oracle
SQL*Plus isn’t known for its report-writing capabilities. But there are a few tricks you can use to spiff up the output of your PL/SQL queries. One of these tricks is setting automatic titles for your output. This can be done
How to list all tablespace names with information using SQL
The script below lists all tablespace names in the database and their associated information. Run the script using SQL*Plus as user DBA. set echo off column TSNAME format a31; column TYPE format a11; column EXT_MGT format a12; column STATUS format
How to set the SQL prompt to the current directory
Have you ever had a SQL*Plus window open, and did not remember what it’s current directory was? This tip will show a method to set your SQL*Plus prompt to include the current directory. The SET SQLPROMPT command can be used
How to create running totals with SQL
There are applications where the running total for a series of numbers needs to be calculated and displayed. While this might be normally considered something that would be done with 3GL programming techniques, it IS possible to calculate and query