Search This Blog

Wednesday, September 8, 2010

Use of variable with comma seperated value in Oracle SQL Query

The requirement is to pass comma seperated value to a procedure and to use that variable in the query to extract values. For e.g. variable p_ord_num_list has a value of '90001234, 90001235, 90001236' and we attempt to use this in variable in the query as below

SELECT * FROM oe_order_headers_all WHERE order_number IN p_ord_num_list 
The above query completes in error
ORA-01722: invalid number
ORA-06512: at line 13

The above requirement can be achieved in following way
DECLARE
   p_ord_num_list   VARCHAR2 (4000) := '90001234, 90001235, 90001236';
BEGIN
   FOR i IN (SELECT order_number
             FROM   oe_order_headers_all e
             WHERE  order_number IN (
                       SELECT EXTRACTVALUE (xt.COLUMN_VALUE, 'e')
                       FROM   TABLE (XMLSEQUENCE (EXTRACT (XMLTYPE (   '<ord_num><e>'
                                                                    || REPLACE (p_ord_num_list, ',', '</e><e>')
                                                                    || '</e></ord_num>'
                                                                   )
                                                         , '/ord_num/*'
                                                          )
                                                 )
                                    ) xt))
   LOOP
      DBMS_OUTPUT.put_line ('a = ' || i.order_number);
   END LOOP;
END;

The way above statement works is that it first generates the XML tag for each comma seperated value and then extracts values from each element.

The other way to do this is by using regular expression functions as shown below
DECLARE
   p_ord_num_list   VARCHAR2 (4000) := '90001234, 90001235, 90001236';
BEGIN
   FOR i IN (SELECT * FROM oe_order_headers_all WHERE order_number IN (         
SELECT     TRIM(REGEXP_SUBSTR(p_ord_num_list   , '[^,]+', 1, LEVEL)) item_id
FROM       (SELECT p_ord_num_list    str
            FROM   DUAL)
CONNECT BY LEVEL <= LENGTH(str) - LENGTH(REPLACE(str, ',')) + 1))
   LOOP
      DBMS_OUTPUT.put_line ('a = ' || i.order_number);
   END LOOP;
END;

3 Comments:

Vijaya said...

Special characters in csv writing:

I am opening the file using 'utl_file.fopen()' and writing
text data into that.There is no issues as far as writing data into file.

We have some data that contains special chars(other than english).Forexample we have data like this

Brandgefördernd


When i am writing this data into text file,It is getting printed as

Brandf ö rdernd

Here the character ó is getting printed as ö.

After analysing I found that if I can open text file in 'UTF-8' format,The issue will be solved.But how to open a file in 'UTF-8' format instead of 'ASCII' format.Is there any way to specify encoding scheme while opening the file?

The defaut character set of database I am using is 'UTF-8'

Any help will be much appreciated.

harshitha said...

HI

i m new bee to oracle i realy found your blog intersting its really stuff filled and infomatory.

regards
oracle fussion middleware

Sree said...

Hi,
MY client using legacy PO application and currently we are implementing oracle Inventory 11i.

I need to know
1). API to create receits in Inventory ( here we are not having Oracle PO Module)
2). After creating receipt, that API will update the on hand qty? If not what is the API to update on Hand Qty.

Thanks in Advance.

Regards,
Sreehari

Copyright (c) All rights reserved. Presented by Suresh Vaishya