What is Formal, Actual, Scalar and Bind Variable in Oracle

Scalar Variable:-
It hold single variable and has no internal components.
Example:-

DECLARE
L_NAME VARCHAR2(20);
BEGIN
SELECT LAST_NAME
INTO L_NAME
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 100;
DBMS_OUTPUT.PUT_LINE(L_NAME);
END;

Bind Variable:-
To reference a bind variable is PL/SQL, you must prefix its name with a colon(:).
Example:-

VARIABLE L_NAME VARCHAR2(10)
BEGIN
SELECT LAST_NAME
INTO :L_NAME
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 100;
END;

PRINT L_NAME;

Formal Parameter:-
Formal parameter are variables declared in parameter list of sub program specification.
Example:-

CREATE OR REPLACE PROCEDURE XXC05_PROC(P_ID NUMBER, P_NAME VARCHAR2)….

**P_ID and P_NAME is formal parameter.

Actual Parameter:-
Actual Parameter are variable or expressions referred  in parameter list of sub program call.
Example:-

DECLARE
L_ID NUMBER;
L_NAME VARCHAR2(20);
BEGIN
XXC05_PROC(L_ID,L_NAME);
END;

**L_ID and L_NAME is actual parameter.

Advertisements