Hi,
I'm using the following query but its throwing the error shown below. Please help.
declare
ln_UID VARCHAR2(200) := '21,24';
ln_NUM NUMBER;
begin
dbms_output.put_line('ln_UID ' || ln_UID);
SELECT COUNT(USAGE_RESOURCE_PERMISSION_ID)
INTO ln_NUM
FROM S_USAGE_RESOURCE_PERMISSIONS SURP,
S_USAGE_PROFILE SUP,
S_RESOURCE_PERMISSIONS SRP,
S_RESOURCE SR,
S_RESOURCE_GROUP SRG
WHERE SUP.USAGE_PROFILE_ID = SURP.USAGE_PROFILE_ID
AND SURP.RESOURCE_PERMISSION_ID = SRP.RESOURCE_PERMISSION_ID
AND SUP.USAGE_PROFILE_ID IN (ln_UID)
AND SRP.RESOURCE_PERMISSION_VAL <> 3 --view
AND SRP.RESOURCE_ID = SR.RESOURCE_ID
AND SR.RESOURCE_GROUP_ID = SRG.RESOURCE_GROUP_ID
AND SRG.PRODUCT_ID = (SELECT PRODUCT_ID FROM S_PRODUCTS WHERE PRODUCT_NAME='RBPS');
dbms_output.put_line('ln_NUM ' || ln_NUM);
end;
-------------------------------------------------------
error-
--------
Error starting at line 1 in command:
declare
ln_UID VARCHAR2(200) := '(21,24)';
ln_NUM NUMBER;
begin
dbms_output.put_line('ln_UID ' || ln_UID);
SELECT COUNT(USAGE_RESOURCE_PERMISSION_ID)
INTO ln_NUM
FROM S_USAGE_RESOURCE_PERMISSIONS SURP,
S_USAGE_PROFILE SUP,
S_RESOURCE_PERMISSIONS SRP,
S_RESOURCE SR,
S_RESOURCE_GROUP SRG
WHERE SUP.USAGE_PROFILE_ID = SURP.USAGE_PROFILE_ID
AND SURP.RESOURCE_PERMISSION_ID = SRP.RESOURCE_PERMISSION_ID
AND SUP.USAGE_PROFILE_ID IN ln_UID
AND SRP.RESOURCE_PERMISSION_VAL <> 3 --view
AND SRP.RESOURCE_ID = SR.RESOURCE_ID
AND SR.RESOURCE_GROUP_ID = SRG.RESOURCE_GROUP_ID
AND SRG.PRODUCT_ID = (SELECT PRODUCT_ID FROM S_PRODUCTS WHERE PRODUCT_NAME='RBPS');
dbms_output.put_line('ln_NUM ' || ln_NUM);
end;
Error report:
ORA-01722: invalid number
ORA-06512: at line 6
01722. 00000 - "invalid number"
*Cause:
*Action:
Thanks.