FOR tbl_det IN (SELECT ts.column_1, ts.column_2 FROM table_name ts WHERE ts.column_3 = p_param_1) LOOP v_csv_col1 := v_csv_col1 || ',' || tbl_det.column_1; END LOOP;
Category Archives: Packages & Procedures
PIPE ROW Oracle Function Example
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;
Simple Oracle Function which Returns Boolean
Simple Oracle Function which Returns Yes or No
CREATE OR REPLACE FUNCTION isStudentInArmy(p_location IN VARCHAR(255), p_age IN LONG) RETURN CHAR IS isArmyMen CHAR(1); BEGIN SELECT CASE WHEN BodyType == 'Well Built and Strong' THEN 'Y' ELSE 'N' END INTO isArmyMen FROM tblMens WHERE Location = p_location AND Age = p_age; RETURN isArmyMen; END isStudentInArmy;
Package Creation in oracle
Package creation in Oracle is a TWO step Process.
1.Declaration of Package
2.Definition of Package
Declaration of Package
CREATE OR REPLACE PACKAGE TEST IS PROCEDURE JUNITPROC(C OUT SYS_REFCURSOR); END TEST;
Definition of Package
CREATE OR REPLACE PACKAGE BODY TEST IS PROCEDURE JUNITPROC(C OUT SYS_REFCURSOR) IS BEGIN OPEN C FOR SELECT 78985450.1245487986418648 DECIMAL_VAL FROM DUAL; END JUNITPROC; END TEST;