Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2006
    Posts
    3

    Unanswered:

    oracle 10g - sqlplus freezes on executing queries - functions

    machine windowsxp - ram 256 mb

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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".

  3. #3
    Join Date
    Sep 2006
    Posts
    3

    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


    declare
    passno1 number(10);

    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);

    srno1 NUMBER(10);

    loopcount NUMBER(10);

    ascii_rep NUMBER(10);

    begin

    loopcount:=65;

    while loopcount<=100 loop

    passno1:=1;

    name1:='ram';

    srno1:=1;

    smartcardno1:=1;

    designation1:='Quality Analyst';

    smartcardno1:=smartcardno1+1;

    name1:=chr(loopcount)||name1;


    srno1:=srno1+1;

    if (loopcount =92) then


    loopcount:=65;

    end if;

    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);


    loopcount:=loopcount+1;

    passno1:=passno1+1;

    smartcardno1:=smartcardno1+1;

    end loop;

    end;

    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 :-)

  4. #4
    Join Date
    Sep 2006
    Posts
    3
    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

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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:
    Code:
    DECLARE
      loopcount NUMBER;
    BEGIN
      loopcount := 65;
      
      WHILE loopcount <= 100
      LOOP
        IF loopcount = 92
        THEN
           loopcount := 65;
        END IF;
    	
        loopcount := loopcount + 1;
      END LOOP;
    END;
    /
    It will 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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •