SQL%ROWCOUNT issues

rtnetworks · #1 (**permalink**) 06-30-09, 16:35

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

LKBrwn_DBA · #2 (**permalink**) 06-30-09, 16:53

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.

carloa · #3 (**permalink**) 06-30-09, 17:01

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;

rtnetworks · #4 (**permalink**) 06-30-09, 17:16

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.

rtnetworks · #5 (**permalink**) 06-30-09, 17:33

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech