-
Compiling procedures with size more than 32K
Hi all
I've got an issue here while transferring procedures from one server to another. Our transfer method is first we dump ddl for all procedures into a table in source server, transfer that table using CTAS to destination server, and then run those ddl's in destination server. All procedures seem to compile perfectly except the ones with size more than 32K. We're using DBMS_METADATA to get ddls. Do you guys have any other methods to transfer LOB objects, as I can see its issue with LOB objects. Following is the procedure to transfer:
CREATE TABLE mymeta
(
md CLOB
);
/
CREATE OR REPLACE procedure my_ddl
(
THIS_SCHEMA VARCHAR
)
AS
h NUMBER;
th NUMBER;
th1 NUMBER;
doc CLOB;
temp_doc CLOB;
sstr VARCHAR2(4000);
objtype VARCHAR2(50);
TYPE col_obj IS TABLE OF VARCHAR2(50);
myobjs col_obj;
BEGIN
myobjs := col_obj('FUNCTION', 'PACKAGE', 'PROCEDURE', 'SEQUENCE', 'SYNONYM', 'TRIGGER', 'TYPE', 'VIEW');
Dbms_Output.put_line(THIS_SCHEMA);
BEGIN EXECUTE IMMEDIATE('DROP TABLE '||THIS_SCHEMA||'.mymeta'); EXCEPTION WHEN OTHERS THEN Dbms_Output.PUT_LINE('Table does not exist'); END;
BEGIN EXECUTE IMMEDIATE('CREATE TABLE '||THIS_SCHEMA||'.mymeta (md CLOB)'); EXCEPTION WHEN OTHERS THEN Dbms_Output.PUT_LINE('Table Already Exists'||SQLERRM); END;
BEGIN EXECUTE IMMEDIATE('TRUNCATE TABLE '||THIS_SCHEMA||'.mymeta'); END;
FOR i IN myobjs.FIRST .. myobjs.LAST
LOOP
objtype:=myobjs(i);
Dbms_Output.put_line(objtype);
h := DBMS_METADATA.OPEN(objtype);
DBMS_METADATA.SET_FILTER(h,'SCHEMA',THIS_SCHEMA);
th1 := DBMS_METADATA.ADD_TRANSFORM(h,'MODIFY');
DBMS_METADATA.SET_REMAP_PARAM(th1,'REMAP_SCHEMA',THIS_SCHEMA, '');
th := DBMS_METADATA.ADD_TRANSFORM(h,'DDL');
dbms_metadata.set_transform_param(th,'PRETTY',TRUE);
LOOP
doc := DBMS_METADATA.FETCH_CLOB(h);
EXIT WHEN doc IS NULL;
IF(objtype = 'TRIGGER' AND InStr(doc,'ALTER') <> 0 ) THEN
temp_doc := substr(doc,1,InStr(doc,'END;')+4);
doc := temp_doc;
END IF;
EXECUTE IMMEDIATE 'INSERT INTO '||THIS_SCHEMA||'.mymeta(md) VALUES (:d1)' USING doc;
COMMIT;
END LOOP;
DBMS_METADATA.CLOSE(h);
COMMIT;
END LOOP;
END;
/
GRANT DEBUG,EXECUTE ON my_ddl TO PUBLIC;
Thanking you in advance
gtcol
-
How can someone reasonably maintain a 32K procedure or package??? You can shrink some of the PL/SQL code if you create views instead of including all of the SQL in the PL/SQL.
Try doing a set long 65536.
-
why not save them off from sql developer or something - lots easier
-
Thanks gandolf989 and davey23uk
I've shrinked the procedures to bring it below 32K, it's workin for the time being. I am also looking at dbms_sql, and also we've plans to move to 11g, may be that'll help.
Thanks again,
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
|