1 2 Previous Next 15 Replies Latest reply: Feb 5, 2013 8:12 AM by user12071749 RSS

    Sql to stored procedure

    751828
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      PL/SQL Release 11.2.0.3.0 - Production
      "CORE     11.2.0.3.0     Production"

      Whether the below sql program can be better coded as stored procedure if so can any one guide me in doing so.
      WHENEVER SQLERROR EXIT FAILURE;
      SET VERIFY OFF;
      --SET SERVEROUTPUT ON;
      
      DECLARE
         l_request_id        fnd_concurrent_requests.REQUEST_ID%TYPE;
         l_program_id        fnd_concurrent_requests.CONCURRENT_PROGRAM_ID%TYPE;
         l_prog_app_id       fnd_concurrent_requests.PROGRAM_APPLICATION_ID%TYPE;
         l_p_include_IC_Ord  VARCHAR2(1);
         l_p_oid             VARCHAR2(10);
         l_db_cnt            VARCHAR2(1):= 0;
         l_err_msg           VARCHAR2(500);
         e_db_excep         EXCEPTION;
      
      BEGIN
         l_request_id  := fnd_global.conc_request_id;
         l_program_id  := fnd_global.conc_program_id;
         l_prog_app_id := fnd_global.resp_appl_id;
         
         l_p_include_IC_Ord     := '&3';
         l_p_oid                := &4;
      
              debug_pkg.initializedebugmessage (p_progname     => 'SAMPLE_ORDER_SYNC_PKG',
                                                    p_debuglevel   => TO_NUMBER('&2'),
                                                    p_debugmode    => TO_NUMBER('&1'),
                                                    p_programappid => l_prog_app_id,
                                                    p_programid    => l_program_id,
                                                    p_requestid    => l_request_id
                                                   );
      
          BEGIN
          l_err_msg := 'Inside DBLink Validation';
          debug_pkg.adddebugmessage(p_recordkey      => NULL,
                                           p_debugmessage   => l_err_msg,
                                           p_messagelevel   => 0,
                                           p_errorcode      => NULL
                                          );
          -- Validating the DB Link
          -- SELECT 1
          --   INTO l_db_cnt 
          --   FROM dual@ERP2SAMPLE;
          EXECUTE IMMEDIATE 'SELECT 1 FROM dual@ERP2SAMPLE' INTO l_db_cnt;
          
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            debug_pkg.adddebugmessage
                                     (p_recordkey      => NULL,
                                      p_debugmessage   => 'DBLINK Not Found',
                                      p_messagelevel   => 0,
                                      p_errorcode      => 'Err:1000'
                                     );
            l_err_msg := TO_CHAR(SQLCODE) || ' and Errmsg: ' ||SUBSTR(SQLERRM, 1, 100);
            RAISE e_db_excep;
          WHEN OTHERS THEN
            
            debug_pkg.adddebugmessage
                                     (p_recordkey      => NULL,
                                      p_debugmessage   => l_err_msg||' And errmsg: '||SUBSTR(SQLERRM, 1, 100),
                                      p_messagelevel   => 0,
                                      p_errorcode      => SQLCODE
                                     );
            l_err_msg := TO_CHAR(SQLCODE) || ' and Errmsg: ' ||SUBSTR(SQLERRM, 1, 100);
            RAISE e_db_excep;
        END;
             
              
              --| Setting the org context to the org_id passsed as parameter. 
              --| The org_id from Resposponsiblity from which ths program is run may be different. 
              --| This is done as _all tables are not being used at every place due to perfromence reason.
      
              SAMPLE_order_sync_pkg.Order_cancellation_PROC( p_Include_IC_Orders => l_p_include_IC_Ord
                                                            , p_Org_Id            => l_p_oid);
                                                      
              SAMPLE_order_sync_pkg.Order_change_PROC( p_Include_IC_Orders => l_p_include_IC_Ord
                                                      , p_Org_Id            => l_p_oid);
              
              debug_pkg.PostDebugMessage;
              COMMIT;
      EXCEPTION
         WHEN e_db_excep THEN
           ROLLBACK;
           debug_pkg.PostDebugMessage;
           COMMIT;
           fnd_file.put_line(fnd_file.output,'DBLink Validation Failed in sample_ord_sync_wrapper.sql Errcode: '||l_err_msg);
      
               
          -- Raise_Application_Error(-20051,
                           --       'DBLink Validation Failed in sample_ord_sync_wrapper.sql Errcode: ' ||l_err_msg);
           
                
         WHEN OTHERS THEN
           ROLLBACK;
           debug_pkg.PostDebugMessage;
           COMMIT;
           fnd_file.put_line(fnd_file.output,'Unknown error in sample_ord_sync_wrapper.sql.  Errcode: '||SQLCODE||' and errmsg: '||SUBSTR(SQLERRM, 1, 200) );
           Raise_Application_Error(-20052,
                                  'Unknown error in sample_ord_sync_wrapper.sql' ||
                                  'SQLCODE=' || TO_CHAR(SQLCODE) || 'SQLERRM=' ||
                                  SUBSTR(SQLERRM, 1, 80));
      END;
      /
        • 1. Re: Sql to stored procedure
          rp0428
          >
          Whether the below sql program can be better coded as stored procedure if so can any one guide me in doing so.
          >
          What do you mean 'better coded'?

          PL/SQL code is PL/SQL code so an anonymous block and a procedure aren't going to be much different.

          What is it you want to do?
          • 2. Re: Sql to stored procedure
            751828
            This program will be called by the ERP Concurrent Manager and will be scheduled accordingly, this might be better coded as a stored procedure instead of calling the SQL file each time the concurrent job runs.
            • 3. Re: Sql to stored procedure
              rp0428
              Well then I still don't understand what help you need.

              Just turn it into a stored procedure.

              Replace
              WHENEVER SQLERROR EXIT FAILURE;
              SET VERIFY OFF;
              --SET SERVEROUTPUT ON;
              with (untested)
              CREATE OR REPLACE PROCEDURE myProc AS
              • 4. Re: Sql to stored procedure
                751828
                rp0428 wrote:
                Well then I still don't understand what help you need.

                Just turn it into a stored procedure.

                Replace
                WHENEVER SQLERROR EXIT FAILURE;
                SET VERIFY OFF;
                --SET SERVEROUTPUT ON;
                with (untested)
                CREATE OR REPLACE PROCEDURE myProc AS
                So by replacing, remaining all will remain same or need to make any changes to the code aswell
                • 5. Re: Sql to stored procedure
                  rp0428
                  Don't be afraid of breaking Oracle.
                  • 6. Re: Sql to stored procedure
                    751828
                    and one more thing as this new procedure is calling some other pages 'debug_pkg.adddebugmessage' &'SAMPLE_order_sync_pkg.Order_cancellation_PROC' .

                    Instead of calling those packages i need to include those logic inside my procedure itself can i do that, if so where i should include..


                    this is procedure calling for debug_pkg.adddebugmessage
                    PROCEDURE AddDebugMessage(p_PackageInfo  in VARCHAR2  default NULL,
                                     p_RecordType   in VARCHAR2  default NULL,
                                     p_RecordKey    in VARCHAR2  default NULL,
                                              p_DebugMessage in VARCHAR2,
                                              p_MessageLevel in NUMBER,
                                              p_ErrorCode    in VARCHAR2  default NULL) AS
                    BEGIN
                       IF( G_Debug_Level >= p_MessageLevel ) THEN
                           G_Debug_Count                           := G_Debug_Count +1;
                           G_Debug_Tbl(G_Debug_Count).PackageInfo  := p_PackageInfo;
                           G_Debug_Tbl(G_Debug_Count).RecordType   := p_RecordType;
                           G_Debug_Tbl(G_Debug_Count).RecordKey    := p_RecordKey;
                           G_Debug_Tbl(G_Debug_Count).Message      := SUBSTR(p_DebugMessage,1,G_Debug_Len);
                           G_Debug_Tbl(G_Debug_Count).MessageLevel := p_MessageLevel;
                           G_Debug_Tbl(G_Debug_Count).ErrorCode    := p_ErrorCode;
                       END IF;
                    END AddDebugMessage;
                    Edited by: user9093700 on Jul 18, 2012 11:51 AM
                    • 7. Re: Sql to stored procedure
                      Purvesh K
                      I remember replying to you on a Similar Thread. Re: function flow

                      user9093700 wrote:
                      and one more thing as this new procedure is calling some other pages 'debug_pkg.adddebugmessage' &'SAMPLE_order_sync_pkg.Order_cancellation_PROC' .
                      Did you mean to say Package instead of Pages?


                      Instead of calling those packages i need to include those logic inside my procedure itself can i do that, if so where i should include..
                      Why duplicate the Code and Functionality when you can Call the Procedures from your code? This is the Main aspect of Maintainability. If in Future, you have to implement a change in Debug Logging, you would have to implement it at as many places you have copied the procedure.
                      So, my suggestion is, just call the procedure from the Stored Program you have written.
                      • 8. Re: Sql to stored procedure
                        751828
                        HI Purvesh, good to hear from you.

                        Actually, this is the existing script which is already been there and now according to my requirement the same script should be used for some other system. So here in this script its calling some other packages lets say (debug_pkg.initializedebugmessage) instead of using the whole package i need to get that logic into this program and use it.So it will be straight forward.
                        • 9. Re: Sql to stored procedure
                          Purvesh K
                          user9093700 wrote:
                          Actually, this is the existing script which is already been there and now according to my requirement the same script should be used for some other system. So here in this script its calling some other packages lets say (debug_pkg.initializedebugmessage) instead of using the whole package i need to get that logic into this program and use it.So it will be straight forward.
                          If you are planning to use the package for another system, you should replicate the package to your new System. Just using the procedure would not help, because, it is just populating the Collection variables, which will be lost if they are re-initialized. Moreover, there is logic to set Debug mode either Log to Tables/do DBMS_OUTPUT (Definitely not a recommended practice) and initialization of collections, which you will be missing in case you just create the mentioned procedure for your purpose. You will benefit of Logging if you are storing the data into your Tables/Files.

                          Hence, I say it again, take the copy of Entire package and Package body and create it in your system (This is with an assumption that both the systems are disconnected and are maintained by separate people).
                          • 10. Re: Sql to stored procedure
                            751828
                            as iam not yet sure whether both the systems are connected or not .

                            but it is using by the same people.

                            But still we can use those packages with some other name extension right?

                            Edited by: user9093700 on Jul 18, 2012 5:31 PM
                            • 11. Re: Sql to stored procedure
                              Purvesh K
                              user9093700 wrote:
                              as iam not yet sure whether both the systems are connected or not .

                              but it is using by the same people.

                              But still we can use those packages with some other name extension right?

                              Edited by: user9093700 on Jul 18, 2012 5:31 PM
                              Do you mean to say, re-use the package with another Name? If so, then Yes. You may export the DDL of the package from the First system and run the DDL after modifying the package name/procedure name into your destination system. But where ever, you are using the package in destination system, do remember to use the modified name rather than the original name of package/procedure.
                              • 12. Re: Sql to stored procedure
                                751828
                                Can you please explain this are they passing hard coded.??
                                l_p_include_IC_Ord     := '&3';
                                   l_p_oid                := &4;
                                so0solodebug_pkg.initializedebugmessage (p_progname     => 'SAMPLE_OC_ORDER_SYNC_PKG',
                                After i replaced with ' CREATE OR REPLACE PROCEDURE sample_ord_sync_wrapper AS' below is my procedure can i run this straight away as my new updated procedure or need to make any changes in order run this as procedure..
                                /*WHENEVER SQLERROR EXIT FAILURE;
                                SET VERIFY OFF;
                                --SET SERVEROUTPUT ON;*/
                                
                                CREATE OR REPLACE PROCEDURE sample_ord_sync_wrapper AS
                                
                                DECLARE
                                   l_request_id        fnd_concurrent_requests.REQUEST_ID%TYPE;
                                   l_program_id        fnd_concurrent_requests.CONCURRENT_PROGRAM_ID%TYPE;
                                   l_prog_app_id       fnd_concurrent_requests.PROGRAM_APPLICATION_ID%TYPE;
                                   l_p_include_IC_Ord  VARCHAR2(1);
                                   l_p_oid             VARCHAR2(10);
                                   l_db_cnt            VARCHAR2(1):= 0;
                                   l_err_msg           VARCHAR2(500);
                                   e_db_excep         EXCEPTION;
                                
                                BEGIN
                                   l_request_id  := fnd_global.conc_request_id;
                                   l_program_id  := fnd_global.conc_program_id;
                                   l_prog_app_id := fnd_global.resp_appl_id;
                                   
                                   l_p_include_IC_Ord     := '&3';
                                   l_p_oid                := &4;
                                
                                        debug_pkg.initializedebugmessage (p_progname     => 'SAMPLE_ORDER_SYNC_PKG',
                                                                              p_debuglevel   => TO_NUMBER('&2'),
                                                                              p_debugmode    => TO_NUMBER('&1'),
                                                                              p_programappid => l_prog_app_id,
                                                                              p_programid    => l_program_id,
                                                                              p_requestid    => l_request_id
                                                                             );
                                
                                    BEGIN
                                    l_err_msg := 'Inside DBLink Validation';
                                    debug_pkg.adddebugmessage(p_recordkey      => NULL,
                                                                     p_debugmessage   => l_err_msg,
                                                                     p_messagelevel   => 0,
                                                                     p_errorcode      => NULL
                                                                    );
                                    -- Validating the DB Link
                                    -- SELECT 1
                                    --   INTO l_db_cnt 
                                    --   FROM dual@ERP2SAMPLE;
                                    EXECUTE IMMEDIATE 'SELECT 1 FROM dual@ERP2SAMPLE' INTO l_db_cnt;
                                    
                                  EXCEPTION
                                    WHEN NO_DATA_FOUND THEN
                                      debug_pkg.adddebugmessage
                                                               (p_recordkey      => NULL,
                                                                p_debugmessage   => 'DBLINK Not Found',
                                                                p_messagelevel   => 0,
                                                                p_errorcode      => 'Err:1000'
                                                               );
                                      l_err_msg := TO_CHAR(SQLCODE) || ' and Errmsg: ' ||SUBSTR(SQLERRM, 1, 100);
                                      RAISE e_db_excep;
                                    WHEN OTHERS THEN
                                      
                                      debug_pkg.adddebugmessage
                                                               (p_recordkey      => NULL,
                                                                p_debugmessage   => l_err_msg||' And errmsg: '||SUBSTR(SQLERRM, 1, 100),
                                                                p_messagelevel   => 0,
                                                                p_errorcode      => SQLCODE
                                                               );
                                      l_err_msg := TO_CHAR(SQLCODE) || ' and Errmsg: ' ||SUBSTR(SQLERRM, 1, 100);
                                      RAISE e_db_excep;
                                  END;
                                       
                                        
                                        --| Setting the org context to the org_id passsed as parameter. 
                                        --| The org_id from Resposponsiblity from which ths program is run may be different. 
                                        --| This is done as _all tables are not being used at every place due to perfromence reason.
                                
                                        SAMPLE_order_sync_pkg.Order_cancellation_PROC( p_Include_IC_Orders => l_p_include_IC_Ord
                                                                                      , p_Org_Id            => l_p_oid);
                                                                                
                                        SAMPLE_order_sync_pkg.Order_change_PROC( p_Include_IC_Orders => l_p_include_IC_Ord
                                                                                , p_Org_Id            => l_p_oid);
                                        
                                        debug_pkg.PostDebugMessage;
                                        COMMIT;
                                EXCEPTION
                                   WHEN e_db_excep THEN
                                     ROLLBACK;
                                     debug_pkg.PostDebugMessage;
                                     COMMIT;
                                     fnd_file.put_line(fnd_file.output,'DBLink Validation Failed in sample_ord_sync_wrapper.sql Errcode: '||l_err_msg);
                                
                                         
                                    -- Raise_Application_Error(-20051,
                                                     --       'DBLink Validation Failed in sample_ord_sync_wrapper.sql Errcode: ' ||l_err_msg);
                                     
                                          
                                   WHEN OTHERS THEN
                                     ROLLBACK;
                                     debug_pkg.PostDebugMessage;
                                     COMMIT;
                                     fnd_file.put_line(fnd_file.output,'Unknown error in sample_ord_sync_wrapper.sql.  Errcode: '||SQLCODE||' and errmsg: '||SUBSTR(SQLERRM, 1, 200) );
                                     Raise_Application_Error(-20052,
                                                            'Unknown error in sample_ord_sync_wrapper.sql' ||
                                                            'SQLCODE=' || TO_CHAR(SQLCODE) || 'SQLERRM=' ||
                                                            SUBSTR(SQLERRM, 1, 80));
                                END;
                                /
                                Edited by: user9093700 on Jul 19, 2012 8:39 AM
                                • 13. Re: Sql to stored procedure
                                  Purvesh K
                                  user9093700 wrote:
                                  Can you please explain this are they passing hard coded.??
                                  l_p_include_IC_Ord     := '&3';
                                  l_p_oid                := &4;
                                  so0solodebug_pkg.initializedebugmessage (p_progname     => 'SAMPLE_OC_ORDER_SYNC_PKG',
                                  Unsure what the first two lines are used for; Normally using '&' is requesting Input Parameters, but no clue what it means in PL/SQL.
                                  The Last line is calling of a stored program, probably procedure, with Named Parameter Call (<a href="http://docs.oracle.com/cd/E18283_01/appdev.112/e17126/subprograms.htm#i4072>Read PL/SQL Subprograms for more information</a>).                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
                                  • 14. Re: Sql to stored procedure
                                    751828
                                    Purvesh K wrote:
                                    user9093700 wrote:
                                    Can you please explain this are they passing hard coded.??
                                    l_p_include_IC_Ord     := '&3';
                                    l_p_oid                := &4;
                                    so0solodebug_pkg.initializedebugmessage (p_progname     => 'SAMPLE_OC_ORDER_SYNC_PKG',
                                    Unsure what the first two lines are used for; Normally using '&' is requesting Input Parameters, but no clue what it means in PL/SQL.
                                    The Last line is calling of a stored program, probably procedure, with Named Parameter Call (<a href="http://docs.oracle.com/cd/E18283_01/appdev.112/e17126/subprograms.htm#i4072>Read PL/SQL Subprograms for more information</a>).                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                                    1 2 Previous Next