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

bbcrock_at_hotmail.com wrote:

> Thanks!
>
> I don't have the Oracle knowledge to debug this.  All this was very
> useful info and I've got a copy of Oracle PL/SQL 101 to look this up.
>
> For some reason it's still not displaying the results from the proc.
> DBMS_OUTPUT.PUT_LINE is not actually outputting anything.
>
> If anyone has any tips and tricks on how to rewrite this monster, let
> me know, otherwise I'll take it out of PL/SQL and put it in another
> language that I understand better.
>
> thanks,
>
> Don

Here is the output for the SCOTT schema (I use mine for testing, so there are more tables than provided by Oracle):

BONUS SELECT * FROM
BONUS;
SELECT * FROM
BONUS
;

**********DELETE********** DELETE FROM
BONUS
;

**********INSERT********** INSERT INTO
BONUS (
ENAME, JOB, SAL, COMM) VALUES ( vENAME,

vJOB,

vSAL,

vCOMM);

**********UPDATE********** UPDATE BONUS
SET
ENAME =
vENAME,

JOB =
vJOB,

SAL =
vSAL,

COMM
= vCOMM

;

CUSTOMER_ADDRESSES SELECT * FROM
CUSTOMER_ADDRESSES;
SELECT * FROM
CUSTOMER_ADDRESSES
;

**********DELETE********** DELETE FROM
CUSTOMER_ADDRESSES
;

**********INSERT********** INSERT INTO
CUSTOMER_ADDRESSES (
ADD_ID, ADDRESS) VALUES ( vADD_ID,

vADDRESS);

**********UPDATE********** UPDATE CUSTOMER_ADDRESSES
SET
ADD_ID =
vADD_ID,
ADDRESS
= vADDRESS

;

CUSTOMER_TABLE SELECT * FROM
CUSTOMER_TABLE;
SELECT * FROM
CUSTOMER_TABLE
;

**********DELETE********** DELETE FROM
CUSTOMER_TABLE
;

**********INSERT********** INSERT INTO
CUSTOMER_TABLE (
NAME, ADDRESS, TELEPHONE, PRICE, FINAL_PRICE) VALUES ( vNAME,

vADDRESS,

vTELEPHONE,

vPRICE,

vFINAL_PRICE);

**********UPDATE********** UPDATE CUSTOMER_TABLE
SET
NAME =
vNAME,

ADDRESS =
vADDRESS,
TELEPHONE =
vTELEPHONE,
PRICE =
vPRICE,

FINAL_PRICE
= vFINAL_PRICE
;

DEPT SELECT * FROM
DEPT;
SELECT * FROM
DEPT
;

**********DELETE********** DELETE FROM
DEPT
;

**********INSERT********** INSERT INTO
DEPT (
DEPTNO, DNAME, LOC) VALUES ( vDEPTNO,

vDNAME,

vLOC);

**********UPDATE********** UPDATE DEPT
SET
DEPTNO =
vDEPTNO,
DNAME =
vDNAME,

LOC
= vLOC

;

DISCOUNT_TABLE SELECT * FROM
DISCOUNT_TABLE;
SELECT * FROM
DISCOUNT_TABLE
;

**********DELETE********** DELETE FROM
DISCOUNT_TABLE
;

**********INSERT********** INSERT INTO
DISCOUNT_TABLE (
NAME, ADDRESS, TELEPHONE, DISCOUNT) VALUES ( vNAME,

vADDRESS,

vTELEPHONE,

vDISCOUNT);

**********UPDATE********** UPDATE DISCOUNT_TABLE
SET
NAME =
vNAME,

ADDRESS =
vADDRESS,
TELEPHONE =
vTELEPHONE,
DISCOUNT
= vDISCOUNT
;

DOTTEST SELECT * FROM
DOTTEST;
SELECT * FROM
DOTTEST
;

**********DELETE********** DELETE FROM
DOTTEST
;

**********INSERT********** INSERT INTO
DOTTEST (
HTML_CONTENT) VALUES ( vHTML_CONTENT);

**********UPDATE********** UPDATE DOTTEST
SET
HTML_CONTENT
= vHTML_CONTENT
;

DUMMY SELECT * FROM
DUMMY;
SELECT * FROM
DUMMY
;

**********DELETE********** DELETE FROM
DUMMY
;

**********INSERT********** INSERT INTO
DUMMY (
DUMMY) VALUES ( vDUMMY);

**********UPDATE********** UPDATE DUMMY
SET
DUMMY
= vDUMMY

;

EMP SELECT * FROM
EMP;
SELECT * FROM
EMP
;

**********DELETE********** DELETE FROM
EMP
;

**********INSERT********** INSERT INTO
EMP (
TTL_SAL, DEPTNAME, EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES ( vTTL_SAL,

vDEPTNAME,

vEMPNO,

vENAME,

vJOB,

vMGR,

vHIREDATE,

vSAL,

vCOMM,

vDEPTNO);

**********UPDATE********** UPDATE EMP
SET
TTL_SAL =
vTTL_SAL,
DEPTNAME =
vDEPTNAME,
EMPNO =
vEMPNO,

ENAME =
vENAME,

JOB =
vJOB,

MGR =
vMGR,

HIREDATE =
vHIREDATE,
SAL =
vSAL,

COMM =
vCOMM,

DEPTNO
= vDEPTNO

;

EMP_SAVE SELECT * FROM
EMP_SAVE;
SELECT * FROM
EMP_SAVE
;

**********DELETE********** DELETE FROM
EMP_SAVE
;

**********INSERT********** INSERT INTO
EMP_SAVE (
EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, TTL_SAL) VALUES ( vEMPNO,

vENAME,

vJOB,

vMGR,

vHIREDATE,

vSAL,

vCOMM,

vDEPTNO,

vTTL_SAL);

**********UPDATE********** UPDATE EMP_SAVE
SET
EMPNO =
vEMPNO,

ENAME =
vENAME,

JOB =
vJOB,

MGR =
vMGR,

HIREDATE =
vHIREDATE,
SAL =
vSAL,

COMM =
vCOMM,

DEPTNO =
vDEPTNO,
TTL_SAL
= vTTL_SAL

;

GRADES SELECT * FROM
GRADES;
SELECT * FROM
GRADES
;

**********DELETE********** DELETE FROM
GRADES
;

**********INSERT********** INSERT INTO
GRADES (
A,

B,

C,

D)

VALUES ( vA,

vB,

vC,

vD);

**********UPDATE********** UPDATE GRADES
SET
A =
vA,

B =
vB,

C =
vC,

D
= vD

;

MYTABLE SELECT * FROM
MYTABLE;
SELECT * FROM
MYTABLE
;

**********DELETE********** DELETE FROM
MYTABLE
;

**********INSERT********** INSERT INTO
MYTABLE (
MYDATE, MYVAL) VALUES ( vMYDATE,

vMYVAL);

**********UPDATE********** UPDATE MYTABLE
SET
MYDATE =
vMYDATE,
MYVAL
= vMYVAL

;

NUMTEST SELECT * FROM
NUMTEST;
SELECT * FROM
NUMTEST
;

**********DELETE********** DELETE FROM
NUMTEST
;

**********INSERT********** INSERT INTO
NUMTEST (
ID,

VAL) VALUES ( vID,

vVAL);

**********UPDATE********** UPDATE NUMTEST
SET
ID =
vID,

VAL
= vVAL

;

SALGRADE SELECT * FROM
SALGRADE;
SELECT * FROM
SALGRADE
;

**********DELETE********** DELETE FROM
SALGRADE
;

**********INSERT********** INSERT INTO
SALGRADE (
GRADE, LOSAL, HISAL) VALUES ( vGRADE,

vLOSAL,

vHISAL);

**********UPDATE********** UPDATE SALGRADE
SET
GRADE =
vGRADE,

LOSAL =
vLOSAL,

HISAL
= vHISAL

;

T

SELECT * FROM
T;
SELECT * FROM
T
;

**********DELETE********** DELETE FROM
T
;

**********INSERT********** INSERT INTO
T (
EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES ( vEMPNO,

vENAME,

vJOB,

vMGR,

vHIREDATE,

vSAL,

vCOMM,

vDEPTNO);

**********UPDATE********** UPDATE T
SET
EMPNO =
vEMPNO,

ENAME =
vENAME,

JOB =
vJOB,

MGR =
vMGR,

HIREDATE =
vHIREDATE,
SAL =
vSAL,

COMM =
vCOMM,

DEPTNO
= vDEPTNO

;

T1

SELECT * FROM
T1;
SELECT * FROM
T1
WHERE ID =
vID
;

**********DELETE********** DELETE FROM
T1
WHERE ID =
vID
;

**********INSERT********** INSERT INTO
T1 (
ID)

VALUES ( vID);

**********UPDATE********** UPDATE T1
SET
ID
= vID

WHERE ID =
vID
;

TBL SELECT * FROM
TBL;
SELECT * FROM
TBL
;

**********DELETE********** DELETE FROM
TBL
;

**********INSERT********** INSERT INTO
TBL (
ITEM, PRICE) VALUES ( vITEM,

vPRICE);

**********UPDATE********** UPDATE TBL
SET
ITEM =
vITEM,

PRICE
= vPRICE

;

This appears to be generic insert, update and delete statements generated for every table listed in ALL_TABLES and ALL_TAB_COLUMNS; in fact these update and delete statements are FAR too generic to use in production. This appears to be a learning exercise by the original author.

David Fitzjarrell Received on Tue Jan 04 2005 - 15:33:13 CST