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