6 Replies Latest reply: Jan 2, 2013 1:05 AM by 846231 RSS

    What Makes An  Excellent PL/SQL program?

    846231
      Hi All pl/sql experts,

      I am a newbie pl/sql programmer. I know how my coded pl/sql program different from an expert's program.
      Can you share me a sample code or a template of the well built pl/sql program? Something like the one that is error proof?
      Meaning it has all the characteristics of a complete program that handles error exception?
      I can not explain it well, But I know when experts check or examine a program, they will know what is lacking in it.


      Thanks a lot,
      Kinz
        • 1. Re: What Makes An  Excellent PL/SQL program?
          ranit B
          >
          I am a newbie pl/sql programmer. I know how my coded pl/sql program different from an expert's program.
          Can you share me a sample code or a template of the well built pl/sql program? Something like the one that is error proof?
          Meaning it has all the characteristics of a complete program that handles error exception?
          I can not explain it well, But I know when experts check or examine a program, they will know what is lacking in it.
          Being a newbie... I'm also curious to know the answer...
          Let the Guru's speak and enlighten us with their excellence.
          • 2. Re: What Makes An  Excellent PL/SQL program?
            846231
            Ok fine :)

            I will give you an exam to write a pl/sql code. And we let the gurus check how do we fair. This is an assignment given to me by my boss. I will code it also and let us compare our answers.

            Here it is:

            Given 2 tables: CI_MR_STAGE_UP, & CI_RR_STAGE_UP
            ==================================

            Here is the pseudo code to be programmed:
            • For each record in CI_MR_STAGE_UP, where GEO_TYPE_CD = ‘NMI’ and MR_UP_STATUS_FLG = ‘E’
                   o Set MRUStagingID = MR_STAGE_UP_ID from the current CI_MR_STAGE_UP record
                   o Get all records from CI_RR_STAGE_UP, where MR_STAGE_UP_ID = MRUStagingID , sort by READ_SEQ in ASCENDING order
                   o Set RecordCountBase = 0
                   o For each record retrieved from CI_RR_STAGE_UP
                            Increment RecordCountBase by 1
                            Set ReadSeqBase = READ_SEQ from current CI_RR_STAGE_UP record
                            Get record from CM_RR_STAGE_UP where MR_STAGE_UP_ID = MRUStagingID, and READ_SEQ = ReadSeqBase
                            If CM_RR_STAGE_UP record was not found
                                   • Get all records from CM_RR_STAGE_UP, where MR_STAGE_UP_ID = MRUStagingID , sort by READ_SEQ in ASCENDING order
                                   • Set RecordCountCustom = 0
                                   • For each record retrieved from CM_RR_STAGE_UP
                                           o Increment RecordCountCustom by 1
                                           o If RecordCountCustom = RecordCountBase
                                                    Update READ_SEQ of CM_RR_STAGE_UP record to ReadSeqBase
                                           o End If
                                   • End Loop
                            End If
                  o End Loop
                  o Get all records from CM_RR_STAGE_UP, where MR_STAGE_UP_ID = MRUStagingID
                  o For each record retrieved from CM_RR_STAGE_UP 
                           Set ReadSeqCustom = READ_SEQ from current CM_RR_STAGE_UP record
                           Get record from CI_RR_STAGE_UP, where MR_STAGE_UP_ID = MRUStagingID and READ_SEQ = ReadSeqCustom
                           If CI_RR_STAGE_UP record was not found
                                   • Delete record from CM_RR_STAGE_UP where MR_STAGE_UP_ID = MRUStagingID and READ_SEQ = ReadSeqCustom
                           End If
                  o End Loop
            • End Loop
            Thanks,
            • 3. Re: What Makes An  Excellent PL/SQL program?
              Billy~Verreynne
              The same things make a PL/SQL program excellent, that make a program in any other language excellent. Software programming principles apply across programming languages. The basic principles are:
              - modularisation (a code unit should do ONE thing only and do it well)
              - use minimal code to achieve the desired result
              - consistent application of basic programming standards (camelcase, pascalcase, lowercase, indentation, etc)

              As PL/SQL executes in a multi-user and multi-process environment, there are additional considerations:
              - thread safety (code can be executed by different sessions at the same time)
              - read consistency
              - maximising SQL (not using PL/SQL to do SQL's job)

              There are no copy-and-paste templates for "excellent" code. In fact, such templates are often an indication of mediocre code by mediocre programmers. A good program is like a work of art. A Van Gogh painting and a Max Ernst painting are both works of art - but very different.

              So too are programming solutions. These are often different and unique. But the basics ito software engineering fundamentals will be the same.
              • 4. Re: What Makes An  Excellent PL/SQL program?
                Purvesh K
                KinsaKaUy? wrote:
                Hi All pl/sql experts,

                I am a newbie pl/sql programmer. I know how my coded pl/sql program different from an expert's program.
                Can you share me a sample code or a template of the well built pl/sql program? Something like the one that is error proof?
                Meaning it has all the characteristics of a complete program that handles error exception?
                I can not explain it well, But I know when experts check or examine a program, they will know what is lacking in it.
                I cannot give you a To-The-Point answer, but the difference of Thinking & Knowledge (of course), probably, segregates the Experts from Beginners.

                One good example is, {message:id=10770358}. See the difference of thinking in solution by Billy and that of mine. ;)
                • 5. Re: What Makes An  Excellent PL/SQL program?
                  ranit B
                  One good example is, {message:id=10770358}. See the difference of thinking in solution by Billy and that of mine. ;)
                  I agree, Purvesh...
                  Billy's solution was just so precise and cool at the same time.

                  @KinsaKaUy -
                  Why did you mark my first post as helpful?

                  >

                  I am a newbie pl/sql programmer. I know how my coded pl/sql program different from an expert's program.
                  Can you share me a sample code or a template of the well built pl/sql program? Something like the one that is error proof?
                  Meaning it has all the characteristics of a complete program that handles error exception?
                  I can not explain it well, But I know when experts check or examine a program, they will know what is lacking in it.

                  Being a newbie... I'm also curious to know the answer...
                  Let the Guru's speak and enlighten us with their excellence.
                  >

                  Please a request - Don't abuse the Helpful/Correct features... :-(
                  • 6. Re: What Makes An  Excellent PL/SQL program?
                    846231
                    Why did you mark my first post as helpful?
                    I am giving you credits because you are going to make an assignment program. :)


                    Thanks