Utility to generate DB Object scripts –

Hi,

Caution: Work-in-Progress

Trying to develop an utility to generate DB Object scripts customized for my personal use.

This might fall in the category called *reinventing-the-wheel* as DBMS_METADATA package does a very similar work, but not same.

Let’s see how it goes:

DECLARE
	TYPE curtyp IS REF CURSOR;

	--------------------------------------- 
	--	declaring all Nested Table types 
	---------------------------------------
	TYPE colname_ntt IS TABLE OF all_tab_columns.column_name%TYPE;
	TYPE tabname_ntt IS TABLE OF all_tab_columns.table_name%TYPE;
	TYPE data_typ_len_ntt IS TABLE OF VARCHAR2(20);

	v_tab_name VARCHAR2(50 CHAR) := 'TABLE_1,TABLE_2,ranit_biswas';

	table_not_found EXCEPTION;
	---------------------------------------
	curs curtyp;
	cols colname_ntt;
	tabs tabname_ntt;
	data_typ_len data_typ_len_ntt; 
	---------------------------------------
	v_table_exists NUMBER := 0;
	

BEGIN

	FOR x IN (
	  SELECT RTRIM(REGEXP_SUBSTR(v_tab_name,'(,?)([^,]*)(,?)',1,LEVEL),',') v_tab_name1
	  FROM dual
	  CONNECT BY LEVEL <= (LENGTH(v_tab_name)-LENGTH(REPLACE(v_tab_name,',')))+1
	)
	LOOP
		BEGIN
			SELECT COUNT(1) INTO v_table_exists
			FROM USER_TABLES
			WHERE table_name = x.v_tab_name1;

				IF(v_table_exists  0) THEN
					OPEN curs FOR
					SELECT 
						table_name, 
						column_name,
						data_type||'('||data_length||')' 
					FROM all_tab_columns
					WHERE table_name = x.v_tab_name1;

					FETCH curs 
					BULK COLLECT INTO tabs, cols, data_typ_len;
					CLOSE curs;

					DBMS_OUTPUT.PUT_LINE('PROMPT CREATE TABLE '||LOWER(x.v_tab_name1));
					DBMS_OUTPUT.PUT_LINE('CREATE TABLE '||LOWER(x.v_tab_name1)||'(');

					FOR i IN tabs.first .. tabs.last
					LOOP
						DBMS_OUTPUT.PUT_LINE(
							CHR(9)||CHR(9)||(CASE WHEN i  tabs.FIRST THEN ', ' END)||LOWER(cols(i))||
							CHR(9)||(
                CASE 
                  WHEN (data_typ_len(i) LIKE '%DATE%') THEN 'DATE' 
                  WHEN (data_typ_len(i) LIKE '%NUMBER%') THEN 'NUMBER'
                  ELSE data_typ_len(i) 
                END
              ) 
						);
					END LOOP;

					DBMS_OUTPUT.PUT_LINE(');'||CHR(10)||CHR(10));
				
				ELSE
					RAISE table_not_found;
				END IF;
		EXCEPTION
		WHEN table_not_found THEN
			DBMS_OUTPUT.PUT_LINE(
'
---+----------------------------------------------------------------------------------------------------------+
---+--'||CHR(9)||'Table: ['||UPPER(x.v_tab_name1)||'] is not found.
---+----------------------------------------------------------------------------------------------------------+');
		END;
	END LOOP;

END;

END;

But there are several pending action items which makes it *Work-in-Progress*.
– Have to merge the “Index Creation” logic and “Alter Table” to add Primary Keys.
– Foreign Key relationship

-----------------------------------------------------------
-- PRIMARY KEY
-----------------------------------------------------------
WITH pkey AS(
    SELECT 
      LOWER(table_name) tbl_name,
      LOWER(index_name) idx_name,
      LISTAGG(CHR(9)||LOWER(column_name),','||CHR(10)) WITHIN GROUP (ORDER BY column_position) lst
    FROM user_ind_columns uic
    WHERE 
      EXISTS(
        SELECT 1 FROM user_constraints uc
        WHERE 
          uic.index_name = uc.constraint_name
          AND uc.constraint_type = 'P'
      )
    GROUP BY table_name,index_name
)
SELECT 
'
ALTER TABLE '||tbl_name||'
ADD CONSTRAINT '||idx_name||' PRIMARY KEY('
||CHR(10)||LST||CHR(10)||
');'
FROM pkey
WHERE UPPER(tbl_name) = ''; 



-----------------------------------------------------------
-- INDEXES // to be re-structured
-----------------------------------------------------------
WITH pkey AS(
    SELECT 
      LOWER(table_name) tbl_name,
      LOWER(index_name) idx_name,
      LISTAGG(CHR(9)||LOWER(column_name),','||CHR(10)) WITHIN GROUP (ORDER BY column_position) lst
    FROM user_ind_columns uic
    WHERE 
      NOT EXISTS(
        SELECT 1 FROM user_constraints uc
        WHERE 
          uic.index_name = uc.constraint_name
          AND uc.constraint_type = 'P'
      )
    GROUP BY table_name,index_name
)
SELECT 
'
CREATE INDEX '||idx_name||CHR(10)||' ON '||tbl_name||' ( 
  '||lst||'
);'
FROM pkey
WHERE UPPER(tbl_name) = ''; 



-----------------------------------------------------------
-- FOREIGN KEYS // yet to start
-----------------------------------------------------------

Thanks and Regards,
— Ranit

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: