Wednesday, July 26, 2017

2. Oracle's Built-in Data Types

2. Oracle's Built-in Data Types

I.Q. 7. what is mean Data type in SQL ? And explain its different types.
A data type is a unit of data storage in a software system.
Some of Oracle's built-in data types are:
Data Types
1.     Number
2.    Char
3.    Varchar ( or ) Varchar2
4.    Date
5.    Time Stamp
6.    Long
7.    Raw
8.    Long Raw
9.    Lob ( Clob, Blob, Bfile & NCLob )

1)   Number Data Type :
It allows only numeric values
Maximum size is 38 digits
Syntax :
X     Number ( P, (S));
P  => It allow show many digits to store
S  => Size
Ex : X   Number (5,2)

      2)   Character Data type:
1.     Char
2.    Varchar and Varchar2
3.    Long

a)   Char:
It allows alphanumeric characters ( Numbers + Characters )
Maximum size is 2000 Bytes/ Characters
Syntax :  X     Char( S );

b)   Varchar ( or ) Varchar2:
It allows alphanumeric characters
Max size 4000 Bytes/ Characters
Memory allocation is dynamic
Syntax :X     Varchar2( S );

      3)   Date:
It is used to store date values
Max size is 7 Bytes
Syntax :X      Date;

Timestamp:
It is used to store date along with fraction of seconds.
Syntax :X       Timestamp;

      4)   Long:
It is used to store information
Max size is 2 GB.
Only once we have to use in entire table.
Syntax : X       Long;

      5)   Raw:
It is used to store images
Max size is 2000 Bytes.
Syntax :X       Raw;

      6)   Longraw:
It is used to store information as well as images.
Max size is 2GB
Syntax :X      Longraw;

      7)   Lob:
a)Clob :
It is used to store huge information
Max size is 4 GB
Syntax :X       Clob;

b)Blob :
It is used to store images but in the form of binary format.
Max size is 4 GB
Syntax :X       Blob;

c)Bfile :
It is used to store the files.
Max size is 4 GB.
Syntax :X       Bfile;

d)NCLob:
It is used to store multiple languages ( Unicode Format )

I.Q. 8 What is difference between char, Varchar and Varchar2 ?
Char
Varchar
Varchar2
Fixed length data type

Maximum size 2000 byte

String values will be space/blank padded before stored on disk.

CHAR is faster than VARCHAR

If this type is used to store variable length strings, it will waste a lot of disk space.

Variable length data type

Maximum size 2000 byte

VARCHAR is going to be replaced by VARCHAR2 in next version.
Variable length data type

Maximum size 4000 byte


I.Q. 9 What is Boolean Data type?
A BOOLEAN data type enables you to represent logical values. In code, BOOLEAN values are represented by values for "no" and "yes" (in any combination of uppercase and lowercase characters). You can represent BOOLEAN values, using:
·         YES, TRUE, ON
·         NO, FALSE, OFF


1. SQL Introduction

1. SQL Introduction
Q 1 What is mean Data base?
Data base is a set of data. A data base is collection of data file and programs that manipulate those data file. This is storing two type of information user data and Meta data (data about data).

Q 2 What is mean Table Space?
the database is divided into one or more logical storage units called as table spaces.


Q 3 What is mean Data File?
The table space in an Oracle database consists of one or more physical data files.

Q 4 What is mean Oracle Instance?
A data base instance (also known as server) is a set of memory Structure and background process that access a set of data files.
1) Development instance
2) Test Instance
          3) Production Instance.
  
Q. 5.  What is mean SQL and where does it come from?
ü  Structured Query Language (SQL) is a language that provides an interface to relational database systems.
ü  The proper pronunciation of SQL, and the preferred pronunciation within Oracle Corp, is "sequel" and not "ess cue ell".
ü  SQL is an English like language consisting of commands to store, retrieve, maintain & regulate access to your database.
ü  SQL was developed by IBM in the 1970s for use in System R, and is a de facto standard, as well as an ISO and ANSI standard.
ü  In common usage SQL also encompasses DML (Data Manipulation Language), for INSERTs, UPDATEs, DELETEs and DDL (Data Definition Language), used for creating and modifying tables and other database structures.
ü  The development of SQL is governed by standards. A major revision to the SQL standard was completed in 1992, called SQL2. SQL3 support object extensions and are (partially?) implemented in Oracle8 and 9i.

Example SQL statements:
1)  Create table table_name 
    (column_name1 number,  column_name2 varchar2(30));
2)  Insert into table_name values (1, 'xyz');
3) Select * from table_name where column_name2 = 'xyz';

