Oracle Magazine, September/October 2018

Database Developer PL SQL Note that you can use all the usual cursor attributes and operations on cursor variables ORACLE MAGAZINE SEPTEMBER OCTOBER 2018 56 FETCH FOUND CLOSE and so on As you will see later the way you use this REF CURSOR type will be a bit different for streaming table functions in the SQL context DEFINE THE TABLE FUNCTION The main distinction between streaming table functions and normal table functions such as those addressed in the previous article is that at least one parameter to that streaming table function is a cursor variable The table function could have more than one cursor variable input and other parameters of other types such as a string or date In this article I will stick with the minimum a single cursor variable parameter Generally the flow within a streaming table function is 1 Fetch a row from the cursor variable 2 Apply the transformation to each row 3 Put the transformed data into the collection 4 Return the collection when done Now lets see how this pattern unfolds in my doubled function one stocks row doubled to two ticker rows and the line descriptions that follow CREATE OR REPLACE FUNCTION doubled rows_ in stock_ mgr stocks_ rc RETURN tickers_ nt AUTHID DEFINER IS TYPE stocks_ aat IS TABLE OF stocks ROWTYPE INDEX BY PLS_ INTEGER 1 2 3 4 5

View the Covers and the Table of Contents pages from every issue of this publication, all gathered together for easy browsing. Just flip pages and zoom as you normally do to see each issue's Cover and Table of Contents, then follow links directly to interesting content.