Working with Stored Procedures and Packages
Stored Procedure : 1) It is sub a program,
which contains set of statements.
2) It is a pre-compiled program at server
side.
3) It carries three type of
parameters, In, Out , InOut
In Parameter à to pass the value
Out
Parameter à
to return the value
InOut
Prameter à
to pass as well as to return the value.
4) It can not
return the values directly from the stored procedure. It returns the values
through the
out parameter or InOut Parameter.
Advantages:
1)
We can obtain more performance as it is a precompiled
program at server side.
2)
Since It will be stored at the server side , it can be
shared by multiple clients at time.
Developing and Registering with Oracle
Application
1 ) Develop the stored procedure
Syntax
:
Create Or Replace Procedure
<ProcedureName> (ErrBuff OUT
varchar2,
RetCode OUT varchar2,
P1
IN NUMBER,
P2
IN VARCHAR2,
P3
IN DATE) AS
Local variables, Cursor, Collections
Declare;
Begin
If statement
For Loop
Procedure Calling
Fnd_File API will be used instead
of DBMS_OUTPUT.Put_Line
FND_FILE.PUT_LINE(Fnd_File.Log ,'Message'||Variable Name);
FND_FILE.PUT_LINE(Fnd_File.Output, 'Message'||Variable
Name);
Exception
When Other then
-Exception Statements;
End [ <Procedure Name> ];
ErrBuff
: to get the runtime error messages into the log file
RetCode: To get status of the
concurrent program
0 à
Normal termination
1 à
Warning
2 à
Error
Eg:
Create or replace procedure test_proc(
ErrBuff
out varchar,
RetCode
out number) as
l_name
varchar2(100):='MNRAO';
begin
FND_FILE.PUT_LINE(Fnd_File.Log , 'Employye
name into the log file'|| l_name );
FND_FILE.PUT_LINE(Fnd_File.Output, 'Employye
name into the log file '|| l_name );
end;
1)
create executable method as PL/SQL Stored Procedure
2)
Create concurrent program as PL/SQL Stored Procedure
3)
Out put format must be in text format only for
PL/SQL Stored Procedure
Repeat the remaining as explained in the
previous examples.
Stored Procedure with parameters
Create or replace
procedure test_param_proc(
ErrBuff out varchar,
RetCode out number,
V_Id IN number,
V_Name IN varchar ) as
l_id number(2);
begin
select
Vendor_Id into l_id from PO_VENDORS where Vendor_ID=V_ID;
update PO_VENDORS set Vendor_Name =
V_name where Vendor_Id=V_Id;
commit;
FND_FILE.PUT_LINE(Fnd_File.Output, 'Vendor
name has been updated successfully '||
V_Name );
exception
FND_FILE.PUT_LINE(Fnd_File.Output, 'Vendor ID does not exits '|| V_Id );
when TOO_MANY_ROWS then
FND_FILE.PUT_LINE(Fnd_File.Output, 'Duplicate Vendor Found '|| V_Id );
when OTHERS then
FND_FILE.PUT_LINE(Fnd_File.Output,
'Other Errors ');
end;
1)
create executable method as PL/SQL Stored Procedure
2) Create concurrent program as
PL/SQL Stored Procedure
Out put format must be in text format
only for PL/SQL Stored Procedure
Parameters
While supplying
parameters Token is not necessary, as the parameters will be maintained in
sequential order by the stored procedures.
Repeat the remaining as explained in the
previous examples.
No comments:
Post a Comment