If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Re: DB Link Variable problem

You can not use a variable in the FROM clause of a sql-statement. Oracle does not know how to evaluate it and thinks that vrec_newconnection.db_link is the actual name of the db_link.

The only way to dynamically run sql-statements is to use Dynamic Sql, Execute Immedate, or DBMS_SQL. Since you want to use the query in a cursor I would suggest the DBMS_SQL since you can loop through the parsing of the data.

Re: DB Link Variable problem

I know it may seem as a dum question, but, I am kind of new at this. How would I run the executed immediate to the an SQL statement that is part of a CURSOR assignment line. What is the syntax to acomplish this:

Re: DB Link Variable problem

Execute Immediate can only run a single DML or DDL. So that would be an SQL statement not containing any PL/SQL code such as the Cursor definition. This is why I suggest the DBMS_SQL package due to the ability to loop through the parsing of the data much like you would do with looping through a cursor.

Re: DB Link Variable problem

As Carloa mentioned, execute immediate can be used only to execute single stmts -- kind of quick & fast dynamic sql. Since you want to loop thru the cursor, here is an example which uses dbms_sql. The quoted example reads the source table over a dblink and inserts row by row into the destination table.

Originally posted by pvelit
I know it may seem as a dum question, but, I am kind of new at this. How would I run the executed immediate to the an SQL statement that is part of a CURSOR assignment line. What is the syntax to acomplish this: