Runtime Query in Oracle store procedures not returning proper result

This is first my stint with procedure and I am trying to execute below oracle procedure but facing some issue. Any inputs on this would be really helpful:

Issue:----- I have a select query that returns two values:

    src_Columns contains:
        ID_ELEMENT
        ID_ELEMENT_SA

Now, When I am trying to travesre a select-query(single column) result using "For Loop", I am not getting the values of the column rather I am getting its name only.

FOR columnItem IN (SELECT src_Columns FROM ELEM90_LNK_ELEM_BOSE)
           LOOP
           dbms_output.put_line('src_Columns 3: ' || columnItem.src_Columns);
           query_test:=  'insert into ' || destination_Table || '(NAME,' || dest_Columns_Value || ') VALUES( ''' || src_name_Value || ''',''' || columnItem.dummyValue || ''')';
           dbms_output.put_line('query_test:' || query_test);
           execute immediate query_test;
           END LOOP;

I mean the test query generated is following when i use variable name(src_columns):

insert into ATT_WTPART(NAME,STRINGVALUE) VALUES( 'ID_ELEMENT_SA','ID_ELEMENT_SA')

whereas if I use ID_ELEMENT_SA instead of src_Columns in FOR LOOP

FOR columnItem IN (SELECT ID_ELEMENT FROM ELEM90_LNK_ELEM_BOSE)

then I get proper values that are desired like

insert into ATT_WTPART(NAME,STRINGVALUE) VALUES( 'ID_ELEMENT_SA','ID05')

How can I make sure that I get the values even when I am using the variable name instead of any hard-coding

Below is the Complete Procedure:-------------

 create or replace 
PROCEDURE ELEM90_Lnk_Elem_ATT_WTPART_MK
AS
CURSOR targ_dest_relation IS
    SELECT sourcecolumn  FROM mapping where destinationtable='ATT_WTPART';
    BEGIN
DECLARE 

dest_Columns varchar2(1000);
src_Columns varchar2(1000); 
src_Type varchar2(1000);
destination_Table varchar2(1000) := 'ATT_WTPART';
source_Table varchar2(1000) := 'ELEM90_LNK_ELEM_BOSE';
query_test varchar2(1000);
query_test2 varchar2(1000);
src_name varchar2(255);
src_Type_Value varchar2(255);
dest_Columns_Value varchar2(255);
src_name_Value varchar2(255);
for_query varchar2(1000);
for_query_data varchar2(1000);
dummyValue varchar2(1000); 

BEGIN

    FOR rec IN targ_dest_relation loop
           dbms_output.put_line('destination_Table: ' || destination_Table);
           dbms_output.put_line('source_Table: ' || source_Table);

           src_Columns :=  rec.sourcecolumn;
           dbms_output.put_line('src_Columns: ' || src_Columns);

           src_Type := 'select data_type from user_tab_columns where table_name ='''||source_Table||'''and column_name='''|| src_Columns ||'''';
           dbms_output.put_line('src_Type: ' || src_Type);

           execute immediate src_Type INTO src_Type_Value;
           dbms_output.put_line('src_Type_Value: ' || src_Type_Value);

           dest_Columns := 'select DEST_COLUMN from ATT_PART_MAPPING where SOURCETYPE='''|| src_Type_Value || '''';
           dbms_output.put_line('dest_Columns: '  || dest_Columns);

           execute immediate dest_Columns INTO dest_Columns_Value;
           dbms_output.put_line('dest_Columns_Value: ' || dest_Columns_Value);

           src_name := 'select column_name from user_tab_columns where table_name ='''|| source_Table ||'''  and column_name= ''' || src_Columns || '''';
           dbms_output.put_line('src_name: ' || src_name);

           execute immediate src_name INTO src_name_Value;
           dbms_output.put_line('src_name_Value: ' || src_name_Value);

           FOR columnItem IN (SELECT src_Columns FROM ELEM90_LNK_ELEM_BOSE)
           LOOP
           dbms_output.put_line('src_Columns 3: ' || columnItem.src_Columns);
           query_test:=  'insert into ' || destination_Table || '(NAME,' || dest_Columns_Value || ') VALUES( ''' || src_name_Value || ''',''' || columnItem.dummyValue || ''')';
           dbms_output.put_line('query_test:' || query_test);
           execute immediate query_test;
           END LOOP;

    END loop;  
END;
END;

Answers


The problem with the line

FOR columnItem IN (SELECT src_Columns FROM ELEM90_LNK_ELEM_BOSE)

is that src_Columns is a local variable. As a result, you end up selecting the same value for each row in ELEM90_LNK_ELEM_BOSE.

If you want the value of the local variable to be used as a column name in the query, you must use dynamic SQL instead. Try replacing FOR columnItem ... LOOP ... END LOOP with the following:

           OPEN curs FOR 'SELECT ' || src_Columns || ' FROM ELEM90_LNK_ELEM_BOSE';
           LOOP
             FETCH curs INTO column_value;
             EXIT WHEN curs%NOTFOUND;
             dbms_output.put_line('src_Columns 3: ' || column_value);
             query_test:=  'insert into ' || destination_Table || '(NAME,' || dest_Columns_Value || ') VALUES( ''' || src_name_Value || ''',''' || column_value || ''')';
             dbms_output.put_line('query_test:' || query_test);
             execute immediate query_test;
           END LOOP;

           CLOSE curs;

You will need to declare the following extra variables:

curs         SYS_REFCURSOR;
column_value VARCHAR2(4000);  

However, in truth it would probably be better to remove the loop entirely. You can replace it with an INSERT INTO ... SELECT ... FROM ... statement instead, such as the following:

       EXECUTE IMMEDIATE 'INSERT INTO ' || destination_Table || ' (NAME,' ||
           dest_Columns_Value || ') SELECT :name,' || src_Columns || 
           ' FROM ELEM90_LNK_ELEM_BOSE' USING src_name_Value;

This also gets rid of the need for the two local variables curs and column_value, and is also likely to be considerably faster, as there's no need to parse dynamic SQL once for each row in the destination table.


Is it working better if you try this one:

query_test:=  'insert into ' || destination_Table || 
   '(NAME,'||dest_Columns_Value||') VALUES (:p1, :p2)';
execute immediate query_test USING src_name_Value, columnItem.dummyValue;

At least it should have a positive impact on performance.


Need Your Help

Bottle with multiple apps

python wsgi bottle multiple-projects

How do I manage multiple apps in Bottle, served from the one run?

Error when filling DataGrid with SQL table

c# wpf datagrid

I am trying to populate a WPF DataGrid with a SQL Server table, but I am getting this exception when I try to start the App (and it enters Break Mode):