1600 PMP mock questions 1400 CAPM mock questions 800 SCJP 6 mock questions 600 OCAJP 7 mock questions 590 OCPJP 7 mock questions 556 SCWCD 5 mock questions 500 OCEJWCD 6 mock questions pdfDownload (java,struts, hibernet etc)

ORACLE Tutorials

Stored Procedure to check schema's between DEVELEOPMENT, STAGING and PRODUCTION environment.

CREATE OR REPLACE PROCEDURE database_health (v_output OUT VARCHAR2)
AS
CURSOR c_table
IS
SELECT TYPE, NAME, SCHEMA
FROM matm_table_list
ORDER BY TYPE, NAME;

v_count NUMBER (20);
v_sql_stmt VARCHAR2 (1000);
v_space_string VARCHAR2 (100);
v_index NUMBER (15);
v_this_constraint VARCHAR2 (30);
v_prev_constraint VARCHAR2 (30);
v_count_constraints NUMBER (15);
v_search_condition VARCHAR2 (2000);
v_tablespace_name VARCHAR2 (50);
v_index_name VARCHAR2 (50);
v_prev_index_name VARCHAR2 (50);
v_table_name VARCHAR2 (50);
v_this_column VARCHAR2 (50);
v_r_constraint_name VARCHAR2 (50);
v_request VARCHAR2 (1);
v_schema VARCHAR2 (30);
v_data_type VARCHAR2 (30);
v_data_length VARCHAR2 (30);
v_data_precision VARCHAR2 (30);
v_nullable VARCHAR2 (2);
v_split VARCHAR2 (30);
v_split_b VARCHAR2 (30);
v_description VARCHAR2 (500);
v_trigger_body VARCHAR2 (500);

TYPE cur_type IS REF CURSOR;

c cur_type;

TYPE customer IS TABLE OF VARCHAR2 (100)
INDEX BY BINARY_INTEGER;

empty_tables customer;
v_char CHAR (1);
BEGIN
v_index := 1;
v_output := '--- DATABASE HEALTH TEXT ---' || CHR (10) || CHR (10);

FOR table_rec IN c_table
LOOP
v_table_name := table_rec.NAME;
v_request := table_rec.TYPE;
v_schema := table_rec.SCHEMA;

BEGIN
v_sql_stmt := 'SELECT COUNT(*) FROM ' || table_rec.NAME;

EXECUTE IMMEDIATE v_sql_stmt
INTO v_count;

DBMS_OUTPUT.put_line ( CHR (10)
|| 'Object dependency report for '
|| table_rec.NAME
|| ' '
|| CHR (10)
);
DBMS_OUTPUT.put_line ( 'Table: '
|| table_rec.NAME
|| CHR (9)
|| 'Number of rows '
|| v_count
|| CHR (10)
);

IF v_count = 0
THEN
empty_tables (v_index) := table_rec.NAME;
v_index := v_index + 1;
END IF;
EXCEPTION
WHEN OTHERS
THEN
p_log_error (TO_CHAR (SYSDATE, 'DD-MON-YYYY'),
'500',
'INTERNAL EXCEPTION',
TO_CHAR (SQLCODE),
SUBSTR (SQLERRM, 1, 200),
'Error selecting count from ' || table_rec.NAME,
'P_DATABASE_HEALTH'
);
END;

BEGIN
v_sql_stmt :=
'SELECT column_name, data_type, data_length, data_precision, nullable from all_tab_columns where table_name=:1 and owner=:2';

IF v_request = 'A'
THEN
OPEN c FOR v_sql_stmt USING table_rec.NAME, table_rec.SCHEMA;

LOOP
FETCH c
INTO v_this_column, v_data_type, v_data_length,
v_data_precision, v_nullable;

EXIT WHEN c%NOTFOUND;

IF LENGTH (v_this_column) < 8
THEN
v_split := CHR (9) || CHR (9) || CHR (9) || CHR (9);
ELSIF LENGTH (v_this_column) < 16
THEN
v_split := CHR (9) || CHR (9) || CHR (9);
ELSIF LENGTH (v_this_column) < 24
THEN
v_split := CHR (9) || CHR (9);
ELSE
v_split := CHR (9);
END IF;

IF LENGTH (v_data_type) < 8
THEN
v_split_b := CHR (9) || CHR (9);
ELSE
v_split_b := CHR (9);
END IF;

DBMS_OUTPUT.put_line ( v_this_column
|| v_split
|| v_data_type
|| v_split_b
|| 'L:'
|| v_data_length
|| CHR (9)
|| 'P:'
|| v_data_precision
|| CHR (9)
|| 'Null?:'
|| v_nullable
);
END LOOP;

v_output := v_output || CHR (10);

CLOSE c;
END IF;
EXCEPTION
WHEN OTHERS
THEN
p_log_error (TO_CHAR (SYSDATE, 'DD-MON-YYYY'),
'500',
'INTERNAL EXCEPTION',
TO_CHAR (SQLCODE),
SUBSTR (SQLERRM, 1, 200),
'Error getting column information from '
|| table_rec.NAME,
'P_DATABASE_HEALTH'
);
END;

BEGIN
v_char := 'P';
v_sql_stmt :=
'SELECT a.constraint_name, b.column_name FROM all_constraints a, all_cons_columns b';
v_sql_stmt :=
v_sql_stmt
|| ' WHERE a.constraint_name=b.constraint_name AND a.owner=b.owner AND a.table_name=:1 AND a.constraint_type=:2 AND a.owner=:3';

IF v_request = 'A'
THEN
OPEN c FOR v_sql_stmt USING table_rec.NAME, v_char, v_schema;

LOOP
FETCH c
INTO v_this_constraint, v_this_column;

EXIT WHEN c%NOTFOUND;

IF v_this_constraint = v_prev_constraint
THEN
DBMS_OUTPUT.put_line ( CHR (9)
|| CHR (9)
|| CHR (9)
|| CHR (9)
|| ' and column '
|| v_this_column
);
ELSE
DBMS_OUTPUT.put_line ( 'Primary Key constraint '
|| v_this_constraint
|| CHR (9)
|| ' '
|| ' based on column '
|| v_this_column
);
END IF;

v_prev_constraint := v_this_constraint;
END LOOP;

v_output := v_output || CHR (10);

CLOSE c;
END IF;
EXCEPTION
WHEN OTHERS
THEN
p_log_error (TO_CHAR (SYSDATE, 'DD-MON-YYYY'),
'501',
'INTERNAL EXCEPTION',
TO_CHAR (SQLCODE),
SUBSTR (SQLERRM, 1, 200),
'Error selecting from all_constraints',
'P_DATABASE_HEALTH'
);
END;

BEGIN
v_char := 'R';
v_sql_stmt :=
'SELECT a.constraint_name, column_name, r_constraint_name from all_cons_columns a, all_constraints b ';
v_sql_stmt :=
v_sql_stmt
|| 'WHERE a.constraint_name=b.constraint_name and a.owner=b.owner ';
v_sql_stmt :=
v_sql_stmt
|| 'AND b.table_name=:1 and constraint_type=:2 AND a.owner=:3';

IF v_request = 'A'
THEN
OPEN c FOR v_sql_stmt USING table_rec.NAME, v_char, v_schema;

LOOP
FETCH c
INTO v_this_constraint, v_this_column, v_r_constraint_name;

EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.put_line ( 'Foreign Key constraint: '
|| v_this_constraint
|| ' references column '
|| v_this_column
|| ' via constraint '
|| v_r_constraint_name
);
END LOOP;

v_output := v_output || CHR (10);

CLOSE c;
END IF;
EXCEPTION
WHEN OTHERS
THEN
p_log_error
(TO_CHAR (SYSDATE, 'DD-MON-YYYY'),
'503',
'INTERNAL EXCEPTION',
TO_CHAR (SQLCODE),
SUBSTR (SQLERRM, 1, 200),
'Error selecting from all_constraints and all_cons_columns where constraint_type='
|| v_this_constraint,
'P_DATABASE_HEALTH'
);
END;

BEGIN
v_char := 'U';
v_sql_stmt :=
'SELECT a.constraint_name, column_name from all_cons_columns a, all_constraints b ';
v_sql_stmt :=
v_sql_stmt
|| 'WHERE a.constraint_name=b.constraint_name and a.owner=b.owner ';
v_sql_stmt :=
v_sql_stmt
|| 'AND b.table_name=:1 and constraint_type=:2 AND a.owner=:3';

IF v_request = 'A'
THEN
OPEN c FOR v_sql_stmt USING table_rec.NAME, v_char, v_schema;

LOOP
FETCH c
INTO v_this_constraint, v_this_column;

EXIT WHEN c%NOTFOUND;

IF v_this_constraint = v_prev_constraint
THEN
DBMS_OUTPUT.put_line ( CHR (9)
|| CHR (9)
|| CHR (9)
|| 'and this column '
|| v_this_column
);
ELSE
DBMS_OUTPUT.put_line ( 'Unique constraint '
|| v_this_constraint
|| ' based on column '
|| v_this_column
);
END IF;

v_prev_constraint := v_this_constraint;
END LOOP;

v_output := v_output || CHR (10);

CLOSE c;
END IF;

v_output := v_output || CHR (10) || CHR (10);
EXCEPTION
WHEN OTHERS
THEN
p_log_error
(TO_CHAR (SYSDATE, 'DD-MON-YYYY'),
'503',
'INTERNAL EXCEPTION',
TO_CHAR (SQLCODE),
SUBSTR (SQLERRM, 1, 200),
'Error selecting from all_constraints and all_cons_columns where constraint_type='
|| v_this_constraint,
'P_DATABASE_HEALTH'
);
END;

BEGIN
v_char := 'C';
v_sql_stmt :=
'SELECT search_condition FROM all_constraints WHERE table_name=:1 AND constraint_type=:2 AND owner=:3';

IF v_request = 'A'
THEN
OPEN c FOR v_sql_stmt USING table_rec.NAME, v_char, v_schema;

LOOP
FETCH c
INTO v_search_condition;

EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.put_line
( 'Constraint definition text for table '
|| table_rec.NAME
|| ' is '
|| v_search_condition
);
END LOOP;

v_output := v_output || CHR (10);

CLOSE c;
END IF;
EXCEPTION
WHEN OTHERS
THEN
p_log_error (TO_CHAR (SYSDATE, 'DD-MON-YYYY'),
'502',
'INTERNAL EXCEPTION',
TO_CHAR (SQLCODE),
SUBSTR (SQLERRM, 1, 200),
'Error selecting from all_constraints for '
|| table_rec.NAME,
'P_DATABASE_HEALTH'
);
END;

BEGIN
v_sql_stmt :=
'SELECT a.index_name, a.tablespace_name, b.column_name ';
v_sql_stmt :=
v_sql_stmt
|| 'FROM all_indexes a, all_ind_columns b WHERE a.table_name=b.table_name ';
v_sql_stmt :=
v_sql_stmt
|| 'AND a.table_owner=b.table_owner AND a.index_name=b.index_name ';
v_sql_stmt := v_sql_stmt || 'AND a.table_name=:1 ';
v_sql_stmt :=
v_sql_stmt || 'AND a.table_owner=:2 ORDER BY index_name';

IF v_request = 'A'
THEN
OPEN c FOR v_sql_stmt USING table_rec.NAME, v_schema;

LOOP
FETCH c
INTO v_index_name, v_tablespace_name, v_this_column;

EXIT WHEN c%NOTFOUND;

IF v_index_name = v_prev_index_name
THEN
DBMS_OUTPUT.put_line ('Index Name ' || v_index_name);
ELSE
DBMS_OUTPUT.put_line ( 'Index name:'
|| v_index_name
|| ' located in tablespace '
|| v_tablespace_name
|| ' based on column '
|| v_this_column
);
END IF;

v_prev_index_name := v_index_name;
END LOOP;

v_output := v_output || CHR (10);

CLOSE c;
END IF;
EXCEPTION
WHEN OTHERS
THEN
p_log_error
(TO_CHAR (SYSDATE, 'DD-MON-YYYY'),
'502',
'INTERNAL EXCEPTION',
TO_CHAR (SQLCODE),
SUBSTR (SQLERRM, 1, 200),
'Error selecting from all_indexes, all_ind_columns for '
|| v_index_name,
'P_DATABASE_HEALTH'
);
END;

BEGIN
v_sql_stmt :=
'SELECT description, trigger_body from user_triggers WHERE table_name=:1';

OPEN c FOR v_sql_stmt USING table_rec.NAME;

LOOP
FETCH c
INTO v_description, v_trigger_body;

EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.put_line ('Trigger: ' || v_description);
DBMS_OUTPUT.put_line (v_trigger_body);
END LOOP;

v_output := v_output || CHR (10);

CLOSE c;
EXCEPTION
WHEN OTHERS
THEN
p_log_error (TO_CHAR (SYSDATE, 'DD-MON-YYYY'),
'502',
'INTERNAL EXCEPTION',
TO_CHAR (SQLCODE),
SUBSTR (SQLERRM, 1, 200),
'Error selecting from user_triggers for '
|| table_rec.NAME,
'P_DATABASE_HEALTH'
);
END;

DBMS_OUTPUT.put_line
('=====================================================');
END LOOP;

IF v_index > 1
THEN
DBMS_OUTPUT.put_line ( CHR (10)
|| 'The following tables have no rows in '
|| CHR (10)
);

FOR ELEMENT IN 1 .. empty_tables.COUNT
LOOP
DBMS_OUTPUT.put_line (empty_tables (ELEMENT) || CHR (10));
END LOOP;

DBMS_OUTPUT.put_line ( 'Number of empty tables = '
|| TO_CHAR (empty_tables.COUNT)
|| CHR (10)
);
END IF;
EXCEPTION
WHEN OTHERS
THEN
p_log_error (TO_CHAR (SYSDATE, 'DD-MON-YYYY'),
'500',
'INTERNAL EXCEPTION',
TO_CHAR (SQLCODE),
SUBSTR (SQLERRM, 1, 200),
'Unknown error!',
'P_DATABASE_HEALTH'
);
END;
/

Reviews and Comments


PMP, CAPM, PMI is a registered certification mark of the Project Management Institute, Inc

Copyright © www.techfaq360.com 2016


About US | Contact US | Privacy Policy | Terms and Conditions  | Website disclaimer  | Cancellation and Refund Policy  | Shipping & Delivery Policy