COALESCE function in Oracle

Syntax

The syntax for the COALESCE function is:

COALESCE( expr1, expr2, ... expr_n )

The COALESCE function can be used in Oracle/PLSQL.

You could use the coalesce function in a SQL statement as follows:

SELECT COALESCE( address1, address2, address3 ) result

FROM suppliers;

The above COALESCE function is equivalent to the following IF-THEN-ELSE statement:

IF address1 is not null THEN

   result := address1;

ELSIF address2 is not null THEN

   result := address2;

ELSIF address3 is not null THEN

   result := address3;

ELSE

   result := null;

END IF;

How to check the Clone date of an Oracle instance

SELECT resetlogs_time FROM v$database;

Get DDL for view

select text from ALL_VIEWS where upper(view_name) like upper('<View name>');

Query to list customers and their sites information

/**********************************************************
*PURPOSE: To list customers and their sites information *
*AUTHOR: RAG *
**********************************************************/
SELECT
  ---------------------- --Customer Information ----------------------
  hp.party_id,
  hp.party_name "CUSTOMER_NAME",
  hca.cust_account_id,
  hca.account_number,
  hcas.org_id, 
  --------------------------- --Customer Site Information ---------------------------
  hcas.cust_acct_site_id,
  hps.party_site_number,
  hcsu.site_use_code, 
  ----------------------- --Customer Site Address -----------------------
  hl.address1,
  hl.address2,
  hl.address3,
  hl.address4,
  hl.city,
  hl.postal_code,
  hl.state,
  hl.province,
  hl.county,
  hl.country,
  hl.address_style
FROM hz_parties hp,
  hz_party_sites hps,
  hz_cust_accounts_all hca,
  hz_cust_acct_sites_all hcas,
  hz_cust_site_uses_all hcsu,
  hz_locations hl
WHERE 1                    =1
AND hp.party_id            = hca.party_id
AND hca.cust_account_id    = hcas.cust_account_id(+)
AND hps.party_site_id(+)   = hcas.party_site_id
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
  --
AND hps.location_id = hl.location_id(+)
  --
AND hp.party_type = 'ORGANIZATION' -- only ORGANIZATION Party types
AND hp.STATUS     = 'A'            -- only Active Parties/Customers
ORDER BY to_number(hp.party_number),
  hp.party_name,
  hca.account_number;

Oracle sequence starting with max value from a table

DECLARE
  ln_max_count NUMBER;
  v_sql        VARCHAR2(250);
BEGIN
DBMS_OUTPUT.PUT_LINE('Starting');
  SELECT 'CREATE SEQUENCE a MINVALUE 0 START WITH '
    ||MAX(attribute17)
    ||' INCREMENT BY 1 CACHE 20'
  INTO v_sql
  FROM HZ_CUST_ACCT_SITES_ALL;
  DBMS_OUTPUT.PUT_LINE('v_sql - '||v_sql);
  EXECUTE IMMEDIATE v_sql;
  DBMS_OUTPUT.PUT_LINE('End');
EXCEPTION
WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Error - '||SQLERRM);
END;

Datatype Limits

Datatypes	Limit	Comments
BFILE	Maximum size: 4 GB Maximum size of a file name: 255 characters Maximum size of a directory name: 30 characters Maximum number of open BFILEs: see Comments	The maximum number of BFILEs is limited by the value of theSESSION_MAX_OPEN_FILES initialization parameter, which is itself limited by the maximum number of open files the operating system will allow.
BLOB	Maximum size: (4 GB - 1) * DB_BLOCK_SIZEinitialization parameter (8 TB to 128 TB)	The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000Foot 1 ).
CHAR	Maximum size: 2000 bytes	None
CHAR VARYING	Maximum size: 4000 bytes	None
CLOB	Maximum size: (4 GB - 1) * DB_BLOCK_SIZEinitialization parameter (8 TB to 128 TB)	The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000Footref 1).
Literals (characters or numbers in SQL or PL/SQL)	Maximum size: 4000 characters	None
LONG	Maximum size: 2 GB - 1	Only one LONG column is allowed per table.
NCHAR	Maximum size: 2000 bytes	None
NCHAR VARYING	Maximum size: 4000 bytes	None
NCLOB	Maximum size: (4 GB - 1) * DB_BLOCK_SIZEinitialization parameter (8 TB to 128 TB)	The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000Footref 1).
NUMBER	999...(38 9's) x10125 maximum value -999...(38 9's) x10125 minimum value	Can be represented to full 38-digit precision (the mantissa) Can be represented to full 38-digit precision (the mantissa)
Precision	38 significant digits	None
RAW	Maximum size: 2000 bytes	None
VARCHAR	Maximum size: 4000 bytes	None
VARCHAR2	Maximum size: 4000 bytes	None

Footnote 1 The absolute maximum number of columns in a table is 1000. However, when you create an object table (or a relational table with columns of object, nested table, varray, or REF type), Oracle maps the columns of the user-defined types to relational columns, creating in effect hidden columns that count toward the 1000-column limit. For details on how Oracle calculates the total number of columns in such a table, refer to Oracle Database Administrator's Guide.

for reference - 

https://docs.oracle.com/cd/B28359_01/server.111/b28320/limits001.htm

How to Use FNDLOAD?

LDT (Data Loader Files)- This files are Used to upload & download setup data on different instances.
LCT (Data Config Files)- This files are used to create LDT Files. There are difference lct files for different sets of data.
Eg: Request Set- afcpreqg.lct, Concurrent Program- afcpprog.lct, Value Set- afffload.lct..etc..
** Now lets take a simple example where you want to migrate 1 concurrent program from Development (DEV) instance to Production (PROD).
Step 1: Login to Unix Development server and change the directory using following Command where you can store your LDT files:cd $CUST_TOP/ patch/115/import/US/ (where CUST_TOP is your custom top on server)
Step 2: Run the following command by supplying the parameters given in {} as per your needFNDLOAD {userid/password@connect_string} O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct {file_name.ldt} PROGRAM APPLICATION_SHORT_NAME="{your_application_short_name}" CONCURRENT_PROGRAM_NAME="{your_concurrent_name}"The above command will generate ldt file in your custom top directory ($CUST_TOP/ patch/115/import/US/ )
Step 3: Copy the LDT file from Development Server to your local desktop
Step 4: Login to Unix Production Server and copy the LDT files from local desktop to Production Server on following path:$CUST_TOP/ patch/115/import/US/ {file_name.ldt}
Step 5: Finally Run the following command by supplying all parameters in {} to upload concurrent program definition on production application server.$ FNDLOAD {userid/password@connect_string} 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct $CUST_TOP/patch/115/import/US/{file_name}.ldt
Note: 1) All above FNDLOAD commands are single line commands.2) All above steps will remain exactly same for all kind of setup data (Eg: Value Set, CP, Req.group, DFF…etc) except the name of LCT file (.lct). LCT file will be different for every different component because the setup data is different.