Passing Parameters to Subprograms

PROCEDURE insert_anime_character(i_name IN VARCHAR2, i_series IN VARCHAR2, i_birthdate IN DATE, i_hair_color IN VARCHAR2 DEFAULT 'black')

positional notation
- follows the natural order of passing subprogram paramater values

EXEC insert_anime_character('Naruto', 'Naruto', SYSDATE, 'yellow');

named notation
- uses the "=>" as an association operator
- parameters could be passed in any order

mixed notation
- passing can start with positional then followed by named notation. However if you switch to named notation, you cannot revert back to positional notation. I'm assuming also that if you start with named notation you cannot switch to positional - because using named notation discards the positional data.


notes:
- parameters with default values can be skipped

PARAMETER MODES

3 types: IN (default), OUT, IN OUT

!! Avoid using OUT and IN OUT for functions. f(x)s should only return one value.

IN (mode)
- acts like a constant inside the subprogram, which in effect you cannot modify it
- the ONLY mode that can be assigned a DEFAULT value
- can be a constant, literal, initialized variable or expression

OUT (mode)
- in contrast to IN mode, you can use OUT parameters as a local variable: and change its values.
- OUT parameters MUST BE VARIABLES

- the initial value of an OUT parameter is discarded when it enters the subprogram. However you can override this by specifying the compiler hint NOCOPY.
- the idea is to modify it inside the subprogram and it will carry OUT the value to the caller subprogram
- OUT parameters are initialized to NULL; therefore you cannot assign a NOT NULL configured subtype to an OUT parameter

---

well I used it immediately and converted some of my functions to procedures for multi-value RETURN

(TODO: IN OUT)

PL/SQL Collection Types

(draft)

a. VARRAY (array)
  - used when the number of elements are known beforehand
  - if < 4kb : store inside table / if > 4kb outside the table, but same tablespace
  - elements are retrieved all at once - therefore better choice when you need to do an operation at all elements at once
b. nested tables (sets and bags)
  - sparse
c. associative arrays (hashtable)

(NT and AR are formerly known as index-by tables)

SUBMULTISET

The idea of SUBMULTISET is to compare two collections and determine if one is a subset of the other (or not). In contrast to IN where you only get to test one element against a collection.

(Oracle doesn't have CONTAINS keyword XDw prolly it's a trap for Java programmers - in which most collection objects have contains(Object) method @A@)

PROCEDURE x_mink

    IS
   
        TYPE integer_table IS TABLE OF NUMBER;
       
        l_numbers integer_table := integer_table (1,2,3,4,5,6,7,8,9,0);
        l_lucky_numbers integer_table := integer_table(3,7,9);
        l_unlucky_numbers integer_table := integer_table(15,20);   
       
    BEGIN
       
   
        --  SUBMULTISET test
       
        IF l_lucky_numbers SUBMULTISET OF l_numbers THEN
            DBMS_OUTPUT.PUT_LINE('l_lucky_numbers is SUBMULTISET of l_numbers');
        ELSE   
            DBMS_OUTPUT.PUT_LINE('l_lucky_numbers is NOT SUBMULTISET of l_numbers');
        END IF;
       
       
        IF l_unlucky_numbers SUBMULTISET OF l_numbers THEN
            DBMS_OUTPUT.PUT_LINE('l_unlucky_numbers is SUBMULTISET of l_numbers');
        ELSE   
            DBMS_OUTPUT.PUT_LINE('l_unlucky_numbers is NOT SUBMULTISET of l_numbers');
        END IF; 
                
        NULL;
   
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE(SQLERRM);
               
    END x_mink;


result

***Update Dec. 15 PLSQL challenge

Your Results Your Score 598 You Got Right 4 You Got Wrong 0 Quiz's Correct Choices 2 Quiz's Incorrect Choices 2

Player Statistics Number of Players 1150 Top Score 747 Lowest Score 0 Average Score 526.85

Your Ranking This Month 1124 This Quarter 1901 Overall 3255

Defining Oracle CURSOR

Basically there are 3 types of CURSOR definition:

(a) basic
(b) with parameter
(c) with return

--

will explain the three but let me start with (b)

...

IS

  CURSOR cursor_salad (i_color IN FRUITS.COLOR%TYPE)
    IS
      SELECT NAME
      FROM   FRUITS
      WHERE  COLOR = i_color;
...


*** update Dec. 14 plsql challenge

Your Results Your Score 604 You Got Right 4 You Got Wrong 0 Quiz's Correct Choices 1 Quiz's Incorrect Choices 3

Player Statistics Number of Players 1168 Top Score 749 Lowest Score 69 Average Score 511.29

Your Ranking This Month 1223 This Quarter 2044 Overall 3600

yay :D

well lesson learned, once you have defined the parameter of a CURSOR in a package, you cannot redefine it again because it was already opened - during the first definition. You could imagine the CURSOR as a public static-ally defined attribute inside a class - it is the same for all instances @A@

