Re: PL/SQL script concatenation

There doesn't appear to be anything wrong with your logic. Run the following statement to make sure there aren't trailing spaces in your driving table:

SELECT REPLACE(srcfilnme,' ','^') FROM LAPREP;

Michael J. Ort

In article <8csu6c$651$1_at_nnrp1.deja.com>,   newopt_at_my-deja.com wrote:
> 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.
>

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