Unfortunately, the application cannot accept one of those neat Oracle collection types as an input parameter without significant alteration to an aging Powerbuilder code base.When I opened up the stored procedure, I noticed that it dated back prior to the conversion of the company's original Sybase database to our current Oracle database.When used in conjunction, these features form a powerful toolset that conquers GTTs that have been used in this manner.Tags: americansexchatChat with sexy teachersCroatian free web cam masturbation sex chattversity folders not updatingFree videosex chat roomSex chat without e mailHermaphrodite hook up site
The example table function I have created, sf_gather_cost_centers,in Listing 1.3 uses a PL/SQL collection to store the gathered cost center values for the specified employee and return a list of all cost centers that the employee is eligible to use depending on that employee's relationship to her department and division.
Listing 1.4 shows three examples that gather the results of the table function that I have just created via the CAST table pseudo-function for later manipulation.
Listing 1.1 shows an example of how the CAST function can be used inside an anonymous PL/SQL block to read from a PL/SQL collection defined by a declared TYPE.
CAST is used here to sort the resulting rows in reverse alphabetical order.
The result set from these SQL statements could then be returned in a reference cursor generated from within the existing stored procedure with a few modifications as mandated by my original requirements. Briefly, a pipelined table function does not require the CAST pseudo-table function to return a result set.
Listing 1.5 shows a modified version of the same function I created in Listing 1.3, and Listing 1.6 shows the same examples as in Listing 1.4, but with the CAST pseudo-function now removed.The datatype, the database implicitly converts the LOB value to character or raw data and then explicitly casts the resulting value into the target datatype.If the resulting value is larger than the target type, then the database returns an error.First, a GTT is still a table, and I have noticed that developers often forget to COMMIT changes written to a GTT.In addition, the overhead of creating and maintaining the schema of a GTT is often overkill in situations like this one - after all, the most records I'd ever return in this answer set is 15.This article provides a brief technical explanation of CAST and table functions as well as some practical, real-world examples of their use in concert.I was assisting one of my developers with a problem with a rather complex stored procedure that is used extensively by our flagship data entry application to determine all eligible cost centers to which an employee can potentially charge his or her expenditures.I could have created the TYPE as a true object, and built a sort function for the object - but CAST lets me use good old SQL to handle the sorting. This time, I am populating a PL/SQL collection with a set of random numbers.I then use CAST to gather the data from the collection and apply various group functions like SUM(), MIN(), and MAX() on the result set.While there is no substitute for direct experience, reading the manual is not a bad idea, either.So in your example (if modified correctly) you're returning multiple rows and putting them into a collection.