Q.6. which structure can you implement for the database to speed up table reads?
Follow the rules of DB tuning we have to: 
ü  Properly use indexes (different types of indexes)
ü  Properly locate different DB objects across different table spaces, files and so on..
ü  Create a special space (table space) to locate some of the data with special data type ( for example CLOB, LOB and …)

Monday, March 2, 2015

Oracle BOM Routing Details Report


Oracle BOM Routing Details Report :
SELECT
MSI.SEGMENT1 ROUTING_ITEM,
BOR.ROUTING_SEQUENCE_ID,
TO_CHAR(DECODE(FU.EMAIL_ADDRESS , NULL , FU.DESCRIPTION , FU.EMAIL_ADDRESS )) ROUTING_CREATED_BY,
TO_CHAR(BOR.CREATION_DATE,'DY DD-MON-YYYY HH12:MI:SS') ROUTINGS_CREATION_DATE,
TO_CHAR(DECODE(FU1.EMAIL_ADDRESS , NULL , FU1.DESCRIPTION , FU1.EMAIL_ADDRESS ))  ROUTINGS_LAST_UPDATED_BY,
TO_CHAR(BOR.LAST_UPDATE_DATE,'DY DD-MON-YYYY HH12:MI:SS') ROUTINGS_LAST_UPDATE_DATE,
TO_CHAR(DECODE(FU2.EMAIL_ADDRESS , NULL , FU2.DESCRIPTION , FU2.EMAIL_ADDRESS )) ROUTINGS_LAST_UPDATE_LOGIN,

BOS.OPERATION_SEQ_NUM,
BSO.OPERATION_CODE ,
BOS.OPERATION_DESCRIPTION,
BD.DEPARTMENT_CODE,
TO_CHAR(DECODE(FU3.EMAIL_ADDRESS , NULL , FU3.DESCRIPTION , FU3.EMAIL_ADDRESS )) OPERATION_SEQ_CREATED_BY,
TO_CHAR(BOS.CREATION_DATE,'DY DD-MON-YYYY HH12:MI:SS') OPERATION_CREATION_DATE,
TO_CHAR(DECODE(FU4.EMAIL_ADDRESS , NULL , FU4.DESCRIPTION , FU4.EMAIL_ADDRESS )) OPERATION_SEQ_LAST_UPDATED_BY,
TO_CHAR(BOS.LAST_UPDATE_DATE,'DY DD-MON-YYYY HH12:MI:SS') OPERATION_SEQ_LAST_UPDATE_DATE,
TO_CHAR(DECODE(FU5.EMAIL_ADDRESS , NULL , FU5.DESCRIPTION , FU5.EMAIL_ADDRESS )) OPR_SEQ_LAST_UPDATE_LOGIN,


BOC.RESOURCE_SEQ_NUM,
BR.RESOURCE_CODE,
BOC.BASIS_TYPE,
BOC.USAGE_RATE_OR_AMOUNT,
BOC.USAGE_RATE_OR_AMOUNT_INVERSE,
TO_CHAR(DECODE(FU6.EMAIL_ADDRESS , NULL , FU6.DESCRIPTION , FU6.EMAIL_ADDRESS )) RESOURCE_CREATED_BY,
TO_CHAR(BOC.CREATION_DATE,'DY DD-MON-YYYY HH12:MI:SS') RESOURCE_CREATION_DATE,
TO_CHAR(DECODE(FU7.EMAIL_ADDRESS , NULL , FU7.DESCRIPTION , FU7.EMAIL_ADDRESS )) RESOURCE_LAST_UPDATED_BY,
TO_CHAR(BOC.LAST_UPDATE_DATE,'DY DD-MON-YYYY HH12:MI:SS') RESOURCE_LAST_UPDATE_DATE,
TO_CHAR(DECODE(FU8.EMAIL_ADDRESS , NULL , FU8.DESCRIPTION , FU8.EMAIL_ADDRESS )) RESOURCE_LAST_UPDATE_LOGIN

FROM 
APPS.MTL_SYSTEM_ITEMS MSI,
APPS.BOM_OPERATIONAL_ROUTINGS BOR,
APPS.BOM_OPERATION_SEQUENCES_V BOS,
APPS.BOM_DEPARTMENTS BD,
APPS.BOM_RESOURCES BR,
APPS.BOM_OPERATION_RESOURCES BOC,
APPS.BOM_STANDARD_OPERATIONS BSO,

APPS.FND_USER FU,
APPS.FND_USER FU1,
APPS.FND_USER FU2,
APPS.FND_LOGINS FL,

