I have a problem with Dynamic SQL that I have been trying to solve for the past 2 weeks but to no avail. Please help!
I am writing a stored procedure that will take in the table name as an input parameter and then to retrieve a particular record based on some criteria, then insert into another table which resides in a different database. Please note that I am dealing with many different databases on different locations so the normal "INSERT INTO TABLEA SELECT * FROM TABLEB" cannot work as it would complain that table or view cannot be found.
As the table name is dynamic, the method that I am trying is to use the 'Execute Immediate'. But as I need to cater for different tables, I need to know the columns that I am retrieving. Therefore, I have try to dynamically declare the variables but it does not seem to work at all. Below is a sample of what I am trying to achieve:
Thanks for your help! But it cannot work as I have try it out. My problem is I am dealing with a few different databases in remote location via DBLinks and I am also not accessing the actual tables but via views.
Thus, the solution that you have suggested cannot work.
Therefore, I am trying to find out can I dynamically declare variables using Execute Immediate? I am trying to achieve the following: