Daily Oracle

2021-11-22T17:11:00.005+08:00

test upload video

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.

Ensuring that a sql query returns all or nothing

2008-06-14T13:49:00.001+08:00

I have a little project which automatically returns the result from a sql query to an Excel worksheet.

I run this job recently and the user complained that the Excel output was unusual.

I did some investigation and found that the query did not run to completion. It bombed out midway because there was a division by zero error.

Because the job was automatic and it was run in Excel, I did not see the error message.

That got me thinking - if the output was empty, I would realize immediately that the something was wrong. When it returned an impartial set, only a user who analyzed can tell whether it was wrong or not.

So, I wanted the query to return all or nothing.

And that is quite easily done, just add an order by clause at the end of the query.

The order by clause will retrieve all the rows and then sort them. If it could not retrieve all the rows because some of the rows has, for example, a division by zero error, nothing will return. The query will then return all or nothing at all.

Technorati tags: sql, tip

Using ifile to centralise tnsnames.ora entries for multiple oracle homes

2007-05-23T14:32:00.001+08:00

Tom Kyte has a great tip for centralising tns entries in one tnsnames.ora for multiple oracle homes.

The summary - maintain all tns entries in 1 tnsnames.ora.

Let's say this file is at:

c:/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora

In all the other tnsnames.ora, delete every thing else and type in this:

ifile=c:/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora

Take note that the ifile parameter is not documented, according to Tom.

The other option is to set the TNS_ADMIN environment variable but this can cause a lot of confusion.

Imagine a situation that you did not know the TNS_ADMIN variable is set, you will be looking at all the tnsnames.ora file in all the oracle homes and wondering why all the homes use one tnsnames.ora.

The ifile option makes this explicit in the place where everyone will usually look.

MySql may soon have Oracle's external table

2007-05-02T08:53:00.001+08:00

It is called mysql-filesystem-engine.

from sqlldr (sql loader) to external table

2006-10-09T11:34:00.000+08:00

Prior to Oracle 9i, to import data from a text file, you will have to run sqlldr (sql loader) to load the data into a table.

Starting from Oracel 9i, you can use external table to read text file data from within sqlplus and you can use the sqlloader control file to help you.

Just run something like this:

sqlldr scott/tiger control=mycontrol.ctl EXTERNAL_TABLE=GENERATE_ONLY.

This will create a log file with :

1. CREATE DIRECTORY statement

2. CREATE EXTERNAL TABLE statement

3. sample INSERT statement

4. sample DROP TABLE statement

It's a quick way to move from sql loader to external table.

External tables have these benefits:

1. you do not have to import the data into a table to read it. You can just read from the text file as if it were a table

2. since external table can be used with SELECT, you can write a load program with either SQL or PL/SQL. Prior to Oracle 9i, it was often asked whether it was possible to invoke sql loader from within PL/SQL. With external table, you do not have to.

find gap in sequence with analytical function

2006-07-10T19:10:00.000+08:00

SQL> WITH aquery AS
2 (SELECT measurement_id after_gap,
3 LAG(measurement_id,1,0) OVER (ORDER BY measurement_id) before_gap
4 FROM sensor_data)
5 SELECT
6 before_gap, after_gap
7 FROM
8 aquery
9 WHERE
10 before_gap != 0
11 AND
12 after_gap - before_gap > 1
13 ORDER BY
14 before_gap;
BEFORE_GAP AFTER_GAP
---------- ----------
2 4
5 7

http://techrepublic.com.com/5110-9592-6088140.html

script to delete duplicates in a table

2006-06-19T12:53:00.000+08:00

DELETE FROM emp
WHERE ROWID IN
(SELECT ROWID
FROM (SELECT ROWID,
ROW_NUMBER() OVER (PARTITION BY empno ORDER BY EMPNO) rn
FROM emp2)
WHERE rn > 1);

source:techrepublic

This is another script to delete duplicates in a table, using analytical functions.

The table is emp and the criteria for whether the row is duplicate is the empno.

If your version of Oracle does not have analytical functions, you can use this instead

DELETE FROM emp e1
WHERE ROWID !=
(SELECT min(rowid)
FROM emp e2
where e2.empno = e1.empno);

From the tests I ran, the second script is faster.

technorati tags:oracle, sql

Blogged with Flock

using function and constant in sql loader

2006-03-28T14:14:00.000+08:00

After a long break from Oracle work, I recently had to load some data from Excel into an Oracle database.

I googled for a ready made solution but the Oracle expert says the easiest is to convert the Excel file into a csv and use sql loader to load the file.

In my solution, I wanted to be able to load data files that :
1. contain ','
2. contain date columns in this format '31-12-06' (31st Dec 06)

