This discussion is archived
6 Replies Latest reply: Feb 8, 2013 12:39 PM by Justin Cave RSS

In with lsttagg function

choti Newbie
Currently Being Moderated
How can i use IN clause with L_list variable from below code.
Declare
l_list varchar2(20000);
BEGIN
select LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
INTO L_LIST
FROM   emp;
insert into emp_target(dept_no,sal,ename)
select dept_no,sal_ename
where ename in (L_list);
commit;
End;
  • 1. Re: In with lsttagg function
    Solomon Yakobson Guru
    Currently Being Moderated
    Your code in nothing more but:
    insert
      into emp_target(dept_no,sal,ename)
      select  dept_no,
              sal,
              ename
        from  emp
        order by ename;
    SY.
  • 2. Re: In with lsttagg function
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    choti wrote:
    How can i use IN clause with L_list variable from below code.
    LISTAGG is good for making a big string with data from multiple rows. Such a string is no good for an IN sub-query; the multiple rows that you started with are much better.

    It sound like you want something like this:
    INSERT INTO emp_target 
           (dept_no, sal, ename)
    SELECT  dept_no, sal, ename
    FROM     emp;
    or maybe:
    INSERT INTO emp_target 
           (dept_no, sal, ename)
    SELECT  dept_no, sal, ename
    FROM     some_other_table
    WHERE     ename IN (
                         SELECT  ename
                   FROM    emp
               );
    You don't need PL/SQL for this.
    Declare
    l_list varchar2(20000);
    LISTAGG will never return more than 4000 characters.
    BEGIN
    select LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
    INTO L_LIST
    FROM   emp;
    insert into emp_target(dept_no,sal,ename)
    select dept_no,sal_ename
    where ename in (L_list);
    commit;
    End;
    The sub-query you posted doesn't have a FROM clause, so it's really hard to guess what you want to do.

    Why make people guess anyway? Why not say what you want to do. For example
    "I have these tables: CREATE TABLE ... INSERT ...
    I want to change the emp_target table so that it looks like this ..."

    See the forum FAQ {message:id=9360002}
  • 3. Re: In with lsttagg function
    €$ħ₪ Expert
    Currently Being Moderated
    try this..
    Declare
    l_list varchar2(20000);
    BEGIN
    select trim(LISTAGG(''''||ename||'''', ',') WITHIN GROUP (ORDER BY ename)) AS employees
    INTO L_LIST
    FROM   emp;
    insert into emp_target(dept_no,sal,ename)
    select dept_no,sal_ename
    where ename in (L_list);
    commit;
    End;
    Before you execute add from clause and check your sql ...
  • 4. Re: In with lsttagg function
    Justin Cave Oracle ACE
    Currently Being Moderated
    Ummm, have you tried this? If you did, you would find that, in the best case, your `INSERT` would insert 0 rows.

    Justin
  • 5. Re: In with lsttagg function
    €$ħ₪ Expert
    Currently Being Moderated
    Yeah i tried this, knew that its not going to work but the original sql missing from clause so just thought of putting "IN" List ..

    Declare
    l_list varchar2(20000);
    BEGIN
    select trim(LISTAGG(''''||ename||'''', ',') WITHIN GROUP (ORDER BY ename)) into l_list
    FROM   emp;
     execute immediate 
     'insert into emp1(ename)
    select ename from emp
    where ename in ('||l_list||')';
    commit;
    
    DBMS_OUTPUT.PUT_LINE(l_list);
    
    End;
    this should work...
  • 6. Re: In with lsttagg function
    Justin Cave Oracle ACE
    Currently Being Moderated
    Assuming that you aren't unlucky enough to employ anyone named, say O'Conner, that you run this infrequently enough that hard parsing the query every time won't be problematic, and that there is no risk of someone modifying the data in the EMP table to create a SQL injection attack, then that will work. It will be much less efficient than the other solutions that have been proposed, it'll be harder to maintain and debug in addition to being harder to write. But it should work.

    Justin

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points