How to execute Oracle Scheduler' Job from SQL Server.

In our work place , we use multiple DBMS to manage our data . Sometimes , we may need trigger a  job in one  DBMS  from another DBMS . If the these jobs are in the same DBMS / Environment , then these steps are straightforward . 

In some cases , we use Job Schedulers such as Autosys , Robot , etc to schedule the dependent jobs across various environments.  This will involve installing clients  / agents in all the environments . For some practical reasons  ( due to budgetary and regulatory concerns )  , this may not be feasible at times .

Sometimes , it is better to trigger the job in another DBMS from the host environment  . 

Let us say , we have a SQL Server as the host DBMS and we would like to trigger a Oracle ( Scheduler's ) Job.  

Here are the steps to achieve that . 

a) Create a linked server

b) Enable RPC out

c) Use Execute command to trigger the job. 

 Let us say , we have a job called 'LOAD_SALES_MONTHLY' in Oracle Database ( configured as 'ORACLE_LINKDB' in SQL Server ) .

The syntax for executing the oracle job from SQLPLUS

 exec dbms_scheduler.run_job('LOAD_SALES_MONTHLY')

The syntax for executing the above mentioned job from SQL Server is
 

Execute ( 'call dbms_scheduler.run_job(''LOAD_SALES_MONTHLY'')') AT ORACLE_LINKDB.

