Solved

Oracle Dynamic SQL with UNION operator

Posted on 2011-02-27
13
1,059 Views
Last Modified: 2012-06-27
Hi,

I have sql string contains two queries separated by UNION operator. When i execute by use DBMS_SQL, it is getting only the first SQL before the UNION operator.

Can anyone correct how can i sue UNION in DBMS_SQL?

Thanks
0
Comment
  • 4
  • 3
  • 3
  • +2
13 Comments
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 34991006
DECLARE
 2    l_cursor INTEGER;
 3    l_output  VARCHAR2(20);
 4    l_rows   INTEGER;
 5    l_sql   VARCHAR2(1000);
 6  BEGIN
 7    l_cursor := DBMS_SQL.OPEN_CURSOR;
 8    
 9    l_sql := 'SELECT ''1st Row'' as txt FROM DUAL UNION SELECT ''2nd Row'' as txt FROM DUAL';  
10    
11    DBMS_SQL.PARSE(l_cursor, l_sql, DBMS_SQL.NATIVE);
12    DBMS_SQL.DEFINE_COLUMN_CHAR(l_cursor, 1, l_output, 20);
13    l_rows := DBMS_SQL.EXECUTE(l_cursor);
14    loop
15      if DBMS_SQL.FETCH_ROWS(l_cursor) = 0 then
16         exit;
17      end if;
18      DBMS_SQL.COLUMN_VALUE_CHAR(l_cursor, 1, l_output);
19      DBMS_OUTPUT.PUT_LINE('Output Text '||l_output);
20    end loop;
21    DBMS_SQL.CLOSE_CURSOR(l_cursor);
22  EXCEPTION
23    when others then
24         DBMS_SQL.CLOSE_CURSOR(l_cursor);
25         raise_application_error(-20000, 'Unknown Exception Raised: '||sqlcode||' '||sqlerrm);
26  END;
27  /
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 34991298
Basically you have done something wrong, like using a semicolon between the first select and the union. DBMS_SQL understands the same syntax as if you issue the command directly.
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 34991378
So could you please correct the posted code as with the correct syntax??
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 34991403
Which code?
0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 34991425
what is there to currect? just replace with your query.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 34991758
are you sure both queries in the union return data it's quite possible your combined query is returning the correct results

for example
      select 'A' from dual union select 'B' from dual where 1= 0  

will return 'A' because the second query doesn't return any rows


or,  does the second query return rows duplicated by the first query?  If so,  UNION will remove all duplicates.

select 'A' from dual union select 'A' from dual

will return 'A' because the UNION will remove the duplicates
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34992008
you did not post any code, so we cannot "correct" it ...
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 34992217
I have concatenated the query in one string when i print the string as the output, it is printing only till the first sql query not printed the second query. My string length is varchar2(32600)  and the query printed only 400 length.
0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 34992255
please post your code.

>>  it is printing only till the first sql query not printed the second query
union operator only retrive one data set.
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 34994390
when i use DBMS_OUTPUT to print the string, it is printing only the first query
DECLARE
    v_sql_string   VARCHAR2(32000);
    v_hanel_opt_type VARCHAR2(5) := NULL;
    v_hanel_command VARCHAR2(5) := NULL;
    i_inv_org_id    number := 2034;
    v_max_conc_date VARCHAR2(5) := NULL;
    
BEGIN
        v_sql_string := 'SELECT REGEXP_REPLACE(INITCAP(mtt.transaction_type_name),''^ *([^ ]*) *$|(^| )([^ ])|.'',''\1\3'')';
        v_sql_string := v_sql_string||' ||''-''||mmt.TRANSACTION_ID  "id",';
        v_sql_string := v_sql_string||' msib.segment1 "itemid",mmt.transaction_type_id,mmt.transaction_action_id,';
        v_sql_string := v_sql_string||' mmt.transaction_source_type_id,mmt.transaction_quantity "requiredquantity",mmt.transaction_uom,';
        v_sql_string := v_sql_string||' NULL "description",TO_CHAR(SYSDATE,''MM/DD/YY HH:MM:SS'') "Creation date",';
        v_sql_string := v_sql_string||' ROW_NUMBER() OVER (PARTITION BY mmt.transaction_id ORDER BY mmt.transaction_id) "positionindex"';
        
        IF v_hanel_opt_type IS NOT NULL
        THEN        
            v_sql_string := v_sql_string||', '||v_hanel_opt_type||' "processing"';
        ELSE
            v_sql_string := v_sql_string||', NULL "processing"';
        END IF;
       
        IF v_hanel_command IS NOT NULL
        THEN        
            v_sql_string := v_sql_string||', '||v_hanel_command||' "command"';
        ELSE
            v_sql_string := v_sql_string||', NULL "command"';
        END IF;

        v_sql_string := v_sql_string||' ,mmt.transaction_date, 1 "type"';
        v_sql_string := v_sql_string||' FROM mtl_material_transactions mmt, mtl_transaction_types mtt,mtl_system_items_b msib';
        v_sql_string := v_sql_string||' WHERE   mmt.organization_id = '||CAST(i_inv_org_id AS VARCHAR2);
        v_sql_string := v_sql_string||' AND NVL(mmt.transaction_quantity,0) < 0 AND mtt.transaction_type_id = mmt.transaction_type_id';
        v_sql_string := v_sql_string||' AND msib.inventory_item_id = mmt.inventory_item_id AND msib.organization_id = mmt.organization_id';
        v_sql_string := v_sql_string||' AND mmt.transaction_action_id = 2 AND EXISTS (SELECT  ''x'' FROM fnd_lookup_values flv';
        v_sql_string := v_sql_string||' WHERE flv.meaning = mmt.transfer_subinventory AND flv.lookup_type = ''EMR VLM MO SOURCE SUBINV MMI''';
        v_sql_string := v_sql_string||' AND flv.language = USERENV(''LANG'') AND flv.enabled_flag = ''Y''';
        v_sql_string := v_sql_string||' AND NVL(flv.end_date_active,SYSDATE) >= SYSDATE AND flv.description = ''MMB'')';                        
        v_sql_string := v_sql_string||' AND NOT EXISTS (SELECT  ''x'' FROM    fnd_lookup_values flv WHERE flv.meaning = mtt.transaction_type_name';
        v_sql_string := v_sql_string||' AND flv.lookup_type = ''EMR TXN TYPE VLM MMI'' AND flv.language = USERENV(''LANG'')';
        v_sql_string := v_sql_string||' AND flv.enabled_flag = ''Y'' AND NVL(flv.end_date_active,SYSDATE) >= SYSDATE)';                

        IF v_max_conc_date IS NULL
        THEN
            v_sql_string := v_sql_string||' AND TO_DATE(mmt.transaction_date,''DD/MM/YYYY'') >= TO_DATE(SYSDATE,''DD/MM/YYYY'')';
        ELSE
            v_sql_string := v_sql_string||' AND mmt.transaction_date BETWEEN TO_DATE('||TO_CHAR(v_max_conc_date,'DD/MM/YYYY')||',''DD/MM/YYYY'') AND SYSDATE';        
        END IF;

        v_sql_string := v_sql_string||' UNION ';
        v_sql_string := 'SELECT REGEXP_REPLACE(INITCAP(mtt.transaction_type_name),''^ *([^ ]*) *$|(^| )([^ ])|.'',''\1\3'')';
        v_sql_string := v_sql_string||' ||''-''||mmt.TRANSACTION_ID  "id",';
        v_sql_string := v_sql_string||' msib.segment1 "itemid",mmt.transaction_type_id,mmt.transaction_action_id,';
        v_sql_string := v_sql_string||' mmt.transaction_source_type_id,mmt.transaction_quantity "requiredquantity",mmt.transaction_uom,';
        v_sql_string := v_sql_string||' NULL "description",TO_CHAR(SYSDATE,''MM/DD/YY HH:MM:SS'') "Creation date",';
        v_sql_string := v_sql_string||' ROW_NUMBER() OVER (PARTITION BY mmt.transaction_id ORDER BY mmt.transaction_id) "positionindex"';
        
        IF v_hanel_opt_type IS NOT NULL
        THEN        
            v_sql_string := v_sql_string||', '||v_hanel_opt_type||' "processing"';
        ELSE
            v_sql_string := v_sql_string||', NULL "processing"';
        END IF;
        
        
        IF v_hanel_command IS NOT NULL
        THEN        
            v_sql_string := v_sql_string||', '||v_hanel_command||' "command"';
        ELSE
            v_sql_string := v_sql_string||', NULL "command"';
        END IF;

        v_sql_string := v_sql_string||' ,mmt.transaction_date, 1 "type"';
        v_sql_string := v_sql_string||' FROM mtl_material_transactions mmt, mtl_transaction_types mtt,mtl_system_items_b msib';
        v_sql_string := v_sql_string||' WHERE   mmt.organization_id = '||CAST(i_inv_org_id AS VARCHAR2);
        v_sql_string := v_sql_string||' AND NVL(mmt.transaction_quantity,0) < 0 AND mtt.transaction_type_id = mmt.transaction_type_id';
        v_sql_string := v_sql_string||' AND msib.inventory_item_id = mmt.inventory_item_id AND msib.organization_id = mmt.organization_id';
        v_sql_string := v_sql_string||' AND mmt.transaction_action_id = 2 AND EXISTS (SELECT  ''x'' FROM fnd_lookup_values flv';
        v_sql_string := v_sql_string||' WHERE flv.meaning = mmt.transfer_subinventory AND flv.lookup_type = ''EMR VLM MO SOURCE SUBINV MMI''';
        v_sql_string := v_sql_string||' AND flv.language = USERENV(''LANG'') AND flv.enabled_flag = ''Y''';
        v_sql_string := v_sql_string||' AND NVL(flv.end_date_active,SYSDATE) >= SYSDATE AND flv.description = ''MMB'')';                        
        v_sql_string := v_sql_string||' AND NOT EXISTS (SELECT  ''x'' FROM    fnd_lookup_values flv WHERE flv.meaning = mtt.transaction_type_name';
        v_sql_string := v_sql_string||' AND flv.lookup_type = ''EMR TXN TYPE VLM MMI'' AND flv.language = USERENV(''LANG'')';
        v_sql_string := v_sql_string||' AND flv.enabled_flag = ''Y'' AND NVL(flv.end_date_active,SYSDATE) >= SYSDATE)';                
        
        IF v_max_conc_date IS NULL
        THEN
            v_sql_string := v_sql_string||' AND TO_DATE(mmt.transaction_date,''DD/MM/YYYY'') >= TO_DATE(SYSDATE,''DD/MM/YYYY'') ';
        ELSE
            v_sql_string := v_sql_string||' AND mmt.transaction_date BETWEEN TO_DATE('||TO_CHAR(v_max_conc_date,'DD/MM/YYYY')||',''DD/MM/YYYY'') AND SYSDATE';        
        END IF;
    DBMS_OUTPUT.PUT_LINE(v_sql_string);
END;

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34994895
well, that is possibly because DBMS_OUTPUT.PUT_LINE does not print such long lines properly.
you will have to "split up" the sv_sql_string" for dbms output ...
0
 
LVL 68

Accepted Solution

by:
Qlemo earned 500 total points
ID: 34995107
Did you see yet you forgot to to append to v_sql_string in line 51? It has ot be the last part of the union, not the first, you see when printing the query.
0
 

Author Closing Comment

by:Suriyaraj_Sudalaiappan
ID: 34999424
Hi i already identified that and corrected it. Thanks for your useful information
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now