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@;;;;

Oracle AF Trains

Steps

1. create a shared (standalone) train region

2. create your train pages (the individual steps)

3. create the links in the shared region
- configure the Destination URI to point on the individual pages
- also configure the Text parameter (this will be the one shown above the pages)

4. In your page's layout region, add a location
- configure the Extends property to point to the shared train region you created in the first step


*make sure you have Application Modules configured on your pages (in contrast to pages, this attribute follows the java package syntax)

5. create a page for your Navigation Bar

6. well, finally create the Navigation Bar region
- configure the First Step and Last Step attributes. Normally you would like to configure First Step to 1 and Last Step to the number of the last page (in this case, 2)

7. create links inside that region
- configure the Destination URI in this format OA.jsp?page=/xxx/xxx/xxx/xxxPG
- configure the text visual property

8. create a pageButtonBar region for each individual step pages (you may create the first then copy paste to the rest)

*make sure the Nav Bar links text and the Train links text are identical

Adding Time Units to SYSDATE

SELECT SYSDATE + INTERVAL '30' MINUTE
FROM SYS.DUAL

simply nice ^^ <3

l_performance_rating_year1 VARCHAR2(10) := TO_CHAR(SYSDATE - INTERVAL '3' YEAR, 'YYYY');
l_performance_rating_year2 VARCHAR2(10) := TO_CHAR(SYSDATE - INTERVAL '2' YEAR, 'YYYY');
l_performance_rating_year3 VARCHAR2(10) := TO_CHAR(SYSDATE - INTERVAL '1' YEAR, 'YYYY');

SYS.DUAL

First time seeing it XD

SELECT WF_NOTIFICATIONS_S.NEXTVAL
INTO nid
FROM SYS.DUAL;

got used to the plain DUAL, I didn't noticed it's from SYS schema XD? just something trivial

(String) Object vs Object.toString()

well, just a thought - I searched around and it seems casting is preferred over the toString() method call XD It's slightly "faster" .. but prolly this info would only be useful if you're dealing with tons of Objects to convert into Strings ^^;;

Oracle DECODE

Well I've used this a dozen of times .. just noting myself :D

SELECT DECODE(XXHLS_STAFF_PKG.GET_CATEGORY_FN(SYSDATE,PERSON_ID), 'PS', 'LIBOR Interest Rate', 'SS', 'Interest Rate') AS TYPE
FROM XXHLS.XXHLS_LOAN_APPLICATION

=A=

Commit in DML Procedure

PROCEDURE armageddon
    IS
    BEGIN
        purge_usr_loan_data_fn(113814);
        commit;
    END armageddon;

Well just a note to myself. If you want your DML changes reflected in the database, issue the command commit =A=

The procedure above calls on another procedure programmed to delete all data I persisted in the database ^^ Having tons of test data could be annoying most of the time, so I need to clean them up once in a while.

Dynamic Messages Using Tokens

First you must create a message in Oracle Apps and tag the dynamic part of the message with an ampersand "&". By convention these tags must be in uppercase. - &LOAN_ENTRIES

Java Code in Controller

MessageToken[] tokens = {new MessageToken("LOAN_ENTRIES", getInstructionLoanEntries(loanTypeCode,am)) };
 oaRawTextBean.setText(pageContext.getMessage("XXHLS", "XXHLS_SIMULATE_PS_INST", tokens));

Import MessageToken class and create an array of it with elements containing the tags you used in the message. Take note of the application short name and the message name - parameters that will be used for the getMessage method.

Random Rant =w=

JDeveloper's intellisense is buggy - when there is an error in the code, the reflector cannot properly detect the methods in the class anymore .. wtf TwT and so as the refactor function .. rawr

Date Manipulation in Java

java.util.Date doesn't have explicit methods for adding days, months, years, etc. However you can indirectly accomplish these operations through the java.util.Calendar class:
       
Calendar calendar = Calendar.getInstance();
calendar.add(Calendar.YEAR, 3);
Date applicationDate = calendar.getTime();

This example gets the current date (aka SYSDATE) then adds 3 years to it; then assigns it to applicationDate variable.

If you already have an existing Date object, just get a Calendar instance then call:

setTime(Date date)

function =A=

DBMS_OUTPUT Function Using SQL*PLUS

you must first enable message output through this panel:

then issue a command like:

SQL> EXEC DBMS_OUTPUT.PUT_LINE('Pogi Ako')
Pogi Ako

PL/SQL procedure successfully completed.

Utility PLSQL Function for Retrieving MEANING from FND_LOOKUP_VALUES

 Java function from the utility class

    public static String getFndLookupValue(Date effectiveDate,
                                            String lookupType,
                                            String lookupCode,
                                            OADBTransaction oaDBTransaction) {

        CallableStatement callableStatement = null;
        String s = "BEGIN ? := xxhls_util_pkg.get_fnd_meaning_fn(?,?,?); END;";

        try {

            callableStatement = oaDBTransaction.createCallableStatement(s, -1);
            callableStatement.registerOutParameter(1, OracleTypes.VARCHAR);
            callableStatement.setDate(2, effectiveDate);
            callableStatement.setString(3, lookupType);
            callableStatement.setString(4, lookupCode);
            callableStatement.execute();
           
            return callableStatement.getString(1);
          
        } catch (Exception sqlexception) {

            throw new OAException(sqlexception.getMessage());

        } finally {

            try {
                if (callableStatement != null)
                    callableStatement.close();               
            } catch (SQLException sqlexception1) {
                throw new OAException(sqlexception1.getMessage());
            }

        }

    }

Java function inside the Application Module (pretty short =w=;; )

    public String getFndLookupValue(String lookupType, String lookupCode) {
       
        Date date = new Date(new java.util.Date().getTime());
       
        return XxhlsServerUtil.getFndLookupValue(date, lookupType, lookupCode, this.getOADBTransaction());
       
    }

PLSQL code

FUNCTION get_fnd_meaning_fn (i_effective_date IN DATE,
                             i_lookup_type IN FND_LOOKUP_VALUES.LOOKUP_TYPE%TYPE,
                             i_lookup_code IN FND_LOOKUP_VALUES.LOOKUP_CODE%TYPE)
                            
    RETURN FND_LOOKUP_VALUES.MEANING%TYPE

    IS
   
        l_meaning FND_LOOKUP_VALUES.MEANING%TYPE;
   
    BEGIN
   
        SELECT  MEANING
        INTO    l_meaning
        FROM    FND_LOOKUP_VALUES
        WHERE   LOOKUP_TYPE = i_lookup_type
                AND LOOKUP_CODE = i_lookup_code
                AND i_effective_date BETWEEN START_DATE_ACTIVE AND END_DATE_ACTIVE;
               
        RETURN  l_meaning;
               
        EXCEPTION
       
            WHEN NO_DATA_FOUND THEN
           
            SELECT  MEANING
            INTO    l_meaning
            FROM    FND_LOOKUP_VALUES
            WHERE   LOOKUP_TYPE = i_lookup_type
                    AND LOOKUP_CODE = i_lookup_code
                    AND END_DATE_ACTIVE IS NULL;
           
            RETURN  l_meaning;
   
    END get_fnd_meaning_fn;