Table Function return a result set that mimics what we would normally expect from a traditional SQL SELECT statement<\/p>\n
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. <\/p>\n
CREATE OBJECT<\/strong> PIPELINED Clause<\/strong><\/p>\n 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. 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.<\/p>\n Working with a simple pipelined function requires 2 things<\/p>\n The CSV_TABLE<\/strong> is a collection which has rows of comma separated value.<\/p>\n Input<\/strong><\/p>\n Output<\/strong><\/p>\n The above output is a collection.<\/p>\n 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… Continue reading
\n Create our own object type called PERSON_DETAILS.Then we create a table of PERSON_DETAILS called PERSON_DETAILS_TABLE.<\/p>\n\r\nCREATE TYPE PERSON_DETAILS AS OBJECT\r\n (USER_NAME VARCHAR2(50),\r\n ADDRESS VARCHAR2(50),\r\n LOCATION VARCHAR2(50));\r\n\/\r\nCREATE TYPE PERSON_DETAILS_TABLE AS TABLE OF PERSON_DETAILS;\r\n\/\r\n<\/pre>\n
\nPIPE ROW(out_rec)<\/p>\n\n
\r\n CREATE OR REPLACE TYPE number_ntt AS TABLE OF NUMBER;\r\n<\/pre>\n
\r\nCREATE FUNCTION row_generator(rows_in IN P_INTEGER) RETURN number_ntt\r\n PIPELINED IS\r\nBEGIN\r\n FOR i IN 1 .. rows_in LOOP\r\n PIPE ROW(i);\r\n END LOOP;\r\n RETURN;\r\nEND;\r\n\/\r\n<\/pre>\n
\r\nCREATE OR REPLACE TYPE "CSV_TABLE" as table of varchar2(32767)\r\nCSV_TABLE\r\n<\/pre>\n
\r\nFUNCTION CSV_TO_TABLE(p_delimted_string VARCHAR2,\r\n p_delimter VARCHAR2 := ',')\r\n RETURN CSV_TABLE\r\n PIPELINED IS\r\n indexCount PLS_INTEGER;\r\n csvString VARCHAR2(32767) := p_delimted_string;\r\nBEGIN\r\n LOOP\r\n indexCount := instr(csvString, p_delimter);\r\n \r\n IF indexCount > 0 THEN\r\n PIPE ROW(substr(csvString, 1, indexCount - 1));\r\n csvString := substr(csvString, indexCount + length(p_delimter));\r\n ELSE\r\n PIPE ROW(csvString);\r\n EXIT;\r\n END IF;\r\n \r\n END LOOP;\r\n RETURN;\r\nEND CSV_TO_TABLE;\r\n<\/pre>\n
\r\nA,B,C,D\r\n<\/pre>\n
\r\nA\r\nB\r\nC\r\nD\r\n<\/pre>\n
\r\n SELECT PACKAGE_NAME.CSV_TO_TABLE('A,B,C,D') FROM DUAL;\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"