This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Nov 27, 2012 4:51 AM by P.Forstmann RSS

Examples of Good Contructed Stored Procedures

846231 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    http://www.plsqlchallenge.com



    Regards
    Asif Kabir
  • 3. Re: Examples of Good Contructed Stored Procedures
    846231 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    END LOOP; is needed to finish the loop construct
  • 5. Re: Examples of Good Contructed Stored Procedures
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    first try to write the logic in simple language and then do coding ..
  • 9. Re: Examples of Good Contructed Stored Procedures
    846231 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points