Monthly Archives: January 2024

Email Checking Validation function

1. Overview This blog explains about, how we can Email validation using PLSQL function. 2. Technologies and Tools Used The following technology has been used to achieve the same. Ø Oracle PLSQL 3. Use Case To Create one function in database 18C 4. Architecture  Oracle Database Using Database 18C ORALCE PL/SQL 5. Examples Step 1: Create one function in Database. Create one function in database. Code: CREATE FUNCTION f_email_validate (pi_email_id IN OUT VARCHAR2)    RETURN BOOLEAN IS    lv_n_check_len      NUMBER;    lv_b_check_in_at    BOOLEAN;    lv_b_check_in_dot   BOOLEAN;    lv_v_extn           VARCHAR2 (10); BEGIN    lv_n_check_len := LENGTH (pi_email_id);    pi_email_id := LOWER (pi_email_id);    SELECT REPLACE (pi_email_id, ‘ ‘, ”)      INTO pi_email_id      FROM DUAL;    SELECT REPLACE (pi_email_id, ‘”‘, ”)      INTO pi_email_id…

Read More

NETWORK ACCESS CONTROL LIST (ACL)

Introduction: This blog explains Network Access control list. What is an ACL? Access Control List is a Network security feature that filters traffic on Layer 3 (IP Address) and Layer…

Read More

Invalid Component of Oracle XML Database

Step:-1 Check invalid Invalid Component:- SQL> SELECT COMP_NAME FROM DBA_REGISTRY WHERE STATUS=’INVALID’; COMP_NAME ——————————————————————————– Oracle XML Database   Step:-2 execute the below packages and triggers   ALTER PACKAGE XDB.DBMS_CLOBUTIL COMPILE BODY ;…

Read More

Step by step apply Rolling PSU Patch in Oracle Database 19c RAC environment

Patch Information: – Patch 30087906 – Database Release Update Revision 19.3.2.0.191015   Step: -1 Environment Details   export ORACLE_HOME=/u01/app/19c/grid export PATH=/u01/app/19c/grid/bin: $PATH [oracle@rac1 ~]$ srvctl config database -verbose oradb /u01/app/oracle/product/19c/dbhome_1 19.0.0.0.0…

Read More

Using Bulk Collect with FORALL

1. Overview This blog explains about, How to insert bulk records within a milli seconds by using Bulk Collect with FORALL. 2. Technologies and Tools Used The following technology has been used to achieve the same. Ø Oracle PLSQL 3. Use Case Create one procedure in database object. Run the procedure. 4. Architecture  Oracle Database 18C PLSQL 5. Examples Step 1: Create one Procedure in Database object like below: CREATE OR REPLACE PROCEDURE increase_salary (    department_id_in   IN employees.department_id%TYPE,    increase_pct_in    IN NUMBER) IS    TYPE employee_ids_t IS TABLE OF employees.employee_id%TYPE            INDEX BY PLS_INTEGER;     l_employee_ids   employee_ids_t;    l_eligible_ids   employee_ids_t;    l_eligible       BOOLEAN; BEGIN    SELECT employee_id      BULK COLLECT INTO l_employee_ids      FROM employees     WHERE department_id = increase_salary.department_id_in;    FOR indx IN 1 .. l_employee_ids.COUNT    LOOP       check_eligibility (l_employee_ids (indx),…

Read More

Using Webutil browse file from desktop to dir

1. Overview This blog explains about, How to Use Webutil browse file from desktop to directory . 2. Technologies and Tools Used The following technology has been used to achieve the same. Ø Oracle FORMS 3. Use Case Create the form like the below. Use the below code In load file “button” using “when-button-pressed” trigger. 4. Architecture  Oracle FORMS FORM Builder 5. Examples Step 1: Create one form like below: Step 2: using the below code In load file “button” using “when-button-pressed” trigger. Code: DECLARE    l_userhome            VARCHAR2 (200)          := webutil_clientinfo.get_system_property (‘user.home’)             || ‘\Desktop’;    l_filename            VARCHAR2 (200) := NULL;    l_bare_filename       VARCHAR2 (200) := NULL;    l_fileprefix          VARCHAR2 (200) := NULL;    l_serverfilename      VARCHAR2 (200) := NULL;    l_upload_path         VARCHAR2 (200) := NULL;    l_version_path        VARCHAR2 (200) := NULL;    l_option_grade_path   VARCHAR2 (200) := NULL;    l_size_curve_path     VARCHAR2 (200) := NULL;    l_filesize            NUMBER;    al_con                alert;    l_status              BOOLEAN;…

Read More

Oracle EBS – Query to concurrent details for past 60 days

oracle-ebs-query-to-concurrent-details-for-past-60-days SELECT distinct ft.user_concurrent_program_name “Concurrent Program Name”, fr.REQUEST_ID “Request ID”, to_char(fr.ACTUAL_START_DATE,’dd-MON-yy hh24:mi:ss’) “Started Date”, to_char(fr.ACTUAL_COMPLETION_DATE,’dd-MON-yy hh24:mi:ss’) “Completed Date”, decode(fr.PHASE_CODE,’C’,’Completed’,’I’,’Inactive’,’P’,’Pending’,’R’,’Running’,’NA’) “Phasecode”, decode(fr.STATUS_CODE, ‘A’,’Waiting’, ‘B’,’Resuming’, ‘C’,’Normal’, ‘D’,’Cancelled’, ‘E’,’Error’, ‘F’,’Scheduled’, ‘G’,’Warning’, ‘H’,’On Hold’,…

Read More

Oracle EBS – Query to get PO details

oracle-ebs-query-to-get-po-details Select * from ( SELECT TO_CHAR (SYSDATE, ‘DD-MON-YYYY HH:MI:SS’) rp_run_date, po.*   FROM (SELECT   ou, supplier_number, supplier_name, po_number,                  po_creation_date, po_approved_date,  …

Read More

Google Cloud Infrastructure Using Terraform

Introduction to Google Cloud and Terraform Google Cloud Platform provides a comprehensive suite of cloud services, including computing, storage, databases, machine learning, and more. With its global network infrastructure and…

Read More

WebADI Create Document Redirects From HTTP To HTTPS

  WebADI Create Document Redirects From HTTP To HTTPS   Error :=  Causing Error ERR_SSL_PROTOCOL_ERROR On Oracle EBS R12.2.5 the default URL is HTTP:// but when you navigate to Desktop…

Read More