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
Read MoreOracle 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>
Read MoreQuery to handle with zero if no onhand quantities in Inventory or onhand is NULL
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 MoreCreate 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…
Read MoreHow 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_name,version,status from dba_registry; … Oracle Real Application…
Read MoreGenerate 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 schema and generates scripts for missing…
Read MoreJob 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_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true); DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY',…
Read MoreDDL 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_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true); DBMS_METADATA.set_transform_param…
Read MoreThe SQLAccess Advisor
DBMS_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 MoreConverting 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 aggregate function…
Read More