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 Go to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12961
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 #663140 is a reply to message #663138] Tue, 23 May 2017 06:38 Go to previous messageGo to next message
EdStevens
Messages: 863
Registered: September 2013
Senior Member
You actually have tables where the table name includes the/a date? This is highly suggestive of a fatally flawed design . . .
Re: Using loop with a variable table name [message #663141 is a reply to message #663139] Tue, 23 May 2017 06:41 Go to previous messageGo to next message
BlackSwan
Messages: 25751
Registered: January 2009
Location: SoCal
Senior Member
Design is flawed which results in "need" for dynamic PL/SQL.
Column_name should not contain DATE component.
Table should contain new column of datatype DATE.
Data should conform to Third Normal Form
Re: Using loop with a variable table name [message #663142 is a reply to message #663138] Tue, 23 May 2017 07:47 Go to previous messageGo to next message
Michel Cadot
Messages: 65256
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

Which line is line 30?
Use SQL*Plus and copy and paste your session, the WHOLE session.

Re: Using loop with a variable table name [message #663152 is a reply to message #663142] Tue, 23 May 2017 12:44 Go to previous messageGo to next message
shahid17
Messages: 13
Registered: January 2012
Junior Member
Thank you all for your inputs. I managed to resolve the issue.
Re: Using loop with a variable table name [message #663153 is a reply to message #663152] Tue, 23 May 2017 14:06 Go to previous messageGo to next message
Michel Cadot
Messages: 65256
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

How?

Re: Using loop with a variable table name [message #663299 is a reply to message #663153] Mon, 29 May 2017 04:02 Go to previous messageGo to next message
shahid17
Messages: 13
Registered: January 2012
Junior Member
As suggested by the members, I changed the table design and had date as column and not part of the table name.
Re: Using loop with a variable table name [message #663303 is a reply to message #663299] Mon, 29 May 2017 07:06 Go to previous message
EdStevens
Messages: 863
Registered: September 2013
Senior Member
shahid17 wrote on Mon, 29 May 2017 04:02
As 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".
Previous Topic: Help in Query to achieve output?
Next Topic: Dependent Objects recursively
Goto Forum:
  


Current Time: Sun Dec 17 18:49:25 CST 2017

Total time taken to generate the page: 0.01415 seconds