Note that , I am using "CALL" command to execute Oracle's Job.
 
 For steps a) and b) , please refer to one of my previous post (
http://mfzahirdba.blogspot.com/2012/04/execute-in-sql-server-2008.html) 



Note :
You can use the same syntax to execute Oracle's stored procedure / package .

Comments welcome.

Duplicating Databases / Restoring Database to New Host using RMAN.

The duplicating  of the database has gotten lot more easier with RMAN.

Assume we have copied the backup sets / backup control file / spfile  into "C:\FlashRecoveryArea" in the target server Or access to the backup sets / backup control file / spfile in the network share , the process is straight forward.   

Of course , there is little bit of leg work to be done before we issue RMAN's  duplicate database . 

Let us look at the steps below.


Step1 : Create a PFILE


In the pfile , we just the db name to "DUP2" ( this is going to be the name of the duplicated database".

C:>echo db_name='DUP2' > C:\app\mohideen\product\11.2.0\dbhome_1\database\INITDUP2.ORA

Step2:  Create Oracle Instance using ORADIM

Being the duplication is done in Windows environment , we would need to create the service using oradim utility.

C:>oradim -new -sid dup2 -intpwd Orapwd$dup2
Instance created.

Step3: Create the directories to hold the datafiles , logfiles , archived redo logs , ....

C:>mkdir  C:\app\mohideen\oradata\dup2\CONTROLFILE\
C:>mkdir C:\app\mohideen\oradata\DUP2\DATAFILE\
C:>mkdir C:\app\mohideen\oradata\DUP2\LOGFILE\
C:>mkdir C:\app\mohideen\oradata\dup2\ARCHIVELOG1\
C:>mkdir C:\app\mohideen\oradata\dup2\ARCHIVELOG2\
C:>mkdir C:\app\mohideen\FRA\dup2

Step4 : Set the SID to the destination database. 
 
C:\>set ORACLE_SID=dup2
 

 Step 5: Connect to the instance . 

As there is no database associated with this instance . it should say " Connected to an idle instance" as below.

C:\>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 25 10:36:03 201
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.


Step 6: Start the database in nomount state

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  313196544 bytes
Fixed Size                  2254704 bytes
Variable Size             255854736 bytes
Database Buffers           50331648 bytes
Redo Buffers                4755456 bytes
SQL>


Step 7:  Determine the location of the data files / log files from the source database.

SQL> select name   FROM v$datafile;

NAME
----------------------------------------
E:\DATA\DBPRD\SYSTEM01.DBF
E:\DATA\DBPRD\SYSAUX01.DBF
E:\DATA\DBPRD\UNDOTBS01.DBF
E:\DATA\DBPRD\USERS01.DBF
E:\DATA\DBPRD\SYSAUX02.DBF
E:\DATA\DBPRD\TBSDW.DBF

6 rows selected.

Get the log file's location .

SQL> select MEMBER  FROM v$logfile;

MEMBER
------------------------------------------------
E:\DATA\DBPRD\REDO03.LOG
E:\DATA\DBPRD\REDO02.LOG
E:\DATA\DBPRD\REDO01.LOG

These locations ( e:\data\dbprd)  need to be mentioned in the RCV File created in  the next step.

Step 8: Prepare the RCV file for RMAN to duplicate. 

Prepare the RCV that contains  the location for the  Control files  , flash recovery area , listener name , ADR location . Also , the file should contain the new locations for the data files / log files .

Finally , we need to provide the location where backups are stored ( c:\fastrecoveryarea ) in this case.


C:\dup_db>type dup.rcv
run
{
duplicate database to dup2
spfile
set control_files='c:\app\mohideen\oradata\dup2\CONTROLFILE\CONTROL1.CTL','c:\app\mohideen\oradata\dup2\CONTROLFILE\CONTROL2.CTL'
set db_file_name_convert='E:\DATA\DBPRD','c:\app\mohideen\oradata\DUP2\DATAFILE'
set log_file_name_convert='E:\DATA\DBPRD','c:\app\mohideen\oradata\DUP2\LOGFILE'
set db_recovery_file_dest='c:\app\mohideen\FRA\dup2\'
set LOG_ARCHIVE_DEST_2='location=c:\app\mohideen\oradata\dup2\ARCHIVELOG1\'
set LOG_ARCHIVE_DEST_3='location=c:\app\mohideen\oradata\dup2\ARCHIVELOG2\'
set diagnostic_dest='C:\app\mohideen'
set local_listener=''
backup location 'c:\FastRecoveryArea';


Step 9 : Issue RMAN  Duplicate Database

Here is where the rubber meets the road ; all of your leg work pays off . RMAN duplicates the database based on the RCV file . 

C:\dup_db>rman auxiliary  /  cmdfile dup.rcv  log  dup.log
RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15>
C:\dup_db>


Look for any errors in the log file ( dup.log ) . You may want to look at the dup.log to see what are the steps RMAN did to perform the duplication process. 

Now you have duplicated the database .  

Hope this was clear . 

Please email me for your feedback.

Equivalent of Oracle's rownum in SQL Server.

In oracle , we use rownum to display the running number for the result set . There is no direct equivalent in SQL Server . From SQL Server 2005 , it is easy to simulate this functionality with the help of Window Functions ( SQL Server ) / Analytical Functions ( Oracle) .

ORACLE
=======

SQL> set pagesize 90
SQL> select ename , rownum from scott.emp ;

ENAME          ROWNUM
---------- ----------
SMITH               1
ALLEN               2
WARD                3
JONES               4
MARTIN              5
BLAKE               6
CLARK               7
SCOTT               8
KING                9
TURNER             10
ADAMS              11
JAMES              12
FORD               13
MILLER             14

14 rows selected.


SQL Server
=========

C:\Users\zahir>sqlcmd -W
1> use EXSQL2008R2
2> go
Changed database context to 'ExSQL2008R2'.
1>
2> Select top 10 name into t from master.sys.tables;
3> go

1> drop table t;
2> go

1> Select top 10 name into t from master.sys.tables;
2> go

(6 rows affected)
1> Select * from t;
2> go
name
----
spt_fallback_db
spt_fallback_dev
spt_fallback_usg
spt_monitor
spt_values
MSreplication_options

(6 rows affected)





We use ROW_NUMBER function to get the desired result .

1> Select name ,
2> ROW_NUMBER() over ( partition by 1 order by (Select 1)  asc) as rownum
3> from t;
4> go
name rownum
---- ------
spt_fallback_db 1
spt_fallback_dev 2
spt_fallback_usg 3
spt_monitor 4
spt_values 5
MSreplication_options 6

(6 rows affected)

Comments Welcome.

In SQL Server , Primary Key needs to be Clustered Index ?

After browsing thru variuous SQL Server discussion forums ,
I found that there is a misunderstanding in the SQL Server community , that stands out.

"Primary Key needs to be a clustered index" .

In most of the cases , it makes sense. But it need not be .

Here are the steps to create a Primary key that is not a clustered index.

a) Create a Heap table .
b) Create clustred index on non-key columns
c) Create a Primary Key on  the key columns

