Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Oracle Dynamic SQL with UNION operator

Posted on 2011-02-27
13
Medium Priority
?
1,326 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 71

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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 71

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 71

Accepted Solution

by:
Qlemo earned 2000 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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

782 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