Converting a Cooma Seperated List to a 'Oracle DB TABLE TYPE collections'

Here input string is 'Apple,  Orange   ,Mango,Grapes' and output is a table as test_table


create or replace TYPE test_type AS TABLE OF VARCHAR2(100);

DECLARE
test_table test_type;
v_counter INTEGER := 0;
BEGIN
 SELECT ltrim(rtrim(regexp_substr('Apple,  Orange   ,Mango,Grapes','[^,]+',1,level))) regexp_substr
 BULK COLLECT INTO test_table FROM dual CONNECT BY level<=regexp_count('Apple,  Orange   ,Mango,Grapes',',')+1;


--Check the data

FOR i in 1..test2.COUNT
LOOP
SYS.DBMS_OUTPUT.PUT_LINE(test_table(i));
END LOOP;

end;

BI Applications: Trees in Peoplesoft Simplified

BI Applications: Trees in Peoplesoft Simplified: Trees in PSFT can be broadly classified into Summer Trees and Winter Trees. I will take the same example of Department for both the cases so...

BI Applications: Resetting the password for SYS User

BI Applications: Resetting the password for SYS User: "Many times we tend to forget the password of the SYS user. In such cases log onto the machine which hosts the DB server and connect as SYSD..."

Analytical Functions

Very good article on Analytical functions -

http://orafaq.com/node/55

BI Applications: PeopleSoft Set Id

BI Applications: PeopleSoft Set Id

BI Applications: DB links and Getting DDL from sqlplus

BI Applications: DB links and Getting DDL from sqlplus: "DB link create Syntax - CREATE DATABASE LINK dblink_name CONNECT TO user IDENTIFIED BY pwd USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP..."

BI Applications: UNSPSC

BI Applications: UNSPSC: "To be frank I had thought UNSPSC to be Unspecified. Later I realized its a standard or a convention used to classify all products and servic..."

BI Applications: Overrides in Informatica

BI Applications: Overrides in Informatica: "Few points regarding overrides in INFA - Source Qualifier Override - 1. The number of columns in the SQ query should match with the numbe..."

BI Applications: Configuring INFA Connections in DAC

BI Applications: Configuring INFA Connections in DAC: "Configuring the physical connections can be quiet daunting for Informatica DW server and REP server. First - Provide all the details in Set..."

BI Applications: Privileges within a PLSQL procedure

BI Applications: Privileges within a PLSQL procedure: "Oracle expects that we mention explicitly what kind of privileges the PLSQL procedure has. If we do not specify it properly there are chance..."

BI Applications: Accessing Table Names of other Schemas

BI Applications: Accessing Table Names of other Schemas: "Many times we would want to select the table names from another schema. In such cases below SELECT would help.. select TABLE_NAME FROM ALL_..."

BI Applications: Database Character Set of a DB

BI Applications: Database Character Set of a DB: "This query will help in finding the database character set of an oracle db. Select value from SYS.NLS_DATABASE_PARAMETERS where PARAMETER =..."

BI Applications: Difference Between COALESCE and NVL

BI Applications: Difference Between COALESCE and NVL: "COALESCE and NVL are used to achieve the same functionality of providing a default value in case the column returns a NULL. The differences..."