topic Re: pl/sql help in Operating System - HP-UX

pl/sql help

robert_177 — Mon, 04 Aug 2003 18:25:43 GMT

Hi list,
I am getting errors when I execute the following pl/sql, any help?

DECLARE
v_row_id siebel.s_addr_per.row_id%TYPE;
v_audit_type s_addr_per_audit.audit_type%TYPE;
v_loc siebel.s_org_ext.loc%TYPE;
v_name siebel.s_org_ext.name%TYPE;
CURSOR audit_type_cur ( v_row_id VARCHAR2 ) IS
SELECT audit_type, row_id
FROM SIEBAUDIT.S_ADDR_PER_AUDIT
WHERE audit_at > sysdate - 1/1440;

CURSOR audit_upd_cur (v_loc VARCHAR2, v_name VARCHAR2, V_row_id VARCHAR2 ) IS
SELECT name, loc
FROM SIEBEL.S_ORG_EXT
WHERE row_id = v_row_id;
BEGIN
OPEN audit_type_cur;
LOOP
FETCH audit_type_cur INTO v_audit_type,v_row_id;
EXIT WHEN audit_type_cur%NOTFOUND;
IF ( audit_type = 'D' ) THEN
OPEN audit_upd_cur;
FETCH audit_upd_cur INTO v_name, v_loc;
UPDATE SIEBAUDIT.S_ADDR_PER_AUDIT
SET old_loc=v_loc
WHERE row_id=v_row_id;
UPDATE SIEBAUDIT.S_ADDR_PER_AUDIT
SET old_name=v_name
WHERE row_id=v_row_id;
CLOSE audit_upd_cur;
ELSE
INSERT INTO SIEBAUDIT.S_ADDR_PER_COPY
SELECT * FROM SIEBEL.S_ADDR_PER
WHERE ROW_ID=v_row_id;
END IF;
COMMIT;
CLOSE audit_type_cur;
END LOOP;
END;
/

SELECT audit_type, row_id
*
ERROR at line 7:
ORA-06550: line 7, column 20:
PLS-00201: identifier 'ROW_ID' must be declared
ORA-06550: line 7, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 15, column 4:
PLS-00306: wrong number or types of arguments in call to 'AUDIT_TYPE_C
UR'
ORA-06550: line 15, column 4:
PL/SQL: SQL Statement ignored
ORA-06550: line 7, column 20:
PLS-00320: the declaration of the type of this expression is incomplet
e or
malformed
ORA-06550: line 17, column 5:
PL/SQL: SQL Statement ignored
ORA-06550: line 19, column 8:
PLS-00201: identifier 'AUDIT_TYPE' must be declared
ORA-06550: line 19, column 3:
PL/SQL: Statement ignored

Re: pl/sql help

Brian Crabtree — Mon, 04 Aug 2003 21:08:29 GMT

DECLARE
v_row_id siebel.s_addr_per.row_id%TYPE;
v_audit_type s_addr_per_audit.audit_type%TYPE;
v_loc siebel.s_org_ext.loc%TYPE;
v_name siebel.s_org_ext.name%TYPE;
CURSOR audit_type_cur IS
SELECT audit_type, rowid
FROM SIEBAUDIT.S_ADDR_PER_AUDIT
WHERE audit_at > sysdate - 1/1440;

CURSOR audit_upd_cur IS
SELECT name, loc
FROM SIEBEL.S_ORG_EXT
WHERE rowid = v_row_id;

BEGIN
OPEN audit_type_cur;
LOOP
FETCH audit_type_cur INTO v_audit_type,v_row_id;
EXIT WHEN audit_type_cur%NOTFOUND;
IF ( v_audit_type = 'D' ) THEN
OPEN audit_upd_cur;
FETCH audit_upd_cur INTO v_name, v_loc;
UPDATE SIEBAUDIT.S_ADDR_PER_AUDIT
SET old_loc=v_loc, old_name=v_name
WHERE rowid=v_row_id;
CLOSE audit_upd_cur;
ELSE
INSERT INTO SIEBAUDIT.S_ADDR_PER_COPY
SELECT * FROM SIEBEL.S_ADDR_PER
WHERE ROWID=v_row_id;
END IF;
COMMIT;
CLOSE audit_type_cur;
END LOOP;
END;
/

Give this a try, and let me know if you get errors.

Brian

Re: pl/sql help

F. X. de Montgolfier — Tue, 05 Aug 2003 08:11:48 GMT

Hi,

Brian made a small error in his correction:

DECLARE
v_row_id siebel.s_addr_per.row_id%TYPE;
~~~~~~~
should be
v_row_id siebel.s_addr_per.rowid%TYPE;

(no undescore)

Otherwise, I think his script will work.

Cheers,

FiX