oracle dynamic query fetch cursor variables through sql

Question

Little help needed. i have a dynamic query that outputs 4 column names and two table names into 6 cursor variables. Now i need to use the cursor variables to select the first 4 columns and then from the two table names using the cursor variables since those contain the data think something with a fetch through query using a variable that contains the query but i don’t know how to go about that. here’s what i have now i just need to fetch the cursor variables and runt hem into a query

DECLARE
arow            VARCHAR2 (1000);

column1         VARCHAR2 (50);
column2         VARCHAR2 (50);
column3         VARCHAR2 (50);
column4         VARCHAR2 (50);
table1          VARCHAR2 (50);
table2          VARCHAR2 (50);
match           VARCHAR2 (50);
match1          VARCHAR2 (50);
sql_statement   VARCHAR2 (500);
BEGIN
FOR arow IN (SELECT   column_name_old,
                     column_name_new,
                     column_name_old_2,
                     column_name_new_2,
                     table_name_old,
                     table_name_new
              FROM   A550003.META_DATA_TABLE)
LOOP
  sql_statement :=
        'INSERT'
     || ' '
     || 'INTO'
     || ' '
     || 'a550003.MATCH_TABLE'
     || ' '
     || 'SELECT '
     || arow.column_name_old
     || ', '
     || arow.column_name_new
     || ', '
     || 'DECODE( '
     || arow.column_name_old
     || ', '
     || arow.column_name_new
     || ','
     || '1'
     || ','
     || '0)'
     || 'AS'
     || ' '
     || 'MATCH'
     || ','
     || arow.column_name_old_2
     || ', '
     || arow.column_name_new_2
     || ','
     || 'DECODE( '
     || arow.column_name_old_2
     || ', '
     || arow.column_name_new_2
     || ','
     || '1'
     || ','
     || '0)'
     || 'AS'
     || ' '
     || 'MATCH1'
     || ' FROM '
     ||' '
     || arow.table_name_old
     || ', '
     || arow.table_name_new
     || ' WHERE '
     || arow.column_name_old
     || '='
     || arow.column_name_new
     || '(+)';
  DBMS_OUTPUT.PUT_LINE (sql_statement);

 EXECUTE IMMEDIATE sql_statement;

  COMMIT;
END LOOP;
END;

kpater87 · Accepted Answer · 2014-07-08 19:52:26Z

0

First of all you sql_statement is wrong. You should set the value of this variable to sth like this (when you will get all needed names):

sql_statement := 'SELECT '
                 || column1 
                 || ', ' 
                 || column2 
                 || ', ' 
                 || column3 
                 || ', ' 
                 || column4 
                 || ' FROM ' 
                 || table1
                 || ', '
                 || table2
                 || ' WHERE '
                 || -- JOIN_CONDITION

And then you can use EXECUTE IMMEDIATE statement:

EXECUTE IMMEDIATE sql_statement
             INTO col1_val
                 ,col2_val
                 ,col3_val
                 ,col4_val
                 ;

Of course variables col[1..4]_val have to be declared.

Also watch out for SQL Injection.

answered Jul 8, 2014 at 19:52

kpater87

1,2701 gold badge14 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Anthony Over a year ago

hey kpater thanks for your response sorry i havnt used dynamic sql a ton. i have edited my original post query and i did't lnow if it looks right. i get missing expression by the execute immediatly, i am not really sure if thats where it should go since its reading from my curosor values

kpater87 Over a year ago

1. Try to formatting you code before putting in the question. 2. You shoud build your query (set sql_statement variable) after fetching data from the cursor. 3. Your select is returning 4 columns so you shouldn't put tab1_val, and tab2_val in the INTO part of you EXECUTE IMMEDIATE statement. 4. If you are using INTO then your select should return always one record. If you are expecting more results you should use BULK COLLECT INTO and variable which is a collection.

Anthony Over a year ago

my above code compiles but nothing actually inserts into the match table

kpater87 Over a year ago

You are changing your question all the time... There was nothing before about inserting data to the table... I don't know you data so it is hard to say why nothing is inserted to your table.

kpater87 Over a year ago

As @Wernfried wrote use DBMS_OUTPUT.PUT_LINE (sql_statement); for debugging. You can also put some output of this statement in the question.

|

Wernfried Domscheit · Accepted Answer · 2014-07-09 12:20:50Z

0

It must be this:

sql_statement := 'SELECT ' 
    || column1 || ', ' || column2 || ', ' || column3 || ', ' || column4 
    || ' FROM ' || table1 || ', ' || table2 
    || ' WHERE ' || column1 ||'=' ||column2||'(+)'; 
EXECUTE IMMEDIATE sql_statement INTO col1_val, col2_val, col3_val, col4_val;

or preferably

sql_statement := 'SELECT ' 
    || column1 || ', ' || column2 || ', ' || column3 || ', ' || column4 
    ||' FROM '||table1||' LEFT OUTER JOIN '||table2||' ON '||column1||'='||column2;
EXECUTE IMMEDIATE sql_statement INTO col1_val, col2_val, col3_val, col4_val;

For debugging DBMS_OUTPUT.PUT_LINE (sql_statement); will be usefull!

Then you don't need a Ref-Cursor, simply do:

BEGIN
   For aRow in (SELECT * FROM a550003.meta_data_table) LOOP
      sql_statement := 'SELECT '||aRow.column1||','||aRow.column2 ...
   END LOOP;
END;

answered Jul 9, 2014 at 12:20

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

1 Comment

Anthony Over a year ago

hey Wernfried thanks for the help it seems that is exactly what i need. i got it working and now if i wanted to have the sql_staement write to a table would i do somthing like a instert into before the (for arow part), i also need to now execute the sql_statement as if i was just putting it into a editor tab and running the query. Now the query i need is in the DBMS output but now i need to get that into somthing i can actually run it

Collectives™ on Stack Overflow

oracle dynamic query fetch cursor variables through sql

2 Answers 2

8 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related