SQLPLUS - Spool Results to a file

One of the useful features of SQL Plus is that it will allow easy manipulation of result rows and columns. Here is a simple script that allows spooling results without header, line breaks, inputs, etc:

SET HEADING OFF
SET PAGESIZE 0
SET LINESIZE 400
SET TERMOUT OFF
SET VERIFY OFF
SET FEEDBACK OFF
SET ECHO OFF
SET SERVEROUTPUT OFF
SET TRIMS OFF
SET COLSEP "|"
SET CONCAT "."
SET NEWPAGE NONE
SET UNDERLINE OFF

COLUMN USER_ID FORMAT 99999999
COLUMN USER_NAME FORMAT A80
COLUMN LAST_LOGON_DATE FORMAT A12
COLUMN CREATION_DATE FORMAT A12
COLUMN CREATED_BY FORMAT 99999999
COLUMN LAST_UPDATE_DATE FORMAT A12
COLUMN LAST_UPDATED_BY FORMAT 999999
COLUMN LAST_UPDATE_LOGIN FORMAT 99999999
COLUMN START_DATE FORMAT A12
COLUMN DISPLAY_NAME FORMAT A80
COLUMN EMPLOYEE_ID FORMAT 99999999
COLUMN EMAIL_ADDRESS FORMAT A80
COLUMN PERSON_PARTY_ID FORMAT 99999999


SPOOL C:\MY_SQL_RESULTS.TXT;


SELECT US.USER_ID, US.USER_NAME, US.LAST_LOGON_DATE,
US.CREATION_DATE, US.CREATED_BY, US.LAST_UPDATE_DATE,
US.LAST_UPDATED_BY, US.LAST_UPDATE_LOGIN, US.START_DATE,
US.DESCRIPTION AS DISPLAY_NAME, US.EMPLOYEE_ID,
US.EMAIL_ADDRESS, US.PERSON_PARTY_ID
FROM APPS.FND_USER US
WHERE US.USER_ID BETWEEN 12345 AND 17654;


SPOOL OFF;


Usage:
1. Set column data type and width appropriately by the usage of DEFINE statements
2. Make sure to have the LINESIZE has more than the total required width for all columns (Eg: SET LINESIZE 600)
3. Change the query (SELECT ...) as per the requirement (This is a simple query, while many practical data spool requires multi page SQLs).
4. Make sure to change the output file path correctly (Otherwise it will overwrite the current file - SPOOL C:\MY_SQL_RESULTS.TXT;)

If heading is required in the spool output, please remove the line "SET HEADING OFF".
 

Organization Structures/Hierarchy - Oracle EBusiness Suite - R12 and 11i

Organization Structures/Hierarchy - Oracle EBusiness Suite - R12 and 11i ...

Oracle 11i and R12 support Multi-Org Architecture. In a very basic level it means that within a single implementation instance, you can have multiple Business Groups, Set of Accounting Books, Legal Entities, Operating Units and Inventory Organizations. The transactions (Material and Accounting) between these entities can be tracked and accounted within the EBusiness Suite.

Here are some simple SQLs that can identify the Organization Hierarchy within an instance.

1. Business Groups:
Business groups are the highest level classification within Organization Hierarchy model. If any multinational organization that has entirely different business lines are they are to be dealt separately by the system, multiple business groups can be used. For an example, if General Electric wants to operate Electric, Consumer, Industrial and Financial divisions independently, this structure can be used.

SELECT BUSINESS_GROUP_ID, ORGANIZATION_ID,
   NAME AS BUSINESS_GROUP_NAME, DATE_FROM AS START_DATE,
  LOCATION_ID, SHORT_NAME, LEGISLATION_CODE AS COUNTRY_CODE,
   CURRENCY_CODE, ENABLED_FLAG
FROM APPS.PER_BUSINESS_GROUPS


2. Legal Entities:
Legal Entities are defined for corporate accounting and tax purposes. Typically they align with the registration of companies for statutory, administrative or legal purposes.

SELECT LEGAL_ENTITY_ID, PARTY_ID, LEGAL_ENTITY_IDENTIFIER,
   NAME AS LEGAL_ENTITY_NAME, GEOGRAPHY_ID, TRANSACTING_ENTITY_FLAG,
   EFFECTIVE_FROM AS START_DATE, LE_INFORMATION_CONTEXT AS COUNTRY_CODE, CREATION_DATE
FROM APPS.XLE_ENTITY_PROFILES XLE


3. Operating Units:
Operating Units are possible sub-sections within Legal Entities that are used to define boundaries of responsibilities within transaction based modules. The examples modules are Order Management, Cash Management, Purchasing, Account Receivables, etc.

SELECT BUSINESS_GROUP_ID, ORGANIZATION_ID AD OPERATING_UNIT_ID, NAME AS OPERATING_UNIT_NAME,
   DATE_FROM AS START_DATE, SET_OF_BOOKS_ID, DEFAULT_LEGAL_CONTEXT_ID AS LEGAL_ENTITY_ID
FROM APPS.HR_OPERATING_UNITS
-- WHERE BUSINESS_GROUP_ID = 81
-- WHERE SET_OF_BOOKS_ID = 2023
-- WHERE OPERATING_UNIT = 101
-- WHERE DEFAULT_LEGAL_CONTEXT_ID = 23273


4. Inventory Organizations:
Inventory Organizations are used to track manufacturing and item tracking. Typically an Operating Unit will have multiple inventory organizations and each one will align to a single manufacturing plant. In many cases, it is also possible to have an item validation organization which is a logical Org with all available items within the OU.

SELECT ORGANIZATION_ID AS INV_ORG_ID, BUSINESS_GROUP_ID,
   USER_DEFINITION_ENABLE_DATE AS START_DATE, ORGANIZATION_CODE,
   ORGANIZATION_NAME AS INV_ORG_NAME, SET_OF_BOOKS_ID,
   CHART_OF_ACCOUNTS_ID, INVENTORY_ENABLED_FLAG, OPERATING_UNIT,
   LEGAL_ENTITY
FROM APPS.ORG_ORGANIZATION_DEFINITIONS
-- WHERE BUSINESS_GROUP_ID = 81
-- WHERE CHART_OF_ACCOUNTS_ID = 101
-- WHERE OPERATING_UNIT = 101
-- WHERE LEGAL_ENTITY = 23273


Here is a simple multi-org architecture diagram with different levels and relations:

Oracle Kill/Terminate Session: Example

 Introduction:

When a SQL session is hanging for a long time, it make sense to terminate/kill the session. This is to improve developer time as well as to free up system resources in server side.

Here are some scenarios where KILL SESSION is useful:

1. System is in deadlock
2. While testing a query, developer missed a join, which makes internal query processing prohibitively expensive
3. Some sessions are taking up a lot of resources and important processes or users get very slow response

 

Steps to Kill Session:

Find the Problem Session:

Identify the session that needs to be killed. Easiest way is to get the user by Database Schema/User name. But it is possible that a single DB user may have many sessions open due to multiple reasons. In that case, finding session is tricky. Use any of the WHERE clauses in the below query to identify the session.

 

-- get session by User/Schema
SELECT SID, SERIAL# FROM V$SESSION WHERE USERNAME = 'BAI';
 
-- Get session by other parameters/inputs
SELECT SID || ',' || SERIAL# AS KILLSTR, SID, SERIAL#, X.*
FROM V$SESSION X
WHERE USERNAME = 'APPS'
AND MACHINE NOT LIKE 'XXABCWEB%' -- REMOVE WEB SERVER TO DB Server Connection
AND OSUSER NOT IN ('APPLPRD') -- REMOVE UNIX AND APPLN SERVER USERS
AND PROGRAM IN ('TOAD.EXE', 'SQL DEVELOPER', 'SQLPLUS.EXE') -- FILTER BY SQL Client Program
AND ROWNUM < 21;

 

Kill/Terminate the Problem Session:

Use inputs from above query in the KILL command

ALTER SYSTEM KILL SESSION '<SID>,<SERIAL>' IMMEDIATE;
 
ALTER SYSTEM KILL SESSION '1794,64680' IMMEDIATE
/
 

 

Keywords:

Kill Session, Terminate, Oracle, ALTER SESSION, PL/SQL, Query, SQL, SQLPLUS

Oracle Date Conversion Functions : Random Examples

 

Introduction:

Some random examples of Oracle PL/SQL Date Conversion functions. Might be useful for some and thought it worth sharing.

 

Examples:

Get current system date in default format (Easy !):

select sysdate from dual;

  

Get current system date in different formats:

select 'x ' || to_char(sysdate, 'yyyymmdd') from dual;
 
select 'x ' || to_char(sysdate, 'yyyymmdd hh24miss') from dual;
 
select 'x ' || to_char(sysdate, 'hh24miss') from dual;

 

Number of seconds since beginning of the day:

-- number of seconds since beginning of the day
select 'x ' || to_char(sysdate, 'sssss') from dual;

  

Number of days since beginning of the year:

-- number of days since beginning of year
select 'x ' || to_char(sysdate, 'ddd') from dual;
 

Get System Timestamp:

select systimestamp from dual;

This will return time up to 6 decimal places. Very useful for performance tuning/improvement as the time is recorded with microsecond accuracy.

Few other Timestamp functions/examples:

select 'x ' || to_char(systimestamp, 'dd-mon-yyyy hh.mi.ss.ff4') from dual;
select 'x ' || to_char(systimestamp, 'yyyymmdd hhmissff4') from dual;
 
select 'x ' || to_char(systimestamp, 'yyyymmdd hhmissff6') from dual;
select 'x ' || to_char(systimestamp, 'hhmissff6') from dual;
select 'x ' || to_char(systimestamp, 'missff6') from dual;
 

Keywords:

Date Conversion, Format, Timestamp, Systimestamp, Date Function, Oracle, PL/SQL, Query, SQL, SQLPLUS

RegEx / Regular Expression: Folder Structure: Get File Name

 

Problem:

You have a list of file names in different directories. You need to get just the file name, stripping directory names.

Here is an example input:

/appsR122/ABCDEV/fs1/FMW_Home/webtier/instances/EBS_web_OHS1/diagnostics/logs/OHS/EBS_web/EBS_web.log
/appsR122/ABCDEV/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/oacore_server1/logs/access.log
/appsR122/ABCDEV/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/oacore_server1/logs/oacore_server1.log
/appsR122/ABCDEV/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/forms_server1/logs/access.log
/appsR122/ABCDEV/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/forms_server1/logs/forms_server1.log
/appsR122/ABCDEV/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/oacore_server2/logs/access.log
/appsR122/ABCDEV/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/oacore_server2/logs/oacore_server2.log
/appsR122/ABCDEV/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/forms_server2/logs/access.log
/appsR122/ABCDEV/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/forms_server2/logs/forms_server2.log
/appsR122/ABCDEV/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/AdminServer/logs/AdminServer.log
/appsR122/ABCDEV/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/AdminServer/logs/AdminServer-diagnostic.log
/appsR122/ABCDEV/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/oafm_server1/logs/oafm_server1.log
/appsR122/ABCDEV/fs1/FMW_Home/user_projects/domains/EBS_domain/aferror.log
/appsR122/ABCDEV/fs1/inst/apps/ABCDEV_hdctabcdevap/logs/appl/rgf/javacache.log
/appsR122/ABCDEV/fs1/inst/apps/ABCDEV_hdctabcdevap/logs/10890.dispatcher.log

 

Expected Output:

Just names of the files:

EBS_web.log
access.log
oacore_server1.log
access.log
forms_server1.log
access.log
oacore_server2.log
access.log
forms_server2.log
AdminServer.log
AdminServer-diagnostic.log
oafm_server1.log
aferror.log
javacache.log
10890.dispatcher.log
 

 

Query:

Use Regular Expression to strip out first part and keep only delimiter:

SELECT REGEXP_SUBSTR(<Input String>, '[^/]+$', 1, 1)
FROM DUAL;
 

Eg:

SELECT REGEXP_SUBSTR('/appsR122/ABCDEV/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/oacore_server1/logs/access.log', '[^/]+$', 1, 1)
FROM DUAL;
 

This can be used to last part of any string, separated by a delimiter. Just substitute the second parameter ('[^/]+$') for the new delimiter.

 

 

Keywords:

RegEx, Regular Expression, REGEXP_SUBSTR, String Function, PL/SQL, Query, SQL, SQLPLUS

 

TCA Basic Queries: Country, State, Zip

 Introduction:

Basic SQLs to get list of Countries, States and Zips (Postal Codes) from Oracle EBS TCA (Trading Community Architecture) Setup.

 

SQLs:

List of Countries:

