Developing and Registering Procedures with Oracle Application ,Working with Stored Procedures and Packages

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
                        when NO_DATA_FOUND then
                        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.                             

Post a Comment