Re: Calling All Experts. Help Needed !!

From: <charlotte_hurley_at_my-deja.com>
Date: 2000/06/14
Message-ID: <8i8ekk$5iu$1_at_nnrp1.deja.com>

Hello Again,

        I also forgot to mention I found this piece of code to create synonyms, and perhaps there is a way to modify it so that it may generate objects instead of synonyms....

---

---

• Begin PROCEDURE generate_synonym_sql.

  ---

  ---

  PROCEDURE generate_synonym_sql (papplname IN VARCHAR2 DEFAULT 'HELP') AS

  /* File name to save script */
  wsynsqlfile VARCHAR2 (24) := LOWER(papplname) || 'syns.sql';   wsynsqlftype UTL_FILE.FILE_TYPE;

  /* Synonyms */
  CURSOR csyn IS

     SELECT DECODE(owner,'PUBLIC',' PUBLIC ', ' ') ifpublic,
            DECODE(owner,'PUBLIC',synonym_name,
owner||'.'||synonym_name) synname,
            table_owner ||'.'|| table_name object,
            DECODE(db_link, null, ' ', '_at_'||db_link) dblink
     FROM   dba_synonyms
     WHERE  table_owner = UPPER(papplname) or
            owner = UPPER(papplname)
     ORDER BY owner, synonym_name;

BEGIN   dbms_output.enable (999999);

  if upper(papplname) = 'HELP' then
    dbms_output.put_line ('Procedure Name : GenSql.Generate_Synonym_Sql');

    dbms_output.put_line

('============================================');
    dbms_output.put_line ('Parameter(s)   : 1. User (Schema) Name');
    dbms_output.put_line ('Description    : To Generate SQL Statements

To Create Synonyms of USER');

    dbms_output.put_line ('Output File : Saved at ' || utldir ||'/***syns.sql');

    dbms_output.put_line ('.                Where *** is the username

passed in as parameter');

    dbms_output.put_line ('Note : This procedure uses utl_file - an Oracle7.3 feature!');

    return;
  end if;

  wsynsqlftype := UTL_FILE.FOPEN(utldir, wsynsqlfile, 'w');

  UTL_FILE.PUT_LINE(wsynsqlftype, 'set echo on feedback on lines 80');   UTL_FILE.PUT_LINE(wsynsqlftype, 'spool '||utldir||'/'||replace(wsynsqlfile,'.sql','.lst'));

  FOR rsyn IN csyn LOOP

    UTL_FILE.PUT_LINE(wsynsqlftype, 'create ' || rsyn.ifpublic || 'synonym ' || rsyn.synname);

    UTL_FILE.PUT_LINE(wsynsqlftype, 'for ' || rsyn.object || rsyn.dblink);

    UTL_FILE.PUT_LINE(wsynsqlftype, '/');

  END LOOP;   UTL_FILE.PUT_LINE(wsynsqlftype, 'set echo off');   UTL_FILE.PUT_LINE(wsynsqlftype, 'spool off');

  UTL_FILE.FCLOSE(wsynsqlftype);

EXCEPTION
  WHEN UTL_FILE.WRITE_ERROR THEN
       dbms_output.put_line ('Error: Utl_file.Write Error - generate_synonym_sql');

       dbms_output.put_line ('An operating system error occured during write operation');
  WHEN UTL_FILE.READ_ERROR THEN
       dbms_output.put_line ('Error: Utl_file.Read Error - generate_synonym_sql');

       dbms_output.put_line ('An operating system error occured during read operation');
  WHEN UTL_FILE.INVALID_FILEHANDLE THEN
       dbms_output.put_line ('Error: Utl_file.Invalid_Filehandle - generate_synonym_sql');

       dbms_output.put_line ('The filehandle was invalid');   WHEN UTL_FILE.INTERNAL_ERROR THEN
       dbms_output.put_line ('Error: Utl_file.Internal Error - generate_synonym_sql');

       dbms_output.put_line ('An unspecified error in PL/SQL occured - generate_synonym_sql');
  WHEN NO_DATA_FOUND THEN
       dbms_output.put_line ('Error: No data found - Check your parameters - generate_synonym_sql');
  WHEN OTHERS THEN
       dbms_output.put_line ('Error: An unhandled exception occured - generate_synonym_sql');

END;

---

---

• End of PROCEDURE generate_synonym_sql.

  ---

  ---

     perhaps it might help, thanks again.

In article <8i8a7v$20j$1_at_nnrp1.deja.com>,   charlotte_hurley_at_my-deja.com wrote:

> Thanks Sybrand,
>
>         I have downloaded and installed TOAD, it's very useful in
> showing me all the tables, indexes etc, and there is a facility to
> generate scripts to build tables, but where is the facility that can
> allow me to build Type Objects based on these tables, with attributes
> being the field names ? I wasn't able to find that one...
>
> Thank you so much,
> Charlotte Hurley
> Faro_at_connection.com
>
> In article <960843221.4180.0.pluto.d4ee154e_at_news.demon.nl>,
>   "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:
> > Download TOAD and you will have your functionality.
> >
> > Hth,
> >
> > Sybrand Bakker, Oracle DBA
> >
> > <charlotte_hurley_at_my-deja.com> wrote in message
> > news:8i3gps$kse$1_at_nnrp1.deja.com...
> > > Hello,
> > >
> > > On Oracle 8.1.6 I need to develop a PL/SQL package of routines
> > > to generate SQL scripts of all the object definitions based on the
> > > underlying tables in a database.
> > >
> > > The routines need to be built such that I can generate an object

 for
 a
> > > single named table or for all tables. The output script should  support
> > > re-generation of an object(s) by including the necessary DROP  statements

> > > for dependent items.
> > > The items being generated will include:
> > > a) the "abstract" object type
> > > b) the "abstract" object view
> > > c) the "abstract" object type body
> > > d) the "wrapper" object type
> > > e) the "wrapper" object view
> > > f) the "wrapper" object type body
> > > g) any grants required
> > >
> > > The output needs to go to a named file, presumably using the

 utl_file
> > > package. This file can then be applied by a database using SQL  Navigator

> > > or SQLPlus etc.
> > > It would be nice to have the option to have the scripts applied
> > > automatically to the database by the generator - this could

 potentially

> > > be achieved using the dbms_sql package.
> > >
> > > The idea is for the package being built to reference the data
> > > dictionary and look up the names of all the tables in the

 database,
 and
> > > create an object for each table, the attributes in this object  should

> > > simply represent every field in that table.
> > >
> > > Does anyone have a script that can do this ??? any idea ?? any
> > > help is much appreciated. Please do not hesitate to ask me for

 more

> > > information.
> > >
> > >
> > > Thank you so much,
> > > Charlotte Hurley
> > > Faro_at_connection.com
> > >
> > >
> > > Sent via Deja.com http://www.deja.com/
[Quoted] > > > Before you buy.
> >
> >
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Jun 14 2000 - 00:00:00 CEST