[INFORMATICA] - How to Create a Target Table in Informatica?

If you have created Target table just using the Target designer and wondering how to create it in actual database then here are the steps.

In the target designer --> select the table You created ---> choose generate sql under target menu
 -->choose your dsn for database -->click edit and generate sql. 

[INFORMATICA] - The specified DSN contains an architecture mismatch between the Driver and Application.

I am trying to connect to oracle using power center designer 9.5.1 > Import Database source option and I get this error "The specified DSN contains an architecture mismatch between the Driver and Application.

" .

Windows 7 OS 64 bit

Oracle 11 G Release 2 64 bit

Informatica Server 9.51 64 bit

Informatica Client 9.5.1 32 bit [because there is no 64 bit exe for client]

I tried to run

c:\windows\syswow64\odbcad32.exe and I didn''t see an oracle entry to create user or system dsn so installed ODAC 32 bit fromhttp://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html then I see an oracle entry in here as well now BUT when I try to connect test connection to oracle it fails. See second snapshot. May be because it i 32 bit driver trying to connect oracle 64 bit database doesn't work.

I tried to run

c:\windows\system32\odbcad32.exe , I see an oracle entry to create user or system dsn. Test connection to oracle works fine. From Start>Programs> ODBC is using the exe from this location on 64 bit windows OS

To Resolve this issue You can use : Data Direct 6.0 Oralce Wire Protocal Driver in informatica

   

click on Test connection--->OK---> APPLY

[INFORMATICA] - Steps to install Informatica Powercenter 9.5 in Windows 8 or windows 8.1

Here am going to give the step by step process to download and install Informatica Powercenter 9.5.x in Windows 8/8.1

Step 1 : Define Schema in Oracle DB for Domain repository:
********************************************************************************************************
Where teaneck_ts is your tablespace(you can choose your own tablespace name)
Where teaneckdb is your own username(you can choose your own tablespace name)
Skip step 1 if already the repository schema in place.
**********************************************************************
CREATE TABLESPACE TEANECK_TS DATAFILE 'C:\app\<your computer name>\oradata\orcl\teaneck_ts.dbf' SIZE 100M AUTOEXTEND ON MAXSIZE 500M;
CREATE USER teaneckdb
IDENTIFIED BY teaneckdb
DEFAULT TABLESPACE TEANECK_TS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
ALTER profile DEFAULT limit password_life_time UNLIMITED;
Create role SSE_ROLE;
Grant connect, resource to SSE_ROLE;
Grant select any table to SSE_ROLE;
Grant create session to SSE_ROLE;
Grant insert any table, delete any table, update any table to SSE_ROLE;
GRANT SSE_ROLE TO teaneckdb;
GRANT GLOBAL QUERY REWRITE TO teaneckdb;
GRANT CREATE MATERIALIZED VIEW TO teaneckdb;
GRANT CREATE TABLE TO teaneckdb;
GRANT CREATE ANY VIEW, DROP ANY VIEW TO teaneckdb;
GRANT CREATE ANY SEQUENCE, DROP ANY SEQUENCE TO teaneckdb;
GRANT CREATE ANY INDEX, DROP ANY INDEX TO teaneckdb;
GRANT UNLIMITED TABLESPACE TO teaneckdb WITH ADMIN OPTION;



Step 2 : Copy and paste the entire SOURCE folder to INFA HOME folder.
*********************************************************************************
1.Download Informatica 9.5.1 from Oracle

    -- Follow steps given in This_Link to download and extract setup files

2. Copy SOURCE folder from 951HF2_Server_Installer_winem-64t folder

3. Paste the SOURCE folder to C:\Informatica\9.5.1   (INFAHOME)

Step 3: GUI is not compatible for Windows 8.1. We have to choose the cmd line execution to create the domain.
*********************************************************************************
Go to INFAHOME(C:\Informatica\9.5.1\Source\isp\bin) in cmd prompt and execute the below command

infasetup.bat defineDomain -da localhost:1521 -dt Oracle -du teaneckdb -dp teaneckdb -ds ORCL -dn domain_localhost -ad administrator -pd <yourpassword> -ld C:\Informatica\log -nn nodelocalhost -na localhost:6000 -mi 6000 -ma 6005 -rf nodeoptions.xml

where du teaneckdb is your oracle repository and du teaneckdb is password.
<Yourpassword> Administrator password.

Step 4 : Start the domain.
*********************************************************************************

Go to C:\Informatica\9.5.1\Source\tomcat\bin in CMD prompt(in INFAHOME)

Run infaservice.bat startup

C:\Informatica\9.5.1\Source\tomcat\bin> infaservice.bat startup

The domain will be kicked off.

Step 5: In browser type the URL
********************************************************************************
http://localhost:6000

This will take you the informatica administrator console.

Step 6 : Install Informatica Client by making compatibility of Windows 7.
*********************************************************************************

Go to folder "951HF2_Client_Installer_win32-x86\Client"

Right click "install.exe" and choose properties

In the compatibility tab choose "Windows 7" and click apply

Now double click the exe file and install it.

********************************************************************************

Step 7: Configure Environment variables

Set varaible INFA_HOME to "C:\Informatica\9.5.1\Source"

Set variable LIBPATH to "C:/Informatica\9.5.1\Source\server\bin"

********************************************************************************

Step 8: Update versions of Plug-ins

when the plug-ins are not registered to the domain and the version of the plug-ins need to be updated so perform the following steps:

1. Shut down the domain.
2. From the $INFA_HOME/server/ directory, run the command: "infasetup.bat validateandregisterallfeatures".
3. Once the command executes successfully, restart the domain and Model Repository Services and connect from the Developer client.

********************************************************************************

You are ready to work in Windows 8.1.

The SQL Model Clause

Refer http://www.dba-oracle.com/oracle_news/10g_dw_model_clause.htm

ORACLE-BASE - Oracle9i Mutating Table Exceptions

ORACLE-BASE - Oracle9i Mutating Table Exceptions

Oracle PL/SQL Articles

• Oracle 8i
• Oracle 9i
• Oracle 10g
• Oracle 11g
• Oracle 12c
• Miscellaneous

Copied from http://www.oracle-base.com/articles/plsql/articles-plsql.php

Oracle 8i

Bulk Binds - Improve performance by reducing the overhead associated with context switches between the PL/SQL and SQL engines.
Collections in Oracle PL/SQL - Use collections in PL/SQL to perform array processing.
Complex Recordsets - Build complex recordsets using temporary or PL/SQL tables within stored procedures.
Data Encryption - DBMS_OBFUSCATION_TOOLKIT - Encrypt and decrypt data using the DBMS_OBFUSCATION_TOOLKIT package.
DBMS_APPLICATION_INFO - Track session and long operation activity more accurately using thes built-in procedures and views.
The DBMS_SYSTEM Package - The DBMS_SYSTEM package contains a number of routines that can be useful on occasion.
Export BLOB - A simple method for exporting the contents of a BLOB datatype to the filesystem.
Export CLOB - A simple method for exporting the contents of a CLOB datatype to the filesystem.
File Handling From PL/SQL - Perform basic file manipulation from PL/SQL using this simple API.
Import BLOB - A simple method for importing the contents of a file into a BLOB datatype.
Import CLOB - A simple method for importing the contents of a file into a CLOB datatype.
InterMedia - Import-Export Of Images - Prior to Oracle 8.1.7 the interMedia support for import and export of data was a little flunky. Even now the API forces you to use directory object to access the file system. The code supplied here will free you from the constraints of inter
Parse XML Documents - Explode unstructured XML documents into relational tables using the XDK for PL/SQL.
Shell Commands From PL/SQL - Use this simple method to perform shell commands from within PL/SQL.

Oracle 9i

ANYDATA Type - This article presents an overview of the ANYDATA type.
Associative Arrays - Oracle9i Release 2 allows you to index-by string values using this renamed collection.
Bulk Binds (BULK COLLECT & FORALL) and Record Processing in Oracle9i Release 2 - Take advantage of bulk binds (BULK COLLECT & FORALL) for performance improvements whilst using record structures.
CASE Expressions And Statements - Learn how to use CASE expressions in both SQL and PL/SQL. In addition, learn how to use the CASE statement in PL/SQL.
Consuming Web Services - Access web services directly from PL/SQL using this simple API.
DBMS_PROFILER - Profile the run-time behaviour of PL/SQL code to identify potential bottlenecks.
DBMS_TRACE - Trace the run-time behaviour of PL/SQL code to identify potential bottlenecks.
DBMS_XPLAN - Easily format the output of an explain plan with this replacement for the utlxpls.sql script.
Dynamic Binds Using Contexts - Simplify dynamic variable binds within dynamic SQL using contexts.
Dynamic Binds Using Query Transformation - Simplify dynamic variable binds within dynamic SQL using query transformation.
Export BLOB Contents Using UTL_FILE - Use the new UTL_FILE functionality to write binary data to files.
Images from Oracle Over HTTP - Retrieve images directly from the database over HTTP.
Metadata API (DBMS_METADATA) - Extract DDL or XML definitions of all database objects using this simple API.
Mutating Table Exceptions - A simple method to prevent triggers producing mutating table exceptions.
Parse XML Documents - Explode unstructured XML documents into relational tables using the new integrated XDB packages.
Pipelined Table Functions - Improve performance of ETL processes by pipelining all transformation functions.
PL/SQL Native Compilation - Improve the performance of PL/SQL procedural code by compiling it to native shared libraries.
PL/SQL Server Pages - Use PL/SQL as a scripting language to generate web pages directly from the database.
PL/SQL Web Toolkit - Generate web pages directly from the database using this simple toolkit.
Stateless Locking Methods - Learn how to avoid data loss in stateless environments.
Storing Passwords In The Database - Store passwords securely in the database using this simple hashing technique.
Useful Procedures And Functions - Procedures and functions you may have overlooked which can come in useful during development.
UTL_FILE Enhancements - Oracle9i Release 2 includes some long overdue enhancements including basic file handling and support for NCHAR and RAW data.
UTL_FILE - Random Access of Files - Use the UTL_FILE package for random access of files from PL/SQL.
Universal Unique Identifier (UUID) - Reduce data migration and replication issues by replacing sequence generated IDs with UUIDs.
XML Generation In Oracle9i Using DBMS_XMLQuery, DBMS_XMLGen, Sys_XMLGen And Sys_XMLAgg - Generate XML and perform XSL transformations with ease using the new XML features of Oracle9i.
XML-Over-HTTP - XML-over-HTTP was the precursor to web services allowing easy access to XML via HTTP GETs and POSTs.
XMLType Datatype - Store XML documents in tables and query them using SQL.

Oracle 10g

Commit Enhancements in Oracle 10g Database Release 2 - Use the WRITE clause of the COMMIT command or the COMMIT_WRITE parameter to influence the way redo information is processed by commit operations.
DBMS_ASSERT - Sanitize User Input to Help Prevent SQL Injection - The DBMS_ASSERT package contains a number of functions that can be used to sanitize user input and help to guard against SQL injection.
DBMS_CRYPTO - Learn how to use the replacement for the DBMS_OBFUSCATION_TOOLKIT package.
DBMS_EPG - The Embedded PL/SQL Gateway in Oracle 10g Database Release 2 - Run mod_plsql applications directly from the database using the XML DB HTTP server, rather than Apache.
PL/SQL Enhancements in Oracle Database 10g - Get to grips with the Oracle 10g enhancements to PL/SQL using simple cut & paste examples.
SQL trace, 10046, trcsess and tkprof in Oracle - An article that combines all previous SQL Trace, event 10046 and tkprof information, along with information on trcsess and DBMS_MONITOR from Oracle 10g Onward.
UTL_DBWS - Consuming Web Services in Oracle 10g - Use the UTL_DBWS package to consume web services from PL/SQL.
The WRAP Utility and the DBMS_DDL Package - Learn how to use the enhanced DBMS_DDL package to dynamically wrap PL/SQL source in Oracle 10g Release 2.

Oracle 11g

APPEND_VALUES Hint in Oracle Database 11g Release 2 - Use the APPEND_VALUES hint to perform direct-path inserts when using the FORALL statement.
Cross-Session PL/SQL Function Result Cache in Oracle Database 11g Release 1 - Improve the performance of PL/SQL functions across the whole database instance by caching return values.
DBMS_PARALLEL_EXECUTE - Use the DBMS_PARALLEL_EXECUTE package to break down large workloads into manageable chunks that can be run in parallel.
DBMS_XA - Process a single transaction across multiple sessions using the DBMS_XA package.
Edition Based Redefinition in Oracle Database 11g Release 2 - Upgrade database components online using this new feature or Oracle Database 11g Release 2.
Native Oracle XML DB Web Services in Oracle 11g Release 1 - Present your exisitng PL/SQL procedures and functions as web services using XML DB in Oracle 11g.
PL/SQL Hierarchical Profiler in Oracle Database 11g Release 1 - Use the DBMS_HPROF package and the plshprof utility to generate and analyze hierarchical profiler data for PL/SQL programs.
PL/SQL New Features and Enhancements in Oracle Database 11g Release 1 - Get to grips with the new features and enhancements to PL/SQL in Oracle 11g Release 1.
Trigger Enhancements in Oracle Database 11g Release 1 - Learn about the enhancements to triggers in Oracle Database 11g Release 1.
UTL_MATCH : String Matching by Testing Levels of Similarity/Difference - Use the UTL_MATCH package to determine the similarity between two strings.

Oracle 12c

WITH Clause Enhancements in Oracle Database 12c Release 1 (12.1) - Define PL/SQL functions and procedures in the WITH clause of SQL statements.

Miscellaneous

Autonomous Transactions - A brief overview of autonomous transactions.
AutoNumber And Identity Functionality - Implement AutoNumber or Identity column behaviour in Oracle.
Database Triggers Overview - An introduction to database triggers in Oracle.
DBMS_PIPE - For Inter-Session Communication - This article presents a brief description of the DBMS_PIPE package, explaining how it can be used for non-secure inter-session mesaging.
DBMS_SESSION : Managing Sessions From a Connection Pool in Oracle Databases - A guide to using the DBMS_SESSION package to manage sessions that form part of a connection pool.
Decoupling to Improve Performance - Learn how to decouple processing to give the impression of improved performance.
Efficient Function Calls From SQL - This article presents a number of solutions for making calls to PL/SQL functions from SQL more efficient.
Email From Oracle PL/SQL (UTL_SMTP) - Email from PL/SQL using UTL_SMTP rather than using external procedures or Java.
FTP From PL/SQL - A description of two methods for triggering FTP jobs directly from PL/SQL.
HTML with Embedded Images from PL/SQL - Use PL/SQL to create HTML with embedded images.
Identifying Host Names and IP Addresses - This article presents a mixed bag of Oracle functionality relating to the identification of host names and IP addresses for Oracle clients and servers.
Implicit vs. Explicit Cursors in Oracle PL/SQL - A comparison of the relative performance of implicit and explicit cursors in Oracle PL/SQL.
Introduction to PL/SQL - A brief overview of some of the important points you should consider when first trying to learn PL/SQL.
Logic/Branch Ordering in PL/SQL - This article demonstrates the affect of branch ordering on the performance of CASE and ELSIF statements.
NOCOPY Hint to Improve Performance of OUT and IN OUT Parameters in PL/SQL Code - This article discusses the benefits of using the NOCOPY hint for passing large or complex OUT and IN OUT parameters in PL/SQL.
NULL-Related Funtions (NVL, DECODE, NVL2, COALESCE, NULLIF, LNNVL, NANVL, SYS_OP_MAP_NONNULL) - A summary of the functions available for handling NULL values.
Oracle Dates, Timestamps and Intervals - An overview of the usage of dates, timestamps and intervals in Oracle databases.
Overlapping Date Ranges - This article presents simple methods to test for overlapping date ranges.
Performance of Numeric Data Types in PL/SQL - This article demonstrates the relative performance of the numeric data types in PL/SQL.
PL/SQL : Stop Making the Same Performance Mistakes - This article describes the common PL/SQL performance mistakes I see people making time and time again.
PL/SQL Vs. Oracle JVM: Speed Comparison for Mathematical Operations - This article does a side-by-side speed comparison of PL/SQL and the Oracle JVM for some basic mathematical operations.
Populating Master-Detail Foreign Key Values Using Sequences - This article presents some safe methods for populating master-detail foreign key columns when using sequences.
Regular Expression Support in Oracle (REGEXP_COUNT, REGEXP_INSTR, REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_LIKE) - Using regular expressions to solve some questions I've been asked over the years.
Retrieving HTML and Binaries into Tables Over HTTP - This article gives two brief examples of how HTML pages and Binaries, such as images or documents, can be downloaded over HTTP and placed directly into tables.
ROWIDs for PL/SQL Performance - This article demonstrates how using ROWIDs in transactions can improve performance.
Schema Owners and Application Users - Protect your schema objects by defining application users with the necessary privileges on those objects, rather than allowing direct connections to the schema owner.
Short-Circuit Evaluation in PL/SQL - This article describes how short-circuit evaluation can be used to improve the performance of some boolean expressions.
Using Ref Cursors To Return Recordsets - Return recordsets from Oracle stored procedures.
UTL_HTTP and SSL (HTTPS) using Oracle Wallets - This article describes how to use the UTL_HTTP package to interact with resources secured by SSL (HTTPS).
XML-Over-HTTP (REST Web Services) From PL/SQL - XML-over-HTTP was the precursor to web services allowing easy access to XML via HTTP GETs and POSTs.

Oracle - Execution Plans , Explain Plans, AWR Reports, Tuning - A handy Guide

There is an inherent different between explain plans and execution plans , and I almost got confused that they were the same.

In order to capture an execution plan , we need to follow the steps:

The user who will be running the sql for which you want the execution plan , should have permissions to alter session in order to enable trace.(This is not mandatory , if you can run the sqls via the user who has permissions then that is also fine)

Grant permission:

grant alter session to ;
revoke alter session from ;

Enable SQL TRACING

To enable Trace run the following DCL:

ALTER SESSION SET sql_trace = true;

To specify trace file identifier which helps to locate specific trace files easily

ALTER SESSION SETtracefile_identifier =tracing_example;

To specify the trace destination other than the oracle default , we could perform below


ALTER SESSION SET user_dump_dest=

Default vaues for the various parameters above can also be found using TOAD->Database->Administer->Parameters

Explain Plan

Using toad , this can be achieved by following the steps below:

1.  Connect to the Oracle SID
2. Open a SQL editor, and write the SQL query for which the explain plan is required.
3. CTRL+E will produce the explain plan for the query - which basically means , this is the most likely path oracle will chose while executing the SQL. 
4. Analyze the cost of the query , and identify the areas which are causing the cost to grow high.
5. Mostly this happens when full table access is performed, or hashed joins are used , instead of full index scans and nested loops.
6. This is the fastest way to identify if a query you have written has some tuning gaps and can be rewritten to perform better in distributed and scalable high volume environments.

Enable SNAPSHOTS

By enabling snapshots we will be able to capture the Oracle activity between regular periods identified by snapshot ids. To view the current snapshot interval we can run the following:

select * from DBA_HIST_WR_CONTROL

To enable snapshots for every 15 minutes , we can run the following procedure. We can change the snapshot interval based on approximately how much time would a process(which we are interested in profiling / tuning ) takes to run .

BEGIN dbms_workload_repository.modify_snapshot_settings(interval => 15, retention => null); END;

Generate AWR Report

An AWR report captures all Oracle activity between given snapshots. This can be helpful to monitor the queries which are taking longer than expected and also identify tuning paramters.

To generate an AWR report we can perform the following steps:

1. Invoke @awrrpt.sql from ..\oracle\product\\dbhome_1\RDBMS\ADMIN
2. Choose report type as html
3. Provide number of days you want the report to span for snapshot ids
4. Provide the snapshots which we want to capture in our report. 
5. Provide a valid report name.
6. AWR report is generated in the ..\oracle\product\\dbhome_1\RDBMS\ADMIN folder with the name provided.

Many of the above options can be chosen as default as well.

See here http://www.oracle-base.com/articles/10g/automatic-workload-repository-10g.php
for detailed explanation.  

Capturing SQL ID

If we do not have a AWR report , but still want to identify the the SQL id based on the query content the following can be used:

SELECT sql_id, child_number, substr(sql_text, 1, 300) sqltxt from v$sql WHERE sql_text LIKE %%' ;

Also the various execution plans used by oracle for this SQL can be generated using:

set linesize 150

set pagesize 2000

SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('SQL_ID'));

Execution Plan

Execution plan on the other hand is the actual path /plan used by oracle to execute a query.

To extract an execution plan, you will have to perform a few more steps as below:

1. Enable snapshots for the SID which you will be executing the queries from.Set the snapshot interval based on your specific enviroment , sql process running
2. Execute the SQL procedure, process or DBMS transaction which you want to tune.
3. Generate AWR report for the snapsots we are interested in.
4. Browse the sql statistics to identify which queries are taking up the most time , in terms of execution , io waits , cpu etc
5. Each SQL will have a unique SQL_ID , capture that.
6. Invoke @awrsqrpt.sql from ..\oracle\product\\dbhome_1\RDBMS\ADMIN
7. Provide the sql id for which we want to generate the execution plan
8. Provide snapshot ids
9. Provide a name for the plan report
10. Execution plan is generated in the ..\oracle\product\\dbhome_1\RDBMS\ADMIN folder with the name provided.

This plan will provide the exact path , and can be used to identify whats causing the costs for SQL runtime to go high.

Tuning Options:

• Disable tracing
• Disable all SQL profilers
• Perform Database connections monitoring - if connections are being returned once transactions complete
• reduce redo logs
• add hints wherever costs of queries are more due to joins and full table scans
• add appropriate indexes 
• Check Init.ORA for Oracle settings ,and modify based on the needs
• The list if huge .......

Finding missing dates using SQL

Query to find missing dates between two date range in a table. We need to LEFT JOIN the table.

 WITH all_dates AS (
  SELECT TO_DATE('01-jan-09') -- Start-date
    + ROWNUM - 1 AS d
  FROM dual
CONNECT BY ROWNUM <= to_number(to_char(to_date('31-dec-16'),'J')-to_char(to_date('01-jan-09'),'J')) + 1   -- Number of days calculated using Julian numbers
)
SELECT all_dates.d
FROM all_dates
LEFT JOIN  t ON ( t.day = all_dates.d )
WHERE t.day IS NULL
ORDER BY all_dates.d;

Queries for DD tables in Oracle

Find the queries for various DD tables in the document https://docs.google.com/open?id=180eCMrfSn-SkDRA-eXEAFIQCi85wdiugzlt9jzwZSjqoCLHyVA_HQnqnqzTa

Data dictionary tables for Roles and Privileges in Oracle

/* Issue this query to find out what roles have been created for the    */

/* oracle db you are accessing. the 1st 19 roles are default roles that */

/* are created when you install oracle; */

select * from dba_roles;

/* Use this query to find what usrs (grantees) have been granted what roles */

select grantee, granted_role from dba_role_privs where grantee not in

('SYS', 'SYSTEM') order by grantee;

/* use this query to see tables that were created by others (=owner),*/

/* that you have been granted access too, including what privileges  */

/* you were granted (=privilege) and whether you can grant access to */

/* others (=GRA) */

select * from user_tab_privs_recd;

/* to find what system privileges have been granted to roles */

Select privilege, admin_option from role_sys_privs where role like ‘ROLENAME%’;

/* to find what roles have been granted to roles */

Select granted_role, admin_option from role_role_privs where role like ‘ROLENAME%’;

/* to find what table privileges have been granted to roles */

Select * from role_tab_privs where role like ‘ROLENAME%’;

/* to find what system privileges have been granted to users and roles */

Select * from dba_sys_privs where grantee like ‘USERORROLENAME;

Installing and configuring OAM server 11g

Steps to Install and configure OAM server 11g:  http://itnaf.org/oracle-access-manager-11g-new/

Enabling SSO for ADF Application using OAM 11g

Useful video link http://www.youtube.com/watch?v=8mOZJM8ZZ2E

Delete duplicate rows from Oracle tables

Removing duplicate rows from Oracle tables with SQL can be very tricky, and there are several techniques for identifying and removing duplicate rows from tables:

•  Subquery to identify duplicate rows
• Use RANK to find and remove duplicate table rows
• Use self-join to remove duplicate rows
• Use analytics to detect and remove duplicate rows
• Delete duplicate table rows that contain NULL values

Use subquery to delete duplicate rows

Here we see an example of using SQL to delete duplicate table rows using an SQL subquery to identify duplicate rows, manually specifying the join columns:

DELETE FROM 
   table_name A
WHERE
  a.rowid >
   ANY (
     SELECT
        B.rowid
     FROM
        table_name B
     WHERE
        A.col1 = B.col1
     AND
        A.col2 = B.col2
        );

Use RANK to delete duplicate rows

This is an example of the RANK function to identify and remove duplicate rows from Oracle tables, which deletes all duplicate rows while leaving the initial instance of the duplicate row:

delete from $table_name where rowid in
  (
  select "rowid" from
     (select "rowid", rank_n from
         (select rank() over (partition by $primary_key order by rowid) rank_n, rowid as "rowid"
             from $table_name
             where $primary_key in
                (select $primary_key from $table_name
                  group by $all_columns
                  having count(*) > 1
                )
             )
         )
     where rank_n > 1
  )

One of the most important features of Oracle is the ability to detect and remove duplicate rows from a table. While many Oracle DBA place primary key referential integrity constraints on a table, many shops do not use RI because they need the flexibility.

Use self-join to delete duplicate rows

The most effective way to detect duplicate rows is to join the table against itself as shown below.

select 
   book_unique_id,
   page_seq_nbr,
   image_key
from
   page_image a
where
   rowid >
     (select min(rowid) from page_image b
      where
         b.key1 = a.key1
      and
         b.key2 = a.key2
      and
         b.key3 = a.key3
      );

Please note that you must specify all of the columns that make the row a duplicate in the SQL where clause. Once you have detected the duplicate rows, you may modify the SQL statement to remove the duplicates as shown below:

delete from 
   table_name a
where
   a.rowid >
   any (select b.rowid
   from
      table_name b
   where
      a.col1 = b.col1
   and
      a.col2 = b.col2
   )
;

Use analytics to delete duplicate rows

You can also detect and delete duplicate rows using Oracle analytic functions:

delete from
   customer
where rowid in
 (select rowid from
   (select
     rowid,
     row_number()
    over
     (partition by custnbr order by custnbr) dup
    from customer)
  where dup > 1);

As we see, there are several ways to detect and delete duplicate rows from Oracle tables

Eliminating special characters from a value or Finding values with special characters

Query to Eliminate special characters from a column value:

          select translate('e%rerA%' , 'A(%$*&@,;''/+-' , 'A') from dual;

Query to retrieve only the special characters from the column value:


select  translate(string_column,'%' || translate(string_column,'X(%$*&@,;''/+-)','X'),'%') special_characters
  from  (select 'test%er'  string_column from dual);

Query to select only the rows whose column values has the special characters:


  SELECT string_column FROM (select 'test%er'  string_column from dual)
  WHERE string_column != nvl(translate(string_column , 'A(%$*&@,;''/+-' , 'A'), 'A');

Oracle Listener service is not starting

When you try to start the listener service and it gives you the below message and stops automatically.

The OracleOraDb10g_home1TNSListener service on Local Computer started and then stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service.


Solution which worked for me:

1) listener.ora file was missing under the NETWORK/ADMIN folder so I have added the file to this folder with the below content.

            LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST =  <IPADDRESS>)(PORT = 1521))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
    )
  )

2) Made sure the sqlnet.ora file has the below entry.



    SQLNET.AUTHENTICATION_SERVICES= (NTS)

     NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)


3) Made sure the tnsnames.ora has the proper entry as shown below


  orcl =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <IPADDRESS>)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )



4) Restart the machine and manually start the oracle service. This time it will start properly and you can able to connect to it via any oracle developer tools.

Note: Even-though the listener is not running you will be able to connect to database via sqlplus available under oracle home application development utility.

Displaying all dates between two given dates

Displaying all dates between two dates. 


Table "tab_dates" contains the below data

START_DATE  |   END_DATE   

---------------------------------
01-JAN-12          07-JAN-12            

08-JAN-12         16-JAN-12            




Required Output:



DAY_DATE                  START_DATE           END_DATE                  
------------------------- ------------------------- ------------------------- 
01-JAN-12                 01-JAN-12                 07-JAN-12                 
02-JAN-12                 01-JAN-12                 07-JAN-12                 
03-JAN-12                 01-JAN-12                 07-JAN-12                 
04-JAN-12                 01-JAN-12                 07-JAN-12                 
05-JAN-12                 01-JAN-12                 07-JAN-12                 
06-JAN-12                 01-JAN-12                 07-JAN-12                 
07-JAN-12                 01-JAN-12                 07-JAN-12                 
08-JAN-12                 08-JAN-12                 16-JAN-12                 
09-JAN-12                 08-JAN-12                 16-JAN-12                 
10-JAN-12                 08-JAN-12                 16-JAN-12                 
11-JAN-12                 08-JAN-12                 16-JAN-12                 
12-JAN-12                 08-JAN-12                 16-JAN-12                 
13-JAN-12                 08-JAN-12                 16-JAN-12                 
14-JAN-12                 08-JAN-12                 16-JAN-12                 
15-JAN-12                 08-JAN-12                 16-JAN-12                 
16-JAN-12                 08-JAN-12                 16-JAN-12 


{code} -- Applies only to Oracle 11g database

with tab_dates as 
      (select to_date('01-JAN-12','dd-mon-yy') start_date,to_date('07-JAN-12','dd-mon-yy') end_date from dual
       union all
        select to_date('08-JAN-12','dd-mon-yy') start_date,to_date('14-JAN-12','dd-mon-yy') end_date  from dual)


    select start_date + i day_date, start_date, end_date from tab_dates,
     xmltable('for $i in 0 to xs:int(D)-1 return $i' passing 
     xmlelement(D,  (end_date-start_date)+1 ) columns i integer path '.') ;
{/code}

Explanation:


Using XMLTable() generate the list of numbers (ex: 0..6 for dates 6 days apart)   


(end_date-start_date)+1  = number of days in-between and the result is substituted in the place of "D" using the XMLElement() function.


The temporary XMLTable now contains a column named "i".

How to Split single Column value into multiple rows?

Consider the data set

 with t as
(
 select 101 job_id, 'P00O0496,,P00O0828,P00O2739,P00O3522,P00O4405,P00O7182,P00U1375' str from dual union all
 select 102 job_id, 'P00O0496,,P00O0828,P00O2739,P00O3522,P00O4405,P00U1375' from dual 

union all
 select 103 job_id,'UUKGQ068,UUKGQ069,UUKGQ071,UUKGQ075,UUKGQ077,

UUKGQ083,,,,UUMO12430' from dual
)

Note: Null Values should not be displayed in the result set.

The output should be as shown below

JOB_ID                 STR       
---------------------- ----------
101                    P00O0496  
101                    P00O0828  
101                    P00O2739  
101                    P00O3522  
101                    P00O4405  
101                    P00O7182  
101                    P00U1375  
102                    P00O0496  
102                    P00O0828  
102                    P00O2739  
102                    P00O3522  
102                    P00O4405  
102                    P00U1375  
103                    UUKGQ068  
103                    UUKGQ069  
103                    UUKGQ071  
103                    UUKGQ075  
103                    UUKGQ077  
103                    UUKGQ083  
103                    UUMO12430 

 20 rows selected 

There are two known ways to do this

Method 1: Using Regular expressions and Connect by clause

{Code }

select job_id, regexp_substr(yourcolumn,'[^,]+',1,r) yourvalue , r
from yourtable,
(select rownum r from dual connect by rownum <= 100) max_users 
where   
    r<= length(regexp_replace(yourcolumn,'[^,]')) +1  -- stop condition for max_users
and regexp_substr(yourcolumn,'[^,]+',1,r) is not null -- show only real values
order by job_id
             ,r  -- you need ordering by r, if you want to keep original order of your values
;

{/Code } 

Method 2: Converting data set into XML and parsing it

{Code }

 select job_id, x.str
  from t,
           xmltable('e' passing xmltype('<e><e>' || replace(str, ',', '</e><e>')    ||  '</e></e>').extract('e/e')
                       columns str varchar2(10) path '.') x
   where x.str is not null;

{/Code } 

How to specify the Window clause (ROW type or RANGE type windows) in Analytic function?

Some analytic functions (AVG, COUNT, FIRST_VALUE, LAST_VALUE, MAX, MIN and SUM among the ones we discussed) can take a window clause to further sub-partition the result and apply the analytic function. An important feature of the windowing clause is that it is dynamic in nature.

The general syntax of the is

[ROW or RANGE] BETWEEN AND <start_expr> AND <end_expr>

<start_expr> can be any one of the following

    UNBOUNDED PECEDING
    CURRENT ROW
    <sql_expr> PRECEDING or FOLLOWING.
 

<end_expr> can be any one of the following

    UNBOUNDED FOLLOWING or
    CURRENT ROW or
    <sql_expr> PRECEDING or FOLLOWING.

For ROW type windows the definition is in terms of row numbers before or after the current row. So for ROW type windows <sql_expr> must evaluate to a positive integer.

For RANGE type windows the definition is in terms of values before or after the current ORDER. We will take this up in details latter.

The ROW or RANGE window cannot appear together in one OVER clause. The window clause is defined in terms of the current row. But may or may not include the current row. The start point of the window and the end point of the window can finish before the current row or after the current row. Only start point cannot come after the end point of the window. In case any point of the window is undefined the default is UNBOUNDED PRECEDING for <start_exp> and UNBOUNDED FOLLOWING for <end_expr>.

If the end point is the current row, syntax only in terms of the start point can be can be

[ROW or RANGE] [<sql_expr> PRECEDING or UNBOUNDED PRECEDING ]

[ROW or RANGE] CURRENT ROW is also allowed but this is redundant. In this case the function behaves as a single-row function and acts only on the current row.

ROW Type Windows

For analytic functions with ROW type windows, the general syntax is:

