1 2 Previous Next 16 Replies Latest reply: Nov 27, 2012 6:51 AM by P.Forstmann RSS

    Examples of Good Contructed Stored Procedures

    846231
      Hi all,

      I want to be an "Stored Procedures" programmer.

      I want to learn Stored Procedure by studying existing ones which are created good . Learning the technique on how it is created.

      Can you give me link of examples/samples of lots of stored procedures? I can learn fast if I read samples/examples.

      I tried googling it but I can not find a good comprehensive ones. I mean sites with lots of sample stored procedures constructs and style.


      Thanks a lot,
      Kinz
        • 1. Re: Examples of Good Contructed Stored Procedures
          Fran
          First, learn the basics.
          http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6009.htm#SQLRF01309
          http://www.oraclecity.com/plsql-tutorial/oracle-create-or-replace-stored-procedure-example-basics/
          http://www.mkyong.com/oracle/oracle-stored-procedures-hello-world-examples/

          Later you can choose the difficult of procedures you want.

          Edited by: Fran on 26-nov-2012 23:58
          • 2. Re: Examples of Good Contructed Stored Procedures
            asifkabirdba
            http://www.plsqlchallenge.com



            Regards
            Asif Kabir
            • 3. Re: Examples of Good Contructed Stored Procedures
              846231
              I thank you all :)


              I am practising my first stored proc. But it won't run. :( I want to drop all database link in the current user schema.
              SQL> CREATE OR REPLACE PROCEDURE DROP_DBLINKS
                2  IS
                3     CURSOR dblink_cur IS SELECT db_link FROM USER_DB_LINKS;
                4
                5  BEGIN
                6    FOR dblink_rec IN dblink_cur
                7    LOOP
                8    execute immediate 'drop database link "dblink_rec.db_link" ';
                9  END;
               10  /
              
              Warning: Procedure created with compilation errors.
              
              SQL>
              SQL> show errors
              Errors for PROCEDURE DROP_DBLINKS:
              
              LINE/COL ERROR
              -------- -----------------------------------------------------------------
              9/4      PLS-00103: Encountered the symbol ";" when expecting one of the
                       following:
                       loop
              Please help ....thanks


              ok I got it ;)
              CREATE OR REPLACE PROCEDURE DROP_DBLINKS
              IS
                 CURSOR dblink_cur IS SELECT db_link FROM USER_DB_LINKS;
                    
              BEGIN
                FOR dblink_rec IN dblink_cur LOOP
                execute immediate 'drop database link "dblink_rec.db_link" ';
              END LOOP;
              END;
              /
              Edited by: KinsaKaUy? on 27-Nov-2012 02:43
              • 4. Re: Examples of Good Contructed Stored Procedures
                John Stegeman
                END LOOP; is needed to finish the loop construct
                • 5. Re: Examples of Good Contructed Stored Procedures
                  Billy~Verreynne
                  KinsaKaUy? wrote:

                  I want to be an "Stored Procedures" programmer.
                  A good programmer is a good programmer - in any language.

                  A good programmer is about understanding basics and fundamentals of software engineering. Like how to modularise. How to create a well designed package interface. What to abstract. What system interfaces and calls to wrap in customer wrappers.

                  Looking at example code does not teach you the basics and fundamentals applied to create the code. And copying example code is not going to help you with tackling different and unique problems that require you to apply knowledge and experience - instead of copy-and-pasting.

                  If you want to become a good programmer, start of with learning the basics of structured programming and modularisation. And then apply it to code you develop in PL/SQL.
                  • 6. Re: Examples of Good Contructed Stored Procedures
                    Fran
                    you forgot close the loop:

                    CREATE OR REPLACE PROCEDURE DROP_DBLINKS
                    2 IS
                    3 CURSOR dblink_cur IS SELECT db_link FROM USER_DB_LINKS;
                    4
                    5 BEGIN
                    6 FOR dblink_rec IN dblink_cur
                    7 LOOP
                    8 execute immediate 'drop database link "dblink_rec.db_link" ';
                    end loop; --closing loop 
                    9 END;
                    10 /
                    • 7. Re: Examples of Good Contructed Stored Procedures
                      846231
                      On the second thought ....you are correct bill :)
                      Maybe for now I just want to learn basics.
                      • 8. Re: Examples of Good Contructed Stored Procedures
                        Think_dba
                        first try to write the logic in simple language and then do coding ..
                        • 9. Re: Examples of Good Contructed Stored Procedures
                          846231
                          Hi all,

                          error again :(
                          CREATE OR REPLACE PROCEDURE DROP_DBLINKS
                          IS
                             CURSOR dblink_cur IS SELECT db_link FROM USER_DB_LINKS;
                                
                          BEGIN
                            FOR dblink_rec IN dblink_cur LOOP
                            execute immediate 'drop database link "dblink_rec.db_link" ';
                          END LOOP;
                          END;
                          / 
                          SQL> exec drop_dblinks;
                          BEGIN drop_dblinks; END;
                          
                          *
                          ERROR at line 1:
                          ORA-02024: database link not found
                          ORA-06512: at "MDC_SC.DROP_DBLINKS", line 7
                          ORA-06512: at line 1
                          How do I control my proc such that if the dblink is not found it will continue looping and not abort?


                          Please help...
                          • 10. Re: Examples of Good Contructed Stored Procedures
                            Fran
                            create or replace package ...
                            *
                            *
                            *
                            *
                            *

                            exception
                            when no_data_found then
                            *
                            *
                            *
                            end;
                            /

                            Edited by: Fran on 27-nov-2012 3:00
                            • 11. Re: Examples of Good Contructed Stored Procedures
                              846231
                              Thanks...


                              I found this package in DBMS_ADDM in SYS
                              create or replace
                              PACKAGE BODY dbms_addm wrapped
                              a000000
                              1
                              abcd
                              abcd
                              abcd
                              abcd
                              abcd
                              abcd
                              abcd
                              abcd
                              abcd
                              abcd
                              abcd
                              abcd
                              abcd
                              abcd
                              abcd
                              b
                              3441 9b6
                              L6eT53dcSUsOvYCG/hqTmpI/quwwg826uiAF344ZA6pEzHDINjDipCAE30n7kfRhvvwG8cPs
                              iumFyA7nUDaEBc04fdCaqCaZPvphkdVKmNzn4LoUckOGvveJPOL+1EzgIdBz/fX+xoaw0jO5
                              uVJntN00PCEzS09pX+Joqc7Rxj6/Mf265AzveNs+9C/klb0HHHe6vtJH85EvBtuAOpRs7nbr
                              iGgBaEO9DL5TEyhLSbYX3wRHCPGiUZnLS8bMfYcF5cQddjBxdALRmasHFjh16OKq6VRZY8N0
                              NeuYlDnezD8PhePRyEOSPxGoeJvpT4rFianLReYDwnJn+ffpPng4Di4vyrwV94qLcoSnbX+b
                              FtF8CXoH4eNrNZpY+jeOTkZggpXhW1nB4dMSUlPLfMa8AuAnoI/2imxuogwKiWZJPFrN5NOT
                              FGZuUzcNNbgYiIuBvo1R4GfwVsmIZqX237QRG0hdOXidhndP77odVJ3Fi2V2DosMg4SXZr/X
                              K3+xYNX2g1fLNtjwJ4L/T7Ds+jzUaw+WXUcMTtFfXKwErDwMdgbjsPa4Vt+bK7QS752wV8DB
                              +zN52Um5ZRiISUFszFWPacLXdezP4KkcAst268L+L5N82YkfntbtWFMy6LBqW3fST/FvBKfm
                              8jDhyjSV/Z+48ZGpAuR3YjvFLWXOORqDMWb0BCS0q/7ExnNiCdI3KfB6bsnfU68bxGl94D0Y
                              T4oPYH6qQfByIKR++A87CPam+36Nvj5LrBu9Rfyd+rpQ+7kJCG+VG5xZrmkYoadpbJaF/fvu
                              eO+Wa8NHk+JG8i2tslgPc9uyYVdDy97HFcq4YFz7U50LeDDfS2+hO1vB4vir1Vqg/byqMFcW
                              5nbaUoF9NK2PVgPzpOS164833ksGf6mWOc9vFJtruFkQ5qgUyNU0rzKjuhDeRkY85GTajXEi
                              kAm/xahZNNU9DIjJX9vzglU4aDdQq16WbKjONmT4PVfZE+YSKdaj0am6dg+cs8Yi/dDGwD/t
                              4xMeUTdhBKHtgfBhIayorMyzeh6MMzaGedsWDKRLGREnIoCGGAID/afg8pWSr2x0bZp2B/yn
                              c2qRkk7OxqgnWyoY4uETxiZpBUiPuXRiqBNDoBsRPSZUlP6XuFCwU6w3GRTSIFMQvcf1onEh
                              PBLgUcxL5QJG4ek2oCrtBul9s3NllbFGUqdkIIEbsNXLtylQpTrJUpfnPQR+eHkuqe9cKQE/
                              nssBTcRG50xGon1p2FU7lWrrVWrrEqAwzqU6+QunppmxOXsJ4R7kcgXQxND+UGxzpaUpgtjE
                              s5yW5b1rwvMAr8GV1UyG6skPU/+1vHOlsXNjU/bUhM55kJ0rOxvWsUQHpG7V3A2QQl50fTU5
                              3j+QRLMA4govg3kqACvtjtfTkThEYWWNOPe0oABuODj7kPGWEv3SbjAorkXm6CcItPDmlvor
                              L2t1Ozh8U3/3q4HIEaEQHSXZII0+PCl1qRIWa5W5bua8QiQtXOHMr50Qiup2O5dBGbGHy9KH
                              snWfVPuvfnrRNjm7c6Z8WlSymOf7SqfPAdVeUtv/CJGDrSbWGIj7to9EqVpGD65g78r92wij
                              8YFG1i2R/1fXV2qsuOMbMWBCK8i0YQfRlsgqK/jOHHsHLJ1USnXs2CkrIJLGwKAjka7uMgiV
                              eKhO3sMbypGdXPQlIxIb/6w8qhrTfzQ4xDbou8TB0IfCnt4WhKUJ6ApeVR3jmulT8hcoxU6a
                              Z9q3X2Bjlf++zBtsTqDyLTeDh+kYyld/1dM7Q9gZZtbQERPfX3snbO8LDt8PDeBgKTlZO7s7
                              WHt128mEL51qd9Hed8fCShLv1gMudP8hOWz3ewf8it/AWEgGZWK+w+eQaCenVZ2qNKqSaCAZ
                              FsFIEudcXhmEtDwvGdcI2ChKjUJV1zKDAAB+zMNg9/BZxrBuAry8iowLhwDyUjlVHeQtnKea
                              fsiILKjYX9COWtTGvaTxQN4IsIc8POHyUk0zdKhjZA6saT4BoS6MA65w3leA4SLMMhL2xVFZ
                              k1F1rM9tCN5MUxjza9IL0I0sSvIOwk5KMndzMIawa2iVfzVyv7luG1zLvfEbE82HKEDARQV9
                              Y7f4VHodOsDBPBMr9QBX5igXzE2eqjnq1c44qL4dgYwsVG7Tg4FzDRc2kmMYBrfhNCt4IgNC
                              nRFDuPKFeAHbwZU/BbYSlEY0RuES2+nqycc7pPoziinU8XH5P6YnMIQOzzXdMM4PQ/VS9VDk
                              Bqm0wNrPZFl2qrhrOOIM++pCjiU2vuU3YIvWn5U4os335OnTrzaV89O23Ah76DSkaPyk4V1E
                              CHFnqlMViDWEsAH5fG7xfh7HfkAb0bpjILZZIHQB9XJWQacvxuVD8QC3Zrs80CeLuiwnpmzq
                              WTyPRbEm3z643FnVqaM2tta2z+XmVHUBCu6ZeHBUT/BR3SpnPQLFOHeLorS6Gxgs/7z4H4TS
                              EMM=
                              What does it do?

                              Thanks
                              • 12. Re: Examples of Good Contructed Stored Procedures
                                Girish Sharma
                                I am sure, I can not write as good as Billy written above, but if I wish to quote my views that having knowledge of solid basics and concept of any programming language is the key of any successful programmer. Now question is how they learnt ? Very simple, by good books and that technologies documentation. Ok.. "I know that, I am reading them, but still I am not confidence on it then?" So, it means, you just started learning the good books and docs without having answers of below questions :

                                1.Am I that person who can go into this tech/domain ?
                                2.Am I having those perquisites and pre-qualifications which is required in this area ?
                                3.Is this learning area is my interest topic ?
                                4.What if, on some other day someone says/confuses for other tech / platform ? Saying that "this" is best tech that "this".
                                5.Do I have answers of how,when and why to learn ?
                                6....

                                I think here great experts were having a good and clear cut answers of above question(s) and then they started their "Planned hard working" and now we says them "Sir..."

                                Regards
                                Girish Sharma
                                • 13. Re: Examples of Good Contructed Stored Procedures
                                  846231
                                  Thanks girish :)


                                  Can you help me with this sample procedure please.


                                  I just want to drop all the database links created in my schema:
                                  CREATE OR REPLACE PROCEDURE DROP_DBLINKS
                                  IS
                                     CURSOR dblink_cur IS SELECT db_link FROM USER_DB_LINKS;
                                        
                                  BEGIN
                                    FOR dblink_rec IN dblink_cur LOOP
                                    execute immediate 'drop database link "DBLINK_REC.DB_LINK" ';
                                  END LOOP;
                                  END;
                                  /
                                  
                                  SQL> exec drop_dblinks;
                                  BEGIN drop_dblinks; END;
                                  
                                  *
                                  ERROR at line 1:
                                  ORA-02024: database link not found
                                  ORA-06512: at "MDC_SC.DROP_DBLINKS", line 7
                                  ORA-06512: at line 1
                                  My line 7 is > execute immediate 'drop database link "DBLINK_REC.DB_LINK" ';

                                  Why can not it find the dblink when it was selected in the cursor?


                                  Thanks a lot,
                                  • 14. Re: Examples of Good Contructed Stored Procedures
                                    P.Forstmann
                                    Try:
                                    - first to build SQL statement into a PL/SQL variable
                                    - to print this variable using DBMS_OUTPUT before running the statement built in this PL/SQL variable
                                    - use EXECUTE IMMEDIATE <PL/SQL variable>
                                    - and do not forget to enable DBMS_OUTPUT with SQL*Plus statement: SET SERVEROUTPUT ON.

                                    Example:
                                    SQL> set serveroutput on
                                    SQL> --
                                    SQL> create or replace procedure drop_dblinks
                                      2  is
                                      3    cursor dblink_cur is select db_link from user_db_links;
                                      4    stmt varchar2(100);
                                      5  begin
                                      6  for dblink_rec in dblink_cur
                                      7  loop
                                      8  stmt := 'drop database link ' || dblink_rec.db_link;
                                      9  dbms_output.put_line('Running:' || stmt || ' ...');
                                     10  execute immediate stmt;
                                     11  dbms_output.put_line('OK');
                                     12  end loop;
                                     13  end;
                                     14  /
                                    
                                    Procedure created.
                                    
                                    SQL> exec drop_dblinks;
                                    Running:drop database link TEST ...
                                    OK
                                    
                                    PL/SQL procedure successfully completed.
                                    
                                    SQL>
                                    In your case you cannot mix single quotes and double quotes: using first single quotes disables any substitution in the character string.

                                    Edited by: P. Forstmann on 27 nov. 2012 13:21
                                    1 2 Previous Next