PL/SQL how to assign variable Variable?

Question

This may be very basic. I am very beginner of PL/SQL, but I am stuck with this issue. If somebody know the solution, please let me know.

This code

DECLARE      
  v_objectID VARCHAR2(100);
  v_account  VARCHAR2(100);

BEGIN           
     v_objectID :='21,22';

     DBMS_OUTPUT.PUT_LINE(v_objectID); 
END;
/

Output is

21,22

Then,

This code

DECLARE      
  v_objectID VARCHAR2(100);
  v_account  VARCHAR2(100);

BEGIN           



     SELECT LISTAGG(x.ACCOUNT, ',') WITHIN GROUP (ORDER BY NULL) AS ACCOUNT
     INTO v_account           
     FROM acctx x
     where x.OBJECT_ID IN (21,22);

     DBMS_OUTPUT.PUT_LINE(v_account); 
END;
/

OUTPUT is

1001,2002

Then I try to do like this

DECLARE      
  v_objectID VARCHAR2(100);
  v_account  VARCHAR2(100);

BEGIN           
     v_objectID :='21,22';


     SELECT LISTAGG(x.ACCOUNT, ',') WITHIN GROUP (ORDER BY NULL) AS ACCOUNT
     INTO v_account           
     FROM acctx x
     where x.OBJECT_ID IN (v_objectID);

     DBMS_OUTPUT.PUT_LINE(v_account); 
END;
/

I added v_objectID :='21,22';; This is causing the problem

The error is

ORA_07122: Invalid number ORA-06512: at line 9

How should I assign variable appropriately to output 1001,2002?

Thanks

XING · Accepted Answer · 2016-08-17 15:15:06Z

1

The error is obvious, In your table object_id would have been of Number Datatype. Now you are trying to compare a number with a varchar, so you faced the issue. Try below:

DECLARE      
  v_objectID VARCHAR2(100);
  v_account  VARCHAR2(100);

BEGIN           
     v_objectID :='21,22';


     SELECT LISTAGG(x.A, ',') WITHIN GROUP (ORDER BY NULL) AS ACCOUNT
     INTO v_account           
     FROM test x
     where to_char(x.A) IN (v_objectID);

     DBMS_OUTPUT.PUT_LINE(v_account); 
END;
/

answered Aug 17, 2016 at 15:15

XING

9,9164 gold badges25 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PL/SQL how to assign variable Variable?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related