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

Thread: Compiling procedures with size more than 32K

  1. #1
    Join Date
    Oct 2006
    Posts
    175

    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

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    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.

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    why not save them off from sql developer or something - lots easier

  4. #4
    Join Date
    Oct 2006
    Posts
    175
    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
  •  


Click Here to Expand Forum to Full Width