Upper-case double quoted objectnames

This script might come in handy if you also didn’t want the free double quoted upper-case object names in the object scripts on the DB, which you get importing and exporting a schema.

The script goes trough the user_source view and reconstructs a file with scripts to create or replace your current objects (packages, types, functions, procedures and triggers).
While constructing the file the first line of each script is altered to the way I wanted it in this case:

 CREATE OR REPLACE <TYPE> <objectname>

The script assumes that the objects all have a certain prefix.

SET heading OFF
SET feedback OFF
SET VERIFY OFF
SET echo OFF
SET termout OFF
SET pages 0
SET linesize 225
SET define ON

COLUMN sort1 noprint
COLUMN sort2 noprint
COLUMN sort3 noprint

-->> ADJUST PREFIX
define schema_pfx="'UBS'"

spool old_sources.sql

prompt SET termout ON
prompt SET heading ON
prompt SET verify ON
prompt SET feedback ON
prompt SET trimspool ON
prompt SET define OFF
prompt
prompt spool old_sources.log
prompt

SELECT name sort1
,      type sort2
,      line sort3
,      CASE WHEN line = 1
            THEN 'PROMPT' || CHR (13)
              || 'PROMPT Create/alter ' || TYPE || ' ' || NAME || ' ...' || CHR (13)
              || 'CREATE OR REPLACE ' || text
            --
            WHEN line = last_line
            THEN CHR (13) || '/' || CHR (13)
            --
            ELSE text
       END new_text
FROM  (
        SELECT t.*
        ,      max(line) over(PARTITION BY name, type) last_line
        FROM   user_source t
        WHERE  NAME LIKE &schema_pfx || '_%'
        AND    TYPE IN ('TYPE', 'TYPE BODY', 'PROCEDURE', 'FUNCTION','PACKAGE', 'PACKAGE BODY')
      )
ORDER  BY sort1
        , sort2
        , sort3
/

prompt spool off

spool off

spool new_sources.sql

prompt SET termout ON
prompt SET heading ON
prompt SET verify ON
prompt SET feedback ON
prompt SET trimspool ON
prompt SET define OFF
prompt
prompt spool new_sources.log
prompt

SELECT NAME sort1
,      TYPE sort2
,      line sort3
,      CASE WHEN  line = 1
            THEN  'PROMPT' || CHR (13)
               || 'PROMPT Create/alter ' || TYPE || ' ' || NAME || ' ...' || CHR (13)
               || 'CREATE OR REPLACE '
               || CASE WHEN  quote1 > 0 -- in case of "SCHEMA"."OBJECTNAME"
                         AND quote4 > 0
                       THEN REPLACE( text
                                   , SUBSTR( text, quote1, (quote4-quote1)+1 )
                                   , LOWER( NAME ) )
                       --
                       WHEN  quote1 > 0 -- in case of "OBJECTNAME"
                         AND quote2 > 0
                       THEN REPLACE( text, SUBSTR( text, quote1, (quote2-quote1)+1 ), LOWER( name ) )
                       --
                       ELSE  -- no quotes, objectname maybe uppercase
                            REPLACE( text, name, LOWER( name ) )
                  END
            --
            WHEN line = last_line -- last line contains code
              AND LENGTH( TRIM( replace( replace( text, CHR (13), ''), CHR (10), '') )) > 0
            THEN ltrim(text) || CHR (13) || '/' || CHR (13)
            --
            WHEN line = last_line -- last line contains no actual code
            THEN '/' || CHR (13)
            --
            ELSE text
       END new_text
FROM  (
        SELECT r.*
        ,      max(line) over(PARTITION BY name, type) last_line
        FROM  (
                SELECT t.*
                ,      INSTR( text, CHR (34),1, 1 ) quote1
                ,      INSTR( text, CHR (34),1, 2 ) quote2
                ,      INSTR( text, CHR (34),1, 4 ) quote4
                FROM   user_source t
                WHERE  NAME LIKE &schema_pfx || '_%'
                AND    TYPE IN ('TYPE', 'TYPE BODY', 'PROCEDURE', 'FUNCTION','PACKAGE', 'PACKAGE BODY')
              ) r
      )
ORDER  BY sort1
        , sort2
        , sort3
/

prompt spool off

spool off

undefine schema_pfx

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>