4 Replies Latest reply on Sep 20, 2013 12:20 PM by BrendanTheAussie

    Connection close after DBMS_LOB.READ and DBMS_METADATA.GET_DDL

    BrendanTheAussie

      Hi All,

       

      I am hoping someone can solve a problem for me.

      I wanted a method to extract source code for a schema that I could control via a java application.

      The idea was that it would work similarly to the Tools>Database extract function in the Oracle SQL Developer.

      I did not want to just use select * from TABLE(DBMS_METADATA.GET_DDL('mytype','myObject') as this only returns the code for one object,

      and as the database I tend to work on have large dictionaries I wanted to improve performance where possible.

      So I wrote a package to call DBMS_METADATA.GET_DDL and at first it appeared to work well, but then I noticed a strange artifact.

      After I use the package, sometimes, if I try to re-compile the package my SQL developer connection crashes.

      So it appears as if I am forgetting to close something, or cleanup something... but I don't know what.

      I have no error messages/log to show why the connection fails.

       

      An example of me calling my package is as follows:

       

      Select OWNER, OBJECT_TYPE, NAME, LINE, TEXT from table(PG_CODE.GET_OBJECT_SOURCE('VIEW')) order by 2, 3, 4;

       

      My package is as follows:

       

      create or replace PACKAGE  PG_CODE IS

         /*

          * Created By Bendan Durkin

          */

       

         g_nBufferLen         CONSTANT    PLS_INTEGER       := 4000;

       

         TYPE TY_DATA_REC IS RECORD (

             OWNER            VARCHAR2(30),

             OBJECT_TYPE      VARCHAR2(30),

             NAME             VARCHAR2(30),

             LINE             NUMBER(10,0),

             TEXT             VARCHAR2(4000) -- Note: VARCHAR2 size must be same as g_nBufferLen value !

         );

        

         TYPE TY_DATA_TABLE IS TABLE OF TY_DATA_REC;

       

         /******************************************

          * Use DBMS_DDL to fetch the source code for all specified objects.

          *

          * Parameters:

          *    P_OBJECT_TYPE : Only return the source for objects that match this type,

          *                    unless this param is null, then return objects of all types

          *    P_OBJECT_TYPE : Only return the source for objects that match this name,

          *                    unless this param is null, then return objects of all names

          *    P_OWNER :       Only return the source for objects that for the given owner,

          *                    unless this param is null, then return objects owned the current user.

          *

          * Notes:

          *  1. The owner of this package must have explicit rights of SELECT on DBA_OBJECTS

          *     and EXECUTE on dbms_metadata. (ie. not inherited from a role)

          *  2. The owner of this package must have explicit rights of SELECT/EXECUTE on

          *     any object you want to get the source for unless the object is owned by

          *     the owner of this package

          *  3. If the DDL fails, 1 row for the object is returned containing the SQLERRM and

          *     the LINE number field is Zero.

          *  4. All "lines" are terminated with a space. All rows that are not terminated with a

          *     space are only part of a complete line.

          */

         FUNCTION GET_OBJECT_SOURCE(P_OBJECT_TYPE IN VARCHAR2 := NULL, P_NAME IN VARCHAR2 := NULL, P_OWNER IN VARCHAR2 := NULL)

            RETURN TY_DATA_TABLE PIPELINED;

       

      END;

      /

       

      create or replace PACKAGE BODY  PG_CODE IS

       

         ---------------------------------------------------------------------

         FUNCTION READ_CLOB_INTO_LIST(P_CLOB IN OUT NOCOPY CLOB, P_TABLE IN OUT NOCOPY TY_DATA_TABLE) RETURN BOOLEAN is

            nOffset                       PLS_INTEGER       := 0;

            nPosCR                        PLS_INTEGER       := 0;

            nRow                          PLS_INTEGER       := 0;

            nCharsToRead                  PLS_INTEGER       := 0;

            nClobLen          CONSTANT    PLS_INTEGER       := DBMS_LOB.GETLENGTH(P_CLOB);

            bSucceeded                    BOOLEAN           := true;

         BEGIN

            -- open the clob

            IF (DBMS_LOB.ISOPEN(P_CLOB) != 1) THEN

               DBMS_LOB.OPEN(P_CLOB, 0);

            END IF;

            BEGIN

               -- Oracle starts its indexes at 1 so I add one to the offset during READ and INSTR calls

               nPosCR := INSTR(P_CLOB, CHR(10), nOffset + 1);

               WHILE ( nOffset < nClobLen)   LOOP

                  -- On each loop read all chars upto and including linefeed

                  -- unless this is more then our buffers length (in which case

                  -- just fill the  buffer)

                  IF nPosCR < 1 THEN

                     nCharsToRead := nClobLen - nOffset;

                  ELSE

                     nCharsToRead := nPosCR - nOffset;

                  END IF;

                  IF nCharsToRead > g_nBufferLen THEN

                     nCharsToRead := g_nBufferLen;

                  END IF;

                  nRow := nRow + 1;

                  P_TABLE.EXTEND;

                  -- Oracle starts its indexes at 1 so I add one to the offset during READ and INSTR calls

                  DBMS_LOB.READ(P_CLOB, nCharsToRead, nOffset + 1, P_TABLE(nRow).TEXT);

                  nOffset := nOffset + nCharsToRead;

                  -- Oracle starts its indexes at 1 so I add one to the offset during READ and INSTR calls

                  nPosCR := INSTR(P_CLOB, CHR(10), nOffset + 1);

               END LOOP;

            EXCEPTION WHEN OTHERS  THEN

               nRow := nRow + 1;

               P_TABLE.EXTEND;

               P_TABLE(nRow).TEXT := SQLERRM||' during READ_CLOB_INTO_LIST';

               bSucceeded:=false;

            END;

            -- Close the Clob

            IF (DBMS_LOB.ISOPEN(P_CLOB) = 1) THEN

               DBMS_LOB.CLOSE(P_CLOB);

            END IF;

           

            RETURN bSucceeded;

         END;

       

         ---------------------------------------------------------------------

         FUNCTION GET_OBJECT_SOURCE(P_OBJECT_TYPE IN VARCHAR2 := NULL, P_NAME IN VARCHAR2 := NULL, P_OWNER IN VARCHAR2 := NULL)

            RETURN TY_DATA_TABLE PIPELINED AS

           

            sName                         VARCHAR2(30);

            sWhereType                    VARCHAR2(255);

            sDDLType                      VARCHAR2(30);

            sWhereName                    VARCHAR2(255);

            sOwner                        VARCHAR2(30);

            g_Sql                         VARCHAR2(255);

            oClob                         CLOB;

            oBulkCollect                  TY_DATA_TABLE;

            oTable                        TY_DATA_TABLE     := TY_DATA_TABLE();

            nLastNonEmptyRow              PLS_INTEGER;                            

            nFirstNonEmptyRow             PLS_INTEGER;                            

            bHadCR                        BOOLEAN;

            bSucceeded                    BOOLEAN;

         BEGIN

            IF P_OBJECT_TYPE IS NULL THEN

               sWhereType := '';

            ELSE

               sWhereType := 'OBJECT_TYPE = :sType AND ';

            END IF;

            IF P_NAME IS NULL THEN

               sWhereName := '';

            ELSE

               sWhereName := 'OBJECT_NAME = :sName AND ';

            END IF;

            IF P_OWNER IS NULL THEN

               select USER into sOwner from dual;

            ELSE

               sOwner := UPPER(P_OWNER);

            END IF;

       

            g_Sql:='SELECT OWNER, OBJECT_TYPE, OBJECT_NAME, 0 as LINE, NULL as TEXT FROM DBA_OBJECTS '||

                    'WHERE '||sWhereType||sWhereName||'OWNER = :1';

            IF P_OBJECT_TYPE IS NULL AND P_NAME IS NULL THEN

               EXECUTE IMMEDIATE g_Sql BULK COLLECT INTO oBulkCollect

               using IN sOwner;

            ELSIF P_OBJECT_TYPE IS NULL THEN

               EXECUTE IMMEDIATE g_Sql BULK COLLECT INTO oBulkCollect

               using IN P_NAME, sOwner;

            ELSIF P_NAME IS NULL THEN

               EXECUTE IMMEDIATE g_Sql BULK COLLECT INTO oBulkCollect

               using P_OBJECT_TYPE, sOwner;

            ELSE

               EXECUTE IMMEDIATE g_Sql BULK COLLECT INTO oBulkCollect

               using P_OBJECT_TYPE, P_NAME, sOwner;

            END IF;

       

            DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true);

            DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);

           

            FOR i in 1..oBulkCollect.COUNT LOOP

               IF oTable.COUNT>0 THEN

                  oTable.DELETE;

               END IF;

               BEGIN

                  oClob := null;

                  -- For some reason GET_DDL needs the type name to contain underscores rather then spaces.

                  -- E.g. 'PACKAGE_BODY' rather the 'PACKAGE BODY'

                  sDDLType:=TRANSLATE(oBulkCollect(i).OBJECT_TYPE,' ','_');

                  oClob := DBMS_METADATA.GET_DDL(sDDLType, oBulkCollect(i).NAME, oBulkCollect(i).OWNER);

                  bSucceeded := READ_CLOB_INTO_LIST(oClob, oTable);

               EXCEPTION WHEN OTHERS THEN

                  -- Return the error message in the TEXT field

                  IF oTable.COUNT>0 THEN

                     -- Should not be possible to get here...but just in case

                     oTable.DELETE;

                  END IF;

                  oTable.EXTEND;

                  oTable(oTable.LAST).TEXT:=SQLERRM;

                  -- The bSucceeded flag lets us know that the text field contains an error message

                  bSucceeded:=false;

                  -- Make sure the clob is closed

                  IF (oClob IS NOT NULL AND DBMS_LOB.ISOPEN(oClob) = 1) THEN

                     DBMS_LOB.CLOSE(oClob);

                  END IF;

               END;

       

               -- GET_DDL returns almost every line terminated with a line feed {CHR(10)}

               -- It does this as it may not neccesarily be cutting the source after a

               -- linefeed. (This will happen if any one line is greater then 4K chars)

               -- Also the first and last lines are normally blank.

              

               -- The scans for the last non-empty line and removes

               -- line feeds from any other line and RTRIMS the lines.

               -- If a line feed was present then I later add a space back.

               -- Thus if you want to recreate the "correct" sql you can examine

               -- each line and if the last character is a space, append a line feed.

               nLastNonEmptyRow:=0;

               FOR n in REVERSE  1..oTable.COUNT LOOP

                  IF INSTR(oTable(n).TEXT,CHR(10),1)>0 THEN

                     oTable(n).TEXT:=REPLACE(oTable(n).TEXT,CHR(10),' ');

                     bHadCR:=true;

                  ELSE

                     bHadCR:=false;

                  END IF;

                  oTable(n).TEXT:=RTRIM(oTable(n).TEXT);

                  IF oTable(n).TEXT IS NOT NULL AND nLastNonEmptyRow=0 THEN

                     nLastNonEmptyRow:=n;

                  END IF;

               END LOOP;

              

               -- Now pipe the source out... but dont start piping

               -- until we find the first non-null line.

               nFirstNonEmptyRow:=0;

               FOR n in 1..nLastNonEmptyRow LOOP

                  IF oTable(n).TEXT IS NOT NULL OR nFirstNonEmptyRow>0 THEN

                     IF nFirstNonEmptyRow=0 THEN

                        nFirstNonEmptyRow:=n;

                     END IF;  

                     -- The bSucceeded flag lets us know that the text field contains an error message

                     -- When error the LINE field is set to zero

                     IF bSucceeded THEN

                        oBulkCollect(i).LINE := n-nFirstNonEmptyRow+1;

                     ELSE

                        oBulkCollect(i).LINE := 0;

                     END IF;

                     -- If a line ends with a space, you know its a "real" line

                     IF bHadCR THEN

                        oBulkCollect(i).TEXT := oTable(n).TEXT||' ';

                     ELSE

                        oBulkCollect(i).TEXT := oTable(n).TEXT;

                     END IF;

                     PIPE ROW (oBulkCollect(i));

                  END IF;  

               END LOOP;

              

               -- If there was no source data I still want to output a line.

               -- The TEXT field will be null and the LINE field will be Zero.

               IF nFirstNonEmptyRow=0 OR nLastNonEmptyRow=0 THEN

                  PIPE ROW (oBulkCollect(i));

               END IF;

            END LOOP;

           

            RETURN;

         END;

      END;

      /

       

       

        • 1. Re: Connection close after DBMS_LOB.READ and DBMS_METADATA.GET_DDL
          rp0428
          After I use the package, sometimes, if I try to re-compile the package my SQL developer connection crashes.

          That would be a SQL Developer issue.

           

          Mark this thread ANSWERED and repost it in the SQL Developer forum. When you post there you will need to provide the full version info for sql developer and the database.

          https://forums.oracle.com/community/developer/english/development_tools/application_development_in_pl_sql/sql_developer_(not_for_general_sql_plsql_questions)

           

          Here are some general comments about the architecture of the code you posted.

           

          Your code is not modular. It also mixes parameter validation with multiple specialized code blocks. That will make maintenance and enhancements difficult since the boundaries between the code blocks are not well defined. An example of a possible future enhancement would be if you wanted to change the TY_DATA_REC type to include additional info: it would be difficult with your current design for a developer to know just what to change or how to change it.

           

          You could make the package much more useful if you broke the code into well-defined modules and then used those modules as building blocks to create more complex modules.

           

          Starting from top to bottom:

           

          1. Use SQL types for PIPELINED functions.

          You are using PL/SQL types

          TYPE TY_DATA_REC IS RECORD
          TYPE TY_DATA_TABLE IS TABLE OF TY_DATA_REC;

          Although using PL/SQL types works Oracle is really using SQL types under the covers. If you check the data dictionary you will find that Oracle is implicitly creating SQL types to use since you did not define explicit types.

           

          The problem with this is that the implicitly created types will have system generated names and, in 11g, those types will be 'hidden' so users may not even know that they exist.

          By defining explicit types you can control the names that are used as well as use those types for other code if desired.

           

          See Solomon's explanation and example in this thread:
          https://forums.oracle.com/forums/thread.jspa?messageID=10423909&#10423909

           

          2. Add additional public functions to support a more modular architecture

          This is your only public function:

             FUNCTION GET_OBJECT_SOURCE(P_OBJECT_TYPE IN VARCHAR2 := NULL, P_NAME IN VARCHAR2 := NULL, P_OWNER IN VARCHAR2 := NULL)

                RETURN TY_DATA_TABLE PIPELINED;

          IMHO it is more standard to use the keyword DEFAULT to make it clear that NULL is the default value for each parameter.

          So instead of

          P_OBJECT_TYPE IN VARCHAR2 := NULL

          Use

          P_OBJECT_TYPE IN VARCHAR2 DEFAULT NULL

          The DBMS_METADATA package returns CLOBs. I suggest you add an overloaded function that returns a table of CLOBs. That function will more closely map to the package.

           

          You would then modify your current function (GET_OBJECT_SOURCE) to:

           

          1. validate the parameters - and do something appropriate if there are validation issues. For example you could return a custom SQL type that contains all of the problems encountered. Currently you are trying to use one type (TY_DATA_REC) to serve two purposes: return meaningful data and also return errors. That is a poor design. An object type should be designed for, and used for, a single purpose.

           

          If you have two purposes then use two types. Part of the complication of your proposed architecture is that your one public function is now trying to do both validation and error reporting as well as its primary function of returning metadata in a predefined format.

           

          The validation/error functionality needs to be in a separate function/module from the primary function.

              *  3. If the DDL fails, 1 row for the object is returned containing the SQLERRM and

              *     the LINE number field is Zero.

          That is a poor design and should be changed to separate the functionality into two, or more, discrete pieces.

           

          2. construct VALID lists of owners, object types and object names - these lists would then be used by a new primary function that would take VALIDATED lists and produce a table of CLOBs containing the metadata for those lists.

           

          A. validate_parameters - this function validates the parameters passed and handles error handling for that function

           

          B. get_object_metadata - this function actually calls DBMS_METADATA for ONE OBJECT and returns the CLOB for that object.
                   there may need to be overloaded versions of this function for different object types that may require different
                   parameters

           

          C. get_all_metadata  this function takes the validated lists of objects and iterates through them calling the
                   new 'get_object_metadata' function for each one.

           

          D. get_object_source - this NEW function (same name as yours for now) takes a single CLOB as input and returns a table of your custom object type

              *  4. All "lines" are terminated with a space. All rows that are not terminated with a

              *     space are only part of a complete line.

          For your custom function you can do specialization like this if that is what you need. But why restrict users to that? By including a standard function that returns a standard CLOB you make it much more powerful. Then by having your custom function call the standard one you make it modular and more powerful.


          E. get_all_object_source - this NEW function takes a table of CLOBs and iterates through them calling the new 'get_object_source' for each one. This function can become the equivalent of the one you have now.
                  
          Each of the above 'pieces' has a very well defined API for both input and output. And you can mix and match them to build more complex modules.

           

          The idea is to separate the 'command and control' functionality from the real work of getting the metadata.

           

          So the main procedure/function would call new function 'A - validate parameters' and then either return a list of errors/problems or construct a VALID list of parameters and use them to get metadata. But it would NOT try to do both things at the same time and put the results in the same object type.

           

          You might also consider adding some security checks to make sure an 'owner' doesn't try to get metadata for objects they should not be referencing. You could use INVOKER rights to help control that or make sure that if the caller is not the package owner that only the callers objects are included in the metadata that is returned.

           

          Hope you take these comments with the spirit in which they were intended.

           


          1 person found this helpful
          • 2. Re: Connection close after DBMS_LOB.READ and DBMS_METADATA.GET_DDL
            BrendanTheAussie

            Thanks for the well considered reply

             

            I have put together a reply to many of you points.

            That would be a SQL Developer issue.

            ...I am hoping it is but I am fishing to see if anyone knows for certain. Your response shows that you paid close attention to my code and and boosts my confidence that my code is not the fault.

             

            Your code is not modular. It also mixes parameter validation with multiple specialized code blocks.

            I heartily agree that my function GET_OBJECT_SOURCE is overly large and should be broken in sub methods, and although many of you other suggestions are good and where worth considering in general, they don't suit my particular environment and requirements.

            For my particular requirements your suggestions as a whole would be over engineering.

             

            As the packages sole purpose is to support the before mentioned select statement, I have no requirement to, and hence should not, have any other exposed methods in the package.

            Always remember the mantra.. "Don't develop for potential needs..only code to meet the requirements"...otherwise you may get fired for wasting your employers resources.

             

            The problem with this is that the implicitly created types will have system generated names and, in 11g, those types will be 'hidden' so users may not even know that they exist.

            By defining explicit types you can control the names that are used as well as use those types for other code if desired.

            The problem as you put it is also a strength. I don't want users to see my types, and I would prefer them not to be able to see my package. I just want them to see result set based on

            Select OWNER, OBJECT_TYPE, NAME, LINE, TEXT from table(PG_CODE.GET_OBJECT_SOURCE('VIEW')) order by 2, 3, 4;

            As the purpose of this package was to aid in creating and maintaining installation/upgrade scripts for our DB products, it was desirable to have the package self contained (i.e. not dependent on other custom packages and types)

             

            In the argument of SQL vs PLSQL type, I find that the choice often comes down to the following

            SQL:  Either a. generalized types, declared with the intention of being used in multiple packages, or b. records used by apps external to oracle e.g. jdbc. As such these type become "entrenched" and difficult to change as it means changing/testing everything.

                     In practice I find SQL type are more obscure to a developer as its definition is not included in the code you are editing and the you has to look elsewhere to see its field names.

            PLSQL:  Highly customized types to meet the needs of this package and its single caller (where the caller is another Oracle object, typically a view or another package) ... so its definition is always immediately on hand and easy to modify. The downside is that the system creates "auto-named" shadow SQL types. I am looking forward to 11g where these types are hidden.

             

            IMHO it is more standard to use the keyword DEFAULT to make it clear that NULL is the default value for each parameter.

            Fair enough but in my experience, I find the lack of the DEFAULT keyword more common, I don't think it adds clarity nor do I see a benefit in making the method declaration longer.

             

            add an overloaded function that returns a table of CLOB

            This would be pointless, it would be doing almost nothing more then the original GET_DDL. The main advantage of the package is that it coverts CLOB's to a table of varchar's,

             

            I disagree with your suggestion to :exclude objects that cause errors and to have another function that validates objects. The only way to validate for all problems is to call the DDL function, and as the DDL function is extremely slow calling it once, just to see if it works and return a boolean, just so you know its ok to call is again is very inefficient.

             

            You might also consider adding some security checks to make sure an 'owner' doesn't try to get metadata for objects they should not be referencing

            I had already considered this, but for our purposes, decided it was over-engineering, as either the package owner can see the object, or the DLL raises and error. In our use the package can only be run by its owner, and its owner, owns all non system objects.

             

             

            Thank you again for your reply, It gave me much food for thought...

            • 3. Re: Connection close after DBMS_LOB.READ and DBMS_METADATA.GET_DDL
              rp0428

              I heartily agree that my function GET_OBJECT_SOURCE is overly large and should be broken in sub methods, and although many of you other suggestions are good and where worth considering in general, they don't suit my particular environment and requirements.

              For my particular requirements your suggestions as a whole would be over engineering.

              Writing modular code can hardly be considered 'over engineering. Your 'particular requirements' are all the more reason to use modular code to get the benefits: easier to understand, easier to test, easier to maintain. And since the use of modular code represents best practices and there are NO disadvantages to using it, even in your case, then there is NO justification for not using it.

              As the packages sole purpose is to support the before mentioned select statement, I have no requirement to, and hence should not, have any other exposed methods in the package.

              Even if true that is no justification for not using a modular architecture; just put the other modular methods in the package body. Then you still get ALL of the benefits of the modular approach.

              Always remember the mantra.. "Don't develop for potential needs..only code to meet the requirements"...otherwise you may get fired for wasting your employers resources.

              That's pure RUBBISH! The best use of resources is writing modular code. Small, well-defined modules are easier to: design, code, test and maintain. I was able to review your code and, in a matter of minutes, identify a possible set of target modules/functions that would be appropriate.

               

              Don't know where you got that but that 'mantra' is more likely the best way to get you fired. Requirements are ALWAYS changing; usually before the previous requirements can even be implemented let alone deployed. Experienced developers must ALWAYS anticipate possible new requirements and design for them when possible. Part of that design is to abstract out the 'common' part of the requirements to use as a base and then add a layer for a specific requirement.

               

              For example, in your use case you have a comment that says this

              -- For some reason GET_DDL needs the type name to contain underscores rather then spaces.

              EXACTLY my point! See table 87-11 'DBMS_METADATA: Object Types' in the Packages and Types doc

              http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_metada.htm#BGBIEDIA

               

              That 'reason' is because that is how the API is defined. So of course you have to use underscores rather than spaces.

               

              You need to code to the API of the library that you are using. That is why your should have a method that uses parameters with VALIDATED values that you know match the API requirements.

               

              Then, if you want users to be able to call them using parameters with spaces (and I don't know why you would even do that) then you should write a procedure/function that accepts the user provided value, transforms it (maps it) to a valid value and then calls the base method that ONLY accepts values that the API defines.

               

              That is the proper way to code for exceptions. And that is why it is important to separate your particular use case requirements (unusual parameter values) from the standard API usage. That allows you (or a future user) to use the standard API without your custom version of things getting in the way. You get the best of both with no overhead at all.

               

              With your implementation mixing everything together you can't make certain types of modifications without rewriting it. And if you do that you risk breaking parts of it that work when you try to change other parts to add something new.

               

              It certainly doesn't take much imagination to think that it might be useful to be able to use the actual API directly without the need to manipulate the parameters looking for spaces and things. Simpler to design, code, test and maintain.

              As the purpose of this package was to aid in creating and maintaining installation/upgrade scripts for our DB products, it was desirable to have the package self contained (i.e. not dependent on other custom packages and types)

              And the point I was making is that your package is NOT self contained even though you may think it is. Oracle is creating multiple SQL types behind the scenes that you won't even know are there and may not be owned by the user you think they are.

              The problem as you put it is also a strength. I don't want users to see my types, and I would prefer them not to be able to see my package.

               

              Go ahead and use a 'controlled' user and create the needed types. Then other users won't be able to see, use or manipulate them. But at least you can now control the types, and their names, and document their usage. Having hidden things created behind the scenes is VERY BAD! What happens when you export your package? Do you think those 'hidden' types get exported with the package? If you do you may want to test that; you are in for a surprise.

               

              And if someone creates a second package Oracle will reuse one of those 'hidden' types for that second package, if needed, without you even knowing it. Now you have a new 'hidden' dependency.

              As such these type become "entrenched" and difficult to change as it means changing/testing everything.

              Nothing becomes 'entrenched' unless it is used. There is no more and no less changing/testing just because SQL types are used.

               

              You are just asking for trouble if you let Oracle create implicit objects like that. You should reconsider that decision as your arguments are not valid.

              add an overloaded function that returns a table of CLOB

              This would be pointless, it would be doing almost nothing more then the original GET_DDL. The main advantage of the package is that it coverts CLOB's to a table of varchar's,

               

              No - that would be the standard, modular way to architect the process.

               

              You want to build small, well-defined modules that work. Then you combine those modules to build more complex functionality.

               

              The first part of that is to build a small module that calls the DBMS_METADATA API properly for ALL of the argument types that you plan to support. That needs to be thoroughly tested.

               

              Chapter 20 'Using the Metadata APIs' extensively covers that topic with example code. It is a very complex API to understand and use properly.

               

              There is even a special section for what you are trying to do:

              Using the DBMS_METADATA API to Retrieve Collections of Different Object Types

               

              The closer you stay to the tried and true path the fewer problems you will have.

               

              Do what you will. But you will have far more success, and far fewer problems, if you adopt modular methods to solving the problems you are working with.

               

              Good luck!

              1 person found this helpful
              • 4. Re: Connection close after DBMS_LOB.READ and DBMS_METADATA.GET_DDL
                BrendanTheAussie

                Hi All,

                 

                I have managed to clarify the events that may lead up to the error(s). (Happens about 50% of the time)

                • Open a connection in SQL Developer
                • run the following query
                • Select OWNER, OBJECT_TYPE, NAME, LINE, TEXT from table(PG_CODE.GET_OBJECT_SOURCE)
                • using the same connection open the package body source for PG_CODE
                • make some minor code change  (say add a comment line) and press the compile button.


                I managed to catch the Oracle error I have been receiving most of the time, it is:

                Error: ORA-00600: internal error code, arguments: [17285], [0xFFFFFD7FFB2F7D50], [1], [0x3C9A689E8], [], [], [], []

                 

                Occasionally, I get the following log entry without the above error

                source: o.d.db.DBUtil      

                Message: Warning, unhandled exception: Closed Connection


                Also very rarely Oracle Sql Developer also show a dialog box with the message

                An error was encountered performing the requested operation:

                     Close connection

                     Vendor code 17008

                 

                ORA-600 is an internal error generated by the generic kernel code of the Oracle RDBMS software. So I believe it is either a bug in my version of Oracle or my version of SQL Developer.

                 

                For reference select * from v$version outputs the following...

                 

                Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

                PL/SQL Release 10.2.0.1.0 - Production                          

                CORE    10.2.0.1.0    Production                                        

                TNS for Solaris: Version 10.2.0.1.0 - Production                

                NLSRTL Version 10.2.0.1.0 - Production         

                 

                My Sql Developer version is

                Version 3.2.20.09.

                Build MAIN-09-87


                One of the Trace files generate can be found at

                      https://www.dropbox.com/s/v0uqpi177zyp4xw/dev01_ora_5894.trc