Hide Query Complication using Types

Hide and control complication within Stored procedure by using types. Perform following steps..

1) Create Type
2) Write Stored Procedure
3) Query

Create Type:
----------------------------------------------------------
Drop type OT_TEST_ROW;
Drop type OT_TEST;

CREATE OR REPLACE
TYPE "OT_TEST_ROW" AS OBJECT
--===========================================================================
-- Application : HCP - NJTA
--
-- Description : This type is to populate Data for High Variance
--
-- Modification History:-
-- --------------------------------------------------------------------------
-- Date Name Comments
-- -------- ------------------- --------------------------------------------
-- 03/13/10 Parag Wardhane Created.
--===========================================================================
(
COL_DATE DATE,
COL_CHAR VARCHAR2(10),
COL_NUMBER NUMBER(10,2)
)
/


CREATE OR REPLACE
TYPE "OT_TEST" AS TABLE OF
--===========================================================================
-- Application : HCP - NJTA
--
-- Description : This type is to populate Data for High Variance
--
--
-- Modification History:-
-- --------------------------------------------------------------------------
-- Date Name Comments
-- -------- ------------------- ---------------------------------------------
-- 03/13/10 Parag Wardhane Created.
--===========================================================================
OT_TEST_ROW;
/

2) Write Stored procedure

-----------------------------------------
CREATE OR REPLACE FUNCTION HideQueryComplication (arg_in_Testid IN NUMBER)
RETURN OT_TEST
IS

V_AttemptStep VARCHAR2(500) := 'attempting to initialize';
V_error_msg VARCHAR2(400);
v_success VARCHAR2(1);
V_ErrorCode NUMBER DEFAULT 0;
V_ErrorText VARCHAR2(400);
V_CustomErrorText VARCHAR2(400);
V_ErrorReturnCode NUMBER;


V_CountOrgs NUMBER := 1;

l_data OT_TEST := OT_TEST();
l_BlankData OT_TEST := OT_TEST();

V_Counter NUMBER := 1;


BEGIN

For TestRec in ( SELECT sysdate col_date,'TEST' col_char,'1234' col_number
FROM DUAL /* Any Complicated Query */ ) LOOP


l_data.extend;

l_data(V_Counter) := OT_TEST_ROW(
TestRec.col_date, /* DATE */
TestRec.col_char, /*VARCHAR2 */
TestRec.col_number /*NUMBER*/ );

V_Counter := V_Counter + 1;


END LOOP;


RETURN l_data;

EXCEPTION
WHEN OTHERS THEN


V_CustomErrorText := ': ' || 'While ' || V_AttemptStep;
V_ErrorCode := SQLCODE;
V_ErrorText := SUBSTR(SQLERRM, 1, 400);

RETURN l_BlankData ;

END HideQueryComplication;
/
--------------------------------------------------------

2) Query:

SELECT HideQueryComplication(1) FROM DUAL;