I have posted the following example in one of the forums.

Let me clarify with an example .

Step1 : Create a ( heap ) table called "emp"
============================================

1> create table emp 
2> ( 
3> empno integer not null , 
4> empfanme varchar(50) not null , 
5> emplanme varchar(50) not null 
6> ) ; 
7> go 

Step 2 : Verify the contents in DB Dictionary
=============================================

1> select name , object_id from sys.tables ; 
2> go 
name object_id 
---- --------- 
emp 341576255 

(1 rows affected) 

1> Select name , index_id , type_desc from sys.indexes where object_id = OBJECT_ID('emp') ; 
2> go 

name index_id type_desc 
---- -------- --------- 
NULL 0 HEAP 

(1 rows affected) 

The above result confirms that we just have a heap.


Step 3: Create Clustered Index on the non key columns
=====================================================
1> 
2> 
3> CREATE UNIQUE CLUSTERED INDEX [CIDX_EMP] ON [dbo].[emp] 
4> ( 
5> [emplanme] ASC 
6> ); 
7> go 

1> Select name , index_id , type_desc from sys.indexes where object_id = OBJECT_ID('emp') ; 
2> go 

name index_id type_desc 
---- -------- --------- 
CIDX_EMP 1 CLUSTERED 

(1 rows affected) 


The above result verifies that we indeed have a clustered index.


Step 4: Create a Primary Key
============================


1> alter table emp add constraint pk_emp primary key (empno); 
2> go 

1> Select name , index_id , type_desc from sys.indexes where object_id = OBJECT_ID('emp') ; 
2> go 

name index_id type_desc 
---- -------- --------- 
CIDX_EMP 1 CLUSTERED 
pk_emp 2 NONCLUSTERED 

(2 rows affected)

Let us re-run the sql and see the contents .
Now , we see that SQL Server created non clustered index on the Primary Key , as there can be only one clustered index in a table.


Comments welcome.

Difference between PERCENTILE_CONT and PERCENTILE_DISC

Treading on the previous post on MEDIAN , I would like to point out the Difference between PERCENTILE_CONT and  PERCENTILE_DISC( MEDIAN being special case of PERCENTILE_CONT) . 

There are subtle differences between PERCENTILE_CONT and  PERCENTILE_DISC.  When the count of record set is odd , there are no differences between them .  The calculation of MEDIAN is identical to PERCENTILE_CONT when computed with percentile value of  0.5

Let us look at the first example.


SQL> DROP
  2    TABLE t purge;

Table dropped.

SQL> CREATE
  2    TABLE t AS
  3  SELECT
  4    'TEST' item ,
  5    'E'   AS region ,
  6    level AS wk ,
  7    ROUND(dbms_random.value(1,500)) forecastqty
  8  FROM
  9    dual
 10    CONNECT BY level <= 3 ;

Table created.


SQL>     SELECT
  2        t.* ,
  3        PERCENTILE_CONT(0.5)
  4        WITHIN GROUP ( ORDER BY forecastqty)
  5        OVER (PARTITION BY ITEM , region ) AS PERCENTILE_CONT ,
  6        MEDIAN(forecastqty)
  7        OVER (PARTITION BY ITEM , region ) AS MEDIAN ,
  8        PERCENTILE_DISC(0.5)
  9        WITHIN GROUP ( ORDER BY forecastqty)
 10        OVER (PARTITION BY ITEM , region ) AS PERCENTILE_DISC
 11      FROM
 12    t ;

