1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to assign individual-quoted values to a single variable.?

Discussion in 'SQL PL/SQL' started by Vicky, Dec 14, 2015.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hi friendz,

    Here, I'm trying to assign individual-quoted values to a variable.,

    Code (SQL):

    SET serveroutput ON;
    DECLARE
    v_emp_name varchar2(100):=''sam','arun','celin','david'';
    BEGIN
    DELETE FROM EMPLOYEES
    WHERE EMP_NAME IN (V_EMP_NAME);
    DBMS_OUTPUT.PUT_LINE('sql%rowcount?>>'||sql%ROWCOUNT);
    END;

    /

    ORA-06550: line 2, COLUMN 29:
    PLS-00103: Encountered the symbol "SAM" WHEN expecting one OF the following:

      * & = - + ; < / > at IN IS MOD remainder NOT rem
      <an exponent (**)> <> OR != OR ~= >= <= <> AND OR LIKE like2
      like4 likec BETWEEN || multiset member submultiset
    06550. 00000 -  "line %s, column %s:\n%s"
    *Cause:  Usually a PL/SQL compilation error.
    *Action:

     
    Doubling the quotes also not helping here/.!
     
  2. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    It works when I try somethng using '||'.,

    v_emp_name varchar2(100):='sam'||','||'arun'||','||'celin'||','||'david';

    But the value is passed only like below from the front end.,

    'sam','arun','celin','david'.,

    Isn't possible to do anythn without '||',.?
     
  3. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    individual-quoted values

    But you assigned value which has comma (,).

    so it is giving error.

    So try like this...

    '''sam''arun''celin''david'''

    we need two single quotes at the beginning and ending.
     
  4. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    But, I'm receiving the value as 'sam','arun','celin','david' from application,.
    Isn't any way to handle it better.,?
     
  5. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    check this..

    v_emp_name varchar2(100):='''sam'',''arun'',''celin'',''david''';
     
    Vicky likes this.
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I think what you want is this:

    Code (SQL):
    DECLARE
    v_emp_name varchar2(100):='sam,arun,celin,david';
    BEGIN
    DELETE FROM EMPLOYEES
    WHERE EMP_NAME IN (V_EMP_NAME);
    DBMS_OUTPUT.PUT_LINE('sql%rowcount?>>'||sql%ROWCOUNT);
    END;
    /
     
    But I doubt it will work as you expect since it won't 'parse' the string. I wrote a blog post a while back that explains how to submit dynamic strings to a procedure to get it to work:

    https://dfitzjarrell.wordpress.com/2008/09/26/how-dynamic/

    You might want to read this before you go any further.
     
    Vicky likes this.