OAF Callable Statement Parameter Binding

unfortunately ..

• when you set parameters using Ordinal binding, you must get OUT parameters via index
• you could only get OUT cursor parameter via index which forces you to use Ordinal binding - because CallableStatement only have getCursor(int index) method.

Looping through REF CURSOR

inside a LOOP you need to FETCH from the REF CURSOR to a RECORD (with matching field declarations). Then from the RECORD you could access the field values. Terminate the LOOP via EXIT command (syntax: EXIT WHEN [condition]) - usually [cursor_name]%NOTFOUND condition is used.

• RECORD must match the REF CURSOR's order/number of attributes
• in case the REF CURSOR is opened to a [SELECT *] on a table, use the table's %ROWTYPE (e.g. syntax PAYMENTS%ROWTYPE)

Nested PLSQL Exceptions

well, it's possible to do this; but you might want to avoid using this approach @w@;;;

USING clause

EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num'

USING dept_id;

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)

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)

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)

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 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.

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;

Returning VARCHAR/String from a PLSQL call in Application Module

Well, not all the time you want a CURSOR

    public String getNextReferenceNumber(String loanTypeCode) {

        CallableStatement callableStatement = null;
        String s = "BEGIN ? := xxhls_housing_loan_pkg.get_next_reference_number_fn(?); END;";

        try {

            callableStatement = getOADBTransaction().createCallableStatement(s, -1);
            callableStatement.registerOutParameter(1, OracleTypes.VARCHAR);
            callableStatement.setString(2, loanTypeCode);
            callableStatement.execute();
           
            return ((OracleCallableStatement)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());
            }

        }

    }

Oracle Function Overloading

things to keep in mind

1. you can't overload by merely changing the return type
  - meaning you must change the number, name, order or data type of the parameters (but I'm a bit skeptic over changing the name - I haven't tested it yet)