Results 1 to 5 of 5

Thread: PL/SQL Error

  1. #1
    Join Date
    Mar 2010
    Posts
    4

    Post Unanswered: PL/SQL Error

    Hi,
    I m using PL/SQL , here is the code

    create or replace procedure get3Itemset(size1 in number) is

    id1 freq_table.itemsets%type;
    id2 freq_table.itemsets%type;

    cursor Itemset3 is
    select f1.itemsets i1,f2.itemsets i2
    from freq_table f1,freq_table f2
    where f1.itemsets != f2.itemsets
    AND f1.itemsets < f2.itemsets
    AND f1.itemsize = size1 AND
    f2.itemsize = size1;

    set3_rec Itemset3%rowType;
    pos number;

    findPos number;
    tempcount number;
    findPos2 number;


    tempItem freq_table.itemsets%type;

    begin
    findPos := 0;
    findPos2 := 0;
    pos := 1;
    tempcount := 0;
    for set3_rec in Itemset3 loop
    id1 := set3_rec.i1;
    id2 := set3_rec.i2;
    dbms_output.put_line(id1 || ' ' || id2);
    while(pos <= size1) loop
    findPos := INSTR(id1,SUBSTR(id2,pos,1));
    if(findPos > 0) then
    EXIT;
    else
    pos := pos + 1;
    end if;
    end loop;
    if(findPos > 0 ) then
    dbms_output.put_line('Match found ');
    if(findPos = 1) then
    tempItem := id1 || SUBSTR(id2,2,1);
    elsif(findPos = 2) then
    findPos2 := INSTR(id2,SUBSTR(id1,findPos,1));
    if(findPos2 = 1) then
    tempItem = id1 || SUBSTR(id2,2,1);
    else
    if(findPos = 2) then
    tempItem := SUBSTR(id1,1,1) || id2 ;
    end if;
    end if;
    end if;


    select count(*)
    into tempcount
    from freq_table
    where itemsets = some
    (select itemsets
    from freq_table
    where itemsets = tempItem);
    if(tempcount = 0) then
    insert into freq_table values(size1+1, tempItem);
    dbms_output.put_line(tempItem || 'inserted');
    end if;
    end if;
    pos := 1;
    findPos := 0;
    end loop;
    end;


    ERROR at line 72: PLS-00103: Encountered the symbol "END" 0.03 seconds

    Please help me to analyse the error.

    Thanks in advances

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    CREATE OR REPLACE PROCEDURE Get3itemset
         (size1  IN NUMBER)
    IS
      id1        freq_table.itemsets%TYPE;
      id2        freq_table.itemsets%TYPE;
      CURSOR itemset3 IS
        SELECT f1.itemsets i1,
               f2.itemsets i2
        FROM   freq_table f1,
               freq_table f2
        WHERE  f1.itemsets != f2.itemsets
               AND f1.itemsets < f2.itemsets
               AND f1.itemsize = size1
               AND f2.itemsize = size1;
      set3_rec   itemset3%ROWTYPE;
      pos        NUMBER;
      findpos    NUMBER;
      tempcount  NUMBER;
      findpos2   NUMBER;
      tempitem   freq_table.itemsets%TYPE;
    BEGIN
      findpos := 0;
      
      findpos2 := 0;
      
      pos := 1;
      
      tempcount := 0;
      
      FOR set3_rec IN itemset3 LOOP
        id1 := set3_rec.i1;
        
        id2 := set3_rec.i2;
        
        dbms_output.Put_line(id1
                             ||' '
                             ||id2);
        
        WHILE (pos <= size1) LOOP
          findpos := Instr(id1,Substr(id2,pos,1));
          
          IF (findpos > 0) THEN
            EXIT;
          ELSE
            pos := pos + 1;
          END IF;
        END LOOP;
        
        IF (findpos > 0) THEN
          dbms_output.Put_line('Match found ');
          
          IF (findpos = 1) THEN
            tempitem := id1
                        ||Substr(id2,2,1);
          ELSIF (findpos = 2) THEN
            findpos2 := Instr(id2,Substr(id1,findpos,1));
            
            IF (findpos2 = 1) THEN
              tempitem := id1
                          ||Substr(id2,2,1);
            ELSE
              tempitem := Substr(id1,1,1)
                          ||id2;
            END IF;
          END IF;
          
          SELECT Count(* )
          INTO   tempcount
          FROM   freq_table
          WHERE  itemsets IN (SELECT itemsets
                              FROM   freq_table
                              WHERE  itemsets = tempitem);
          
          IF (tempcount = 0) THEN
            INSERT INTO freq_table
            VALUES     (size1 + 1,
                        tempitem);
            
            dbms_output.Put_line(tempitem
                                 ||'inserted');
          END IF;
        END IF;
        
        pos := 1;
        
        findpos := 0;
      END LOOP;
    END;
    formatting helps
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2010
    Posts
    4

    PL/SQL Error

    Thanks for formatting...

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    plus I fixed multiple syntax errors.

    It complies now, but no guarentee it does what is desired.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Mar 2010
    Posts
    4
    Thanks a lot anacedent.Its compiling without error..I m trying to use this procedure as a part of Data Mining

Posting Permissions

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