I've been working on this for some time now, and I cant get it to work.
Basically Im trying to compare one Table with another. One of the tables is owned by one user, and the other is by me. The point of this liitle programme is to compare the two tables, and ensure that my table is the same as the other users. (Not worried about primary keys just yet.)
Ok so what I have.
this Query:
SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH FROM ALL_TAB_COLUMNS WHERE OWNER = 'SOMEUSER' AND TABLE_NAME = 'STUDENT' MINUS SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH FROM ALL_TAB_COLUMNS WHERE OWNER = 'ME' AND TABLE_NAME = 'STUDENT';
Now the difference between the two tables, is that I am missing one field, yet the results select 6 ROWS, so I think the Query is wrong.
The next is my actual code:
Here is the code Im working with, I left out some simple things, but the main content is there.
CURSOR simple_cursor IS select TABLE_NAME from USER_TABLES;
CURSOR checkCol_cursor IS SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH FROM ALL_TAB_COLUMNS WHERE OWNER = 'SOMEUSER' AND TABLE_NAME = '||tableName||' MINUS SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH FROM ALL_TAB_COLUMNS WHERE OWNER = 'ME' AND TABLE_NAME = '||tableName||';
tableName ALL_TABLES.TABLE_NAME%TYPE
;
Begin
OPEN simple_cursor;
Loop
FETCH simple_cursor INTO tableName;
EXIT WHEN simple_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Tabl
e Name ::' || tableName);
FOR r_checkCol IN checkCol_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Insi
de lOOp 2');
DBMS_OUTPUT.PUT_LINE('Crea
ting New column for :: ' || tableName);
IF (r_checkCol.COLUMN_NAME='C
HAR')
THEN r_checkCol.DATA_TYPE:='CHA
R('||r_che
ckCol.DATA
_LENGTH||'
)';
END IF;
cStatement := 'ALTER TABLE '||tableName||' ADD COLUMN '||r_checkCol.COLUMN_NAME|
|''||r_che
ckCol.DATA
_TYPE||'';
Execute Immediate cStatement;
DBMS_OUTPUT.PUT_LINE('Comp
lete !!');
END LOOP;
END LOOP;
CLOSE simple_cursor;
End
Now for some reason, it wont actually get into the 'FOR r_checkCol IN checkCol_cursor LOOP', I dont understand why, because I manually remove some fields from my own table, so as to force it in. But still it doesnt work. I think its due to the loop. But Im not really sure.
Any ideas, comments or suggestions would be great.
