Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Static vs Dynamic Query over a database link

763302Mar 29 2010 — edited Mar 29 2010
Hi,
I ran into this problem yesterday, and wasn't sure why this would occur. I seemed to have found a workaround, but I need an explanation why one works, and the other doesn't...

This query works ->
SELECT alias_name.column_1 
 FROM table_name@database_line alias_name
WHERE alias_name.column2 = 'xyz';
But this fails ->
DECLARE 
  v_return  VARCHAR2(1000); 
BEGIN 

 SELECT alias_name.column_1
    INTO v_return
  FROM table_name@database_line alias_name
    WHERE alias_name.column2 = 'xyz';

  dbms_output.Put_line(v_return); 
EXCEPTION 
  WHEN OTHERS THEN 
    dbms_output.Put_line(sqlerrm); 
END;
Not in the second query, the parser doesn't recognize the alias_name, or column names. Not when i change the second query into a dynamic sql query, and use Execute immediate...into... it works...
Additional Info - the query is fired from an Oracle 10 database to an Oracle 8 Database over a db link.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 26 2010
Added on Mar 29 2010
3 comments
695 views