ORACLE SQL and PL/SQL

UTL_SMTP (EMAIL From Database Procedure)

Setup a Email trigger with specific Oracle table

Email From Oracle PL/SQL (UTL_SMTP)
Email the output of a concurrent program as Attachment

 

Remove 'ENTER' from text data

Select replace(replace(TEXT,char(10),''),char(13),'')

PL/SQL New Features and Enhancements in Oracle Database 11g Release 1

WRAP THE PLSQL CODE

PL/SQL Wrap Utility and DBMS_DDL.WRAP Function to Encrypt your PL/SQL Code

PL/SQL Source Text Wrapping

Wrapping PL/SQL Source Code

 

BULK COLLECT

Bulk collect requirement

Bulk Binds (BULK COLLECT & FORALL) and Record Processing in Oracle

Bulk Processing with BULK COLLECT and FORALL (By ORACLE)

Insert with NOLOGGING option.

Direct-Load INSERT

On BULK COLLECT
Bulk Inserts with Oracle
Oracle Array Processing with Bulk Collection & FORALL
Best practices for knowing your LIMIT and kicking %NOTFOUND
Bulk Inserts with Oracle
 

PLSQL RECORD

SELECTing INTO a Record
Oracle Pipelined Table Functions
PL/SQL Tables and User-Defined Records

Using PL/SQL Collections and Records (DML Operations)

Ref Cursor Understanding

Implicit vs. Explicit Cursors in Oracle PL/SQL
Understanding Ref Cursors
Working with Cursors
Oracle/PLSQL: Cursors
the difference between the cursor refcursor and sys_refcursor
DIFFERENCE BETWEEN STRONG AND WEEK CURSORS IN ORACLE
Cursor Variable Declaration
Getting output from Ref Cursors in PL/SQL
Open a Ref Cursor. - FOR LOOP {OraFAQ}
What is diff between strong and weak ref cursors
Using Ref Cursors To Return Recordsets

ORACLE CASE Statement

Using Case Statement in Where Clause
How to Use CASE Statement in WHERE Clause to Build Dynamic Query?
Conditional WHERE clause with CASE statement in Oracle

ORACLE Views

change datatype of a view
creating parameterized views in oracle11g
SYS_CONTEXT
Parameterized View - Passing Parameters in Views

WITH in ORACLE SQL

Oracle WITH clause

WITH Clause : Subquery Factoring

Anatomy of a SELECT Statement – Part 1: The WITH Clause

 

MERGE Statement

MERGE Statement

MERGE

Continue MERGE after EXCEPTION
Merge into table name using cursor

 

 Hierarchical Queries

Hierarchical Queries

Hierarchical queries - Orafaq

Hierarchical Queries in Oracle

Anatomy Of A SELECT Statement – Part 2: The Hierarchical Query Clause 

Date Format in ORACLE APPS

APP_DATE and FND_DATE: Date Conversion APIs

Introduction to APPCORE Routine APIs

Oracle canonical formats for flexfields

BI Publisher Date Formats

Date Format Models

TO_CHAR (datetime)

TIMESTAMP in ORACLE

Format [TIMESTAMP] field value in WHERE clause?

How To Format Timestamp

Systimestamp format

SYSTIMESTAMP in ORACLE Doc

 

1. where CAST (table.timestamp_col as DATE) between :p_from_date and :p_to_date

2. where table.timestamp_col between CAST (:p_from_date as TIMESTAMP) and CAST (;p_to_date AS TIMESTAMP)

INBOUND and OUTBOUND  INTERFACES

INTRO TO INTERFACES(For Freshers to oracle Apps)-1

INTERFACES
UTL File Read a file, UTL Write a file , UTL Move the file, UTL Remove the file in Oracle Applications

AR Outbound Interface code

Outbound Interface Process in Oracle

Oracle Apps Interfaces

R12 API Changes

EXCEPTIONS

EXCEPTION_INIT Pragma

10 Handling PL/SQL Errors
Oracle/PLSQL: SQLERRM Function
Term: DUP_VAL_ON_INDEX

For Loop With Exceptions

Merge into table name using cursor (For Loop )

PRAGMA AUTONOMOUS_TRANSACTION

Solving “Cannot perform DML inside a query” error in Oracle
Solution to “cannot perform a DML operation inside a query”?

METADATA Retrieval

Retrieving Stored procedures, Views, Functions, Triggers using Toad for Oracle
Oracle RDBMS: Extracting the Table, Index & View Definitions (DDL) and Indexed Columns

In general, you should use dbms_metadata to retrieve DDL statements. eg.

select dbms_metadata.get_ddl('VIEW', 'V_MYVIEW') from dual;

This can of course be wrapped in a query over the data dictionary, eg.

select dbms_metadata.get_ddl(object_type, object_name) 
from user_objects
where object_type in ('VIEW', 'PROCEDURE', 'FUNCTION', 'TRIGGER');

SYS_CONTEXT

 

Repeat rows dynamically in Oracle based on a condition

Generating Duplicate Rows

Repeat Rows N Times According to Column Value

 -------------
SELECT *
  FROM (SELECT level_num, period, org_id, ref_no, bucket,
               CASE
                  WHEN level_num IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14)
                     THEN NULL
                  ELSE amount2
               END amount2, CASE
                  WHEN level_num IN (3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14)
                     THEN NULL
                  ELSE amount3
               END amount3, CASE
                  WHEN level_num IN (4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14)
                     THEN NULL
                  ELSE amount4
               END amount4, CASE
                  WHEN level_num IN (5, 6, 7, 8, 9, 10, 11, 12, 13, 14)
                     THEN NULL
                  ELSE amount5
               END amount5, CASE
                  WHEN level_num IN (6, 7, 8, 9, 10, 11, 12, 13, 14)
                     THEN NULL
                  ELSE amount6
               END amount6, CASE
                  WHEN level_num IN (7, 8, 9, 10, 11, 12, 13, 14)
                     THEN NULL
                  ELSE amount7
               END amount7, CASE
                  WHEN level_num IN (8, 9, 10, 11, 12, 13, 14)
                     THEN NULL
                  ELSE amount8
               END amount8, CASE
                  WHEN level_num IN (9, 10, 11, 12, 13, 14)
                     THEN NULL
                  ELSE amount9
               END amount9, CASE
                  WHEN level_num IN (10, 11, 12, 13, 14)
                     THEN NULL
                  ELSE amount10
               END amount10, CASE
                  WHEN level_num IN (11, 12, 13, 14)
                     THEN NULL
                  ELSE amount11
               END amount11, CASE
                  WHEN level_num IN (12, 13, 14)
                     THEN NULL
                  ELSE amount12
               END amount12, CASE
                  WHEN level_num IN (13, 14)
                     THEN NULL
                  ELSE amount13
               END amount13, CASE
                  WHEN level_num IN (14)
                     THEN NULL
                  ELSE amount14
               END amount14
          FROM (WITH with_tab AS
                     (SELECT '12345678901234' AS row_count, period, org_id, ref_no, bucket, amount2, amount3, amount4,
                             amount5, amount6, amount7, amount8, amount9, amount10, amount11, amount12, amount13,
                             amount14
                        FROM xxesl_perc_avg_all_v
                       WHERE ref_no = 11)
                SELECT     row_count, LEVEL level_num, period, org_id, ref_no, bucket, amount2, amount3, amount4,
                           amount5, amount6, amount7, amount8, amount9, amount10, amount11, amount12, amount13,
                           amount14
                      FROM with_tab t
                CONNECT BY LEVEL <= LENGTH (t.row_count) AND PRIOR row_count = row_count AND PRIOR SYS_GUID () IS NOT NULL))
--------------

REGEXP

Oracle / PLSQL: REGEXP_LIKE Condition

Oracle: SQL query that returns rows with only numeric values

a sql code to remove all the special characters from a particular column of a table

LISTAGG

Removing Duplicate Values From LISTAGG and REGEXP_REPLACE

Oracle 12c: MATCH_RECOGNIZE and LISTAGG: Vanquish ORA-01489: result of string concatenation is too long

Oracle - ORA-01489: result of string concatenation is too long [duplicate]

 ORA-01489: Oracle - ORA-01489: result of string concatenation is too long (LISTAGG)

XMLAGG

