when to use NOCOPY and when to avoid
hi guys
I am trying to understand when to use NOCOPY and when to avoid it. The documentation says
Requests that the compiler pass the corresponding actual parameter by reference instead of value (for the difference, see "Subprogram Parameter Passing Methods"). Each time the subprogram is invoked, the optimizer decides, silently, whether to obey or disregard NOCOPY.
i am writing a store procedure and my package specification is like this ::
create or replace PACKAGE STORE_DATA
AS
TYPE store_cur IS REF CURSOR;
PROCEDURE get_store_data(in_year NUMBER
,out_store_cursor OUT NOCOPY store_cur);
END STORE_DATA
This procedure is called from a java program.
so now going back to the documentation'Requests that the compiler pass the corresponding actual parameter by reference instead of value '
it means the compiler passes the cursor 'out_store_cursor ' by reference instead of by value back to the java program, right?
so what is the author trying to say?
thx
I am trying to understand when to use NOCOPY and when to avoid it. The documentation says
Requests that the compiler pass the corresponding actual parameter by reference instead of value (for the difference, see "Subprogram Parameter Passing Methods"). Each time the subprogram is invoked, the optimizer decides, silently, whether to obey or disregard NOCOPY.
i am writing a store procedure and my package specification is like this ::
create or replace PACKAGE STORE_DATA
AS
TYPE store_cur IS REF CURSOR;
PROCEDURE get_store_data(in_year NUMBER
,out_store_cursor OUT NOCOPY store_cur);
END STORE_DATA
This procedure is called from a java program.
so now going back to the documentation'Requests that the compiler pass the corresponding actual parameter by reference instead of value '
it means the compiler passes the cursor 'out_store_cursor ' by reference instead of by value back to the java program, right?
so what is the author trying to say?
thx
ASKER
>>This means any changes made to the variable within your procedure will be applied to the host variable of the calling procedure (in your case a java variable)
this will happen regardless of whether the procedure succeeds or fails.
ok got it.
and if a variable is not defined as NOCOPY does it mean that
the variable within the procedure will not be applied to the host variable if the procedure fails ?
thx
this will happen regardless of whether the procedure succeeds or fails.
ok got it.
and if a variable is not defined as NOCOPY does it mean that
the variable within the procedure will not be applied to the host variable if the procedure fails ?
thx
yes, run the sample I provided, procedure "one" is exactly that scenario
ASKER
ok ranthe example..clear now
one question:
so actually in procedure one() --the parameter which is not NOCOPY will never be applied to the host variable, right ?
one question:
so actually in procedure one() --the parameter which is not NOCOPY will never be applied to the host variable, right ?
as it is written, yes, because the procedure has an embedded error
if you take the RAISE line out, the procedure will complete successfully and value should be returned
if you take the RAISE line out, the procedure will complete successfully and value should be returned
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks.
ASKER
one last question
what does RAISE PROGRAM_ERROR; mean ?
thx
what does RAISE PROGRAM_ERROR; mean ?
thx
This means any changes made to the variable within your procedure will be applied to the host variable of the calling procedure (in your case a java variable)
this will happen regardless of whether the procedure succeeds or fails.
simple example...
run this... you'll see procedure "one" does NOT update the variable, but "two" does
DECLARE
v VARCHAR2(3) := '---';
PROCEDURE one(p IN OUT VARCHAR2)
IS
BEGIN
p := 'one';
RAISE PROGRAM_ERROR;
END;
PROCEDURE two(p IN OUT NOCOPY VARCHAR2)
IS
BEGIN
p := 'two';
RAISE PROGRAM_ERROR;
END;
BEGIN
DBMS_OUTPUT.put_line(v);
BEGIN
one(v);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
DBMS_OUTPUT.put_line(v);
BEGIN
two(v);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
DBMS_OUTPUT.put_line(v);
END;