metadata_makes_tables.sql

CREATE OR REPLACE PACKAGE meta_model_pck
IS
	PROCEDURE create_table(p_schema_name IN VARCHAR2, p_table_name IN VARCHAR2);
	PROCEDURE create_table_constraint(p_schema_name IN VARCHAR2, p_table_name IN VARCHAR2, p_constraint_name IN VARCHAR2);
	PROCEDURE main;
END meta_model_pck;
/

CREATE OR REPLACE PACKAGE BODY meta_model_pck
IS
	-- CREATE OR REPLACE
	FUNCTION existe_tabla(p_schema_name IN VARCHAR2, p_table_name IN VARCHAR2) RETURN BOOLEAN
	IS
		v_cursor INTEGER := dbms_sql.open_cursor;
	BEGIN
		dbms_sql.parse(v_cursor, 'SELECT table_name FROM USER_TABLES WHERE table_name = :b_table_name', dbms_sql.native);
		dbms_sql.bind_variable(v_cursor, 'b_table_name', p_table_name);
		IF dbms_sql.execute_and_fetch(v_cursor) > 0 THEN
			RETURN TRUE;
		ELSE
			RETURN FALSE;
		END IF;
	END existe_tabla;

	-- CREATE OR REPLACE
	FUNCTION existe_constraint(p_schema_name IN VARCHAR2, p_constraint_name IN VARCHAR2) RETURN BOOLEAN
	IS
		v_cursor INTEGER := dbms_sql.open_cursor;
	BEGIN
		dbms_sql.parse(v_cursor, 'SELECT constraint_name FROM USER_CONSTRAINTS WHERE constraint_name = :b_constraint_name', dbms_sql.native);
		dbms_sql.bind_variable(v_cursor, 'b_constraint_name', p_constraint_name);
		IF dbms_sql.execute_and_fetch(v_cursor) > 0 THEN
			RETURN TRUE;
		ELSE
			RETURN FALSE;
		END IF;
	END existe_constraint;

	-- CREATE OR REPLACE
	PROCEDURE create_table(p_schema_name IN VARCHAR2, p_table_name IN VARCHAR2)
	IS
		v_cursor_create_table INTEGER := dbms_sql.open_cursor;
		v_ddl VARCHAR2(400) := '';
		v_row_processed INTEGER;
	BEGIN
		-- Recorremos el SELECT a model_columns con un cursor implicito para construir el DDL
		FOR v_columns IN (SELECT * FROM test.model_columns WHERE table_name=p_table_name order by position) LOOP
			v_ddl := v_ddl || v_columns.column_name || ' ' || v_columns.data_type || '(' || v_columns.data_length || ')';
	
		IF v_columns.data_type = 'DATE' THEN
			v_ddl := substr(v_ddl, 0, length(v_ddl)-3);
		END IF;
	
			IF NOT v_columns.nullable = 'N' THEN
				v_ddl := v_ddl || ' NOT NULL,';
			ELSE
				v_ddl := v_ddl || ',';
			END IF;
		END LOOP;
		v_ddl := substr(v_ddl, 0, length(v_ddl)-1);
		v_ddl := '(' || v_ddl || ')';
		v_ddl := 'CREATE TABLE ' || p_table_name || ' ' || v_ddl;

		-- Ejecutamos la sentencia DDL que previamente construimos para crear la tabla
		dbms_sql.parse(v_cursor_create_table, v_ddl, dbms_sql.native);
		v_row_processed := dbms_sql.execute(v_cursor_create_table);
	END create_table;

	-- CREATE OR REPLACE
	PROCEDURE create_table_constraint(p_schema_name IN VARCHAR2, p_table_name IN VARCHAR2, p_constraint_name IN VARCHAR2)
	IS
		v_cursor_create_cons INTEGER := dbms_sql.open_cursor;
		v_column_name model_cons_columns.column_name%TYPE;
		v_constraint_type model_constraints.constraint_type%TYPE;
		v_ddl VARCHAR2(400) := '';
		v_r_schema_owner VARCHAR2(50);
		v_r_constraint_name VARCHAR2(50);
		v_reference_table VARCHAR2(50);
		v_row_processed INTEGER;
	BEGIN
		SELECT column_name, constraint_type, r_schema_owner, r_constraint_name
		INTO  v_column_name, v_constraint_type, v_r_schema_owner, v_r_constraint_name
		FROM model_cons_columns NATURAL JOIN model_constraints
		WHERE constraint_name = p_constraint_name;

		IF v_constraint_type = 'P' THEN
			v_ddl := 'ALTER TABLE ' || p_table_name || ' ADD CONSTRAINT ' || p_constraint_name || ' PRIMARY KEY ' || ' ( ' ||		v_column_name || ')';
		ELSE
			SELECT table_name
			INTO v_reference_table
			FROM USER_CONSTRAINTS
			WHERE constraint_name = v_r_constraint_name;
			v_ddl := 'ALTER TABLE ' || p_table_name || ' ADD CONSTRAINT ' || p_constraint_name || ' FOREIGN KEY ' || '(' ||  v_column_name || ')' || ' REFERENCES ' || v_reference_table || '(' || v_column_name || ')';
		END IF;

		dbms_sql.parse(v_cursor_create_cons, v_ddl, dbms_sql.native);
		v_row_processed := dbms_sql.execute(v_cursor_create_cons);
	END create_table_constraint;

	-- CREATE OR REPLACE
	PROCEDURE main
	IS
		v_schema VARCHAR2(50);
		v_table_name VARCHAR2(50);
		v_constraint_name VARCHAR2(50);
		CURSOR c_cursor_tables IS SELECT 'schema', table_name FROM model_tables;
		CURSOR c_cursor_constraint(p_table_name VARCHAR2) IS SELECT constraint_name FROM model_cons_columns WHERE table_name = p_table_name;
		table_already_exist EXCEPTION;
		constraint_already_exist EXCEPTION;
	BEGIN
		OPEN c_cursor_tables;
		LOOP
			FETCH c_cursor_tables INTO v_schema, v_table_name;
			EXIT WHEN c_cursor_tables%NOTFOUND;
			IF existe_tabla(v_schema, v_table_name) THEN
				RAISE table_already_exist;
			ELSE
				create_table(v_schema, v_table_name);
			END IF;

			OPEN c_cursor_constraint(v_table_name);
			LOOP
				FETCH c_cursor_constraint INTO v_constraint_name;
				EXIT WHEN c_cursor_constraint%NOTFOUND;
				IF existe_constraint(v_schema, v_constraint_name) THEN
					RAISE constraint_already_exist;
				ELSE
					create_table_constraint(v_schema, v_table_name, v_constraint_name);
				END IF;
			END LOOP;
			CLOSE c_cursor_constraint;
		END LOOP;
		CLOSE c_cursor_tables;
	EXCEPTION
		WHEN table_already_exist THEN
			dbms_output.put_line('La tabla ya existe');
		WHEN constraint_already_exist THEN
			dbms_output.put_line('La restriccion de integridad ya existe');
	END main;

END meta_model_pck;
/