ITEM REGION             WK FORECASTQTY PERCENTILE_CONT     MEDIAN PERCENTILE_DISC
---- ---------- ---------- ----------- --------------- ---------- ---------------
TEST E                   2          10              41         41              41
TEST E                   1          41              41         41              41
TEST E                   3         326              41         41              41




As you can see there are no difference between the values of PERCENTILE_CONT , PERCENTILE_DISC and MEDIAN. The computation involves the ordering of the result set ( 10 , 41 , 326 ) and picking up the middle value ( 41 ) . 


Let us at look at the second example.



SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> DROP
  2    TABLE t purge;

Table dropped.

SQL> CREATE
  2    TABLE t AS
  3  SELECT
  4    'TEST' item ,
  5    'E'   AS region ,
  6    level AS wk ,
  7    ROUND(dbms_random.value(1,500)) forecastqty
  8  FROM
  9    dual
 10    CONNECT BY level <= 4 ;

Table created.

SQL>   column region format a10
SQL>   set linesize 800
SQL> select * from t order by forecastqty;

ITEM REGION             WK FORECASTQTY
---- ---------- ---------- -----------
TEST E                   3         137
TEST E                   2         190
TEST E                   1         232
TEST E                   4         400



SQL>     SELECT
  2        t.* ,
  3        PERCENTILE_CONT(0.5)
  4        WITHIN GROUP ( ORDER BY forecastqty)
  5        OVER (PARTITION BY ITEM , region ) AS PERCENTILE_CONT ,
  6        MEDIAN(forecastqty)
  7        OVER (PARTITION BY ITEM , region ) AS MEDIAN ,
  8        PERCENTILE_DISC(0.5)
  9        WITHIN GROUP ( ORDER BY forecastqty)
 10        OVER (PARTITION BY ITEM , region ) AS PERCENTILE_DISC
 11      FROM
 12    t ;

ITEM REGION             WK FORECASTQTY PERCENTILE_CONT     MEDIAN PERCENTILE_DISC
---- ---------- ---------- ----------- --------------- ---------- ---------------
TEST E                   3         137             211        211             190
TEST E                   2         190             211        211             190
TEST E                   1         232             211        211             190
TEST E                   4         400             211        211             190

There are differences between the values of PERCENTILE_CONT , PERCENTILE_DISC . 

The computation of the  PERCENTILE_CONT   and MEDIAN involves the ordering of the result set ( 137 , 190 , 232 , 400  ) , aggregates the middle values ( 190 , 232 ) and then makes the mean / average of the aggregate [ ( 190 + 232 ) / 2  ] .  

The computation of the   PERCENTILE_DISC   involves the ordering of the result set ( 137 , 190 , 232 , 400  ) ,  pickups the first of the middle values ( 190 , in this case ) .  In other words , it picks up the value where cume_dist is lowest of the middle values. 

PERCENTILE_CONT and MEDIAN is not guaranteed to return one of the value in the distribution  . This may not be acceptable for certain business process . In that case , we would use PERCENTILE_DISC.


Note : 
SQL Server 2012 introduced these functions as well.  

Comments welcome.

Using MODEL clause to extrapolate values.

I was given interesting assignment from one of my good friend .

Here are the details .

a) There is a forecast table that has forecast for 8 weeks per item and region .
b) Need to extrapolate additional 5 more weeks for these records.
   Here is the algorithm for computing 5 more weeks of forecast .
  
   F(9) = median ( F[6] , F[7] , F[8])
   F(10) = median ( F[7] , F[8] , F[9])
   F(11) = median ( F[8] , F[9] , F[10])
   F(12) = median ( F[9] , F[10] , F[11])
   F(13) = median ( F[10] , F[11] , F[12])
  
Note : F(X) is the forecast for Xth week.   
  
Only the  input values for  F(9)  are stored in the database ; rest of them has to be computed.
Being this is one of the best use case for MODEL clause . I dive into it .
  
Here is an example.

MODEL clause works only from Oracle 10g onwards.

Comments Welcome.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production


SQL> Drop table t purge
  2  /

Table dropped.



