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

Based upon the SCOTT schema the output is as follows (I use the SCOTT schema for testing so there are more tables in my SCOTT schema 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

;

Again these are VERY generic DML statements, so generic, in fact, I wouldn't use them (the UPDATE statements update the entire table specified -- not usually desirable in a production system). It may have been a learning exercise by the author to generate such statements. I see no real purpose for this in a production environment as it is now written.

David Fitzjarrell Received on Tue Jan 04 2005 - 16:09:01 CST