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
Suriyaraj_SudalaiappanAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
QlemoConnect With a Mentor Batchelor, Developer and EE Topic AdvisorCommented:
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
 
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Suriyaraj_SudalaiappanAuthor Commented:
So could you please correct the posted code as with the correct syntax??
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
Suriyaraj_SudalaiappanAuthor Commented:
Hi i already identified that and corrected it. Thanks for your useful information
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.