Function( ) OVER (PARTITIN BY <expr-1> ORDER BY <expr-2> ROWS BETWEEN <start_expr> AND <end_expr>)
or
Function( ) OVER (PARTITON BY <expr-1> ORDER BY <expr-2> ROWS [ <start_expr> PRECEDING or UNBOUNDED PRECEDING]

For ROW type windows the windowing clause is in terms of record numbers.

The query Query-01 has no apparent real life description (except column FROM_PU_C) but the various windowing clause are illustrated by a COUNT(*) function. The count simply shows the number of rows inside the window definition. Note the build up of the count for each column for the YEAR 1981.

The column FROM_P3_TO_F1 shows an example where start point of the window is before the current row and end point of the window is after current row. This is a 5 row window; it shows values less than 5 during the beginning and end.

{code: ** QUERY-01 **}

-- The query below has no apparent real life description (except
-- column FROM_PU_C) but is remarkable in illustrating the various windowing
-- clause by a COUNT(*) function.

SELECT empno, deptno, TO_CHAR(hiredate, 'YYYY') YEAR,
COUNT(*) OVER (PARTITION BY TO_CHAR(hiredate, 'YYYY')
ORDER BY hiredate ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) FROM_P3_TO_F1,
COUNT(*) OVER (PARTITION BY TO_CHAR(hiredate, 'YYYY')
ORDER BY hiredate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM_PU_TO_C,
COUNT(*) OVER (PARTITION BY TO_CHAR(hiredate, 'YYYY')
ORDER BY hiredate ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) FROM_P2_TO_P1,
COUNT(*) OVER (PARTITION BY TO_CHAR(hiredate, 'YYYY')
ORDER BY hiredate ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) FROM_F1_TO_F3
FROM emp
ORDEDR BY hiredate

 EMPNO  DEPTNO YEAR FROM_P3_TO_F1 FROM_PU_TO_C FROM_P2_TO_P1 FROM_F1_TO_F3
------ ------- ---- ------------- ------------ ------------- -------------
  7369      20 1980             1            1             0             0
  7499      30 1981             2            1             0             3
  7521      30 1981             3            2             1             3
  7566      20 1981             4            3             2             3
  7698      30 1981             5            4             3             3
  7782      10 1981             5            5             3             3
  7844      30 1981             5            6             3             3
  7654      30 1981             5            7             3             3
  7839      10 1981             5            8             3             2
  7900      30 1981             5            9             3             1
  7902      20 1981             4           10             3             0
  7934      10 1982             2            1             0             1
  7788      20 1982             2            2             1             0
  7876      20 1983             1            1             0             0

14 rows selected.

{/code: ** QUERY-01 ** }

The column FROM_PU_TO_CURR shows an example where start point of the window is before the current row and end point of the window is the current row. This column only has some real world significance. It can be thought of as the yearly employee build-up of the organization as each employee is getting hired.

The column FROM_P2_TO_P1 shows an example where start point of the window is before the current row and end point of the window is before the current row. This is a 3 row window and the count remains constant after it has got 3 previous rows.

The column FROM_F1_TO_F3 shows an example where start point of the window is after the current row and end point of the window is after the current row. This is a reverse of the previous column. Note how the count declines during the end.

RANGE Windows
For RANGE windows the general syntax is same as that of ROW:

Function( ) OVER (PARTITION BY ORDER BY RANGE BETWEEN AND )
or
Function( ) OVER (PARTITION BY ORDER BY RANGE [ PRECEDING or UNBOUNDED PRECEDING]

For or we can use UNBOUNDED PECEDING, CURRENT ROW or PRECEDING or FOLLOWING. However for RANGE type windows must evaluate to value compatible with ORDER BY expression .

is a logical offset. It must be a constant or expression that evaluates to a positive numeric value or an interval literal. Only one ORDER BY expression is allowed.

If evaluates to a numeric value, then the ORDER BY expr must be a NUMBER or DATE datatype. If evaluates to an interval value, then the ORDER BY expr must be a DATE datatype.

Note the example (Query-02) below which uses RANGE windowing. The important thing here is that the size of the window in terms of the number of records can vary.

{code: ** QUERY-02 ** }

-- For each employee give the count of employees getting half more that their
-- salary and also the count of employees in the departments 20 and 30 getting half
-- less than their salary.

SELECT deptno, empno, sal,
Count(*) OVER (PARTITION BY deptno ORDER BY sal RANGE
BETWEEN UNBOUNDED PRECEDING AND (sal/2) PRECEDING) CNT_LT_HALF,
COUNT(*) OVER (PARTITION BY deptno ORDER BY sal RANGE
BETWEEN (sal/2) FOLLOWING AND UNBOUNDED FOLLOWING) CNT_MT_HALF
FROM emp
WHERE deptno IN (20, 30)
ORDER BY deptno, sal

 DEPTNO  EMPNO   SAL CNT_LT_HALF CNT_MT_HALF
------- ------ ----- ----------- -----------
     20   7369   800           0           3
     20   7876  1100           0           3
     20   7566  2975           2           0
     20   7788  3000           2           0
     20   7902  3000           2           0
     30   7900   950           0           3
     30   7521  1250           0           1
     30   7654  1250           0           1
     30   7844  1500           0           1
     30   7499  1600           0           1
     30   7698  2850           3           0

11 rows selected.

{/code: ** QUERY-02 ** }