• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1383
  • Last Modified:

Oracle Dynamic SQL with UNION operator

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
Suriyaraj_Sudalaiappan
Asked:
Suriyaraj_Sudalaiappan
  • 4
  • 3
  • 3
  • +2
1 Solution
 
Aaron ShiloChief Database ArchitectCommented:
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
 
QlemoDeveloperCommented:
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
 
Suriyaraj_SudalaiappanAuthor Commented:
So could you please correct the posted code as with the correct syntax??
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
QlemoDeveloperCommented:
Which code?
0
 
Aaron ShiloChief Database ArchitectCommented:
what is there to currect? just replace with your query.
0
 
sdstuberCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you did not post any code, so we cannot "correct" it ...
0
 
Suriyaraj_SudalaiappanAuthor Commented:
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
 
Aaron ShiloChief Database ArchitectCommented:
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
 
Suriyaraj_SudalaiappanAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
QlemoDeveloperCommented:
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
 
Suriyaraj_SudalaiappanAuthor Commented:
Hi i already identified that and corrected it. Thanks for your useful information
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 4
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now