This content has been marked as final. Show 16 replies
Where is the definetion of "allServerLocations".
You are might be getting error because i is '0' initially in allServerLocations(i) and this value is outside the limit of your neted/varray.
Legal values for Nested table is 1..2147483647 (the upper limit of PLS_INTEGER) and for varrays 1.. size_limit, where you specify the limit in the declaration (size_limit cannot exceed 2147483647)
For associative arrays with a numeric key, -2147483648..2147483647 and for associative arrays with a string key, the length of the key and number of possible values depends on the VARCHAR2 length limit in the type declaration, and the database character set
The question is what's the point of the collection?
DECLARE CURSOR get_all_locations (type IN VARCHAR2) IS SELECT * FROM group WHERE type = type; TYPE t_all_locations is TABLE OF get_all_locations%ROWTYPE INDEX BY PLS_INTEGER; v_all_locations t_all_locations; BEGIN OPEN get_all_locations; FETCH get_all_locations BULK COLLECT INTO v_all_locations; CLOSE get_all_locations; END;
On another note, your cursor will always return all rows.
The parameter has the same name as the column (type). When used without any prefix, the column will be used in both sides of the where clause.
Oh, and I wouldn't use "type" as name for a column or variable.
CURSOR get_all_locations (type IN VARCHAR2) IS SELECT * FROM group WHERE *type = type*;
Welcome to the forum!
Whenever you post provide your 4 digit Oracle version.
I want to read a number of rows into an array of records
Why? Your procedure doesn't do anything with the data and doesn't have an OUT parameter to return the data so that a caller can do anything with it.
Why don't you explain what you are REALLY wanting to do so we can help you find the best way to do that. Using PL/SQL may not be the way to go.
SQL is almost always faster than PL/SQL so if SQL can be used to get your job done that is probably the better approach. Until you tell us what you are trying to do we won't know.
What I am hoping to do is to create a global collection in this one method that will be used elsewhere (iterating through the collection in another procedure in the PL/SQL package).
I included the very minimum to showcase the problem. What I do with the collection afterwards I can explain if you would like. I simply thought that including the details of what I am doing with the collection would be unnecessary noise to helping solve the problem of why it cannot be created.
We have a customer that has mucked up their database and I need to move a lot of data around. PL/SQL is probably the best approach here, I need to do some procedural things. I could do it in Java or any other language but there is too much overhead given the amount of data that we need to move around.
SQL is great but it does not offer me the procedural capabilities of the 20+ things I have to do.
What am I trying to do: For each server location, I need to query the tree of group locations, and create a device group for each one of those (then 20 other things follow for which this has an implication). I can send you the the analysis document from our architect that would explain more.
Edited by: user12200443 on Nov 19, 2012 10:20 AM
You have a (poor) solution in search of a problem.You do not know what the problem is and you do not know the solution.
PL/SQL will never be faster than plain SQL to access data within Oracle DB.
The only thing you know is a syntax error that I am having with a very small piece of code that is trying to do a very specific thing.
You have tried to reach too far and are blinded by trying to solve a big problem when there is really a small problem to solve.
I (we) have a well thought out solution and algorithm that will solve our problem. I am attempting to implement the solution that has already been analyzed over several weeks now by someone that has 10+ years of knowledge of what data we need to move around and where. The problem is unknown to you. The solution is unknown to you, yet you are making statements about both.
I cannot write procedures, functions and have them called in the order that I need with plain SQL. Also after the implementation is complete in PL/SQL it will be fast enough for what we need to do. The requirement I have is not to have the one time script run as blazingly fast as it possibly can at all costs to providing an implementation. If I were to spend ten weeks writing this in assembly, it would be much faster than SQL but "faster" is not always better nor what is wanted here.
is faster than this plane
and is far superior in many, many ways, but there is a reason you have never flown one and a reason all airports everywhere do not have them coming and going every minute AND a big reason they were grounded to never fly again. I am not sure why I have to have this same discussion every now and then when someone comes to me and says "this would be faster".
Right now in another area I am having an unbelievable battle: Do we write our own messaging system that would be much faster than JMS, AMQP or anything we could simply pull off the shelf - I mean it will be faster but only cost $200K in its first year of writing and maintenance.
Please go home with comments like this and if you are not going to help contribute to the solution do not log on cheez.
"Fast" is not a requirement nor is it a concern with this problem and solution. It is completely beside the point.