Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
cancel
Showing results for 
Search instead for 
Did you mean: 

pl/sql help

robert_177
Occasional Contributor

pl/sql help

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

what is passat
2 REPLIES
Brian Crabtree
Honored Contributor

Re: pl/sql 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 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
F. X. de Montgolfier
Valued Contributor

Re: pl/sql help

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