Home » SQL & PL/SQL » SQL & PL/SQL » Using loop with a variable table name (10G)
Using loop with a variable table name [message #663138] |
Tue, 23 May 2017 06:18  |
 |
shahid17
Messages: 13 Registered: January 2012
|
Junior Member |
|
|
Hi All,
I need to create a procedure that updates a variable table using a loop. I m getting the below error when I run the procedure. Please assist and guide.
ORA-06550: line 30, column 3:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
; <identificateur> <identificateur entre guillemets>
The symbol ";" was substituted for "end-of-file" to continue.
ORA-06512: at "CVBSREPORTS.SS_TEST1", line 46
ORA-06512: at line 2
The procedure is as below:
CREATE OR REPLACE PROCEDURE cvbsreports.ss_test1
AS
BEGIN
DECLARE
v_prov VARCHAR2 (100);
v_date VARCHAR2 (8);
v_sql1 VARCHAR2 (5000);
BEGIN
v_date := TO_CHAR (SYSDATE - 1, 'yyyymmdd');
v_sql1 :=
'
begin
FOR i in (select * from ss_points_'
|| v_date
|| ' where province is null )
LOOP
SELECT UPPER (z.area_name)
INTO v_prov
FROM bfm_area@link_cc z
WHERE z.parent_id = 1
AND z.area_id =
(SELECT k.parent_id
FROM bfm_area@link_cc k,
subs@link_cc s,
prod@link_cc p,
cust@link_cc c
WHERE s.subs_id = p.prod_id
AND s.cust_id = c.cust_id
AND p.prod_state IN (''A'', ''D'', ''E'', ''G'')
AND k.area_id = c.area_id
AND s.acc_nbr = i.msisdn);
UPDATE ss_points_'
|| v_date
|| ' x
SET x.province = v_prov
WHERE x.province IS NULL AND x.msisdn = i.msisdn;
END LOOP;
END';
EXECUTE IMMEDIATE (v_sql1);
END;
END ss_test1;
/
[mod-edit: code tags added by bb]
[Updated on: Fri, 26 May 2017 20:50] by Moderator Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Jun 22 20:28:32 CDT 2025
|