This discussion is archived
1 2 3 Previous Next 35 Replies Latest reply: Mar 4, 2013 4:22 AM by javaMan2012 Go to original post RSS
  • 30. Re: Will the real PL/SQL language standards please stand up?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    APC wrote:

    But you can't go around calling people monkeys just because they capitalize SELECT and FROM. Every single example in the Oracle documentation uses that convention. So while it is not actually a standard it certainly feels like a standard.
    I was referring to PL/SQL specifically Andrew - not SQL. SQL is a bit of a different beast due to language structure.
  • 31. Re: Will the real PL/SQL language standards please stand up?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Hemant K Chitale wrote:

    Either Billy hasn't read Oracle documentation for some time or he has made a conscious decision not to criticize the Oracle Documentation team here.
    Hemant, the documentation standards used date back to the early 90's, if not earlier. Technical writers are not necessarily programmers. And as such will convey the technical information provided, using the "standard" used in the past, and using the data as provided by developers.

    As for criticising the documentation - I have. With actual on-line feedback (using my OTN account) provided for documentation pages. For example, SMTP sample code violate RFC standards - causing the exact same errors to be made by almost every single piece of SMTP code ever posted to this forum, as the Oracle sample code is copy and pasted.

    But I'm also pretty sure that such feedback seldom get to the actual decision makers that determines just how sample code is presented, and how correct these samples are.
  • 32. Re: Stored Procedure
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    tinku981 wrote:
    Billy  Verreynne  wrote:
    Sandeep98191 wrote:

    I tried and it is working (without any error)
    Of course. As all errors are SUPPRESSED in your code - pretending that whatever the error is, it never happens.

    Now how can you call that a robust solution?
    Sorry did not get what you mean?

    I thought post was that while making call to procedure an error is coming and not related to what is the best way to write the code!
    And that is what I responded to. The code (ignoring the issue of code standards), is wrong - as it suppresses any exception that may occur with the update. The code has the following exception handler:
    10 EXCEPTION
    11    WHEN OTHERS THEN
    12        ROLLBACK;
    13 END;
    So if any error occurs (deadlock, space, constraint violation), the exception handler silently suppresses the error. The caller of the code will correctly assume that the update was successful - when that was not the case.

    A basic rule for exception handling - if your exception handler does not fix the error, then it must raise the exception again. This means that if you do want to add a rollback to a procedure (questionable in most cases as the caller should handle when and where to commit or rollback the business transaction), the exception handler needs to look as follows:
    exception when OTHERS then
      rollback; --// re-act to the exception (does not fix it)
      raise; --// re-raise the exception that has occurred
    end;
  • 33. Re: Will the real PL/SQL language standards please stand up?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Stew Ashton wrote:

    Can you provide a link to a site that proposes "standard sensible and de-facto programming and naming standards", and that are applicable to PL/SQL?
    I have provided several links to language naming standards in posting {message:id=10880950}.

    Personally, I used Pascal standards for PL/SQL. PL/SQL is a member of the Pascal family and Pascal remains my favourite language. However, as PL/SQL is the direct descendent of Ada, the Ada standards are likely the best to follow for programmers with little Pascal or C language experience - and these standards are in the Ada 95 Quality and Style Guide. These standards will immediately make sense to any PL/SQL programmer - PL/SQL and Ada looks pretty much identical from a basic source code perspective. You will be able to read and understand the Ada examples without a problem.

    If you are a Java or .Net programmer, then apply the naming conventions for these.

    The core set of naming conventions and coding style are pretty much the same from Ada, Pascal and C, to Java and .Net. So there is a lot of similarity - and none of these standards supports or proposes that reserved words be written in upper case.
  • 34. Re: Will the real PL/SQL language standards please stand up?
    APC Oracle ACE
    Currently Being Moderated
    Billy  Verreynne  wrote:
    APC wrote:

    But you can't go around calling people monkeys just because they capitalize SELECT and FROM. Every single example in the Oracle documentation uses that convention. So while it is not actually a standard it certainly feels like a standard.
    I was referring to PL/SQL specifically Andrew - not SQL. SQL is a bit of a different beast due to language structure.
    Billy

    I could just as easily have said BEGIN and END. My point applies to [url http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/fundamentals.htm#sthref524]the PL/SQL documentation too.

    Cheers, APC
  • 35. Re: Will the real PL/SQL language standards please stand up?
    javaMan2012 Newbie
    Currently Being Moderated
    Hi Guys,

    The following works on my end:


    CREATE OR REPLACE PROCEDURE  update_invoices_credit_total (
    invoice_number_param  VARCHAR2,
    credit_total_param    NUMBER)
    AS
    BEGIN
    UPDATE invoices
    SET credit_total  = credit_total_param
    WHERE invoice_number = invoice_number_param;

    COMMIT;


    END;
    /

    CALL update_invoices_credit_total('367447', 19);

    :)
1 2 3 Previous Next

Legend

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