Re: PL/SQL script concatenation

I would suspect that the Column SRCFILNME in Table LAPREP has blank spaces appended to the end of it!

If this is the case, your suggestion of using rtrim to remove the trailing blanks would work, but use it on the select statement in your for loop.

i.e. FOR Tablename_Rec IN (SELECT rtrim(srcfilnme) srcfilnme FROM LAPREP) LOOP Hope this helps,

Dean Belshaw

<newopt_at_my-deja.com> wrote in message news:8csu6c$651$1_at_nnrp1.deja.com...
> 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