SQL> Create table t as
  2  SElect 'TEST'  item , 'E' as region ,  level  as wk ,  round(dbms_random.value(1,25))  forecastqty
  3  from dual connect by level <= 8
  4  union all
  5  SElect 'TEST'  item , 'W' as region ,  level  as wk ,  round(dbms_random.value(1,25))  forecastqty
  6  from dual connect by level <= 8
  7  union all
  8  SElect 'TEST'  item , 'N' as region ,  level  as wk ,  round(dbms_random.value(1,25))  forecastqty
  9  from dual connect by level <= 8
 10  union all
 11  SElect 'TEST'  item , 'S' as region ,  level  as wk ,  round(dbms_random.value(1,25))  forecastqty
 12  from dual connect by level <= 8
 13  /

Table created.


SQL> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ITEM                                               CHAR(4)
 REGION                                             CHAR(1)
 WK                                                 NUMBER
 FORECASTQTY                                        NUMBER


SQL> select * from t
  2  /

ITEM R         WK FORECASTQTY
---- - ---------- -----------
TEST E          1          16
TEST E          2          12
TEST E          3          24
TEST E          4          13
TEST E          5          21
TEST E          6          24
TEST E          7           7
TEST E          8          22
TEST W          1           7
TEST W          2          10
TEST W          3           3
TEST W          4           8
TEST W          5          23
TEST W          6           2
TEST W          7           5
TEST W          8           3
TEST N          1           6
TEST N          2          14
TEST N          3          15
TEST N          4          13
TEST N          5           2
TEST N          6          21
TEST N          7          15
TEST N          8          12
TEST S          1          21
TEST S          2          12
TEST S          3           9
TEST S          4          24
TEST S          5          18
TEST S          6          20
TEST S          7          14
TEST S          8          17

SQL> SELECT
  2    item ,
  3    region ,
  4    wk ,
  5    forecastqty
  6  FROM
  7    t
  8  model
  9  IGNORE NAV
 10  partition BY( item ,region ) dimension BY( wk )
 11  measures( forecastqty  )
 12  rules upsert
 13  (
 14    forecastqty[9]                        = median( forecastqty ) [ wk BETWEEN 6 AND 8] ,
 15    forecastqty[10]                       = median( forecastqty ) [ wk BETWEEN 7 AND 9] ,
 16    forecastqty[11]                       = median( forecastqty ) [ wk BETWEEN 8 AND 10] ,
 17    forecastqty[12]                       = median( forecastqty ) [ wk BETWEEN 9 AND 11] ,
 18    forecastqty[13]                       = median( forecastqty ) [ wk BETWEEN 10 AND 12]
 19  )
 20  /

ITEM R         WK FORECASTQTY
---- - ---------- -----------
TEST W          1           7
TEST W          2          10
TEST W          3           3
TEST W          4           8
TEST W          5          23
TEST W          6           2
TEST W          7           5
TEST W          8           3
TEST S          1          21
TEST S          2          12
TEST S          3           9
TEST S          4          24
TEST S          5          18
TEST S          6          20
TEST S          7          14
TEST S          8          17
TEST S          9          17
TEST S         10          17
TEST S         11          17
TEST S         12          17
TEST S         13          17
TEST W          9           3
TEST W         10           3
TEST W         11           3
TEST W         12           3
TEST W         13           3
TEST E          1          16
TEST E          2          12
TEST E          3          24
TEST E          4          13
TEST E          5          21
TEST E          6          24
TEST E          7           7
TEST E          8          22
TEST N          1           6
TEST N          2          14
TEST N          3          15
TEST N          4          13
TEST N          5           2
TEST N          6          21
TEST N          7          15
TEST N          8          12
TEST E          9          22
TEST E         10          22
TEST E         11          22
TEST E         12          22
TEST E         13          22
TEST N          9          15
TEST N         10          15
TEST N         11          15
TEST N         12          15
TEST N         13          15

52 rows selected.


SQL> select * from t where item ='TEST' and region = 'N'
  2  /

ITEM R         WK FORECASTQTY
---- - ---------- -----------
TEST N          1           6
TEST N          2          14
TEST N          3          15
TEST N          4          13
TEST N          5           2
TEST N          6          21
TEST N          7          15
TEST N          8          12

8 rows selected.

SQL> SELECT
  2    item ,
  3    region ,
  4    wk ,
  5    forecastqty
  6  FROM
  7    t
  8  where item ='TEST' and region = 'N'
  9  model
 10  IGNORE NAV
 11  partition BY( item ,region ) dimension BY( wk )
 12  measures( forecastqty  )
 13  rules upsert
 14  (
 15    forecastqty[9]                        = median( forecastqty ) [ wk BETWEEN 6 AND 8] ,
 16    forecastqty[10]                       = median( forecastqty ) [ wk BETWEEN 7 AND 9] ,
 17    forecastqty[11]                       = median( forecastqty ) [ wk BETWEEN 8 AND 10] ,
 18    forecastqty[12]                       = median( forecastqty ) [ wk BETWEEN 9 AND 11] ,
 19    forecastqty[13]                       = median( forecastqty ) [ wk BETWEEN 10 AND 12]
 20  )
 21  /

ITEM R         WK FORECASTQTY
---- - ---------- -----------
TEST N          1           6
TEST N          2          14
TEST N          3          15
TEST N          4          13
TEST N          5           2
TEST N          6          21
TEST N          7          15
TEST N          8          12
TEST N          9          15
TEST N         10          15
TEST N         11          15
TEST N         12          15
TEST N         13          15

13 rows selected.

SQL>

Scratch Editor in SQL Developer

I have been using SQL Developer since its inception ( I started reading about it from the original project "raptor" ) .  I can't say , I have explored all the possiblities with this tool .  

Today , I ran into  menu item "Scratch Editor"  . This neat utility lets me to translate T-SQL component into T-SQL . Also , it has options for MS-ACCESS to PL/SQL  , SYBASE T-SQL to PL/SQL , DB2 SQL to PL/SQL.

I have not tried all the variations of the T-SQL  to PL/SQL. So far , almost all the simple SQL gets translated correctly.

Source T-SQL:


select top 5 (FName + ', ' + LName) as Full_Name,
isnull(Graduated, 'GOK - God only Knows')
, COALESCE( null , 1 , 2, 3  ) as Test_Colasece
from dbo.Student
order by student_id


Translated PL/SQL:

SELECT *
  FROM ( SELECT (FName || ', ' || LName) Full_Name  ,
                NVL(Graduated, 'GOK - God only Knows') ,
                COALESCE(NULL, 1, 2, 3) Test_Colasece 
  FROM /*dbo.*/Student
  ORDER BY student_id )
  WHERE ROWNUM <= 5;


Few Observations :

1) The schema  ( dbo ) has been commented in the translated PL/SQL. Not sure why  , as the schema in MS SQL Server is almost same as the schema in Oracle.

2) The concatenation operator has been tranlated correctly.

3) ISNULL has been translated as NVL


To acccess this menu item , you would click on "Tools -> Translation Scratch Editor" .

Just one side note:
For some reason , SQL Developer does not support PostgreSQL. I wish SQL Developer supports connectivity to Postgres natively  in the near future.  

EXECUTE in SQL Server 2008

Prior to SQL Server 2008 , the EXECUTE command could be used only in the local server .
Now , with SQL Server 2008 onwards , the EXECUTE command could be local as well as linked servers.

The prerequisite is that you would create a linked server ( of course ) and enable RPC OUT.

Here is an example .


C:>sqlcmd -S sql08 -W
1> use dbtest
2> go
Changed database context to 'dbtest'.

..........

.... ......

execute sp_serveroption 'ORACLE_LINKDB', 'RPC OUT' , True

I have created a linked server pointing to Oracle Database , in this case ORACLE_LINKDB.
Now , I can execute any oracle sql statements / PL SQL block ,
provided the user   have access to the objects.


