Oracle Object Oriented Programming

CREATE OR REPLACE TYPE xx_data_struc_obj AS OBJECT(
   num_col  NUMBER
 , num_col2 NUMBER);
/

CREATE OR REPLACE TYPE xx_data_construct_obj AS OBJECT(

   --Define member Functions
   xx_data_struc xx_data_struc_obj

 -- this function performs addition
 , MEMBER FUNCTION addition
      RETURN NUMBER

 -- this function performs substraction
 , MEMBER FUNCTION substraction
      RETURN NUMBER

 -- this function performs multiplication
 , MEMBER FUNCTION multiplication
      RETURN NUMBER

 -- this function performs dividation
 , MEMBER FUNCTION dividation
      RETURN NUMBER);
/


CREATE OR REPLACE TYPE BODY xx_data_construct_obj
AS
   MEMBER FUNCTION addition
      RETURN NUMBER
   AS
      l_result                                     NUMBER;
   BEGIN
      l_result := xx_data_struc.num_col + xx_data_struc.num_col2;
      RETURN l_result;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line(   'ADDITION : UNEXP_ERROR : '
                              || SUBSTR(SQLERRM
                                      , 1
                                      , 250));
   END addition;

   MEMBER FUNCTION substraction
      RETURN NUMBER
   AS
      l_result                                     NUMBER;
   BEGIN
      l_result := xx_data_struc.num_col2 - xx_data_struc.num_col;
      RETURN l_result;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line(   'SUBSTRACTION : UNEXP_ERROR : '
                              || SUBSTR(SQLERRM
                                      , 1
                                      , 250));
   END substraction;

   MEMBER FUNCTION multiplication
      RETURN NUMBER
   AS
      l_result                                     NUMBER;
   BEGIN
      l_result := xx_data_struc.num_col * xx_data_struc.num_col2;
      RETURN l_result;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line(   'MULTIPLICATION : UNEXP_ERROR : '
                              || SUBSTR(SQLERRM
                                      , 1
                                      , 250));
   END multiplication;

   MEMBER FUNCTION dividation
      RETURN NUMBER
   AS
      l_result                                     NUMBER;
   BEGIN
     l_result:= ROUND(xx_data_struc.num_col/xx_data_struc.num_col2);
      RETURN l_result;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line(   'DIVIDATION : UNEXP_ERROR : '
                              || SUBSTR(SQLERRM
                                      , 1
                                      , 250));
   END dividation;
END;
/

--------------Execution -------------------------

DECLARE
   data_struc  xx_data_struc_obj := xx_data_struc_obj(25, 60);
   data_construct xx_data_construct_obj 
                        := xx_data_construct_obj( data_struc);
BEGIN
   DBMS_OUTPUT.put_line( 'Addition Output: '||data_construct.addition);
   DBMS_OUTPUT.put_line( 'Substraction Output: '||data_construct.substraction);
   DBMS_OUTPUT.put_line( 'Multiplication Output: '||data_construct.multiplication);
   DBMS_OUTPUT.put_line( 'Dividation Output: '||data_construct.dividation);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line(   'BLOCK : UNEXP_ERROR : '
                           || SUBSTR(SQLERRM
                                   , 1
                                   , 250));
END;

Oracle User-Defined Aggregate Functions Interface (Convert into a Single String)

This section describes the routines that need to be implemented to define a user-defined aggregate function. The routines are implemented as methods in an object type. Then the CREATE FUNCTION statement is used to actually create the aggregate function

ODCIAggregateDelete()	Removes an input value from the current group.
ODCIAggregateInitialize()	Initializes the aggregation context and instance of the implementation object type, and returns it as an OUT parameter.
ODCIAggregateIterate()	Iterates through input rows by processing the input values, updating and then returning the aggregation context.
ODCIAggregateMerge()	Merges two aggregation contexts into a single object instance during either serial or parallel evaluation of the user-defined aggregate.
ODCIAggregateTerminate()	Calculates the result of the aggregate computation and performs all necessary cleanup, such as freeing memory.
ODCIAggregateWrapContext()	Integrates all external pieces of the current aggregation context to make the context self-contained.

---First define the Object type

CREATE OR REPLACE TYPE string_agg_obj_typ AS OBJECT(
   g_string VARCHAR2(32767)

, STATIC FUNCTION odciaggregateinitialize( 
  sctx IN OUT string_agg_obj_typ) RETURN NUMBER
 
, MEMBER FUNCTION odciaggregateiterate(
  self  IN OUT string_agg_obj_typ
, VALUE     IN VARCHAR2)RETURN NUMBER
 
, MEMBER FUNCTION odciaggregateterminate(
  self        IN     string_agg_obj_typ
, returnvalue OUT    VARCHAR2
, flags       IN     NUMBER) RETURN NUMBER
 
, MEMBER FUNCTION odciaggregatemerge(
  self        IN OUT string_agg_obj_typ
, ctx2        IN     string_agg_obj_typ) RETURN NUMBER);
/
SHOW ERRORS

---First define the Object type body 
CREATE OR REPLACE TYPE BODY string_agg_obj_typ
IS
   STATIC FUNCTION odciaggregateinitialize( 
      sctx IN OUT string_agg_obj_typ)
      RETURN NUMBER
   IS
   BEGIN
      sctx    := string_agg_obj_typ( NULL);
      RETURN 0;
   END;

   MEMBER FUNCTION odciaggregateiterate(
  self         IN OUT string_agg_obj_typ
, VALUE        IN     VARCHAR2)
      RETURN NUMBER
   IS
   BEGIN
      self.g_string :=
            self.g_string
         || ','
         || VALUE;
      RETURN 0;
   END;

   MEMBER FUNCTION odciaggregateterminate(
  self          IN  string_agg_obj_typ
, returnvalue  OUT  VARCHAR2
, flags         IN  NUMBER)
      RETURN NUMBER
   IS
   BEGIN
      returnvalue   :=
         RTRIM(LTRIM(self.g_string
                   , ',')
             , ',');
      RETURN 0;
   END;

   MEMBER FUNCTION odciaggregatemerge(
  self          IN OUT string_agg_obj_typ
, ctx2          IN     string_agg_obj_typ)
      RETURN NUMBER
   IS
   BEGIN
      self.g_string  :=
            self.g_string
         || ','
         || ctx2.g_string;
      RETURN 0;
   END;