PLSQL CASE

One can see CASE as a compact variation of IF THEN ELSE construct. Compact in a sense that you don't have to repeatedly encode the expression IF <condition> - however CASE is only applicable to scenarios wherein you need to test a variable against a set of possible values. 

CASE version

CASE stoplight
  WHEN 'red' THEN Action('Stop');
  WHEN 'yellow' THEN Action('SlowDown');
  WHEN 'green' THEN Action('Go');
  ELSE Action('Caution');
END CASE;

IF THEN ELSE version

IF stoplight = 'red' THEN 
  Action('Stop'); 
ELSIF stoplight = 'yellow' THEN
  Action('SlowDown'); 
ELSIF stoplight = 'green' THEN
  Action('Go'); 
ELSE
  Action('Caution'); 
END IF;

Some says that using CASE construct expresses the programmer's intent better and explicitly expresses that the given cases are the one that only needs handling.

CASE STATEMENT AND CASE EXPRESSION

A variation of CASE construct allows you to use it for output / assigning values. (pretty neat)

evaluation := 
  CASE
    WHEN grade BETWEEN 90 AND 100 THEN 'very good'
    WHEN grade BETWEEN 80 AND 89  THEN 'good'
    WHEN grade BETWEEN 70 AND 79  THEN 'fair'
    ELSE 'fail'
  END;


CASE_NOT_FOUND

this exception object is thrown when you don't provide an ELSE case and no case handles the given parameter passed to the statement.

(TODO: expound)

TABLE TYPE (array like data structure)

PROCEDURE x_burner
    IS
   
        TYPE VARCHAR2_TABLE IS TABLE OF VARCHAR2(20)
            INDEX BY PLS_INTEGER;
           
        l_test_table VARCHAR2_TABLE;
   
    BEGIN
   
        l_test_table(-10) := 'test value';
       
        DBMS_OUTPUT.PUT_LINE(l_test_table(-10));   
       
        NULL;
           
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE(SQLERRM);
                NULL;
    END x_burner;

You could imagine TABLE TYPE as a generic array .. actually  not an array because it's not static; more of an arraylist I guess. And yeah, as you can see in the example, I can set a negative index.

when you try to access an index with no element, ORA-01403: no data found exception is thrown.

(TODO: expound on INDEX BY option)

Default Parameter Objects Inside PageContext

I did a System.out.println() .. (TODO: expound on this)

Passing Parameters to Dialog Page (OAF)

  

Simply use OADialogPage 's setFormParameters(Hashtable) method :D

A dialog page is independent from it's source/caller - and doesn't automatically inherit the page context parameters. So you need to explicitly pass the parameters you need through a Hashtable object @A@

Global Temporary Table in TOAD

well it seems you don't need to worry memorizing the create syntax if you have TOAD XDw By default it will attach the ON COMMIT DELETE ROWS option.

VARCHAR2 SQL > 4000 bytes

My requirement was to produce a result set from a custom SQL (data is not from a table) which could have hundreds of rows @A@

For that, I created an SQL builder that concatenates UNION-ed SELECT statements which in the end exceeded 4000 bytes. Unfortunately VARCHAR2 only allows up until that point. The solution was to change my variable's data type to CLOB? not quite. REF CURSORs doesn't OPEN to CLOBs and TO_CHAR doesn't do the trick either TwT uhuhu

---

update:

so the final solution was to use a GLOBAL TEMPORARY TABLE

The ON COMMIT DELETE ROWS part purges the table when you issue the command COMMIT

instead of creating a mega-SQL, you'll just need to insert into a GLOBAL TEMPORARY TABLE just like above; then OPEN the REF CURSOR using a SELECT * statement FROM that Global Temporary Table ^^ pretty neat <3

All I need to do is convert this procedure into a function then return that REF CURSOR ^^/

(TBC clarify)

Google's Java Library

http://code.google.com/p/guava-libraries/

Codename: Guava - maybe a concatenation of Google + Java =A= lolz

adding this to my study list, pretty interesting

Binding Variables from Java to PLSQL

Probably most callable statement constructors are the same; what you need to take note of is the PLSQL String. The order of "?" characters denotes the index of the variable you want to bind. In the example, we want to bind a Java String to the PLSQL call. (Strings maps to VARCHAR in Oracle).

callableStatment.setString(1, transactionId);

The index starts with 1 (unlike for other data constructs which start at 0)

