Blog / Category
PL/SQL
118 articles in this category
WIP Job Close via Apex Application
Introduction / Issue Closing a WIP job in EBS normally means going through the Discrete Jobs form manually — checking if all transactions are done, confirming t…
Integration of Payment and SSO in Oracle Apex
Introduction/ Issue: The ATS Resume Scanner app needed two things before a user could actually use it: a login that didn’t require yet another password, and a w…
ATS Apex application with Ollama hosted in Cloud Fare
Introduction/ Issue: This is a walkthrough of the mini project that was built end to end in Oracle APEX – an ATS Resume Builder. It takes a user through…
Preventing Duplicate Form Submission in Oracle APEX
Introduction / Issue One of the common issues encountered while developing Oracle APEX applications is duplicate form submission. Users may unintentionally clic…
UI Mistakes Oracle APEX Developers Should Avoid
Introduction / Issue Many Oracle APEX applications successfully meet business requirements but often overlook one critical aspect—the User Interface (UI) and Us…
Calling Concurrent Program for Reference Document from APEX to EBS Application
Objective The objective of this document is to explain the process of invoking an Oracle EBS Concurrent Program from the Oracle APEX application using a PL/SQL…
Pre-requisite for the Sales Orders purging
Introduction In the oracle EBS the concept of Purging and before purging the order management related tables, pre-requisite to be done for the smooth purging pr…
Item Where-Used Analysis Report (Excel – PL/SQL)
Introduction: This blog has the SQL query that can be used for Item where used Report in Oracle Apps R12. Cause of the issue: Business wants a PLSQL excel based…
Centralized Content Management with APEX Shortcuts
Introduction: In Oracle APEX 21.2, managing label content across multiple pages can become inefficient and error-prone when updates are required. To centralize…
The Oracle Migration Challenge – Datatype Conversion Nightmares
When Database Migrations Go Wrong: Snowflake to Oracle Every database migration project starts with optimism: “We’ve mapped the tables, the data looks similar –…
From Slow to Lightning Fast: Optimizing SQL Bulk Operations with Python’s executemany()
The Problem: When Good Code Goes Slow Picture this: You have a Python application that needs to insert thousands of records into a database. Your code works per…
Oracle PLSQL to Generate XML Tag Using Standard Functionality
declare l_ctx dbms_xmlquery.ctxHandle; l_clob clob; begin l_ctx := dbms_xmlquery.newContext(‘select * from <TABLE_NAME>’); dbms_lob.createtemporary(:g_clob,true…
Oracle R12 SQL Query – Price List or Fee Schedule for Customer
select HP.PARTY_NAME “Customer Name”,HCAA.ACCOUNT_NUMBER,HCAA.ACCOUNT_NAME,qph.name “Price List Name”,HCSUA.SITE_USE_CODE,HCSUA.LOCATION from qp_list_headers qp…
Streams in Snowflake
In snowflake, Streams are a powerful feature designed for tracking changes (like inserts, updates, and deletes) to a table. They allow you to capture data chang…
Snowpipe (a continuous data ingestion pipeline) in Snowflake
To create a new Snowpipe (a continuous data ingestion pipeline) in Snowflake, follow these steps: Prerequisites Storage Integration: Set up a cloud storage inte…
Deterministic Functions in Oracle
Deterministic Functions in Oracle Overview A deterministic function in Oracle is a function that always returns the same result for the same input values and ha…
Blob File Move From DB to Directory
Introduction/ Issue: CSV blob file to raw file move from database table blob column to database directory. Why we need to do / Cause of the issue: We can move f…
File Store From Client to DB
Introduction/ Issue: CSV file move from client local machine to database table blob column. Why we need to do / Cause of the issue: We can store the CSV file fr…
Understanding SQL Server Execution Plans: A Beginner’s Guide
SQL Server execution plans are valuable tools for developers and database administrators aiming to optimize query performance.
SQL Server Indexes: The Ultimate GPS for Your Data Journey
We all have noticed how some SQL Server queries feel like a luxury express train, zooming to their destination, while others resemble a sluggish car through tra…
Conquering Primary Key Violation – SQL Server Replication
Transactional replication in SQL Server is a robust feature that allows you to replicate data from a primary database (publisher) to one or more secondary datab…
Creating Dynamic SQL Generator in Oracle PL/SQL
Table of Contents Introduction Technologies and Tools Used Steps Conclusion 1. Introduction The Dynamic SQL Generator in Oracle PL/SQL allows you to dynamically…
Oracle APEX Application Availability Using PL/SQL
Overview : The objective is to enhance Oracle APEX Application Availability through the implementation of PL/SQL code, ensuring uninterrupted and reliable opera…
How to Handle Client Secret Expiry in Azure Authentication for Oracle APEX
Overview : The primary objective of this content is to guide users on how to handle client secret expiry in Azure Authentication for Oracle APEX. Specifically,…
Interatcive Organizational Charts in Oracle APEX
Overview: This blog talks about organization charts in Oracle Apex using third-party libraries or plugins since Oracle APEX does not have a built-in organizatio…
Virtual Private Database (VPD) in Oracle APEX for securing data access
Overview: This blog talks about VPD (Virtual Private Database) in Oracle Apex, a feature that helps address security concerns by restricting access to data at t…
Active EBS users and their respective responsibilities in Oracle E-Business Suite
Every time a customer or client asks for a list of EBS Users and their responsibilities, the DBA team would generally execute the “Active Users” Concurrent Prog…
STEPS TO INSTALL SQLSERVER IN WINDOWS 10
STEP – 1 1 First download .netframework 3.5 Completed. Now the .net framework 3.5 has been installed in the windows system STEP-2 -Download software for…
METABASE INSTALLING IN ORACLE DATABASE
METABASE INSTALLING IN ORACLE DATABASE Requirements – Metabase requires a Java Runtime Environment (JRE), with a Java version of 11 or higher. – Check yo…
Query to get the executable details for the concurrent program or package And Query to get the Concurrent Program’s and their Request Groups
1-) Query to get the executable details for the concurrent program or package Introduction: This query is used to fetch the user concurrent program name, concur…
Interval Datatype in Oracle 23C
Introduction Oracle provides you with two date time data types: DATE and TIMESTAMP for storing point-in-time data. In addition, it provides the INTERVAL data ty…
Oracle 23-C New Features Table Value Constructor
Introduction The database’s SQL engine now supports a VALUES clause for many types of statements. This new clause allows for materializing rows of data on the f…
How to use a oracle stored procedure in jasper report.
Overview This document is about how to use a oracle stored procedure in jasper report. Technologies and Tools Used The following technologies have been used to…
Appending Image to The Email using Email Template Feature in Oracle APEX
Overview This document talks about how to Customize your html tags to show your images inline in Email body using Email Template feature in Oracle Apex. Technol…
Using DBMS_SQL.RETURN_RESULT in Oracle PL-SQL and C#
Introduction: In this post, we will explain how to use the DBMS_SQL.RETURN_RESULT procedure in Oracle PL/SQL to return a result set from a stored procedure and…
SQL Loader using External table
Introduction: This blog has the SQL loader using external Table. Cause of the issue: Business wants to load the data with a field having multiple line we can us…
How To Merge JSON Nodes In Existing JSON Data?
Overview This document is about how to merge JSON nodes with existing JSON data in oracle. Technologies and Tools Used The following technologies have been used…
How To Add or Remove Columns with Polymorphic Table Functions In Oracle PLSQL?
Overview This document is about how to add or remove columns from the input table based on the parameters you pass in oracle. Technologies and Tools Used The fo…
Generating an Excel file and Sending an Email using Oracle Apex
Overview : The process of generating an Excel file and sending it as an email attachment using PL/SQL packages in Oracle Apex is a powerful solution for automat…
Advanced Customization of LOV Items in Oracle Apex
Overview : Oracle Apex is a powerful platform for rapid application development that enables developers to create dynamic web applications effortlessly. The adv…
Invoking SQL loader from UNIX for multiple files
Overview This document says about how to load multiple data files with csv format using sql loader from UNIX and also explains about control file and database c…
Qualified Expressions for Associative Array
Overview This document talks about how Qualified Expressions for Associative Array works before and after the oracle 18c version. Technologies and Tools Used Th…
BLOCK STRUCTURES OF PL/SQL
INTRODUCTION: PL/SQL stands for Procedural Language/Structured Query Language. PL/SQL offers a set of procedural commands (IF statements, loops), organized with…
Display Image/PDF from BLOB in Interactive Grid using Oracle Apex URL Type Region
Introduction: For displaying the Blob content in the column in interactive grid, we don’t have a predefined column type for blob content display. This can be im…
How to run SQL tuning advisor manually in Oracle Database
Step 1: Get the hash value of the SQL using the SQL ID. Enter the SQL ID select a.plan_hash_value from v$sql a where sql_id=’&a’; Step 2: Create a tuning…
How to Recompile Invalid Schema Objects
When we are doing the operations like upgrades, patches, and DDL changes the associated schema objects will get invalid. So it is difficult to recompile all the…
How To PLAY_SOUND in Oracle Forms
Introduction Play sound is used to play audio files in Oracle Forms, Play Sound plays the sound object in the specified sound item PLAY_SOUND examples The follo…
Inline External Tables
Overview Inline external tables enable the run time definition of an external table to be part of a SQL statement, thus eliminating the need to create an extern…
Loading CSV Files from External Tables
Overview This topic provides examples of how to load CSV files from external tables under various conditions. Some of the examples build on previous examples. T…
19C LISTAGG DISTINCT
Overview The LISTAGG aggregate function now supports duplicate elimination by using the new DISTINCT keyword. The LISTAGG aggregate function orders the rows for…
Oracle Analytical Functions
The easiest way to understand analytic functions is to start by looking at aggregate functions. An aggregate function aggregates data from several rows into a s…
Break Comma Separated String
1. Overview This document will be helpful to print comma separated string with the new line report 2. Technologies and Tools Used Oracle Plsql code. 3. U…
Get working days between two working date excluding weekends
This function which will help you to get number of working days between two date excluding the weekends. This will help other to get logic between two working d…
oracle unpivot function
The Oracle UNPIVOT clause allows you to transpose columns to rows. The UNPIVOT clause is opposite to the PIVOT clause except that it does not de-aggregate data…
ORA-29532: Java call terminated by uncaught Java exception: java.lang.OutOfMemoryError – ecx_utils.INITIALIZE
When trying to submit one of GL report end with error. The error message shows ORA-29532: Java call terminated by uncaught Java exception. select * from *******…
Oracle R12 Query To Find Customer Notes
SELECT hca.account_number, ( SELECT hp.party_name FROM apps.hz_parties hp WHERE hp.party_id = hca.party_id ) customer_name, jnb.creation_date notes_creation_dat…
Oracle R12 GL To AR Transaction Details
SELECT DISTINCT –gcck.concatenated_segments, gcck.segment1 company, gcck.segment2 location, gcck.segment3 product, gcck.segment4 function, gcck.segment5 account…
Oracle R12 AR Lockbox Query
SELECT hou.name operating_unit, xep.name legal_entity, ala.lockbox_number “NUMBER”, absa.name batch_source, cebb.bank_name, –cebb.bank_number, –cebb.bank_branch…
Oracle SQL Query To Find Square Root For A Number
Syntax: SQRT(number); Query: select SQRT(25) from dual; Output: 5
Oracle SQL Query To Find Power Of A Number
Syntax: POWER(<base number>, <exponent number>) Query: select POWER(3,3) from dual; Output: This function return base number raised to the exponent’s power. 27
Oracle SQL Query To Return Negative Numbers Within Bracket
Syntax: to_char(-123456,’99G99G99G999PR’) Query: select to_char(-123456,’99G99G99G999PR’) from dual; Output: <1,23,456>
ORA-20000
ORA-20000 Programs that rely on PL/SQL can often be hit with run-time errors that occur due to faults in design, problems with coding, and a number of other iss…
Sending Email Using Oracle Apex and PL/SQL With Examples
Objective: Using Oracle Apex Package Apex mail, How we can frame mails and send with the help of oracle PL/SQL Program. Definition: We have 3 Proc…
Create a New PL/SQL Library
The steps in this section will show you how to create a new PL/SQL library, then create a function that will live in this library. To create the library: Launch…
How to fix INVALID status for RAC component in dba_registry
Before upgrade or after upgrade, database may show INVALID status for component Real Application Clusters in dba_registry as below . SQL> select comp_nam…
Generate DDL By comparing 2 Schema.
Table of Contents Overview Technologies and Tools Used Use Case Architecture Screen Shot 1. Overview This document will be helpful to compares Prod and Dev sche…
Split A String separated by Comma using oracle pl/sql Pipelined Fuction
Objective: To split the given string separated by special character as an individual column value using oracle pipelined Function. Step 1: Create table type of…
Table Alter Script by comparing 2 schema
Table of Contents Overview Technologies and Tools Used Use Case Architecture Screen Shot 1. Overview This document will be helpful to compares Prod and D…
RETURNING CLAUSE with EXECUTE IMMEDIATE
RETURNING Clause With EXECUTE IMMEDIATE 1. Overview The RETURNING clause allows you to retrieve values of columns (and expressions based on columns) that…
RETURNING CLAUSE with BULK COLLECT
RETURNING Clause Using Bulk Collect 1. Overview The RETURNING clause can return multiple rows of data, in which case you will use the RETURNING BULK COLLECT INT…
Returning Clause in Oracle Plsql
Use RETURNING Clause to Avoid Unnecessary SQL Statements 1. Overview The RETURNING clause allows you to retrieve values of columns (and expressions based on col…
Job DDL
Creates the DDL for the specified job:- SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON BEGIN DBMS_METADATA.set…
DDL of Foreign key
Script to create foreign key DDL on all tables. SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON BEGIN DBMS_META…
Converting rows to columns using Oracle Pivot clause
The Oracle introduced the PIVOT clause from oracle 11g. This makes converting rows to column easy. The value in the new columns must be the result of an aggrega…
Find the Invalid Email Address using Oracle SQL and PL/SQL
Objective: To find the validity of the email address using Oracle Pl/sql and to find the invalid email address from a object like table using Oracle Sql. Using…
oracle compound trigger to avoid mutating trigger
— Trigger to check if the sal greater than 100000, if yes, log the error message, else update sucessfully create or replace trigger trig_validate_sal before upd…
Caps Lock (On/Off) Indicator with JavaScript in Oracle APEX
Overview This document talks about how to indicate a message for whether the Caps Lock is on or off while inserting a password in login page by using JavaScript…
To Find IP Address of the User who login into Apex Application
This query will help to sql, Plsql and Apex Developer to find the user IP Address. Normally we tracking only page id, username and time of the user for login Hi…
Excel Template in oracle Apex for Excel Upload Page using JavaScript and Oracle pl/sql
Objective: The Excel Upload Page in Oracle Apex Involves Excel Upload template design. Usually the templates will be stored in Shared Components Workspace File…
PLSQL Stored Procedure to calculate number of particular day between two input dates.
CREATE OR REPLACE PROCEDURE PR_TOT_DAYS ( P_START_DATE IN DATE, P_END_DATE IN DATE, P_DAY IN VARCHAR2, P_TOT_DAYS OUT NUMBER ) IS LV_LOOP NUMBER; LV_DATE DATE;…
Technical Solutions For Customized Error Message using PL/SQL
Overview This document explains how to give a customized Error Message using minimal PL/SQL code in Oracle APEX. Technologies and Tools Used The following techn…
Query to Check locks on a table
Select object_id, session_id, oracle_username, os_user_name, Process, locked_mode From sys.v_$locked_object; Select a.object_name, b.oracle_username From all_ob…
Find The details about apex workspace ip, port and other client details
Attachment: apex-workspace-ip-port-and-other-client-details This is very helpful plsql code , which will give you details of your workspace, ip, port and…
Oracle R12 Customer TaxPayer ID Update API
/*Upload the customer data that needs to be updated in a temp table*/ CREATE TABLE scratch.RITM0669580 (account_number VARCHAR2(30), tax_payer_id VARCHAR2(20),…
Oracle R12 Customer Remittance Email Update API
DECLARE x_return_status VARCHAR2 (200) := NULL; x_msg_count NUMBER := 0; x_msg_data VARCHAR2 (200) := NULL; t_output VARCHAR2 (200) := NULL; t_msg_dummy VARCHAR…
Oracle R12 Customer Site Remittance Email Update API
DECLARE x_return_status VARCHAR2 (200) := NULL; x_msg_count NUMBER := 0; x_msg_data VARCHAR2 (200) := NULL; t_output VARCHAR2 (200) := NULL; t_msg_dummy VARCHAR…
Oracle R12 Journal Details Query
SELECT gjst.user_je_source_name source, ( SELECT gjct.user_je_category_name FROM apps.gl_je_categories_tl gjct WHERE gjh.je_category = gjct.je_category_name AND…
Pipelined Function for String Separation
We might face some scenarios for convert string data into rows. Here you can separate a string into rows by giving the string separator as input. Users can give…
query to check ORA errors
This query is used to check the ORA error in past one hour. query: COLUMN ORIGINATING_TIMESTAMP FORMAT A40 COLUMN message_text FORMAT A100 set linesize 300 SELE…
All About Oracle DB Audit
The auditing mechanism for Oracle is extremely flexible. Oracle stores information that is relevant to auditing in its data dictionary. Every time when a user a…
EBS – API to Create Bank Branch Account creation in single procedure
CREATE OR REPLACE PROCEDURE INSERT_SINGLE_BANKS IS x_response_rec apps.iby_fndcpt_common_pub.result_rec_type; l_init_msg_list VARCHAR2 (2000); o_bank_id NUMBER;…
Create Requisition interface against the blanket purchase agreement for different Locations
AIM: Component is used for Create the Requisition against the Blanket Purchase Agreement For different Locations. SCRIPT: CREATE OR REPLACE PACKAGE BODY APPS.XX…
Estimate space required for index creation
SET SERVEROUTPUT ON DECLARE v_used_bytes NUMBER(10); v_Allocated_Bytes NUMBER(10); BEGIN DBMS_SPACE.CREATE_INDEX_COST ( ‘ create index PROD.INDEX1 on PROD.EMP(E…
Global Temporary Table ( GTT) – key facts
The data in a GTT is written to the temporary tablespace, which is not directly protected by redo, so using a GTT improves performance by reducing redo generati…
Oracle 12c SQL Plan Directives – Disable | Enable | Use As Hint
What is it? In previous releases the database stored compilation and execution statistics in a shared sql area which is non persistent. Starting in 12c the data…
ORA-20005: object statistics are locked (stattype = ALL) – Solution
During tuning a query, I found one table has stale statistics. While running gather stats for that table, got below error. Let me demonstrate with a demo table:…
Ignore Duplicate Rows (ORA-00001) with the ignore_row_on_dupkey_index Hint
The simplest method is to add a hint to the query to overcome ORA-00001 Added in 11.2, the ignore_row_on_dupkey_index hint silently ignores duplicate values: in…
Create a Virtual Column:
A virtual column applies a function to a column in the table. The database only computes this at runtime. The value is not stored in the table. So instead of cr…
Covert to Operating Unit Timezone
Below is the function to get current operating unit Timezone. FUNCTION convert_to_ou_tz(p_date DATE, p_org_id NUMBER DEFAULT fnd_global.org_id) RETURN DATE IS l…
Email Validation using PL/SQL Function
Introduction This post describes the steps required for email validation using PL/SQL Function Script for Email Validation using Pl/Sql Function CREATE FUNCTION…
Script to get dependents for Employee
Description: Script to get dependents for employee describes dependencies between procedures, packages, functions, package bodies, and triggers accessible to th…
DB HINTS
Introduction DB Hints / Optimizer Hints can be used with SQL statements to alter execution plans. What is the purpose of DB Hints? DB Hints let you make decisio…
PROJECT UNBILLED BALANCES- FUSION
This report will display all project unbilled details. SELECT ppa.project_id, ppa.segment1 project_number, N…
HCKT Hard Limit Reached Events in Fusion
Purpose of the report is to display project hours and hard-limit amount with interactive mode. with XXANSR_PA_EVENTS_cte as (SELECT evt.ROWID as “ROW_ID”,  …
REVENUE TRANSFER REPORT (FUSION)
Purpose of this report is to derive all projects expenditure and event details with cost information. SELECT revenue_type, TO_CHAR (customer_id) cus…
ASSET DEPRECIATION LOGIC QUERY
This query fetches the details of those assets which are partially and fully depreciated. SELECT ASSET_CATEGORY_ID ,MAJOR_CATEG…
NEW ASSETS ADDITION QUERY
This query fetches the detailed information related to those assets which are added newly for a particular period. SELECT FAL.segment1 SOL_ID …
Map Member Functions For Multiset Operations
MAP member function are special functions used to compare objects.The MAP member functions are used for performing comparisons between a single attribute of an…
EXCEL UPLOAD IN APEX USING SELECT LIST TEMPLATE
Follow the below steps to achieve, the process of uploading a .XLSX file with multiple tabs with Select List in APEX using PL/SQL. ü Step 1 : Create…
.XLSX UPLOAD IN APEX USING PL/SQL
Follow the steps below to achieve, the process of uploading a .XLSX file in APEX ( 4,5 AND 18C) using PL/SQL. ü Step 1 : We need to create a P…
Email Validation using PL/SQL Function
Email Validation Function using plsql function CREATE FUNCTION f_email_validate (pi_email_id IN OUT VARCHAR2) RETURN BOOLEAN IS lv_n_c…
Script to replace junk characters
This script can be used to remove junk characters from a string CREATE OR REPLACE FUNCTION xx_Replace_Junk_Chars_func (p_string IN VARCHAR2) RETURN…
Generate xls, xlsx file in Oracle Directory Using Oracle PL/SQL
Objective: To generate xls, xlsx file in Oracle Directory using Oracle PL/SQL. Scenario: Customer asks us to generate excel report in oracle directory itself; f…
Generate csv file in Oracle Directory Using Oracle PL/SQL
Objective: To generate excel (csv) file in Oracle Directory using Oracle PL/SQL. Scenario: Customer asks us to generate excel report in oracle directory itself;…
Generate DBF file in Oracle Directory Using Oracle PL/SQL
Objective: To generate Database File (DBF) in Oracle Directory using Oracle PL/SQL. Scenario: Customer asks us to generate database file format report in oracle…
Trigger Enhancements in Oracle Database 11g Release 1
· Execution Order of Triggers · Compound Triggers · &…
Sending E-mail with Excel Attachment Using UTL_SMTP
The utl_smtp package is used to send e-mails from any database to a valid recipient/receipents with or without attachements.The generic code is given to send e-…
Load XML data into Oracle table
Steps to Load XML File to Oracle Tables Solution : Sample XML file has been used for illustration Step 1 : Create a directory using the below command. The below…