Difference between %ROWTYPE and %TYPE Variable

 

Simple / Single Variable  :-                 VAR_1 VARCHAR2(100);

TABLE_NAME%ROWTYPE :-                VAR_1    XX_EMP%ROWTYPE

CURSOR_NAME%ROWTYPE :-  (Multi_table ) :-   CURSOR_NAME%ROWTYPE

TABLE_NAME.COLUMN_NAME%TYPE :-      VAR_1    XX_EMP.SAL%TYPE

 

EMPNO

ENAME

JOB

MGR

HIREDATE

SAL

COMM

DEPTNO

7369

SMITH

CLERK

7902

17-Dec-80

800

 

20

7499

ALLEN

SALESMAN

7698

20-Feb-81

1600

300

30

7521

WARD

SALESMAN

7698

22-Feb-81

1250

500

30

7566

JONES

MANAGER

7839

02-Apr-81

2975

 

20

7654

MARTIN

SALESMAN

7698

28-Sep-81

1250

1400

30

7698

BLAKE

MANAGER

7839

01-May-81

2850

 

30

7788

SCOTT

ANALYST

7566

09-Dec-82

3000

 

20

7844

TURNER

SALESMAN

7698

08-Sep-81

1500

0

30

7876

ADAMS

CLERK

7788

12-Jan-83

1100

 

20

7900

JAMES

CLERK

7698

03-Dec-81

950

 

30

7902

FORD

ANALYST

7566

03-Dec-81

3000

 

20

7782

CLARK

MANAGER

7839

09-Jun-81

2450

 

10

7839

KING

PRESIDENT

 

17-Nov-81

5000

 

10

7934

MILLER

CLERK

7782

23-Jan-82

1300

 

10

9001

Manoj

CLERK

 

08-Nov-20

1000

450

40

 

 

--====================================================

Use of Normal Variable

--====================================================

 

DECLARE

 

V_NAME VARCHAR2(100);

 

BEGIN

 

select ENAME INTO V_NAME

from XX_EMP

WHERE EMPNO = '7369';

 

dbms_output.put_line ('Employee Name : '|| V_NAME);

 

END;

 

 

 

--====================================================

Use of   %ROWTYPE

--====================================================

 

DECLARE

 

V_ENAME VARCHAR2(100);

V_JOB VARCHAR2(100);

V_MGR VARCHAR2(100);

V_HIREDATE  VARCHAR2(100);

V_SAL  NUMBER;

V_DEPTNO VARCHAR2(100);

 

BEGIN

 

select  ENAME,       JOB,       MGR,       HIREDATE,      SAL,        DEPTNO

INTO  V_ENAME,   V_JOB,   V_MGR,   V_HIREDATE,   V_SAL,     V_DEPTNO

from XX_EMP

WHERE EMPNO = '7369';

 

dbms_output.put_line ('Employee Name : '|| V_ENAME);

dbms_output.put_line ('Job Name : '|| V_JOB);

dbms_output.put_line ('Manager Code : '|| V_MGR);

dbms_output.put_line ('DOJ : '|| V_HIREDATE);

dbms_output.put_line ('Department ID : '|| V_DEPTNO);

 

 

END;

 

 

--===================================================

 

DECLARE

 

V_EMP XX_EMP%ROWTYPE;

 

BEGIN

 

--select  ENAME,       JOB,       MGR,       HIREDATE,      SAL,        DEPTNO

select *

INTO V_EMP

from XX_EMP

WHERE EMPNO = '7369';

 

dbms_output.put_line ('Employee Name : '||V_EMP.ENAME);

dbms_output.put_line ('Job Name : '|| V_EMP.JOB);

dbms_output.put_line ('Manager Code : '|| V_EMP.MGR);

dbms_output.put_line ('DOJ : '|| V_EMP.HIREDATE);

dbms_output.put_line ('Department ID : '|| V_EMP.DEPTNO);

 

 

END;

 

--====================================================

HOW TO USE %ROWTYPE for Multuitable

--====================================================

 

DECLARE

 

 

CURSOR C1 IS

select A.*, B.DNAME

from XX_EMP A, XX_DEPT B

WHERE A.DEPTNO = B.DEPTNO

AND EMPNO = '7369';

 

V_EMP C1%ROWTYPE;

 

 

BEGIN

 

OPEN C1;

FETCH C1 INTO V_EMP;

 

dbms_output.put_line ('Employee Name : '||V_EMP.ENAME);

dbms_output.put_line ('Job Name : '|| V_EMP.JOB);

dbms_output.put_line ('Manager Code : '|| V_EMP.MGR);

dbms_output.put_line ('DOJ : '|| V_EMP.HIREDATE);

dbms_output.put_line ('Department ID : '|| V_EMP.DEPTNO);

dbms_output.put_line ('Department NAME : '|| V_EMP.DNAME);

 

CLOSE C1;

 

END;

 

 

 

--====================================================

Use of %TYPE

--====================================================

 

 

 

DECLARE

 

--V_ENAME VARCHAR2(100);

--V_JOB VARCHAR2(100);

--V_MGR VARCHAR2(100);

--V_HIREDATE  VARCHAR2(100);

--V_SAL  NUMBER;

--V_DEPTNO VARCHAR2(100);

 

 

V_ENAME XX_EMP.ENAME%TYPE;

V_JOB XX_EMP.JOB%TYPE;

V_MGR XX_EMP.MGR%TYPE;

V_HIREDATE  XX_EMP.HIREDATE%TYPE;

V_SAL  XX_EMP.SAL%TYPE;

V_DEPTNO XX_EMP.DEPTNO%TYPE;

 

 

BEGIN

 

select  ENAME,       JOB,       MGR,       HIREDATE,      SAL,        DEPTNO

INTO  V_ENAME,   V_JOB,   V_MGR,   V_HIREDATE,   V_SAL,     V_DEPTNO

from XX_EMP

WHERE EMPNO = '7369';

 

dbms_output.put_line ('Employee Name : '|| V_ENAME);

dbms_output.put_line ('Job Name : '|| V_JOB);

dbms_output.put_line ('Manager Code : '|| V_MGR);

dbms_output.put_line ('DOJ : '|| V_HIREDATE);

dbms_output.put_line ('Department ID : '|| V_DEPTNO);

 

 

END;

 

 

 


Comments

Post a Comment

Popular posts from this blog

E-Text Report In Fusion | Types of E-Text reports

 WHAT IS Oracle Fusion E-Text Report ? An eText template is an RTF-based template that is used to generate text output  for Electronic Funds Transfer (EFT) and Electronic Data Interchange (EDI). How it generate the output for User or Bank? At runtime, BI Publisher applies this template to an input XML data file to create an output text file. that can be transmitted to a bank or other customer.  Because the output is intended for electronic communication, the eText templates must follow very specific format instructions for exact placement of data. Example :- Bank payment file , Bank MT940 File .. etc what is look and feel of this file ? Note:  There are two types of eText templates:  1. fixed-position based (EFT templates)  2. Delimiter-based (EDI templates) What IS EFT File? An EFT is an electronic transmission of financial data and payments to banks in a specific fixed-position format flat file (text). Link for download:- EFT :-  https://docs.google....
Read more

Supplier API's

API's for Create Supplier, Create Supplier Site, Create Supplier Site Contacts --======================================================================= -------------Supplier API ------------- 1. First Write a CURSOR for Required Data 2. FND_GLOBAL.APPS_INITIALIZE 3. Pass required Values in Proper way. 4. API :- For Supplier Create (pos_vendor_pub_pkg.create_vendor)    API :- For Supplier Site Create (pos_vendor_pub_pkg.create_vendor_site)    API :- For Supplier Create Contact (ap_vendor_pub_pkg.create_vendor_contact) 5. Run API 6. Checking ------------------------------  Query For APPS_INITIALIZE   --------------------------------------------- select 'USER_ID' REMARKS , user_name NAME, user_id ID from fnd_user where user_name like 'DEVELOPER' union select 'RESPONSIBILITY_ID' NAME, responsibility_name , RESPONSIBILITY_ID from fnd_responsibility_tl where responsibility_name like 'Payables Manager' union s...
Read more

How to pass default Parameter in cursor

--==================================================== --Parameterized Cursors /* Syntax of Parameterized Cursor :- CURSOR cur _ name (parameter list may be one, two, or more or Default ) IS SELECT statement; OPEN cur _ name (Parameter value) */ EMPNO ENAME HIREDATE DOB GENDER SAL DEPTNO COMM 1000 Sachin 11-Sep-85 01-Mar-20 M 25000 20 1002 Rena 01-Aug-82 11-Mar-20 F 125000 20 1008 Sonu 11-Sep-85 01-Mar-20 M 2500 10 100 1003 Chavi 25-Mar-81 15-Feb-20 F 5000 20 1006 Ravi 11-Sep-89 01-Mar-19 M 2500 10 100 1005 Vinod 18-Sep-86 01-Mar-20 ...
Read more