To fulfil 1., my control file cannot use the standard fields terminated by ',' quoted in most examples.
Instead my fields will be terminated by tab.
There were no examples on the internet that I could find.
I experimented with '\t' and it worked.

Secondly, I wanted sql loader to convert '31-12-06' to 31 Dec 06 before loading, otherwise I get the invalid date error.
I needed to use a function to process the field first.

GL_DATE "to_date(:gl_date, 'dd-mm-yy')" - worked

Thirdly, I populated a column with a constant value (C)

STATEMENT_BASIS constant 'C'

My eventual control file is like this:

load data
infile 'C:/1/sql/t.txt'
into table MY_TABLE
append
fields terminated by '\t'
trailing nullcols
(CSL_UID,
COMPANY,
RC_UID,
REPORT_CENTER_NAME,
BILL_CODE,
GROSS_WORKING_INTEREST,
PARTY_UID,
PARTY_NAME,
AMOUNT,
GL_DATE "to_date(:gl_date, 'dd-mm-yy')",
MAJOR_ACCOUNT,
LINK_INFO,
STATEMENT_BASIS constant 'C')

SQL Loader is a very useful and powerful tool.

2005-05-10T16:52:00.000+08:00

N dates on the go from DUAL

select * from (select TO_DATE ( '1-feb-04') + level from dual connect by level < 100)

2005-05-10T16:49:00.000+08:00

N numbers on the go from DUAL

select * from (select level from dual connect by level < N)

2005-04-08T10:30:00.001+08:00

Best of both search engines

Google + Yahoo in one search !!

2005-04-08T10:30:00.000+08:00

Best of both search engines

Google + Yahoo in one search !!

2005-02-24T10:48:00.000+08:00

Export or import with Oracle Data Pump

In Oracle 10g, exp and imp have been redesigned as the Oracle Data Pump (although Oracle still ships and fully supports exp and imp). If you're used to exporting exp and imp, the Data Pump command-line programs have a syntax that will look very familiar.