1> Execute ( ' Select sysdate from dual')  AT ORACLE_LINKDB
2> go
SYSDATE
-------
2012-04-18 21:10:02.0000000
(1 rows affected)



1> Execute ( ' Select * from Control_Table where status =''running''')  AT ORACLE_LINKDB
2> go
JOBNAME     STATUS     STARTTIME   
-------     ------     ---------   
WLY_LOAD    running 2012-03-20  


In the following example , the user do not have any privileges on SCOTT schema .

1> Execute ( ' Select  *  from scott.emp')  AT ORACLE_LINKDB
2> go
OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINKDB" returned message "ORA-00942: table or view does not exist".
Msg 7215, Level 17, State 1, Server SQL08, Line 1
Could not execute statement on remote server 'ORACLE_LINKDB'.


Comments welcome.

How to format Zipcode / Postal Code in Oracle

Sometimes , in the applications , zipcodes are stored as numbers .  That means , the leading zeros for some of the postal codes will not be displayed  in the applications / reports.  This may not be desirable to the end user. 


As an example , in New Jersey , all the zip codes starts with leading zero.  But  , if you look at the database , the leading zero will be missing .



SQL> SELECT *
  2  FROM
  3    (SELECT u.*
  4    FROM uszipcode u
  5    WHERE state = 'NJ'
  6    )
  7  WHERE rownum < 5;


POSTALCODE CITY                                     ST
---------- ---------------------------------------- --
      7001 Avenel                                   NJ
      7002 Bayonne                                  NJ
      7003 Bloomfield                               NJ
      7004 Fairfield                                NJ


SQL>


To format the postal code , you may want to use the following SQL.




SQL> SELECT *
  2  FROM
  3    (SELECT u.* ,
  4      TO_CHAR(postalcode ,'fm00000') formatted_zipcode
  5    FROM uszipcode u
  6    WHERE state = 'NJ'
  7    )
  8  WHERE rownum < 5;


POSTALCODE CITY                                     ST FORMAT
---------- ---------------------------------------- -- ------
      7001 Avenel                                   NJ 07001
      7002 Bayonne                                  NJ 07002
      7003 Bloomfield                               NJ 07003
      7004 Fairfield                                NJ 07004

Joins ( ANSI Vs Legacy )

I am not a big fan of ANSI style joins especially outer joins . For me , the SQL becomes large , applying hints is a bit problematic.

Some people would disagree with this . Their take is that is a standard ( I can't win on that ) and second , it is readable ( I have to say , this is subjective).For most of the queries written in ANSI way , Oracle translates itself in a legacy style ( like table1.column1 = table2.column1 (+) ... ) , if we look at the trace files.

Not all joins can be done in a legacy style . For example , FULL OUTER JOIN , PARTITION OUTER JOIN can be written only in ANSI style.

I try to use legacy joins , wherever possible :-)  
Here is an example .


SQL> create table zahir.emp as Select * from scott.emp; 
Table created.

SQL> create table zahir.dept as Select * from scott.dept;

Table created.

Example of Equi Join or Inner Join
-----------------------------------
SQL> select d.deptno , d.dname , e.empno , e.ename from dept d , emp e where d.deptno = e.deptno;
 
     DEPTNO DNAME               EMPNO ENAME
 ---------- -------------- ---------- ----------
         20 RESEARCH             7369 SMITH
         30 SALES                7499 ALLEN
         30 SALES                7521 WARD
         20 RESEARCH             7566 JONES
         30 SALES                7654 MARTIN
         30 SALES                7698 BLAKE
         10 ACCOUNTING           7782 CLARK
         20 RESEARCH             7788 SCOTT
         10 ACCOUNTING           7839 KING
         30 SALES                7844 TURNER
         20 RESEARCH             7876 ADAMS
         30 SALES                7900 JAMES
         20 RESEARCH             7902 FORD
         10 ACCOUNTING           7934 MILLER
         10 ACCOUNTING              7 James Bond
 
15 rows selected.
SQL> select d.deptno , d.dname , e.empno , e.ename from dept d  inner join emp e on ( d.deptno = e.deptno);

    DEPTNO DNAME               EMPNO ENAME
