Re: Stored Procedure Arguments

If you use the DBMS_SQL package, then you can construct the SQL you need on the fly to do the job. This will allow you to specify the database and anything else you like.

Have a look in the admin directory under oracle e.g. \orant\rdbms73\admin\dbmssql.sql for info on how to use this very useful package

Nigel

Meinaz wrote in message <01be4f7a$8d4eaae0$157d74cb_at_default>...
>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 - 15:28:34 CET