How to call a Function in Oracle?
Table of Contents
Trigger for preventing insertion of duplicate values in a table in Oracle
Table of Contents
Trigger for preventing insertion of
…Trigger to restrict salary within 1000
Table of Contents
Oracle function return Array of Varchar2
Oracle function return Array of Varchar2
To return an array from a function, we will store the array inside database.
Oracle will raise compilation error if we don't store the data inside DB.
Setup for return an array of Varchar2
…How To Pass Array in where clause in Oracle?
How To Pass Array in where clause in Oracle?
Array can be passed in WHERE clause of sql query and in PLSQL as well.
But to use any array in a SQL query, we will store the array data inside …
How to store string array in Oracle DB?
Table of Contents
How to convert Array to String in Oracle?
How to convert Array to String in Oracle?
We can convert an array to string both in SQL and PLSQL.
SQL Conversion:
In Oracle SQL we have a predefined aggregate function LISTAGG which can be used to convert an Array …
How to pass array in Oracle SQL query?
How to pass array in Oracle SQL query?
We can use PIPELINED TABLE FUNCTION to pass an array to Oracle SQL query.
We can also use normal function instead of Pipelined table function but from performance perspective Pipelined table function …
Oracle check if value exists in associative array
Oracle check if value exists in associative array:
Use EXISTS collection method to check if any value exists in associative array or not.
--Oracle check if value exists in associative array-- SET SERVEROUTPUT ON; DECLARE TYPE T_NUM IS TABLE OF…
How to exclude column from GROUP BY clause in Oracle?
How to exclude column from GROUP BY clause in Oracle?
To exclude column from GROUP BY clause we can use Analytical function.
SELECT A.EMPLOYEE_ID,
A.FIRST_NAME,
A.LAST_NAME,
A.JOB_ID,
A.SALARY,
A.DEPARTMENT_ID,
COUNT(1) OVER (PARTITION BY A.DEPARTMENT_ID, A.JOB_ID) JOB_WISE_EMP_COUNT,
SUM(A.SALARY) OVER (PARTITION BY …