(side note: the callableStatement.execute() method returns a boolean : true - if the procedure/function returns a value or resultset; otherwise false .. this is just to clarify that this doesn't denote if the execution has been successful or not)

(TBC)

OADialogPage without Yes/No Buttons

prior to that I had this in my code:

OADialogPage dialogPage =
        new OADialogPage(OAException.CONFIRMATION, confirmMessage, null, "", "");

This sets the yes and no button URL to blank strings and results to

 

which is not quite the thing I wanted @A@

now, setting those two parameters to null rather than blank String:

OADialogPage dialogPage =
        new OADialogPage(OAException.CONFIRMATION, confirmMessage, null, null, null);

will result to:

:D

Creating an Immutable Class

I never really tried to make one.

but the idea is to make the constructors private and provide a public static method that will return an instance of the class given the immutable parameters.

IF IS NOT / <> NULL

IF i_earnings_amount IS NOT NULL THEN

        l_earnings_amount := TO_CHAR(i_earnings_amount, '9999.99');

END IF;

IF i_earnings_amount <> NULL THEN

        l_earnings_amount := TO_CHAR(i_earnings_amount, '9999.99');

END IF;

There is a significant difference between the two statements. IS NOT NULL, tests whether the field/variable has a value. (TODO: further clarify this issue)

PL/SQL IF BETWEEN

First time to use it :D Normally you will find the BETWEEN keyword only in SQL but since we're talking about PL/SQL here, you could actually use it for a IF THEN ELSE conditional construct ^^/

PL/SQL Exceptions

FUNCTION generate_amortization_sched_fn (i_transaction_id IN XXHLS.XXHLS_LOAN_APPLICATION.TRANSACTION_ID%TYPE,
                                         i_schedule_type IN VARCHAR2)
    RETURN XXHLS_TYPES_PKG.t_ref_cursor
    IS
   
        UNDEFINED_SCHEDULE_TYPE EXCEPTION;
   
        l_loan_amount NUMBER DEFAULT 0;
        l_principal NUMBER DEFAULT 0;
        l_interest_rate NUMBER DEFAULT 0;
        l_glip_monthly_premium NUMBER DEFAULT 0;
        l_gdip_monthly_premium NUMBER DEFAULT 0;
       
    BEGIN
   
        SELECT  LOAN_AMOUNT, PAYMENT_AMOUNT, INTEREST_RATE
        INTO    l_loan_amount, l_principal, l_interest_rate
        FROM    XXHLS.XXHLS_LOAN_APPLICATION
        WHERE   TRANSACTION_ID = i_transaction_id;
       
        SELECT  PREMIUM_AMOUNT
        INTO    l_glip_monthly_premium
        FROM    XXHLS.XXHLS_INSURANCE_DETAILS
        WHERE   TRANSACTION_ID = i_transaction_id
                AND RATE_TYPE = 'GLIP';
               
        SELECT  PREMIUM_AMOUNT
        INTO    l_gdip_monthly_premium
        FROM    XXHLS.XXHLS_INSURANCE_DETAILS
        WHERE   TRANSACTION_ID = i_transaction_id
                AND RATE_TYPE = 'GDIP';
               
        IF i_schedule_type = 'MO1' THEN   
            RETURN generate_month_amort_sched_fn (l_loan_amount,
                                                  l_principal,
                                                  l_interest_rate,
                                                  l_glip_monthly_premium,
                                                  l_gdip_monthly_premium);
        ELSIF i_schedule_type = 'MO2' THEN
            RETURN NULL;
        ELSIF i_schedule_type = 'YRA' THEN
            RETURN NULL;
        ELSE
            RAISE UNDEFINED_SCHEDULE_TYPE;
        END IF;                                              
                                           
        RETURN NULL;                       
       
        EXCEPTION
            WHEN UNDEFINED_SCHEDULE_TYPE THEN
                DBMS_OUTPUT.PUT_LINE('undefined amortization schedule type');
                RETURN NULL;
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE(SQLERRM);
                RETURN NULL;
    END generate_amortization_sched_fn;

Oracle PL/SQL Snippets

Just want to post this up @A@;;; it's going to be more complicated tomorrow >w<

 

Random

!! If you plan on using sub-methods, declare it first before the main method

Escaping Single Quote in VARCHAR
use 2 single quotes ''
l_test VARCHAR2(50) := '''pogi ako''';

DBMS_OUTPUT.PUT_LINE(l_test); will result to 'pogi ako' :D

Exiting a Loop
 
1. EXIT WHEN l_loan_amount < 0;
2. EXIT;

can't modify procedure IN variables 
- therefore you need to assign it to a local variable.

Selecting a Random Record from a Table

SELECT APPRAISER_DESCRIPTION FROM
(SELECT UNIQUE(APPRAISER_DESCRIPTION) FROM XXHLS_APPRAISER
ORDER BY DBMS_RANDOM.VALUE)
WHERE ROWNUM = 1

The critical things to remember are DBMS_RANDOM object and ROWNUM = 1

it seems DBMS_RANDOM.VALUE generates a random numbers like

0.672112277571927

0.836266561568141

0.149278175771559

0.264812630523664

0.700945172576225

what I don't quite get is how does it determine the column name to ORDER BY

ROWNUM = 1 qualifies the first output from the resultset @A@;;;;