Data Pump runs as a job inside the database, rather than as a stand-alone client application. This means that jobs are somewhat independent of the process that started the export or import. One machine (say a scheduled job) could start the export, while another machine (such as a DBA's laptop) can check the status of the job. Since the job is inside the database, if you want to export to a file, the first thing that you must do is create a database DIRECTORY object for the output directory, and grant access to users who will be doing exports and imports:

create or replace directory dumpdir as 'c:\';
grant read,write on directory dumpdir to scott;

Once the directory is granted, you can export a user's object with command arguments that are very similar to exp and imp:

expdp scott/tiger directory=dumpdir dumpfile=scott.dmp

While the export job is running, you can press [Ctrl]C (or the equivalent on your client) to "detach" from the export job. The messages will stop coming to your client, but it's still running inside the database. Your client will be placed in an interactive mode (with Export> prompt). To see which jobs are running, type status. If you run expdp attach=, you can attach to a running job.

Data Pump doesn't necessarily have to write to files. Now there are options to allow you to export database objects directly into a remote database over SQL*Net. You simple specify the remote option with the connect string of the remote database. This is something like a one-time database replication job.

Data Pump is much faster than the old exp and imp client commands. One new feature that really helps make it faster is the "parallel" option. With this option, the Data Pump will pump data in four different threads. For example, I ran the following job, pressed [Ctrl]C, and queried the status of the background jobs:

expdp scott/tiger directory=dumpdir dumpfile=scott2.dmp parallel=4
job_name=scott2

Export: Release 10.1.0.2.0 - Production on Friday, 31 December, 2004 14:54

Copyright (c) 2003, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 -
Production
With the Partitioning, OLAP and Data Mining options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."SCOTT2": scott/******** directory=dumpdir
dumpfile=scott2.dmp parallel=4 job_name=scott2
Estimate in progress using BLOCKS method...

Export> status

Job: SCOTT2
  Operation: EXPORT
  Mode: SCHEMA
  State: EXECUTING
  Bytes Processed: 0
  Current Parallelism: 4
  Job Error Count: 0
  Dump File: C:\SCOTT2.DMP
& &  bytes written: 4,096

Worker 1 Status:
  State: EXECUTING

Worker 2 Status:
  State: WORK WAITING

Worker 3 Status:
  State: WORK WAITING

Worker 4 Status:
  State: WORK WAITING

Not only is the Data Pump running inside the database, but also, most of the command-line features are exposed from inside the database through a PL/SQL api, DBMS_DATAPUMP. For example, you can start the export job from a PL/SQL package with the following PL/SQL code:

declare
& &  handle number;
begin
& &  handle := dbms_datapump.open('EXPORT','SCHEMA');
& &  dbms_datapump.add_file(handle,'SCOTT3.DMP','DUMPDIR');
& &  dbms_datapump.metadata_filter(handle,'SCHEMA_EXPR','= ''SCOTT''');
& &  dbms_datapump.set_parallel(handle,4);
& &  dbms_datapump.start_job(handle);
& &  dbms_datapump.detach(handle);
end;
/

Check out Data Pump to learn about many of its other great new features. For instance, Data Pump contains features with the ability to rename datafiles, move objects to different tablespaces, or select schema objects or schemas using wildcard patterns or expressions. The Data Pump can also act as an interface to external tables (i.e., a table can be linked to data stored in a data pump export file like the Oracle Loader interface available since Oracle 9i).

Scott Stephens worked for Oracle for more than 13 years in technical support, e-commerce, marketing, and software development.

Desktop Search Engines - my favourite

2004-12-17T10:01:00.000+08:00

I have long been interested in Desktop Search products.
It's great for finding stuff on my PC without doing the Explorer find which can take a very long time.

These are the Desktop Search products I have tried and my take on them:

1. Copernic Desktop
  My favourite desktop search because it can index any type of text file. I have text files with .java, .sql, .jnl extensions and I can set up Copernic to index these files too
2. msn toolbar suite
  indexes only common files like microsoft excel, word, text files.
3. Google Desktop
  familiar interface but indexes only common files but not pdf
4. blinkx
  not stable, occasionally crashes on windows xp

10g explain plan output

2004-12-10T11:13:00.000+08:00

found a new explain plan statement.

in addition to normal expected explain plan output like operation, access plan, cost and rows returned,
it also includes predicate information like:

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("D"."OPERATOR_ID"="V"."FRANCHISE_ID" AND "D"."BILLING_NETWORK_OPERATOR_ID"="V"."NOP_ID")
     filter("D"."YEAR_MONTH_START_DATE">=TRUNC("V"."START_DATE") AND
     "D"."YEAR_MONTH_START_DATE"<=TRUNC(NVL("V"."END_DATE","D"."YEAR_MONTH_END_DATE")) AND
     NVL("D"."OP_VARIANCE_PERCENT","D"."BOP_VARIANCE_PERCENT")>=CASE WHEN
     ("V"."PREVIOUS_PERCENT"="V"."PERCENTAGE") THEN 0 WHEN ("V"."PREVIOUS_PERCENT"<"V"."PERCENTAGE") THEN
     "V"."PREVIOUS_PERCENT"+0.0000001 END AND "V"."PERCENTAGE">=NVL("D"."OP_VARIANCE_PERCENT","D"."BOP_VARIAN
     CE_PERCENT"))

The predicate information explains why a certain operation was taken at the step.

The script is simple:

SELECT plan_table_output
FROM table(dbms_xplan.display('plan_table', null,'SERIAL'));
-- parameters:
-- 1. plan TABLE name
-- 2. statement id -- Null will fetch the last explain plan
-- 3. level OF detail eg BASIC, SERIAL, TYPICAL, ALL

Scalar Subquery to avoid outer join

2004-11-08T10:03:00.000+08:00

An outer join is used when there are some rows in 1 table (say t) that are not matched in another table (say t_c1) but you still want to return rows from table t.
An outer join prevents the optimizer from using the most optimal execution path and thus impacts performance negatively.

With scalar subquery (highlighted in bold below), we can avoid outer joins like this:

select t.some_column ,
   (select t_c1.some_column,
       t_c1.some_other_column
       from t_c1
       on some_column = t_c1.some_column)
from t

The above returns rows even when t.some_column does not match t_c1.some_column achieving an optional join just like outer join.

Read more about Scalar subquery and outer join here.

Mindmapping tool

2004-11-02T10:29:00.000+08:00

A digression from this blog's theme.

I discovered Freemind yesterday.

Freemind is a tool for creating mind-maps on your PC.
Mind-mapping is an alternative technique pioneered by Tony Buzan for putting thoughts on paper.

I used mind-mapping in my university days and I did not do too badly using this then unconventional technique to study.

I am excited to use it for my work now.
Finding a tool to use it on my PC is great.

Explode range of dates

2004-10-20T10:35:00.000+08:00

Requires Oracle 9i and above

create or replace type dates_t as table of DATE;
/

create or replace function date_range_from_cursor ( p_cursor in sys_refcursor )
return dates_t
pipelined
as
    l_b DATE;
    l_e DATE;
    v_date DATE := NULL;
begin
    loop
        fetch p_cursor into l_b, l_e;
        exit when p_cursor%notfound;
        v_date := l_b;
        WHILE v_date <= l_e
        LOOP
          BEGIN
            pipe row(v_date);
            v_date := v_date + 1;
          END;
        END LOOP;
    end loop;
    close p_cursor;
    return;
end;
/

select *
from table
  (date_range_from_cursor(cursor(select sysdate, SYSDATE + 10 from dual
  UNION
  SELECT SYSDATE + 13, SYSDATE + 20 FROM dual)))
/
COLUMN_VA
---------
20-OCT-04
21-OCT-04
22-OCT-04
23-OCT-04
24-OCT-04
25-OCT-04
26-OCT-04
27-OCT-04
28-OCT-04
29-OCT-04
30-OCT-04
02-NOV-04
03-NOV-04
04-NOV-04
05-NOV-04
06-NOV-04
07-NOV-04
08-NOV-04
09-NOV-04

dbms_profiler for code coverage

2004-09-10T10:13:00.000+08:00

Prerequisite:
1. dbms_profiler is installed
2. current user has execute privilege on dbms_profiler
select, insert, update, delete privileges on
plsql_profiler_data
plsql_profiler_units
plsql_profiler_runs
3. synonyms (private or public (preferred) created

Steps:

1. exec dbms_profiler.start_profiler(run_comment IN VARCHAR2 := SYSDATE,
run_comment1 IN VARCHAR2 := '',
run_number OUT BINARY_INTEGER);

2. run the application

3. exec dbms_profiler.stop_profiler

Run this script to get code coverage:
Note: set value for :owner
:runid (this is the out variable in Step 1)

SELECT EXEC.unit_name, -- code coverage by package
ROUND (EXEC.cnt/total.cnt * 100, 1) "Code% coverage"
FROM (SELECT u.unit_name,
COUNT(1) cnt
FROM plsql_profiler_data d, plsql_profiler_units u
WHERE d.runid = :runid
AND u.runid = d.runid
AND u.unit_number = d.unit_number
AND u.unit_owner = UPPER(:owner)
GROUP BY u.unit_name) total,
(SELECT u.unit_name,
COUNT(1) cnt
FROM plsql_profiler_data d, plsql_profiler_units u
WHERE d.runid = :runid
AND u.runid = d.runid
AND u.unit_number = d.unit_number
AND u.unit_owner = UPPER(:owner)
AND d.total_occur > 0
GROUP BY u.unit_name) EXEC
WHERE EXEC.unit_name = total.unit_name

ADF UIX Component Guide

2004-09-09T19:35:00.000+08:00

Get ADF UIX Component Guide if required.

text in all_source ends with chr(10)

2004-09-09T14:12:00.000+08:00

Did you know that all_source.text ends with chr(10) ?

if you want to search for /*single line comment*/ in all_source,
this will not work:

select *
from all_source
where ltrim(text) like '/*%*/'

this will work

select *
from all_source
where ltrim(text) like '/*%*/' || chr(10)

Adopting an MVC approach to Oracle Development

2004-09-09T11:06:00.000+08:00

MVC is very popular in Java for separating the model from the view from the controller.
Its advantage is that it segments an application to distinct components - Model, View, Component.

We can adopt this segmented approach for pl/sql development.

Let's take an example that we want to build an application that will compare 2 tables for differences and populate a table for the differences.

Let's say the tables have these common columns
k1, k2, a1, a2

Step 1 - The V

Build a view based on a select statement that will identify the difference.

In our example the sql might look like this:

select k1, k2, a1, a2
from table1
minus
select k1, k2, a1, a2
from table2

We build a view for the above sql statement.
Let's call the view difference_v.
The view is also encapsulating our business rule for what differences are.

Step 2 - the business work

A package that uses the view
with pseudo logic like this:

for each row in difference_v
insert row info into difference_table
next

What have we achieved so far?
We have :
1. segmented the difference business rule in the difference_v
if the business rule changes, we just change the view creation select statement

2. segmented the 'what to do with difference' in the package in Step 2

Using this segmented approach, we can start with step 1 with certain assumptions which can be revised later and move on to Step 2.

dbms_profiler

2004-09-09T10:20:00.000+08:00

How to use dbms_profiler

use dbms_profiler to :
1. search out performance bottlenecks
2. analyse code coverage

7 Rules for Project Progress (moving forward)

2004-09-08T07:37:00.000+08:00

1. Untested code isn't done.
2. Unreleased code isn't done.
3. Undocumented code isn't done.
4. Set many, small, specific goals.
5. Do something every day. Finish something every week.
6. One feature finished is better than ten features planned.
7. Simplicity gives you more options than complexity.

Exception Handling

2004-08-20T08:32:00.000+08:00

Picked this up from asktom.oracle.com (http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1932043078402)

exceptions should be caught IF and ONLY IF you

a) expect them
b) can do something about them

eg: when no_data_found -- catch it, deal with it.

other exceptions that you cannot possibly recover from should not be caught and
the job queues will absolutely record the line number and everything exactly
right in the alert log.