APPS.FND_USER FU3,
APPS.FND_USER FU4,
APPS.FND_USER FU5,
APPS.FND_LOGINS FL1,

APPS.FND_USER FU6,
APPS.FND_USER FU7,
APPS.FND_USER FU8,
APPS.FND_LOGINS FL2 

WHERE
MSI.ORGANIZATION_ID     ='123'
AND MSI.SEGMENT1                 = '410215' 
AND MSI.INVENTORY_ITEM_ID     = BOR.ASSEMBLY_ITEM_ID
AND MSI.ORGANIZATION_ID       = BOR.ORGANIZATION_ID
AND BOR.ROUTING_SEQUENCE_ID     = BOS.ROUTING_SEQUENCE_ID
AND BOS.DEPARTMENT_ID           = BD.DEPARTMENT_ID
AND BOR.ROUTING_SEQUENCE_ID      = BOR.COMMON_ROUTING_SEQUENCE_ID
AND BOS.OPERATION_SEQUENCE_ID    = BOC.OPERATION_SEQUENCE_ID
AND BR.RESOURCE_ID                 = BOC.RESOURCE_ID
AND BOS.STANDARD_OPERATION_ID = BSO.STANDARD_OPERATION_ID (+)

AND FU.USER_ID  = BOR.CREATED_BY
AND FU1.USER_ID = BOR.LAST_UPDATED_BY
AND FL.LOGIN_ID (+)= BOR.LAST_UPDATE_LOGIN
AND FU2.USER_ID (+)= FL.USER_ID

AND FU3.USER_ID = BOS.CREATED_BY
AND FU4.USER_ID = BOS.LAST_UPDATED_BY
AND FL1.LOGIN_ID (+)= BOS.LAST_UPDATE_LOGIN
AND FU5.USER_ID (+)= FL1.USER_ID

AND FU6.USER_ID (+)= BOC.CREATED_BY
AND FU7.USER_ID (+)= BOC.LAST_UPDATED_BY
AND FL2.LOGIN_ID (+)= BOC.LAST_UPDATE_LOGIN
AND FU8.USER_ID (+) = FL2.USER_ID
ORDER BY MSI.SEGMENT1 , BOS.OPERATION_SEQ_NUM , BOS.CREATION_DATE ,BOC.RESOURCE_SEQ_NUM

Wednesday, February 25, 2015

Inventory Part Numbers with Catalog Values Details Report


Inventory Part Numbers with Catalog Values Details Report : 

SELECT UNIQUE
OOD.ORGANIZATION_NAME "ORGANIZATION NAME #",
MSI.SEGMENT1 "PART NUMBER #",
MIAV.ELEMENT_SEQUENCE "ELEMENT SEQUENCE #",
MIAV.ELEMENT_NAME "ELEMENT NAME #”,
MIAV.ELEMENT_VALUE "ELEMENT VALUES #",
FU.USER_NAME "ELEMENT CREATED BY",
TO_CHAR (MIAV.CREATION_DATE,'DY DD-MON-YYYY HH:MI:SS AM') "ELEMENT CREATION DATE",
FU1.USER_NAME "ELEMENT LAST UPDATED BY",
TO_CHAR (MIAV.LAST_UPDATE_DATE,'DY DD-MON-YYYY HH:MM:SS AM') "ELEMENT LAST UPDATED DATE" ,
MSI.ITEM_CATALOG_GROUP_ID "CATALOG GROUP ID #",
MICGB.SEGMENT1||' ' || MICGB.SEGMENT2||' '||MICGB.SEGMENT3 "CATALOG GROUP NAME #"            
FROM
APPS.MTL_SYSTEM_ITEMS MSI,
APPS.MTL_DESCR_ELEMENT_VALUES MIAV,
APPS.ORG_ORGANIZATION_DEFINITIONS OOD,
APPS.FND_USER FU,
APPS.FND_USER FU1,
APPS.MTL_ITEM_CATALOG_GROUPS_B MICGB
WHERE
MSI.INVENTORY_ITEM_ID=MIAV.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID=121
AND MSI.ORGANIZATION_ID=OOD.ORGANIZATION_ID
AND FU.USER_ID=MIAV.CREATED_BY
AND FU1.USER_ID=MIAV.LAST_UPDATED_BY
AND MSI.ITEM_CATALOG_GROUP_ID = MICGB.ITEM_CATALOG_GROUP_ID (+)
AND MSI.SEGMENT1 IN ( ‘PART NUMBER / OR LIST OF PART NUMBERS’)
ORDER BY
MSI.ITEM_CATALOG_GROUP_ID,
MSI.SEGMENT1,
MIAV.ELEMENT_SEQUENCE