Tags: contains, convert, cursorwithout, database, mysql, oracle, output, populated, procedure, queries, sql, stored, table, type
Convert PL/SQL table to cursor..without using SQL type Table.
I have an Oracle stored procedure which contains a pl/sql table. This pl/sql table is populated using the output from a set of queries. I tried to convert this pl/sql table to a cursor but the code doesn't compile. I am not supposed to create an SQL type table(no privileges). In this context how do I convert the pl/sql table to a cursor. Or is there any alternative way of coding such requirement?(populating a data structure with data from a set of queries and returning it to java).
Thanks in advance.
Leave a comment...
- 4 Comments
- try pipelined function
Eugene Roytman#1; Fri, 22 Feb 2008 11:59:00 GMT
- How did you define the table type, was it within your pl/sql code or did you define it globally. If defined globally then you could try this in your query :
select * from TABLE( CAST (var_type as global_type))#2; Fri, 22 Feb 2008 12:00:00 GMT
- > In this context how do I convert the pl/sql table to a cursor.
A cursor is a pointer to a compiled SQL statement.
A SQL statement can only select from objects recognized by SQL.
A PL/SQL table type is an array in a programming language.
> I am not supposed to create an SQL type table(no privileges)
Easiest way would be to get someone with the privileges to create it.
However it appears
Data is retrieved from a set of queries.
Data is put into arrays in code.
Data is needed as a result set in Java.
That by simply removing the second step you can do what you want.#3; Fri, 22 Feb 2008 12:01:00 GMT
- Perhaps there is a collection type you can reuse?
SELECT ct.owner, ct.type_name, ct.elem_type_name, ct.lengthFROM all_coll_types ct , all_types otWHERE ct.coll_type = 'TABLE'AND ot.type_name(+) = ct.elem_type_nameAND ot.owner(+) = ct.elem_type_ownerAND ot.type_name IS NULLORDER BY ct.owner, ct.type_name;
though I agree with 3360 that if you have a valid business need for some database object, "I don't have permission to create one" doesn't really stand up as an immovable design constraint.#4; Fri, 22 Feb 2008 12:02:00 GMT