Objective: Query to handle with zero if no onhand quantities in Inventory or onhand is NULL SQL: Select NVL(MAX(COUNT(moq.TRANSACTION_QUANTITY)),0) –MAX(NVL(SUM(moq.TRANSACTION_QUANTITY),0)) from mtl_onhand_quantities moq, MTL_ITEM_LOCATIONS_KFV mil, mtl_system_items_b msib where moq.LOCATOR_ID…
Read MoreThe 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…
Read MoreBefore upgrade or after upgrade, database may show INVALID status for component Real Application Clusters in dba_registry as below . SQL> select comp_name,version,status from dba_registry; … Oracle Real Application…
Read MoreTable of Contents Overview Technologies and Tools Used Use Case Architecture Screen Shot 1. Overview This document will be helpful to compares Prod and Dev schema and generates scripts for missing…
Read MoreCreates 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_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true); DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY',…
Read MoreScript 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_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true); DBMS_METADATA.set_transform_param…
Read MoreDBMS_ADVISOR :- 1. Gather a number of SQL statements that will form the tuning workload. 2. Check that the user running DBMS_ADVISOR has the ADVISOR privilege, and has SELECT access…
Read MoreThe 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 aggregate function…
Read MoreObjective: 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 Pl/Sql: DECLARE…
Read MoreThis 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…
Read More