newbie needs help running old oracle 8i code. procedure? function?

From: <bbcrock_at_hotmail.com>
Date: 4 Jan 2005 07:46:37 -0800
Message-ID: <1104853597.671514.47380@f14g2000cwb.googlegroups.com>

Please help me identify this code! I am a web developer with limited Oracle knowledge. I'm running Oracle 8.1.7. This code was created by another developer we had on staff to build a text file from Oracle metadata. I can't figure out where it's supposed to be saved- is this a procedure? Function? It was meant to be a proc, but I run into errors when I try to compile it. I run into errors cutting and pasting the code in SQL Plus and TOAD. Our Oracle DBA had excellent maintenance skills, but he can't figure out what this is missing. Can any one of you tell me what this needs to run correctly in SQLPlus or to be compiled as a proc or function I can call? If all I need is a link to an online tutorial, that is cool.

please post your response here.

THANKS! Don

---

DECLARE vOwner VARCHAR2(100) := 'Schema100'; vTable_Name VARCHAR2(100) := 'Employee'; vCol_Cnt NUMBER := 0;
vCnt NUMBER := 0;

CURSOR Get_Table_Names
IS
SELECT *
FROM All_Tables
WHERE Owner = vOwner;
--AND Table_Name = vTable_Name;

CURSOR Get_Column_Names (pTable_Name IN VARCHAR2) IS
SELECT *
FROM All_Tab_Columns
WHERE Owner = vOwner
AND Table_Name = pTable_Name;

CURSOR Get_Column_Cnt (pTable_Name IN VARCHAR2) IS
SELECT COUNT(*) Col_Cnt
FROM All_Tab_Columns
WHERE Owner = vOwner
AND Table_Name = pTable_Name;

CURSOR Get_PK (pTable_Name IN VARCHAR2)
IS
SELECT UC.Constraint_Name, UIC.Column_Position, UIC.Column_Name FROM User_Constraints UC, User_Ind_Columns UIC WHERE Owner = vOwner

AND    UC.Constraint_Type = 'P'
AND    UC.Table_Name = UIC.Table_Name
AND    UC.Table_Name = pTable_Name

ORDER BY UIC.Column_Position;

BEGIN

FOR Get_Table_Names_Rec IN Get_Table_Names LOOP
OPEN Get_Column_Cnt (Get_Table_Names_Rec.Table_Name);
FETCH Get_Column_Cnt INTO vCol_Cnt;

CLOSE Get_Column_Cnt;

DBMS_OUTPUT.PUT_LINE(Get_Table_Names_rec.Table_Name); DBMS_OUTPUT.PUT_LINE('SELECT * FROM
'||Get_Table_Names_rec.Table_Name||';');
DBMS_OUTPUT.PUT_LINE('SELECT * FROM
'||Get_Table_Names_rec.Table_Name);

FOR Get_PK_Rec IN Get_PK(Get_Table_Names_rec.Table_Name) LOOP IF Get_PK_Rec.Column_Position = 1 THEN
DBMS_OUTPUT.PUT_LINE(' WHERE '||Get_PK_Rec.Column_Name||' = v'||Get_PK_Rec.Column_Name);
ELSE
DBMS_OUTPUT.PUT_LINE(' AND '||Get_PK_Rec.Column_Name||' = v'||Get_PK_Rec.Column_Name);
END IF;
END LOOP;

DBMS_OUTPUT.PUT_LINE(';');
DBMS_OUTPUT.PUT_LINE('**********DELETE**********');
DBMS_OUTPUT.PUT_LINE('DELETE FROM

'||Get_Table_Names_rec.Table_Name);

FOR Get_PK_Rec IN Get_PK(Get_Table_Names_rec.Table_Name) LOOP IF Get_PK_Rec.Column_Position = 1 THEN
DBMS_OUTPUT.PUT_LINE(' WHERE '||Get_PK_Rec.Column_Name||' = v'||Get_PK_Rec.Column_Name);
ELSE
DBMS_OUTPUT.PUT_LINE(' AND '||Get_PK_Rec.Column_Name||' = v'||Get_PK_Rec.Column_Name);
END IF;
END LOOP;

DBMS_OUTPUT.PUT_LINE(';');
DBMS_OUTPUT.PUT_LINE('**********INSERT**********');
DBMS_OUTPUT.PUT_LINE('INSERT INTO

'||Get_Table_Names_rec.Table_Name||' (');
vCnt := 0;
FOR Get_Column_Names_Rec IN
Get_Column_Names(Get_Table_Names_rec.Table_Name) LOOP vCnt := vCnt + 1;
IF vCol_Cnt = vCnt THEN

DBMS_OUTPUT.PUT_LINE(Get_Column_Names_Rec.Column_Name||')'); ELSE DBMS_OUTPUT.PUT_LINE(Get_Column_Names_Rec.Column_Name||','); END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('VALUES (');
vCnt := 0;
FOR Get_Column_Names_Rec IN
Get_Column_Names(Get_Table_Names_rec.Table_Name) LOOP vCnt := vCnt + 1;
IF vCol_Cnt = vCnt THEN

DBMS_OUTPUT.PUT_LINE('v'||Get_Column_Names_Rec.Column_Name||');'); ELSE DBMS_OUTPUT.PUT_LINE('v'||Get_Column_Names_Rec.Column_Name||','); END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('**********UPDATE**********'); vCnt := 0;
DBMS_OUTPUT.PUT_LINE('UPDATE '||Get_Table_Names_rec.Table_Name||' SET ');
FOR Get_Column_Names_Rec IN
Get_Column_Names(Get_Table_Names_rec.Table_Name) LOOP vCnt := vCnt + 1;
IF vCol_Cnt = vCnt THEN
DBMS_OUTPUT.PUT_LINE(Get_Column_Names_Rec.Column_Name||' = '||'v'||Get_Column_Names_Rec.Column_Name); ELSE DBMS_OUTPUT.PUT_LINE(Get_Column_Names_Rec.Column_Name||' =
'||'v'||Get_Column_Names_Rec.Column_Name||',');
END IF;
END LOOP;
FOR Get_PK_Rec IN Get_PK(Get_Table_Names_rec.Table_Name) LOOP IF Get_PK_Rec.Column_Position = 1 THEN
DBMS_OUTPUT.PUT_LINE(' WHERE '||Get_PK_Rec.Column_Name||' = v'||Get_PK_Rec.Column_Name);
ELSE
DBMS_OUTPUT.PUT_LINE(' AND '||Get_PK_Rec.Column_Name||' = v'||Get_PK_Rec.Column_Name);
END IF;
END LOOP;
           DBMS_OUTPUT.PUT_LINE(';');
  END LOOP;
END; Received on Tue Jan 04 2005 - 09:46:37 CST