Re: Help: Create synonyms ?

From: James Petts <jpetts>
Date: Fri, 30 Oct 1998 15:48:28 GMT
Message-ID: <3639dbac.83119679@firewall.celltech>

On Fri, 30 Oct 1998 15:13:02 +0100, "Martin Fuks" <mfuks_at_post3.tele.dk> wrote:

>I would like to do this automaticly so I have thought of creating a stored
>procedure to do it for me.
>
>---
>Create Procedure Create_synonyms
> (User_Id IN VarChar2)
>AS
>BEGIN
> Create Synonym User_Id.<Synonym_Name> For <Table_Name>;
>END Create_Synonyms;
>---
>Can I use a Parameter in a Sql statement like this ?

You can't use DDL like this directly in PL/SQL. You will have to look at using the DBMS_SQL package. Using this you can write procedures that take arguments. This is the easiest way to do what you want. It is not as quick and easy as I make it sound, but it is fairly straightforward. For an excellent reference look at "Oracle Buil-In Packages' by Feuerstein, Dye and Beresniewicz, published by O'Reilly.

An example (truncating a table) is given below:

PROCEDURE Truncate_Table(Table_IN VARCHAR2)

IS

/*

|| Called By: Compute_All_Composite_Results
||
|| Calls: None
||
|| Author: James Petts
||
|| History:
||  Date           Action
|| ======          ====================================
|| 980519          Procedure created
||

*/

cursor_handle INTEGER;
return_value INTEGER;
date_stamp DATE;

BEGIN

      /* Open cursor for dynamic SQL */
      cursor_handle := DBMS_SQL.OPEN_CURSOR;
      /* Construct the SQL statement and parse it in V7 mode */
      DBMS_SQL.PARSE (cursor_handle,
                      'TRUNCATE TABLE :table_name', DBMS_SQL.V7);
      /* Set the bind variable */
      DBMS_SQL.BIND_VARIABLE (cursor_handle, 'table_name', Table_IN);

      /* Execute the cursor */
      return_value := DBMS_SQL.EXECUTE(cursor_handle);

      DBMS_SQL.CLOSE_CURSOR(cursor_handle);

      COMMIT;

   END IF; EXCEPTION
   WHEN OTHERS THEN

      SELECT SYSDATE INTO date_stamp FROM dual;
      INSERT INTO COMPOSITE_UPDATE_LOG (log_date, log_message)
         VALUES (date_stamp,
                'Truncation of '||Table_IN||' failed');

      DBMS_SQL.CLOSE_CURSOR(cursor_handle);

      COMMIT;

END Truncate_Table; Received on Fri Oct 30 1998 - 09:48:28 CST