-
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
-
You can't write DDL statements in PL/SQL like this.
-
Thank you,
but does it mean I can not at all write ddl statements in plsql, or is there a way to do so?
-
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|