---------- -------------- ---------- ----------
        20 RESEARCH             7369 SMITH
        30 SALES                7499 ALLEN
        30 SALES                7521 WARD
        20 RESEARCH             7566 JONES
        30 SALES                7654 MARTIN
        30 SALES                7698 BLAKE
        10 ACCOUNTING           7782 CLARK
        20 RESEARCH             7788 SCOTT
        10 ACCOUNTING           7839 KING
        30 SALES                7844 TURNER
        20 RESEARCH             7876 ADAMS
        30 SALES                7900 JAMES
        20 RESEARCH             7902 FORD
        10 ACCOUNTING           7934 MILLER
        10 ACCOUNTING              7 James Bond

15 rows selected.

Example of Outer Join
---------------------
SQL> select d.deptno , d.dname , e.empno , e.ename from dept d , emp e where d.deptno = e.deptno (+);

    DEPTNO DNAME               EMPNO ENAME
---------- -------------- ---------- ----------
        20 RESEARCH             7369 SMITH
        30 SALES                7499 ALLEN
        30 SALES                7521 WARD
        20 RESEARCH             7566 JONES
        30 SALES                7654 MARTIN
        30 SALES                7698 BLAKE
        10 ACCOUNTING           7782 CLARK
        20 RESEARCH             7788 SCOTT
        10 ACCOUNTING           7839 KING
        30 SALES                7844 TURNER
        20 RESEARCH             7876 ADAMS
        30 SALES                7900 JAMES
        20 RESEARCH             7902 FORD
        10 ACCOUNTING           7934 MILLER
        10 ACCOUNTING              7 James Bond
        40 OPERATIONS

16 rows selected.


SQL> select d.deptno , d.dname , e.empno , e.ename from dept d  left outer join emp e on ( d.deptno = e.deptno);

    DEPTNO DNAME               EMPNO ENAME
---------- -------------- ---------- ----------
        20 RESEARCH             7369 SMITH
        30 SALES                7499 ALLEN
        30 SALES                7521 WARD
        20 RESEARCH             7566 JONES
        30 SALES                7654 MARTIN
        30 SALES                7698 BLAKE
        10 ACCOUNTING           7782 CLARK
        20 RESEARCH             7788 SCOTT
        10 ACCOUNTING           7839 KING
        30 SALES                7844 TURNER
        20 RESEARCH             7876 ADAMS
        30 SALES                7900 JAMES
        20 RESEARCH             7902 FORD
        10 ACCOUNTING           7934 MILLER
        10 ACCOUNTING              7 James Bond
        40 OPERATIONS

16 rows selected.


Example of Full Join
--------------------- 
Here is where , leacgy style doesn't work.

 SQL> select d.deptno , d.dname , e.empno , e.ename from dept d , emp e where d.deptno (+) = e.deptno (+);
select d.deptno , d.dname , e.empno , e.ename from dept d , emp e where d.deptno (+) = e.deptno (+)
                                                                                     *
ERROR at line 1:
ORA-01468: a predicate may reference only one outer-joined table


SQL> select d.deptno , d.dname , e.empno , e.ename from dept d  full outer join emp e on ( d.deptno = e.deptno);

    DEPTNO DNAME               EMPNO ENAME
---------- -------------- ---------- ----------
        20 RESEARCH             7369 SMITH
        30 SALES                7499 ALLEN
        30 SALES                7521 WARD
        20 RESEARCH             7566 JONES
        30 SALES                7654 MARTIN
        30 SALES                7698 BLAKE
        10 ACCOUNTING           7782 CLARK
        20 RESEARCH             7788 SCOTT
        10 ACCOUNTING           7839 KING
        30 SALES                7844 TURNER
        20 RESEARCH             7876 ADAMS
        30 SALES                7900 JAMES
        20 RESEARCH             7902 FORD
        10 ACCOUNTING           7934 MILLER
        10 ACCOUNTING              7 James Bond
        40 OPERATIONS
       
       
    Comments Welcome.