DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: help on pl/sql

  1. #1
    Join Date
    Apr 2001
    Posts
    107

    Question help on pl/sql

    Hi everybody,
    I have a problem with one of my customers, who wants to write a pl/sql procedure... well the problem is mostly that I am not in developpement, hence, don't know the "how to"...
    No doubt the following will be very simple for you.
    The goal is do dynamically create the following sql code:

    ALTER TABLE "SILVERUSER"."TBLTEST" ADD ("FLT$$" NUMBER(38, 19)) ;
    UPDATE "SILVERUSER"."TBLTEST" SET "SILVERUSER"."TBLTEST"."FLT$$" = "SILVERUSER"."."TBLTEST"."FLT";
    ALTER TABLE "SILVERUSER"."TBLTEST" DROP ("FLT");
    ALTER TABLE "SILVERUSER"."TBLTEST" RENAME COLUMN "FLT$$" TO "FLT";

    the pl/sql package is that one, but doesn't work to dynamically create the code:

    DECLARE
    CURSOR c1 IS
    SELECT TABLE_NAME , COLUMN_NAME from DBA_TAB_COLUMNS
    WHERE OWNER = 'SILVERUSER' AND TABLE_NAME like 'TBL%' and DATA_TYPE ='FLOAT';
    tableName VARCHAR(50);
    colName1 VARCHAR(50);
    colName2 VARCHAR(52);

    BEGIN
    FOR col_rec IN c1 LOOP
    tableName = col_rec.TABLE_NAME;
    colName1 = col_rec.COLUMN_NAME;
    colName2 = col_rec.COLUMN_NAME + "$$";
    ALTER TABLE "SILVERUSER".tableName
    ADD (colName2 NUMBER(38, 19)) ;
    UPDATE "SILVERUSER".tableName SET "SILVERUSER".tableName.colName2 = "SILVERUSER".tableName.colName1;
    ALTER TABLE "SILVERUSER".tableName
    DROP (colName1);
    ALTER TABLE "SILVERUSER".tableName
    RENAME COLUMN colName2 TO colName1;
    END LOOP;
    END


    Any information welcome!

    Thanks for your help

    Fabien

  2. #2
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    You can't write DDL statements in PL/SQL like this.

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  3. #3
    Join Date
    Apr 2001
    Posts
    107
    Thank you,

    but does it mean I can not at all write ddl statements in plsql, or is there a way to do so?

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

  5. #5
    Join Date
    Apr 2001
    Posts
    107
    Many thanks to both "The specialist" Raminder, and the world most famous "super-genius" Marist89 !

    Cheers

    Fabien

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width