Results 1 to 5 of 5
  1. #1
    Join Date
    May 2004
    Posts
    184

    Unanswered: SQL%ROWCOUNT issues

    I have the following package function in 10g that doesn't seem to be counting the number of rows correctly returned by the execute immediate command. When I run the query manually, it returns 1 row, however when the query runs inside the function, it returns 0 rows. The function and the tables are owned by the same account, so I don't think it's related to that, but I could be wrong.

    Any ideas would be greatly appreciated.

    Thank you.

    Code:
    function isValidDomain(sessdomainid in number, laid in number)
    return number
    is
    Begin
         declare 
         sqlDomains varchar2(500);
         rows number(10):=0;
         begin
           sqlDomains :=' SELECT count(*) into recCount 
             FROM DRLearningActivityInstance 
             WHERE learningActivityID = ' || laid ||
             ' AND id NOT IN 
               (SELECT DM.EntityID 
                 FROM DRDomainMembership DM, DRLearningActivityInstance LAI 
                 WHERE DM.entityTypeID IN (Select id from drDomainEntityType where upper(tableName)=''DRLEARNINGACTIVITYINSTANCE'')
                 AND DM.entityId = LAI.ID 
                 AND LAI.learningActivityID= ' || laid || 
                 ' AND DM.domainId IN (' || sessdomainid || '))';
            print_out(sqlDomains);
            execute immediate (sqlDomains);
            rows:= SQL%ROWCOUNT;
            print_out('the number of rows returned was ' || rows);
            if rows=0 then
               return 1;
            else
               return 0;
            end if;
         end;
    end isValidDomain;
    This returns
    Code:
    SELECT id, code
             FROM DRLearningActivityInstance
             WHERE learningActivityID = 8461 AND id NOT IN
               (SELECT DM.EntityID
                 FROM DRDomainMembership DM, DRLearningActivityInstance LAI
                 WHERE DM.entityTypeID IN
    (Select id from drDomainEntityType where upper(tableName)='DRLEARNINGACTIVITYINSTANCE')
                 AND DM.entityId = LAI.ID
                 AND LAI.learningActivityID= 8461 AND DM.domainId IN
    (1))
    the number of rows returned was 0
    1

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down El syntax es malo.

    Your SQL statement in the function is mal-formed:

    1) The into in "sqlDomains :=' SELECT count(*) into recCount " is invalid DML syntax.
    2) The "execute immediate (sqlDomains);" statement return no values.

    The SQL%ROWCOUNT only will return values if the previos statement was DML.

    And, dynamic sql is completely unnecesary for what you need to accomplish.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    try the INTO clause

    function isValidDomain(sessdomainid in number, laid in number)
    return number
    is
    Begin
    declare
    sqlDomains varchar2(500);
    rows number(10):=0;
    begin
    sqlDomains :=' SELECT count(*)
    FROM DRLearningActivityInstance
    WHERE learningActivityID = ' || laid ||
    ' AND id NOT IN
    (SELECT DM.EntityID
    FROM DRDomainMembership DM, DRLearningActivityInstance LAI
    WHERE DM.entityTypeID IN (Select id from drDomainEntityType where upper(tableName)=''DRLEARNINGACTIVITYINSTANCE'')
    AND DM.entityId = LAI.ID
    AND LAI.learningActivityID= ' || laid ||
    ' AND DM.domainId IN (' || sessdomainid || '))';
    print_out(sqlDomains);
    execute immediate sqlDomains into rows;

    -- you are getting the count of records in your query
    -- rows:= SQL%ROWCOUNT;

    print_out('the number of rows returned was ' || rows);
    if rows=0 then
    return 1;
    else
    return 0;
    end if;
    end;
    end isValidDomain;

  4. #4
    Join Date
    May 2004
    Posts
    184
    Thanks. Sorry, my bad... I copied the wrong version to my clipboard. The syntax that I started with, but that is still not working is...

    Code:
    function isValidDomain(sessdomainid in number, laid in number)
    return number
    is
    Begin
         declare 
         sqlDomains varchar2(500);
         rows number(10):=0;
         begin
           sqlDomains :=' SELECT id  
             FROM DRLearningActivityInstance 
             WHERE learningActivityID = ' || laid ||
             ' AND id NOT IN 
               (SELECT DM.EntityID 
                 FROM DRDomainMembership DM, DRLearningActivityInstance LAI 
                 WHERE DM.entityTypeID IN (Select id from drDomainEntityType where upper(tableName)=''DRLEARNINGACTIVITYINSTANCE'')
                 AND DM.entityId = LAI.ID 
                 AND LAI.learningActivityID= ' || laid || 
                 ' AND DM.domainId IN (' || sessdomainid || '))';
            print_out(sqlDomains);
            execute immediate (sqlDomains);
            rows:= SQL%ROWCOUNT;
            print_out('the number of rows returned was ' || rows);
            if rows=0 then
               return 1;
            else
               return 0;
            end if;
         end;
    end isValidDomain;
    and it's output is ...
    Code:
    SELECT id
             FROM DRLearningActivityInstance
             WHERE learningActivityID = 8461 AND id NOT IN
               (SELECT DM.EntityID
                 FROM DRDomainMembership DM, DRLearningActivityInstance LAI
                 WHERE DM.entityTypeID IN
    (Select id from drDomainEntityType where upper(tableName)='DRLEARNINGACTIVITYINSTANCE')
                 AND DM.entityId = LAI.ID
                 AND LAI.learningActivityID= 8461 AND DM.domainId IN
    (1))
    the number of rows returned was 0
    1
    Lk_brwn, thanks for the hint about dynamic sql being overkill. I honestly hadn't given it much thought.

    Thanks.

  5. #5
    Join Date
    May 2004
    Posts
    184
    Thanks everyone. By bypassing the dynamic sql (which I originally used for debub/print purposes) and just using a count(*) into recCount, the code worked perfectly.

    Thanks for the second and third pairs of eyes.

Posting Permissions

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