What is NOCOPY Hint | Oracle Pass by reference & Pass by Value | Will NOCOPY improve performance?

What is NOCOPY Hint | Oracle Pass by reference & Pass by Value | Will NOCOPY improve performance?

List of all Interview questions @ https://sivakacademy.blogspot.com/p/s...

Oracle Interview questions and Answers at    • oracle interview questions and answers for...  

Oracle 18C New features at    • Oracle 18C New Feature  

Oracle 12C New features at    • Oracle 12C New Feature Series  

Oracle PLSQL records and collections at    • oracle plsql records and collections  

-- Demo for NOCOPY


CREATE OR REPLACE PROCEDURE proc_p1 (
param_value IN OUT VARCHAR2
) AS
BEGIN
param_value := 'ASSIGNED INSIDE PROCEDURE';
END;
/

SET SERVEROUTPUT ON

DECLARE
lv_var VARCHAR2(100);
BEGIN
lv_var := 'ASSIGNED IN anonymous block';
proc_p1(lv_var);
dbms_output.put_line(lv_var);
END;
/


-------------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE proc_copy(param_value IN OUT VARCHAR2) AS
x NUMBER;
BEGIN
param_value := 'ASSIGNED INSIDE PROCEDURE';
x := 1 / 0;
END;
/

set serveroutput on
DECLARE
lv_var VARCHAR2(100);
BEGIN
-- To check the proc_copy
lv_var := 'ASSIGNED IN anonymous block';
BEGIN
proc_copy(lv_var);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
DBMS_OUTPUT.PUT_LINE(lv_var);
END;
/
-------------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE proc_nocopy(param_value IN OUT NOCOPY VARCHAR2) AS
x NUMBER;
BEGIN
param_value := 'ASSIGNED INSIDE PROCEDURE';
x := 1 / 0;
END;
/

set serveroutput on
DECLARE
lv_var VARCHAR2(100);
BEGIN
-- To check the proc_nocopy -- NOCOPY
lv_var := 'ASSIGNED IN anonymous block';
BEGIN
proc_nocopy(lv_var);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
DBMS_OUTPUT.PUT_LINE(lv_var);
END;
/