Solved

Oracle Dynamic SQL with UNION operator

Posted on 2011-02-27
13
1,167 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 70

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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 70

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 74

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
 
LVL 143

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 143

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 70

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

751 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