END;
/

SHOW ERRORS

---- Create a function which will accept the string a input 
---- parameter and return a concatenated String

CREATE OR REPLACE FUNCTION string_agg_fnc( p_input VARCHAR2)
   RETURN VARCHAR2
   PARALLEL_ENABLE
   AGGREGATE USING string_agg_obj_typ;
/

SHOW ERRORS

--Now run the SQL as below  
 SQL>   SELECT organization_id
  2         , string_agg_fnc( segment1)
  3      FROM mtl_system_items_b
  4     WHERE organization_id = 103
  5       AND ROWNUM <= 10
  6  GROUP BY organization_id;

ORGANIZATION_ID     STRING_AGG_FNC(SEGMENT1)
---------------    ----------------------------------------
            103     01R0593,06P5760,06P5759,06P5755,06P5323
SQL>



------------------------------------------------------------------
SQL to Convert ROWS into Concatenated string
-----------------------------------------------------------------
SQL> SELECT organization_id,
       LTRIM(MAX(SYS_CONNECT_BY_PATH(segment1,','))
       KEEP (DENSE_RANK LAST ORDER BY curr),',') AS items
FROM   (SELECT organization_id,
               segment1,
 ROW_NUMBER() OVER (PARTITION BY organization_id ORDER BY segment1) AS curr,
 ROW_NUMBER() OVER (PARTITION BY organization_id ORDER BY segment1) -1 AS prev
 FROM   mtl_system_items_b where organization_id = 103 and rownum <= 5)
GROUP BY organization_id
CONNECT BY prev = PRIOR curr 
      AND organization_id = PRIOR organization_id
START WITH curr = 1

ORGANIZATION_ID    ITEMS
---------------   ------------------------------------------
            103   01R0593,06P5323,06P5755,06P5759,06P5760
           
SQL>

SQL to get DB Server Details

SELECT (SELECT UTL_INADDR.get_host_address FROM DUAL) local_host_address
     , (SELECT UTL_INADDR.get_host_name( (SELECT UTL_INADDR.get_host_address FROM DUAL)) FROM DUAL) host_name
     , (SELECT UTL_INADDR.get_host_address( 'google.com') FROM DUAL) host_address
     , (SELECT UTL_INADDR.get_host_name FROM DUAL) local_host_name
     , (SELECT SYS_CONTEXT('USERENV', 'TERMINAL') FROM DUAL) host_terminal
     , (SELECT SYS_CONTEXT('USERENV', 'HOST') FROM DUAL) host
     , (SELECT SYS_CONTEXT('USERENV', 'IP_ADDRESS') FROM DUAL) ip_Address
     , (SELECT SYS_CONTEXT('USERENV', 'SERVER_HOST') FROM DUAL) server_host
     , (SELECT host_name FROM v$instance) local_host_name_v$instance
     , (SELECT LOWER(USER|| '@' || SYS_CONTEXT('USERENV', 'INSTANCE_NAME')) x FROM DUAL) instance_name
  FROM DUAL

Build Dynamic block for DML operations....

PROCEDURE execute_dml_dynamically(
          p_table_name         IN VARCHAR2
        , p_where_column       IN VARCHAR2
        , p_where_column_val   IN INTEGER
        , p_upd_col_name       IN VARCHAR2
        , p_set_value          IN VARCHAR2)
IS
   l_cursor            PLS_INTEGER := DBMS_SQL.open_cursor;
   l_execute           PLS_INTEGER;
BEGIN
   DBMS_SQL.parse(l_cursor
                ,    'BEGIN update '
                  || p_table_name
                  || ' set '
                  || p_upd_col_name
                  || ' = '
                  || p_set_value
                  || ' where '
                  || p_where_column
                  || ' = '
                  || p_where_column_val
                  || '; END;'
                , DBMS_SQL.native);

   l_execute       := DBMS_SQL.execute( l_cursor);

   IF l_execute > 0
   THEN
      DBMS_OUTPUT.put_line(   'Value of '
                           || p_upd_col_name
                           || ' updated to '
                           || p_set_value);
   ELSE
      DBMS_OUTPUT.put_line(   'Update of '
                           || p_upd_col_name
                           || '  to '
                           || p_set_value
                           || ' failed.');
   END IF;

   DBMS_SQL.close_cursor( l_cursor);
END;
/

How to use Limits for Bulk Collect (PL/SQL Collections)

DECLARE
   CURSOR c_emp_cur
   IS
      SELECT * FROM emp;

   TYPE emp_cur_tbl_typ IS TABLE OF c_emp_cur%ROWTYPE
      INDEX BY PLS_INTEGER;

   emp_cur_tbl                                  emp_cur_tbl_typ;
BEGIN
   OPEN c_emp_cur;

   LOOP
      FETCH c_emp_cur BULK COLLECT INTO emp_cur_tbl LIMIT 100;

      EXIT WHEN c_emp_cur%NOTFOUND;

      FOR indx IN 1 .. emp_cur_tbl.COUNT
      LOOP
         DBMS_OUTPUT.put_line( emp_cur_tbl( indx));
      END LOOP;
   END LOOP;

   CLOSE c_emp_cur;
END ;