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
|
|
|
Re: Using loop with a variable table name [message #663139 is a reply to message #663138] |
Tue, 23 May 2017 06:26 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Standard rule for debugging dynamic SQL.
Assign the dynamic string to a variable.
Output the string using dbms_output or similiar.
Run string as an actual statement and see what happens.
A dynamic block is going to need a dynamic declare.
|
|
|
|
|
|
|
|
|
Re: Using loop with a variable table name [message #663303 is a reply to message #663299] |
Mon, 29 May 2017 07:06 |
|
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
shahid17 wrote on Mon, 29 May 2017 04:02As suggested by the members, I changed the table design and had date as column and not part of the table name.
Bravo!
You are one of the very few people we see who actually corrects fundamental design flaws instead of insisting on finding a "work-around".
|
|
|