-- get all countries
select * from FND_TERRITORIES;
 

 

List of City / State / Zip Combinations:

-- get all state/zip/city combinations
SELECT GEOGRAPHY_ELEMENT4 CITY, GEOGRAPHY_ELEMENT3 COUNTY,
    GEOGRAPHY_ELEMENT2_CODE STATE, GEOGRAPHY_ELEMENT5 ZIP_CODE,
    GEOGRAPHY_ELEMENT1_CODE COUNTRY
FROM HZ_GEOGRAPHIES
WHERE GEOGRAPHY_ELEMENT5_ID = GEOGRAPHY_ID AND GEOGRAPHY_ELEMENT1_CODE = 'US';

 

Note: Based on Address Validation profile option, the system will validate and give error, if the City / State / Zip Combination mismatch while address input.

 

List of State Codes (Eg: AL, NY, MO, IL, etc):

-- get all state codes
SELECT DISTINCT GEOGRAPHY_ELEMENT2_CODE STATE
FROM HZ_GEOGRAPHIES
WHERE GEOGRAPHY_ELEMENT5_ID = GEOGRAPHY_ID AND GEOGRAPHY_ELEMENT1_CODE = 'US';

 

Keywords:

TCA, Customer Address, Bill To Address, Ship To Address, Trading Community Architecture, FND_TERRITORIES, HZ_GEOGRAPHIES, HZ_PARTIES, HZ_PARTY_SITES, HZ_LOCATIONS, EBS, R12, Query, SQL, SQLPLUS

 

Oracle PL/SQL – Create Temp Table (T0101) Complete Script

 

Business Need:

Have a basic local table in Oracle. Can be used for any logging or tracking of program execution by inserting data. The indexes and timesstamps are pre-populated by database triggers.

 

SQL Script (Execute one by one, not as a single script):

Create Table:

C1 – Index, populated by trigger

C2 – Main content, add from program

C3 – Timestamp, populated by trigger

C4 – SYSTIMESTAMP, required only for microsecond time (may be required in SQL Performance Optimization). Otherwise remove or do not use)

CREATE TABLE T0101 (
            C1 NUMBER,
            C2 VARCHAR2(4000),
            C3 DATE DEFAULT SYSDATE,
            C4 TIMESTAMP DEFAULT SYSTIMESTAMP);
 
 
CREATE PUBLIC SYNONYM T0101 FOR APPS.T0101;
 
COMMIT;

Create Sequence for Primary Key:

 
CREATE SEQUENCE T0101SEQ
  START WITH 1
  MAXVALUE 999999999999999999999999999
  MINVALUE 1;
 
 
COMMIT;

 

Create DB Trigger:

 
CREATE OR REPLACE TRIGGER T0101B4I
  BEFORE INSERT
ON T0101   FOR EACH ROW
DECLARE
    V_C1   NUMBER;
BEGIN
    SELECT T0101SEQ.NEXTVAL INTO V_C1 FROM DUAL;
 :NEW.C1 := V_C1;
EXCEPTION
    WHEN OTHERS THEN
      RAISE;
END T0101B4I;
/
 
 
GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE, ON COMMIT REFRESH, QUERY REWRITE, DEBUG, FLASHBACK ON  T0101 TO PUBLIC WITH GRANT OPTION;
 
 
COMMIT;
 

Example Insert/Select Scripts:

 
INSERT INTO T0101 (C2) VALUES ('TEST2');
COMMIT;
 
 
SELECT C1, C2, C3, C4 FROM T0101
WHERE C3 > SYSDATE-1
ORDER BY C1 DESC;
 
 
COMMIT;
 

Insert script from program:

 
INSERT INTO T0101 (C2) select 'SID:'||sys_context('userenv','sessionid')||';SoldTo:'||p_sold_to_org_id from dual;
 
 
CREATE OR REPLACE PROCEDURE t0101_log(V_STRING VARCHAR2) IS
   PRAGMA autonomous_transaction;
BEGIN
       INSERT INTO T0101 (C2) VALUES ('x'||V_STRING);
       COMMIT;
 
end t0101_log;
/
 
COMMIT;
 

Keywords:

Oracle EBS, R12, R12.2.8, Oracle Applications

XML, PL/SQL, Database, 19c, 12.1.0.0, SQL Plus, Parser, APEX JSON, Patch