Dynamic Declaration of variables

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

Dynamic Declaration of variables in PLSQL

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

Variable datatype(Size)

when you are facing issue to declare any variable like Size and type

we can avoid this kind of error by using type compairtability keyword %TYPE

select * from XXEMP_TL

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO	MOBILENO	GENDER
7369	Smith	CLERK	7902	17-Dec-80	800		20	1234543256	M
7499	Allen	SALESMAN	7698	20-Feb-81	1600	300	30	2123212541	M
7521	Ward	SALESMAN	7698	22-Feb-81	1250	500	30	9898989898	M
7566	Jones	MANAGER	7839	02-Apr-81	2975		20	8989898989	M
7654	MARTIN	SALESMAN	7698	28-Sep-81	1250	1400	30	7878787878	M
7698	BLAKE	MANAGER	7839	01-May-81	2850		30	6767676767	M
7782	CLARK	MANAGER	7839	09-Jun-81	2450		10	5656565656	M
7788	Scott	ANALYST	7566	09-Dec-82	3000		20	4545454545	M
7839	KING	PRESIDENT		17-Nov-81	5000		10	3434343434	M
7844	TURNER	SALESMAN	7698	08-Sep-81	1500	0	30	2323232323	M
7876	ADAMS	CLERK	7788	12-Jan-83	1100		20	1212121212	M
7900	James	CLERK	7698	03-Dec-81	950		30	9090909090	M
7902	FORD	ANALYST	7566	03-Dec-81	3000		20	101010101	M
7934	MILLER	CLERK	7782	23-Jan-82	1300		10	202020202	M
7999	Rani	SALESMAN	7698	23-Sep-88	1900	10	10	303030303	F

--========

Example:-1

--========

DECLARE

    V_NAME VARCHAR(5);

BEGIN

    SELECT ENAME INTO V_NAME FROM XXEMP_TL WHERE EMPNO = :V_EMPNO;

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

EXCEPTION when others THEN

    dbms_output.put_line (SQLCODE||' - ERROR MSG :- '||SQLERRM);

END;

--========

Example:-2

--========

DECLARE

    V_SAL NUMBER(3);

BEGIN

    SELECT SAL INTO V_SAL FROM XXEMP_TL WHERE EMPNO = :V_EMPNO;

    dbms_output.put_line ('Employee Salary is : -  '||V_SAL);

EXCEPTION when others THEN

    dbms_output.put_line (SQLCODE||' - ERROR MSG :- '||SQLERRM);

END;

--========

Example:-3

-- Dynamic Declaration --

--========

DECLARE

    V_NAME XXEMP_TL.ENAME%TYPE;

    V_SAL XXEMP_TL.SAL%TYPE;

BEGIN

    SELECT ENAME, SAL INTO V_NAME, V_SAL  FROM XXEMP_TL WHERE EMPNO =  :V_EMPNO;

    dbms_output.put_line ('Employee Name : -  '||V_NAME || '           and Salary is :- '|| V_SAL);

EXCEPTION when others THEN

    dbms_output.put_line (SQLCODE||' - ERROR MSG :- '||SQLERRM);

END;

output:-
Employee Name : -  Smith                  and Salary is :- 800
Employee Name : -  TURNER           and Salary is :- 1500