1600 PMP mock questions 1400 CAPM mock questions 800 SCJP 6 mock questions 600 OCAJP 7 mock questions 590 OCPJP 7 mock questions 556 SCWCD 5 mock questions 500 OCEJWCD 6 mock questions pdfDownload (java,struts, hibernet etc)

ORACLE Tutorials

Returns a specific field from a record that is comma separated

CREATE OR REPLACE FUNCTION get_field (
p_line IN VARCHAR2,
p_delimiter IN VARCHAR2,
p_field_no IN NUMBER,
p_encapsulator IN VARCHAR2 DEFAULT '"'
)
RETURN VARCHAR2
IS
--==============================================================================
-- NAME: Get_Field
--
-- PURPOSE: Get value of field n from a delimited line of ascii data
--
-- DESCRIPTION
-- This utility function will returrn the value of a field from
-- a delimited line of ascii text
--
-- PARAMETERS
-- p_line IN VARCHAR2 - line of data
-- p_delimiter IN VARCHAR2 - Delimiter character
-- p_field_no IN NUMBER - Field occurance to be
-- returned
-- p_encapsulator IN VARCHAR2 - Character used to encapsulate text
-- Defaults to double quotes "
-- RETURNS
-- VARCHAR2 - Value of field
--
-- Local variables
l_current NUMBER := 0;
l_count NUMBER := 1;
l_start NUMBER := 0;
l_end NUMBER := 0;
l_skip BOOLEAN := FALSE;
BEGIN
-- Determine start position
IF p_field_no = 1
THEN
l_start := 0;
ELSE
l_current := 1;

LOOP
IF SUBSTR (p_line, l_current, 1) = p_delimiter AND NOT l_skip
THEN
l_count := l_count + 1;

IF p_field_no = l_count
THEN
IF SUBSTR (p_line, l_current + 1, 1) = p_encapsulator
THEN
l_start := l_current + 1;
ELSE
l_start := l_current;
END IF;

EXIT;
END IF;
ELSIF SUBSTR (p_line, l_current, 1) = p_encapsulator AND NOT l_skip
THEN
l_skip := TRUE;
ELSIF SUBSTR (p_line, l_current, 1) = p_encapsulator AND l_skip
THEN
l_skip := FALSE;
END IF;

l_current := l_current + 1;

IF l_current > LENGTH (p_line)
THEN
RAISE NO_DATA_FOUND;
END IF;
END LOOP;
END IF;

IF SUBSTR (p_line, l_current + 1, 1) = p_encapsulator
THEN
l_start := l_current + 1;
END IF;

l_current := l_current + 1;
l_skip := FALSE;

-- Determine end position
LOOP
IF SUBSTR (p_line, l_current, 1) = p_delimiter AND NOT l_skip
THEN
EXIT;
ELSIF SUBSTR (p_line, l_current, 1) = p_encapsulator AND NOT l_skip
THEN
l_skip := TRUE;
ELSIF SUBSTR (p_line, l_current, 1) = p_encapsulator AND l_skip
THEN
l_skip := FALSE;
END IF;

IF l_current = LENGTH (p_line)
THEN
l_current := l_current + 1;
EXIT;
END IF;

l_current := l_current + 1;
END LOOP;

IF SUBSTR (p_line, l_current - 1, 1) = p_encapsulator
THEN
l_end := l_current - 1;
ELSE
l_end := l_current;
END IF;

-- Extract the field data
IF (l_end - l_start) = 1
THEN
RETURN NULL;
ELSE
RETURN SUBSTR (p_line, (l_start + 1), ((l_end - l_start) - 1));
END IF;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END get_field;

Reviews and Comments


PMP, CAPM, PMI is a registered certification mark of the Project Management Institute, Inc

Copyright © www.techfaq360.com 2016


About US | Contact US | Privacy Policy | Terms and Conditions  | Website disclaimer  | Cancellation and Refund Policy  | Shipping & Delivery Policy