PL/SQL script concatenation

From: <newopt_at_my-deja.com>
Date: 2000/04/10
Message-ID: <8csu6c$651$1@nnrp1.deja.com>#1/1

I have a pl/sql script that drops 1 table and then renames a new table by the same name with a "_V40" suffx appended to the original table name back to the original table name:

i.e. ARCTLPF is dropped and then ARCTLPF_V40 is renamed to ARCTLPF. I am doing this to roll out a new release of a offline reporting system. the "_V40" tables are the new table formats.

Here's my pl/sql:
SQL> get icomstableprep.sql
  1 Declare
  2 cid INTEGER;
  3 v31tablename Varchar2(10);
  4 v40tablename Varchar2(14);
  5 sqlstr Varchar2(200);
  6 -- This procedure is used to drop the ICOMS V3.1 PTF 15 tables  7 -- and then renames the staged V4.0 PTF 2 tables with a "_V40" suffix
  8 -- dropping the "_V40" suffix. This action swaps in the V4.0 PTF2   9 -- table set for the V3.1 PTF 15 table set.  10 procedure prep_table (v_prep_tablename Varchar2) IS  11 BEGIN
 12 -- Open new cursor and return cursor ID.  13 cid := DBMS_SQL.OPEN_CURSOR;
 14 -- Build the "Drop Table" SQL statement  15 sqlstr := 'DROP TABLE '||v_prep_tablename||'';  16 -- Execute the dynamic SQL statement built by concatenating the  17 -- ICOMS table name to the DROP TABLE command.  18 DBMS_SQL.PARSE(cid, sqlstr, dbms_sql.native);  19 -- Build the V4.0 PTF 2 table name from the V3.1 PTF 15 tablename.  20 v40tablename := v_prep_tablename || '_V40';  21 -- Build the "Rename Table" SQL statement  22 sqlstr := 'RENAME '||v40tablename||' TO '||v_prep_tablename||'';  23 -- Execute the dynamic SQL statement built by concatenating the  24 -- ICOMS table name to the RENAME TABLE command.  25 DBMS_SQL.PARSE(cid, sqlstr, dbms_sql.native);  26 -- Close cursor.
 27 DBMS_SQL.CLOSE_CURSOR(cid);
 28 EXCEPTION
 29 -- If an exception is raised, close cursor before exiting.

 30       WHEN OTHERS THEN
 31          DBMS_SQL.CLOSE_CURSOR(cid);
 32          RAISE;  -- reraise the exception

 33 END prep_table;
 34 -- Mainline Processing
 35 BEGIN
 36 -- Open the ICOMSPREP Subquery

 37        FOR Tablename_Rec IN (SELECT srcfilnme FROM LAPREP) LOOP
 38           v31tablename := Tablename_Rec.srcfilnme;
 39           v40tablename := v31tablename || '_V40';
 40    dbms_output.put_line(v31tablename);
 41    dbms_output.put_line(v40tablename);
 42    --       prep_table (v31tablename);
 43        END LOOP;

 44* END;
 45 /

And here's the results?? If the table name length varies do I need to use the trim right function to strip off the training spaces in the v31tablename variable before I concatenate the "_V40" string to the end of the name?
See staement 38 & 39 above for what I have.

ARCTLPF
ARCTLPF _V40
ARDSCPF
ARDSCPF _V40
ARTRNHPF
ARTRNHPF _V40 Thanks in advance!!

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Apr 10 2000 - 00:00:00 CDT