Window 2 World

---------------------------------------------
getting values from a cursor to a table type
--------------------------------------------

-- following things can be defined in package specification

-- define record

TYPE studentDetails IS RECORD(
v_student_id NUMBER(8),
v_student_name VARCHAR2(25));

-- define table [nested table]

TYPE studentDetailsTab IS TABLE OF studentDetails INDEX BY BINARY_INTEGER;


-- followng things will be inside package body and inside a procedure in a declaration section

-- define a cursor


CURSOR cur_student_details(p_class_id NUMBER)
IS
SELECT student_id,
student_name
FROM student_table
WHERE class_id = p_class_id;


-- define a variable of type studentDetailsTab

v_t_studentDetailsTab studentDetailsTab;


-- follwoing things comes in the procedure body
-- open the cursor and fetch the values into the table type

OPEN cur_student_details(v_t_classid);
FETCH cur_student_details BULK COLLECT INTO v_t_studentDetailsTab;
CLOSE cur_student_details;

-- looping throgh the table type

IF v_t_studentDetailsTab.COUNT > 0 THEN
FOR tempIter IN v_t_studentDetailsTab.FIRST..v_t_studentDetailsTab.LAST
LOOP
DBMS_OUTPUT.PUT_LINE('student id ' || v_t_studentDetailsTab(tempIter).v_student_id);
DBMS_OUTPUT.PUT_LINE('student name ' || v_t_studentDetailsTab(tempIter).v_student_name);
END LOOP;
END IF;

--------------------------------------------------
getting values from a SYS_REFCURSOR to table type
--------------------------------------------------

-- cosider there is a function returnting the sys_refcursor in some other package

FUNCTION FNC_STUDENTDETAILS(p_class_id NUMBER,
p_sql_code OUT PLS_INTEGER,
p_sql_errm OUT VARCHAR2) RETURN sys_refcursor IS

v_cur_studentdetails sys_refcursor;
BEGIN
open v_cur_studentdetails FOR
SELECT student_id,
student_name
FROM student_table
WHERE class_id = p_class_id;

return v_cur_studentdetails;

END;
EXCEPTION
WHEN OTHERS THEN
p_sql_code := SQLCODE;
p_sql_errm := SQLERRM;
RETURN v_cur_studentdetails;
END FNC_STUDENTDETAILS;

-- how to use it
-- following things can be defined in package specification

-- define record

TYPE studentDetails IS RECORD(
v_student_id NUMBER(8),
v_student_name VARCHAR2(25));

-- define table [nested table]

TYPE studentDetailsTab IS TABLE OF studentDetails INDEX BY BINARY_INTEGER;

--- define the following in package body , package declaration section

v_t_student_details SYS_REFCURSOR;
v_t_studentDetailsTab studentDetailsTab;

-- call the following in the procdure body

v_t_student_details := .FNC_STUDENTDETAILS(v_t_classid,p_errcd,p_errm);

-- fetch student details
FETCH v_t_student_details BULK COLLECT INTO v_t_studentDetailsTab;
-- we can check
IF v_t_student_details%NOTFOUND THEN
null; -- do some thing
END IF;

# posted by srikanth : 12:00 PM  1 Comments

create types in oracle

1) create or replace TYPE IDHolderList IS TABLE OF NUMBER;
2) create public synonym S_IDHOLDERLIST for IDHOLDERLIST; -- incase of multiple schemas create this public synonym

3) write the procedure

PROCEDURE prc_test(v_IDList OUT s_IDHolderList)
IS
BEGIN -- START OF PROCEDURE

v_IDList.EXTEND;
v_IDList(v_IDList.COUNT) := 1;
v_IDList.EXTEND;
v_IDList(v_IDList.COUNT) := 2;

END prc_test;


4) java code

// sepcial imports needed
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
import oracle.sql.ARRAY;
import java.math.BigDecimal;

// sample code
Connection objConnection = getConnection();
OracleCallableStatement objCall = null;
objCall = (OracleCallableStatement)objConnection.prepareCall("call prc_test(?)");
objCall.registerOutParameter(3,OracleTypes.ARRAY,"S_IDHOLDERLIST");
objCall.execute();
ARRAY simpleArray = objCall.getARRAY(3);
if(simpleArray!=null){
BigDecimal[] values = (BigDecimal[])simpleArray.getArray();
}



few clarifications about oracle collection types
------------------------------------------------

PL/SQL offers these collection types:

Index-by tables, also known as associative arrays, let you look up elements using arbitrary numbers and strings for subscript values. (They are similar to hash tables in other programming languages.)

Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts. You can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.

Varrays (short for variable-size arrays) hold a fixed number of elements (although you can change the number of elements at runtime). They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.

Arrays in other languages become VARRAYs in PL/SQL.

Sets and bags in other languages become nested tables in PL/SQL.

Hash tables and other kinds of unordered lookup tables in other languages become associative arrays in PL/SQL.

-------------

Choosing Between Nested Tables and Associative Arrays
Both nested tables and associative arrays (formerly known as index-by tables) use similar subscript notation, but they have different characteristics when it comes to persistence and ease of parameter passing.

Nested tables can be stored in a database column, but associative arrays cannot. Nested tables are appropriate for important data relationships that must be stored persistently.

Associative arrays are appropriate for relatively small lookup tables where the collection can be constructed in memory each time a procedure is called or a package is initialized. They are good for collecting information whose volume is unknown beforehand, because there is no fixed limit on their size. Their index values are more flexible, because associative array subscripts can be negative, can be nonsequential, and can use string values instead of numbers when appropriate.

PL/SQL automatically converts between host arrays and associative arrays that use numeric key values. The most efficient way to pass collections to and from the database server is to use anonymous PL/SQL blocks to bulk-bind input and output host arrays to associative arrays.

Choosing Between Nested Tables and Varrays
Varrays are a good choice when the number of elements is known in advance, and when the elements are usually all accessed in sequence. When stored in the database, varrays retain their ordering and subscripts.

Each varray is stored as a single object, either inside the table of which it is a column (if the varray is less than 4KB) or outside the table but still in the same tablespace (if the varray is greater than 4KB). You must update or retrieve all elements of the varray at the same time, which is most appropriate when performing some operation on all the elements at once. But you might find it impractical to store and retrieve large numbers of elements this way.

Nested tables can be sparse: you can delete arbitrary elements, rather than just removing an item from the end. Nested table data is stored out-of-line in a store table, a system-generated database table associated with the nested table. This makes nested tables suitable for queries and updates that only affect some elements of the collection. You cannot rely on the order and subscripts of a nested table remaining stable as the table is stored and retrieved, because the order and subscripts are not preserved when a nested table is stored in the database.

using struts validation frame work as well as the validation method in validatorform
=====================================================================================
1) add the validation rules in the validation framework
2) for more validations override the validation method of the ValidatorForm as follows

public ActionErrors validate(ActionMapping map,HttpServletRequest req){

// get the validation errors from the frame work
ActionErrors objActionErros = super.validate(map,req);

// start custom validations
String strPageNumber = req.getParameter("page");
if(strPageNumber!=null && !strPageNumber.eqlus("2")){
if(objActionErros==null){
objActionErros = new ActionErrors();
}
objActionErros.add("errors",new ActionError("errors.wrongpage"));// this key will be in the message resource file
}

return objActionErros;
}

------------------
A) Proposal STAGE
------------------
1) proposal

2) Estimation [can use functional point estimation] [consider fixed bid , time and management]

-------------------------------------------------------
B) Once Proposal/Estimation is approved start Analysis
-------------------------------------------------------

1) prepare the document for project [called as PDSP] , which contains details about harware requirements,
configuration management tool , Quaility person ,Quality audit frequencey, other trimmings for the quality
policy of the company in specific to the project

2) should start maintaining the Trancebility matrix [horizantal , vertical tracebility matrix]
horizantal will contain functional points against deliverables [HLD,SRS,LLDs,UTCs,ITCs,STCs]
Vertical will contain functional points against functional points to get an idea of impact of change
in one functionality on other

3) preparing check lists for variuos artifacts

4) Software requirement specification [SRS]

----------------------------------------------
C) Once architecture is approved start Design
----------------------------------------------

1) High Level Document [Architecture] [HLD]

2) If one have to choose between technologies one has to prepare a sheet for comparing them for taking a decision

the reoport is called Decision Analysis Report [DAR]

3) Low Level Document [should be done for all the use cases]

4) Unit Test Cases

5) Integration Test Cases[integrating modules or with other projects]

6) System Test Cases [functionality test cases]

----------------------------------------------
D) Once Design is approved start Construction
----------------------------------------------

1) coding , code review

2) unit testing , capturing unit test results


------------------------------------------
D) Once construction is done start testing
------------------------------------------

1) integration testing , capture results , raise defects , fix and release

2) system testing , capture results , raise defects , fix and release

3) user acceptance testing , capture results , raise defects , fix and release


Each one of the above should follow the following steps

i) Preparation

ii) each artifcat will have a check list, the check list has to be filled with self review

iii)external Review [in some cases it will goto SME (System Mater Expert) review ]

iv) all the review/testing defects should be captured and fixed

v) Rework [again goes back to review/testing until every thing is ok]

vi) Each deliverable should go after FIR (Final Inspection Report) from Quality person

Vii) after each page there will be a casual analysis meeting for discussing where more
defects are coming and how to reduce them [training or adding items to check lists etc]
and the strategy for the upcoming phase

Viii) every discussion either with customer or internal should have Minutes of Meeting and open items for action captured

ix) every artifact , evidence (like customer mail , Minutes of meeting) should be in configuration management tool

x) In the above Phases one should ensure to get the best practices , resuable components.

-------------------------------------------------------
E) Once UAT (user acceptence) is done start Maintanance
-------------------------------------------------------

1) raising tickets , closing , tracking time