/**************************************************************************************
Procedure Name : sp_dyn_create_ing_view
Returns : nothing
Uses Tables : user_nested_ing_extension and whatever other table gets passed to it
via argument.
Calls Fns : none
Called by : create_ing_view_all
Description : This procedure creates a view dynamically based off of the passed table
and user specified name. It is joined to the material and ingredient id
from user_nested_ing_extensions. These views will be primarily used
for rules.
Created On : 3/5/02
Created By : Mendo
**************************************************************************************/
CREATE OR REPLACE PROCEDURE sp_dyn_create_ing_view
( ais_table_name VARCHAR2,
ais_ing_view_name VARCHAR2, // NULL if you don't want an ingredient extension view.
ais_ing_ext_view_name VARCHAR2) // NULL if you don't want an ingredient view.
AS
// Local variable instantiation
ls_col VARCHAR2(30);
ls_concat_cols1 VARCHAR2(4000);
ls_concat_cols2 VARCHAR2(4000);
ls_concat_cols3 VARCHAR2(4000);
ls_table_exists VARCHAR2(15);
cursor_handle INTEGER;
sql_feedback INTEGER;
error_feedback EXCEPTION;
// Cursor to retrieve all columns for the passed table.
CURSOR c_get_columns IS
SELECT cname
FROM sys.col
WHERE LOWER(tname) = LOWER(ais_table_name) AND
LOWER(cname) <> 'material_id'
ORDER BY colno;
// Retrieve from sys.col to see if the passed table exists.
CURSOR c_table_exists IS
SELECT 'TRUE'
FROM sys.col
WHERE LOWER(tname) = LOWER(ais_table_name);
BEGIN
// Must intialize to a ' '. If not, the length() conditional below will always fail.
ls_concat_cols1 := ' ';
ls_concat_cols2 := ' ';
OPEN c_table_exists;
FETCH c_table_exists INTO ls_table_exists;
CLOSE c_table_exists;
// If the table doesn't exist, skip the view creation logic.
IF ls_table_exists IS NOT NULL THEN
// Open our cursor and loop to concat all the columns.
OPEN c_get_columns;
LOOP
FETCH c_get_columns INTO ls_col;
EXIT WHEN c_get_columns%notfound;
// If we are nearing the end of the length limit for our variable, move onto variable 2,
// and then variable 3.
// 1) column limit per table is 255
// 2) each column name can be 30 characters
// 3) 770 chars max for ',x.'
// I.E. 255 * 30 + 255 * 3 = 8415
IF LENGTH(ls_concat_cols1) < 3969 THEN
ls_concat_cols1 := ls_concat_cols1 || ',x.' || ls_col;
ELSIF LENGTH(ls_concat_cols2) < 3969 THEN
ls_concat_cols2 := ls_concat_cols2 || ',x.' || ls_col;
ELSE
ls_concat_cols3 := ls_concat_cols3 || ',x.' || ls_col;
END IF;
END LOOP;
CLOSE c_get_columns;
// Strip off the comma at the beginning of concat_cols1 if it is not null.
// Start at char(3) since the beginning will look like: ' ,x.column_name'
ls_concat_cols1 := SUBSTR(ls_concat_cols1, 3);
// Only process the view if the user passes an ingredient (non nested) view name.
IF ais_ing_view_name IS NOT NULL THEN
cursor_handle := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE (cursor_handle,
'CREATE OR REPLACE VIEW ' || ais_ing_view_name || ' AS SELECT i.material_id AS material_id, i.ingredient_id AS ingredient_id,' ||
ls_concat_cols1 || ls_concat_cols2 || ls_concat_cols3 || ' FROM pmcat_ingredients i,' || ais_table_name ||
' x WHERE i.ingredient_id = x.material_id',
DBMS_SQL.NATIVE);
DBMS_SQL.CLOSE_CURSOR (cursor_handle);
END IF;
// Only process the view if the user passes an ingredient extension view name.
IF ais_ing_ext_view_name IS NOT NULL THEN
cursor_handle := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE (cursor_handle,
'CREATE OR REPLACE VIEW ' || ais_ing_ext_view_name || ' AS SELECT i.material_id AS material_id, i.ingredient_id AS ingredient_id,' ||
ls_concat_cols1 || ls_concat_cols2 || ls_concat_cols3 || ' FROM user_nested_ing_extensions i,' || ais_table_name ||
' x WHERE i.ingredient_id = x.material_id',
DBMS_SQL.NATIVE);
DBMS_SQL.CLOSE_CURSOR (cursor_handle);
END IF;
END IF;
// Used only for testing purposes. You can change the error message to display whatever you want.
EXCEPTION
WHEN error_feedback THEN
RAISE_APPLICATION_ERROR (-20001, 'Table does not exists. Press Yes to continue to the next views.');
END;^
Facebook
Twitter