Oracle Collection Objects
-
Whenever we create either permanent table or temporary tables it requires modification of the database schema. Is the same applicable for Collection objects? ie when we create collection objects does it modify the database schema/metatdata?
-
Whenever we create either permanent table or temporary tables it requires modification of the database schema. Is the same applicable for Collection objects? ie when we create collection objects does it modify the database schema/metatdata?
This depends on the situation. For example if a collection is used in a procedure, information about the collection is stored in the system tables. Could you explain the situation a bit more, what kind of collection are you thinking of and what usage?
The need to optimize rises from a bad design.My articles[^]
-
This depends on the situation. For example if a collection is used in a procedure, information about the collection is stored in the system tables. Could you explain the situation a bit more, what kind of collection are you thinking of and what usage?
The need to optimize rises from a bad design.My articles[^]
Hi, I am using a SSRS report and the report is generated by executing a stored procedure. The procedure gets 4 input values a,b,c,d where values c and d is a string containing multiple values. I am executing a complex SQL statement. For this i am using nested loop: A as INPUT B as INPUT C as INPUT (String input seperated by ,) D as input (String input seperated by ,) Cursor Cur as OUTPUT Loop for c Loop for d . . execute the above SQL Statement(select query) . . END loop END Loop problem: Now the results of the SQL statement has to be saved temporarily somewhere I am not allowed to use permanent/temporary tables as they require definition at schema level and modificaton of schema is not allowed. I need to save the value in the cursor at each runs, but in that case the previous results get overwritten. So i wanted to know collection objects and wanted to know if that too requires definition at schema level.
-
Hi, I am using a SSRS report and the report is generated by executing a stored procedure. The procedure gets 4 input values a,b,c,d where values c and d is a string containing multiple values. I am executing a complex SQL statement. For this i am using nested loop: A as INPUT B as INPUT C as INPUT (String input seperated by ,) D as input (String input seperated by ,) Cursor Cur as OUTPUT Loop for c Loop for d . . execute the above SQL Statement(select query) . . END loop END Loop problem: Now the results of the SQL statement has to be saved temporarily somewhere I am not allowed to use permanent/temporary tables as they require definition at schema level and modificaton of schema is not allowed. I need to save the value in the cursor at each runs, but in that case the previous results get overwritten. So i wanted to know collection objects and wanted to know if that too requires definition at schema level.
Ok, well basically when you create a procedure (of any kind) and store it in the database, you modify the schema. So in this sense the requirement of leaving the schema intact doesn't make sense... Anyhow, you can use collections and for example if you're using a package, you can define the collection in your procedure or at package level. When you loop through the cursor you can store the results in the collection and later on modify them or add more results etc. If you're using package level variables, you can store the results in them even between procedure calls.
The need to optimize rises from a bad design.My articles[^]
-
Ok, well basically when you create a procedure (of any kind) and store it in the database, you modify the schema. So in this sense the requirement of leaving the schema intact doesn't make sense... Anyhow, you can use collections and for example if you're using a package, you can define the collection in your procedure or at package level. When you loop through the cursor you can store the results in the collection and later on modify them or add more results etc. If you're using package level variables, you can store the results in them even between procedure calls.
The need to optimize rises from a bad design.My articles[^]
when i store the results in the collection while i run the loop. can i save the results into the cursor.
-
when i store the results in the collection while i run the loop. can i save the results into the cursor.
Not directly. If you're using output cursors, you pass the cursor to the caller at the end but the cursor is based on a query from an object. I know that you can create a cursor based on a nested table but I have never tried that with associative arrays or varrays.
The need to optimize rises from a bad design.My articles[^]