Ytukyg

Code:

lc_tab1_col1 VARCHAR2(4000);

lc_tab1_col2 VARCHAR2(4000);

lc_tab2_col2 VARCHAR2(4000);

lc_tab2_col2 VARCHAR2(4000);

CURSOR my_cursor IS select col1, col2 from tab1;



[...]



    OPEN my_cursor;



       LOOP

       FETCH my_cursor INTO lc_tab1_col1, lc_tab1_col2;

       EXIT WHEN my_cursor%NOTFOUND;



           SELECT lc_tab1_col2.col1, lc_tab1_col2.col2 INTO lc_tab2_col2, lc_tab2_col2 FROM lc_tab1_col2 WHERE lc_tab1_col2.col3 = lc_tab1_col1;





           [...]



       END LOOP;



    CLOSE my_cursor;

Hey folks,

I am trying to get the above code working.
The issue I am having is that a SELECT INTO statement apparently does not support using a variable (in that case lc_tab1_col2) as the table name in the FROM clause of the statement.

When compiling the package an ORA-000942 is thrown (table or view does not exist), which tells me the variable is interpreted directly instead of being replaced and interpreted at runtime.

I can't think of a workaround on the fly, any ideas on how to fix this?

Some more background: lc_tab1_col2 contains the name of a table in the database whereas lc_tab1_col1 contains an ID.

This ID is present in all of the tables that can be contained in lc_tab1_col2 (hence the WHERE clause).

Apart from the ID there are two other columns (lc_tab1_col2.col1 and lc_tab1_col2.col2) that are present in all those tables, but that are not present in tab1. I need to select those two values to work with them inside the loop.

As there are many tables to consider, I need this SELECT INTO statement to be dynamic. It wouldn't be feasible to parse the tables one by one. Looking forward to anyone sharing a clever idea for overcoming this issue :) Thanks in advance!

I think, you exception really means that this table does not exist or you don't have privileges to SELECT it.

I've executed a below code and everything was ok. I have tried to compile it in a package and also I didn't have any compilation errors

DECLARE 

  user_tables varchar2(30) := 'TBLCOMPANIES';

BEGIN

  SELECT table_name

    INTO user_tables 

    FROM user_tables

   WHERE user_tables.table_name = user_tables;



  dbms_output.put_line(user_tables) ;

END;

/