SELECT (SUBSTR( cast ( (SELECT REGEXP_REPLACE (rtrim( xmlcast( XMLAGG( xmlelement(e, gcc_ctld_link.segment3 || ' - ' || flv_ctld_link.description || ',') order by gcc_ctld_link.segment3 || ' - ' || flv_ctld_link.description) AS clob), ','),'([^,]+)(,\1)+', '\1') gl_account_segement3
                              -- REGEXP_REPLACE (LISTAGG (gcc_ctld_link.segment3 || ' - ' || flv_ctld_link.description, ',') WITHIN GROUP ( ORDER BY gcc_ctld_link.segment3 || ' - ' || flv_ctld_link.description),'([^,]+)(,\1)+', '\1')   gl_account_segement3
FROM  
ra_customer_trx_lines_all ctl,
ra_customer_trx_lines_all ctl_link,
ra_cust_trx_line_gl_dist_all ctld_link,
gl_code_combinations_kfv gcc_ctld_link,
fnd_flex_values_vl flv_ctld_link
 WHERE 1 = 1
AND ctl_link.customer_trx_line_id =     ctl.link_to_cust_trx_line_id
AND ctld_link.customer_trx_line_id =  ctl_link.customer_trx_line_id
--
AND ctld_link.code_combination_id =     gcc_ctld_link.code_combination_id
AND flv_ctld_link.flex_value_set_id = :p_flex_value_set_id
AND flv_ctld_link.flex_value = gcc_ctld_link.segment3
AND ctl_link.customer_trx_id = ctl.customer_trx_id) as varchar2(4000)), 1, 4000)) gl_account_segement3 FROM DUAL

 

 Connect With  ',' Separate all the row values 

with a (id,val) as

(select 10, 'x' from dual union all select 20, 'abc' from dual)

select listagg(val, ',') within group (order by id) as l_agg,

rtrim( xmlcast( xmlagg( xmlelement(e, val || ',') order by id) as clob), ',') as clob_agg

from a ;

CSV Data Loading

 

Import CSV File in Oracle Table Using Stored Procedure

How to select csv data stored in a BLOB column as if it were an external table?
 

Global Temporary Table

ORA-14452: attempt to create, alter or drop an index on temporary table already in use

External Table

External Tables Concepts
----------
DROP TABLE XXCUST.XXYCC_LOCATOR_DATA
CREATE TABLE XXCUST.XXYCC_LOCATOR_DATA
(
  organization_code      VARCHAR2(50),
 subinv_code         VARCHAR2(100),
 loc_segment1   VARCHAR2(50),
 loc_desc1  VARCHAR2(100),
 loc_segment2 VARCHAR2(50),
 loc_desc2   VARCHAR2(100),
 loc_segment3 VARCHAR2(50),
 loc_desc3 VARCHAR2(100),
 loc_segment4 VARCHAR2(50),
 loc_desc4 VARCHAR2(100),
 process_status VARCHAR2(50),
 record_number VARCHAR2(4)
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY  ODPDIR
     ACCESS PARAMETERS
       ( RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY  ','
    OPTIONALLY ENCLOSED BY '"' LRTRIM
    MISSING FIELD VALUES ARE NULL
    (
 organization_code      CHAR(50),
 subinv_code         CHAR(100),
 loc_segment1   CHAR(50),
 loc_desc1  CHAR(100),
 loc_segment2 CHAR(50),
 loc_desc2   CHAR(100),
 loc_segment3 CHAR(50),
 loc_desc3 CHAR(100),
 loc_segment4 CHAR(50),
 loc_desc4 CHAR(100),
 process_status CHAR(50),
 record_number CHAR(10)
)
              )
     LOCATION (ODPDIR:'rejected_locators_ravi.dat')
  )
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;
----------

SQL*Loader FAQ

API Wizard works with any API

RETCODE & ERRBUFF Parameters in Concurrent Program

Problem with Arabic Font in TOAD

OPTIMIZAR Hints

APPEND_VALUES Hint in Oracle Database 11g Release 2
APPEND Hint
Using Oracle's Parallel Execution Features
Oracle nologging tips
Oracle insert tuning
Optimizing GROUP and ORDER BY

ANALITICAL FUNCTIONS

Howto select first value in a group by bunch of rows

select
a.name
, MAX(c.task) KEEP (DENSE_RANK FIRST ORDER BY c.task_code) as task_code
, MAX(e.code_subject) KEEP (DENSE_RANK FIRST ORDER BY c.task_code) as code_subject
from .....
group by a.name

A Window into the World of Analytic Functions
LAST_VALUE
SQL: Rows between unbounded preceding
What does “preceding” mean in Oracle?

TOAD Option

How to ignore '&' in Executing Script in TOAD