1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

SELECT INTO fails when seletion returns NULL

Discussion in 'SQL PL/SQL' started by Al_Alter, Apr 12, 2011.

  1. Al_Alter

    Al_Alter Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    Hi All!
    I am using SELECT INTO clause in procedures to set value of variable. All is good while SELECT returns value. But when there is nothing to return according to given WHERE condition SELECT INTO fails. Error message is fired. I tried to use “SELECT Count() INTO” to check existence of record but it fails too.
    What to do?
    Please help!
    Alex
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Could you show the code to help us know what exactly you are doing. count() usually works under such scenarios. Post your code to get a better response.
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    If you're using count() as you posted it won't work as count needs an operand:

    select count(*) into my_ct from mytable where mycol = 'Myvalue';

    That should return 0 if no records meet the criteria:

    Code (SQL):
    SQL> CREATE TABLE mytable(
      2          mycol varchar2(40));
    T
    able created.
     
    SQL>
    SQL> BEGIN
      2          FOR i IN 1..1000 loop
      3                  INSERT INTO mytable VALUES ('Myvalue'||i);
      4          END loop;
      5
      6          commit;
      7  END;
      8  /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> SET serveroutput ON SIZE 1000000
    SQL>
    SQL> DECLARE
      2          my_ct NUMBER;
      3
      4  BEGIN
      5
      6          SELECT COUNT(*) INTO my_ct FROM mytable WHERE mycol = 'Myvalue';
      7
      8          IF my_ct = 0 THEN
      9                  dbms_output.put_line('No records found');
     10          ELSE
     11                  dbms_output.put_line('Data exists');
     12
     13          END IF;
     14
     15  END;
     16  /
    No records found
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    As you see, no error returned when count is used properly.
     
  4. Al_Alter

    Al_Alter Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    Thanks to all. You are quite right David about “Count()”. I am using field name Count(xxx). The code of procedure:
    CREATE OR REPLACE PROCEDURE DBO_HISTSQL.GetStrOfWids
    (
    SRangeII IN NUMBER DEFAULT NULL,
    SectsWidthsStringer OUT VARCHAR2,
    SummWdth OUT NUMBER
    ) AS
    groupWidth NUMBER(3,0);
    Counter NUMBER(3,0);
    CurWidth NUMBER(3,0);
    CurNmbr NUMBER(3,0);
    CurStr VARCHAR2(18);
    ccc VARCHAR2(18);
    BEGIN
    CurNmbr := 0;
    SummWdth := 0 ;
    CurStr := '' ;
    SELECT Count(SectNmbr) INTO Counter FROM Sections WHERE SrangeID = SRangeII AND SectNmbr = CurNmbr;
    If Counter > 0 Then
    BEGIN
    SELECT SectWdth INTO CurWidth FROM Sections WHERE SrangeID = SRangeII AND SectNmbr = CurNmbr;
    ccc := CAST(CurWidth AS CHAR) ;
    WHILE Counter > 0
    LOOP
    BEGIN
    CurStr := RTRIM(CurStr) || CAST(CurWidth AS CHAR) ;
    SummWdth := SummWdth + CurWidth ;
    CurNmbr := CurNmbr +1;
    SELECT Count(*) INTO Counter FROM Sections WHERE SrangeID = SRangeII AND SectNmbr = CurNmbr;
    If Counter > 0 Then
    SELECT SectWdth INTO CurWidth FROM Sections WHERE SrangeID = SRangeII AND SectNmbr = CurNmbr;
    End If;
    END;
    END LOOP;
    SectsWidthsStringer := SUBSTR((RTRIM(CurStr) || '000000000000000000'), 0, 18) ;
    END;
    ELSE
    SectsWidthsStringer := '000000000000000000' ;
    END IF;

    UPDATE NomnklVars
    SET RealWdth = SummWdth,
    SectsWidthsString = RTRIM(SectsWidthsStringer)
    WHERE SrangeID = SRangeII;
    END GetStrOfWids;

    There is another complication. When I am debugging proc GetStrOfWids directly - at list “SELECT Count(SectNmbr) INTO…”causes no error. But when I am debugging “After Insert trigger of Sections” which calls the proc GetStrOfWids even “SELECT Count(SectNmbr) INTO…” fails
    After Insert trigger does nothing to Sections table.

    The proc GetStrOfWids with all database was migrated from SQL Server. There it worked fine. But now I see it would be better to make fields RealWdth and SectsWidthsString of table NomnklVars calculated fields. RealWdth with expression “SELECT sum(SectWdth) FROM Sections WHERE SrangeID = [NomnklVars.SrangeID]” and SectsWidthsString by using function or proc. Is it possible?
    Would you kindly correct my “SELECT…” expression for calculated field with accent on syntax not on the sence.
    Best regards, Alex
     
  5. Al_Alter

    Al_Alter Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    Additionally. Now proc is working well. But when it is run under “After Insert trigger of Sections” the “SELECT Count(SectNmbr) INTO…” expression is regarded by Oracle as attempt to change table that fired trigger.
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Yes, Oracle considers that a possible table modification and throws the 'mutating table' error. A trigger in Oracle cannot select from the table the trigger is on; you can put such logic into a function and call that function from the trigger:

    Code (SQL):
     
    --  function
    CREATE OR REPLACE FUNCTION sel_count
    RETURN NUMBER AS
         RESULT NUMBER;
    BEGIN
         SELECT COUNT(sectnmbr) INTO RESULT FROM ... WHERE ...;
         RETURN RESULT;
    END;
    /
     
    -- trigger code
    ...
         reslt NUMBER;
    ...
         reslt:=sel_count;
    ...
     
    That sort of construct could eliminate the 'mutating table' error you're seeing.
     
  7. Al_Alter

    Al_Alter Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    Thank you David. Can I use such a function to feed calculated fields in my table NomnklVars? Then question of trigger would lose its meaning.
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    That would depend upon if you pass parameters to the function or not; you could write it to accept a column value then return the count for that passed value. If you do that you could easily populate counts in another table with that function.
     
  9. Al_Alter

    Al_Alter Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    Let me to be more exact. The first table is the sours of data. That table is inserted, updated or deleted (records I mean). At the moments of modifying function must act – to get IN parameter and to put result into the target table. The target table is frequently asked for data and I don’t want it to be messed in any calculation. So to deploy the function I must put it in all triggers of the source table (or in “one for all” trigger). Am I right? And is that all?
     
  10. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    If I understand you correctly then, yes, you can write the function to take a parameter and call the update of the target table from the trigger on the source table.
     
  11. Al_Alter

    Al_Alter Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    Thank you
    Best regards, Alex
     
  12. karthikeyanc2003

    karthikeyanc2003 Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    100
    Hi,
    While you inserting the data you are inserting as ('Myvalue'||i) so it will insert as
    Myvalue1
    Myvalue2
    Myvalue3
    rather then Myvalue. so it will return zero row only. Try the like condition so it will work
     
  13. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Clearly you don't understand the concept behind that example; I WANT to return no rows to prove that count(), when used correctly, will return a 0 when no records meeting the criteria are found. The query is written as intended and produces the desired result.