Monthly Archives: October 2015

சிந்தனைகள்

Posted on by

சிந்தனை 1

‘செல்லாத காசுக்குள்ளும் செப்பு இருக்கு’னு சொல்வாங்க. என் வாழ்க்கையில் நான் கத்துக்கிட்ட ஒரே விஷயம் இதுதாங்க. அடுத்த மனுஷனை மதிக்கணும். அவரையும் நம்மோட ஒருத்தரா நேசிக்கணும். இங்க பெரியவங்க, சின்னவங்கனு யாரும் இல்லை. எல்லாப் பெரியவங்களுக்கும் ஒரு ‘நேத்து’ இருக்கும். அதுபோல சின்னவங்களுக்கும் ஒரு ‘நாளை’ இருக்கும். எனக்கு டீ தர்றான் ஒரு பையன். அது அவர் வேலை. அவர் எங்க வீட்டுக்கு வந்தா நான்தான் அவருக்கு டீ எடுத்துட்டு வந்து தருவேன். தரணும்.

என்னைப் பொறுத்தவரைக்கும் மனுஷங்க மகான்களாவறது பெரிய விஷயம் இல்லை; மனுஷங்களாவே இருக்க முடிஞ்சா… அதுதாங்க சந்தோஷம்!”

-தேவா

சிந்தனை 2

வாழ்வதற்கான செலவு மிகவும் குறைவு…
அடுத்தவன் போல் வாழ்வதற்குத் தான் செலவு அதிகம்…!!

PIPE ROW Oracle Function Example

Posted on by

Table Function return a result set that mimics what we would normally expect from a traditional SQL SELECT statement

Table functions are a new feature in Oracle9i that allow you to define a set of PL/SQL statements that will, when queried, behave just as a regular query to table would. The added benefit to having a table function is that you can perform transformations to the data in question before it is returned in the result set.

CREATE OBJECT
Create our own object type called PERSON_DETAILS.Then we create a table of PERSON_DETAILS called PERSON_DETAILS_TABLE.

CREATE TYPE PERSON_DETAILS AS OBJECT
       (USER_NAME     VARCHAR2(50),
        ADDRESS       VARCHAR2(50),
        LOCATION      VARCHAR2(50));
/
CREATE TYPE PERSON_DETAILS_TABLE AS TABLE OF PERSON_DETAILS;
/

PIPELINED Clause

Within the CREATE FUNCTION clause, there is a new option called PIPELINED. This option tells Oracle to return the results of the function as they are processed, and not wait for a complete execution or completion of the result set. This pipelining of the result set to one row at a time has the immediate advantage of not requiring excessive memory or disk staging resources.
PIPE ROW(out_rec)

The PIPE ROW statement is the interface or mechanism to send a piped row through the PIPELINED option through to the caller of the function.

Working with a simple pipelined function requires 2 things

  • collection type
  • pipelined function
 CREATE OR REPLACE TYPE number_ntt AS TABLE OF NUMBER;

CREATE FUNCTION row_generator(rows_in IN P_INTEGER) RETURN number_ntt
  PIPELINED IS
BEGIN
  FOR i IN 1 .. rows_in LOOP
    PIPE ROW(i);
  END LOOP;
  RETURN;
END;
/

The CSV_TABLE is a collection which has rows of comma separated value.

CREATE OR REPLACE TYPE "CSV_TABLE" as table of varchar2(32767)
CSV_TABLE

FUNCTION CSV_TO_TABLE(p_delimted_string VARCHAR2,
                         p_delimter        VARCHAR2 := ',')
  RETURN CSV_TABLE
  PIPELINED IS
  indexCount PLS_INTEGER;
  csvString  VARCHAR2(32767) := p_delimted_string;
BEGIN
  LOOP
    indexCount := instr(csvString, p_delimter);
  
    IF indexCount > 0 THEN
      PIPE ROW(substr(csvString, 1, indexCount - 1));
      csvString := substr(csvString, indexCount + length(p_delimter));
    ELSE
      PIPE ROW(csvString);
      EXIT;
    END IF;
  
  END LOOP;
  RETURN;
END CSV_TO_TABLE;

Input

A,B,C,D

Output

A
B
C
D

The above output is a collection.

 SELECT PACKAGE_NAME.CSV_TO_TABLE('A,B,C,D') FROM DUAL;

Using NVL in Select Query

Posted on by

When the Column value is NULL it would be replaced by ‘~’ in output

  SELECT NVL(FAX_NO, '~') FROM DUAL;
  SELECT NVL(NULL, '~') FROM DUAL;

The Below query can be used in scenarios where 3 drop downs are used and Location is mandatory and loaded first based on which Area and Pincode should be populated and selection can be made based on that later.

  SELECT Name, Age, PhoneNo
    FROM Person 
   WHERE Location =  p_location and 
         Area =  NVL(p_area, Area) and 
         PinCode =  NVL(p_pin_code, PinCode);

Having more than one input in where clause
i.e

 SELECT * 
  FROM Person 
 WHERE Location IN('North Chennai', 'South Chennai');

When the same query is used in search screen it may have Three possible Values

  • NULL
  • Single Value
  • Multiple Value

The above query works for Single and Multiple value but does not work for NULL.The Above query works for NULL and Single Value but not for Multiple Value.

The below is a simple query which works when PERSON_ID is NULL, Single Value, Multiple Value(CSV).

 SELECT PERSON_ID
  FROM PERSONS P
 WHERE ((CASE
         WHEN 'P101' IS NULL THEN
          NULL
         ELSE
          'P101'
       END) IS NULL OR P.WATERFALL_ID IN ('P101'));

For Multiple Value we need to do slight modification.We need to convert the CSV values into table and give it as input.

 SELECT DISTINCT PERSON_ID
   FROM tblPerson 
  WHERE nvl(PERSON_ID, '~') IN (SELECT column_value
                                  FROM TABLE(PACKAGE_NAME.CSV_TO_TABLE(P_CSV_PERSON_ID))
                                UNION ALL
                                SELECT '~'
                                  FROM dual);

The select query in the where clause will take the value of P_CSV_PERSON_ID (Single or Multiple value) else it will take ~ in case it is NULL

The Other workaround to this is as below

SELECT PERSON_ID
    FROM tblPerson 
   WHERE (((CASE
           WHEN P_CSV_PERSON_ID IS NULL THEN
            NULL
           ELSE
            P_CSV_PERSON_ID
         END)) IS NULL
      OR PERSON_ID IN
         (SELECT *
            FROM TABLE(PACKAGE_NAME.CSV_TO_TABLE(P_CSV_PERSON_ID))));

where 

 P_CSV_PERSON_ID= 'P101,P102'

For more details on CSV_TO_TABLE refer Link