pl/sql help,urgent

[mkey]

Hi all,

I need to check if a set of views exist in the dictionary.
My code is as follows;
Basically, I'm selecting list of view_names from a table where the names are stored.
Then I need to check if those views exist in the data dictionary. I have to use all_objects dictionary b/c of privileges. Once I find the views that do not exist in the data dictionary I need to concatenate all of them in one string and return to the calling app.
I'm sort of stuck in the part where I have to find the views that do not exist and return as a string.

SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE VIEW_CHECK
IS
FLAG NUMBER := 0;
A_VIEWNAME VARCHAR2(100);
B_VIEWNAME VARCHAR2(100);
C_VIEWNAME VARCHAR2(100);
D_VIEWNAME VARCHAR2(100);

CURSOR C_ONE IS SELECT DISTINCT TABLE_NAME FROM table_a
WHERE FEATURE_TYPE IN ('LINEAR','CLINEAR','POINT')
ORDER BY TABLE_NAME;

BEGIN
OPEN C_ONE;
LOOP
FETCH C_ONE INTO A_VIEWNAME;
B_VIEWNAME := CHR(39)||'V_'||A_VIEWNAME||CHR(39);

EXIT WHEN C_ONE%NOTFOUND;
BEGIN
SELECT OBJECT_NAME INTO C_VIEWNAME
FROM ALL_OBJECTS
WHERE OBJECT_NAME = B_VIEWNAME
AND OBJECT_TYPE = 'VIEW'
AND OWNER = 'schema1';
FLAG := 1;


EXCEPTION
WHEN NO_DATA_FOUND then
FLAG := 0;
END;
D_VIEWNAME := CHR(39)||'V_'||C_VIEWNAME||CHR(39);
DBMS_OUTPUT.PUT_LINE('VIEWS NOT FOUND: '||B_VIEWNAME);
END LOOP;

CLOSE C_ONE;

END;
/
Any thoughts or help would be appreciated.
Thanks again!

 

[sem]

First of all the view owner may hardly be 'schema1' (lowercase). Then, you may make it in one query:

SELECT DISTINCT TABLE_NAME FROM table_a
WHERE FEATURE_TYPE IN ('LINEAR','CLINEAR','POINT')
and not exists (select 1 from all_objects
WHERE OBJECT_NAME = TABLE_A.TABLE_NAME
AND OBJECT_TYPE = 'VIEW'
AND OWNER = 'SCHEMA1')
ORDER BY TABLE_NAME

I do not understand the meaning of A_VIEWNAME, B_VIEWNAME, C_VIEWNAME (as I suppose they must have the same value) and all that manipulations with quotation marks. The query above returns all the names you need.

I also suppose that VARCHAR2(100) is too small for this list.
And at last, can you provide a bit more information on the list structure? Shall it be comma/dot/tab/anything else separated, shall it contain quoted names etc.