Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2012
    Posts
    3

    Unanswered: [Help] Varchar2 parameter as condition in Proc

    Hi,

    I have the procedure bellow:

    create or replace procedure spr_test(val in varchar2) is
    begin
    delete * from xtable where nro in (val);
    end spr_test;


    And I'm calling it like:

    exec spr_test('1,3,7');

    But no records are affected.

    Will I need to use EXECUTE IMMEDIATE ?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Will I need to use EXECUTE IMMEDIATE ?
    yes
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2012
    Posts
    3
    Quote Originally Posted by anacedent View Post
    >Will I need to use EXECUTE IMMEDIATE ?
    yes
    Alright thanks!!

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Although it is possible to do it with dynamic query, there are better was for achieving the same.

    The best option would be passing VAL as collection (TABLE OF <data type of XTABLE.NRO column>) SQL type. Supposing it is INTEGER, the code would be:
    Code:
    create or replace type integer_list_t as table of integer;
    /
    
    create or replace procedure spr_test(val in integer_list_t) is
    begin
      delete xtable
      where nro in (select column_value from table(val));
    end spr_test;
    /
    
    exec spr_test( integer_list_t( 1,3,7 ) )
    (by the way, using '*' in DELETE statement in your initial post is wrong)

    Even if you insist in passing comma separated string, you should avoid dynamic SQL and rather use any technique from this article: http://tkyte.*************/2006/06/v...-in-lists.html

    [Edit: added code for execution of modified procedure]

  5. #5
    Join Date
    Mar 2012
    Posts
    3
    Quote Originally Posted by flyboy View Post
    Although it is possible to do it with dynamic query, there are better was for achieving the same.

    The best option would be passing VAL as collection (TABLE OF <data type of XTABLE.NRO column>) SQL type. Supposing it is INTEGER, the code would be:
    Code:
    create or replace type integer_list_t as table of integer;
    /
    
    create or replace procedure spr_test(val in integer_list_t) is
    begin
      delete xtable
      where nro in (select column_value from table(val));
    end spr_test;
    /
    
    exec spr_test( integer_list_t( 1,3,7 ) )
    (by the way, using '*' in DELETE statement in your initial post is wrong)

    Even if you insist in passing comma separated string, you should avoid dynamic SQL and rather use any technique from this article: http://tkyte.*************/2006/06/v...-in-lists.html

    [Edit: added code for execution of modified procedure]
    Hi flyboy,

    About the * delete, yes that was my fault, in fact I changed the previous 'select' keyword for 'delete' just before posting here and I forgot the '*'.

    Thanks for answering and link since it fits as I want!

    Matt.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •