var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
oracle 10g - sqlplus freezes on executing queries - functions
machine windowsxp - ram 256 mb
There are just too many possible causes to this; you should provide more information about the problem because it is almost impossible to give any suggestion.
What kind of functions? Oracle built-in ones? Do other queries (that do not require functions) work properly? Did you test the function on the paper? Does it end up in a neverending loop? Are tables large? Did you create indexes on columns that consist the WHERE clause? Are both tables and indexes analyzed? etc. etc.
In other words (you, talking to a mechanic): "I've got a car and it won't work. Why?" - A mechanic, talking to you: "Because".
this is the oracle script - sql plus was working fine until today morning
This is the script which is not working - it takes too long to execute
The script worked fine yesterday
I have chedked it on other machines - it works fine
name1 VARCHAR2(50 BYTE);
name2 VARCHAR2(50 BYTE);
smartcardno1 VARCHAR2(50 BYTE);
designation1 VARCHAR2(50 BYTE);
id_mark1 VARCHAR2(50 BYTE);
allergy1 VARCHAR2(50 BYTE);
blood_group1 VARCHAR2(50 BYTE);
color1 VARCHAR2(50 BYTE);
imagename1 VARCHAR2(50 BYTE);
while loopcount<=100 loop
if (loopcount =92) then
insert into tabidentitycard (passno, name,smartcardno, designation,id_mark, allergy, blood_group,color,imagename, srno)
values (loopcount,name1,loopcount,designation1,'something ','nothing','O +ve','red',null,loopcount);
even if i try to run a query like select * from tabidentitycard;
it takes around 30 seconds to bring up the result. well i guess now im telling the mechanic all that he needs :-)
actually there was a point in ur telling to check wheter it was going in an infinite loop
- when i reduced the counter from 100 to 70 the procedure finally executed
- is there any way i can increase the buffer size in sql plus - i tried options like going to options ->environment and increased the maxdata to 10000
- i also increased screen buffer width and screen buffer length - both of them to 10000
- when i try to use a query -> delete from tabidentitycard; - the screen got frozen and showed the tile as - Not Responding - but after five minutes it deleted the entries in the table
- would increasing the virtual memory size increase
- i cannot increase the RAM chip size
Yes, not it makes a difference. Posting the code helps a lot.
However, in my opinion, you didn't tell the whole truth. This script couldn't work fine yesterday or ever before because it is an endless loop. Your shortened script looks like this:
loopcount := 65;
WHILE loopcount <= 100
IF loopcount = 92
loopcount := 65;
loopcount := loopcount + 1;
NEVER exit the WHILE loop. OK, will exit if you unplug the power cord. Although you add 1 to "loopcount", whenever it reaches 92 you reset it back to 65. And this goes on and on.
P.S. Now I noticed your next post.
But of course that it ends when you change 100 to 70 ... it would end whenever WHILE is limited to a number < 92.
Output buffer size can be set using SET SERVEROUTPUT ON SIZE <up to 1.000.000> in SQL*Plus session.
DELETE from a table takes a long time when there are many records in it. In this (your) case, I'd suggest you to TRUNCATE TABLE instead of deleting it.