passing string
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: passing string

  1. #1
    Join Date
    Dec 2003
    Posts
    90

    passing string

    ok so far i am using the USING clause for dynamic bind variable query.

    sql_stmt := 'SELECT id FROM caw_communities_x WHERE id IN (:1)';
    EXECUTE IMMEDIATE sql_stmt BULK COLLECT INTO CommunityList USING communities;

    where communities is the parameter passed to the function as a comma delimited list. But this query doesn't work this one will however work


    sql_stm := 'SELECT id FROM caw_communities_x WHERE id IN (' || communities || ')';
    EXECUTE IMMEDIATE sql_stm BULK COLLECT INTO CommunityList;


    but i'd like to do it with the dynamic bind, is it possible?

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    No, using bind variable as the parameter for the IN operator is not possible like this. But you can revrite the query like this to use the comma delimited list as bind variable (assuming column ID is of NUMBER type):

    sql_stmt :=
    'SELECT id FROM caw_communities_x
    WHERE INSTR(:1, '',''||TO_CHAR(id)||'','') > 0';

    EXECUTE IMMEDIATE sql_stmt BULK COLLECT INTO CommunityList
    USING ','||communities||',';

    The execution plan for this query might of course be totaly different (it will perform full table scan), but if the number of records in your table is relatively small and/or the number of elements in your in-list parameter is relatively large the performance might be acceptable.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

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


Click Here to Expand Forum to Full Width