Originally posted by Mark Spritzler:
Hve you checked with your DBA. Meaning have you analyzed the tables, analyzed the query, gotten an explain plan. Checked indexes already?
That is one way of speeding up your query, if you are forced to stick witht he thin driver.
Other things that can speed up queries, but not neccessariy for your case. Do you have a connection pool with connection already to handle your query. Creating the connection is always added time.![]()
Hope that helps some.![]()
Mark
Originally posted by Mark Spritzler:
If your clients will have the Oracle client on their machines then I would suggest going for the oci Driver. If they don't I suggest the thin driver.
If you have an OTN Oracle Tech Net Login you can go here.
Mark
Originally posted by Mark Spritzler:
OK, for some reason I feel I am still off here.
It looks like the procedure is trying to first see if the record already exists. If it does it returns the ID, if not it inserts and returns a new ID.
FOr new ID, I would use the nextVal rather than currval.
Second, I don't think you need a Cursor for this type of function. And also that way my solution of the Exception handling would work, and also give less code.
Now try this
I feel much better about that one.
Here are a couple of sites I'd suggest you add to your favorites
a great link is Ask Tom.
Ask Tom is a guy at Oracle that knows everything. You ask him a question and he'll respond,
usually within the day. I have never had him fail me yet.![]()
For those that like a good Oracle FAQ There is no better than
Ari Kaplan's Oracle Tips.
Hope that helps
Mark
Originally posted by Michael Pearson:
Why do you want to move the data from the ResultSet to an Array?
You already have a while loop that prints the ResultSet, so it would not take much to assign the ResultSet values to an Array.
Originally posted by Beksy Kurian:
Looks like you haven't created the object type rectype correctly! Are you doing this under system??!!!
Beksy
Originally posted by Muhammad Farooq:
Issue command
sql> show errors;
and then see what is the problem.
HIH,
Originally posted by Muhammad Farooq:
Try this,may be this work out. Sorry as I don't have all the data on which you are working
CREATE OR REPLACE Function show(iorec in out rectype)
return number
IS
v_Table NumTab := iorec.col1;
v_Count BINARY_INTEGER :=1;
BEGIN
LOOP
IF v_Table.EXISTS(v_Count) THEN
DBMS_OUTPUT.PUT_LINE('v_Table(' || '); ' || v_Table(v_Count));
v_Count := v_Count + 1;
ELSE
EXIT;
END IF;
END LOOP;
END;
/
HIH
Originally posted by Beksy Kurian:
let me test the code and I will get back to you soon.
Beksy