Re: Stored Procedure Arguments

Nigel is correct. You must use DBMS_SQL to dynamically generate the insert statement. This is because the PL/SQL compiler performs early binding. That is, it attempts to resolve object names at compile time not run time. Hence, the compiler inteprets tablename1_at_TO_DB literally.

Tim Taylor

Meinaz wrote:

> Hi,
>
> I have this stored procedure which is supposed to move a record from one
> database to another database. I passed in the database names as arguments
> to the procedure.
>
> CREATE OR REPLACE FUNCTION MOVE_RECORD
> { ID IN VARCHAR2,
> FROM_DB IN VARCHAR2,
> TO_DB IN VARCHAR2 }
>
> RETURN VARCHAR2 AS ....
>
> BEGIN
> ...
> INSERT INTO tablename1_at_TO_DB VALUES
> (.....)
>
> END MOVE_RECORD.
>
> The problem is during execution, "tablename1_at_TO_DB" is interpreted as the
> table to insert the record. Instead of inserting the record into
> tablename1 at the database named in TO_DB. The only working way is to
> hardcode the database name in the procedure which is not neat programming.
>
> Does anyone knows how can I overcome this problem?
>
> Thanks in advance!!
>
> Meina.
Received on Wed Feb 03 1999 - 19:35:33 CET