Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to convert Character string to individual Numbers for IN clause?

554899Jul 18 2008 — edited Jul 18 2008
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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 15 2008
Added on Jul 18 2008
4 comments
450 views