Hey there folks…noob to the forum, and hoping some here may be able to help.
I’m attempting to test a packaged procedure through SQL Nav 5.5. I’ve successfully retrieved sets of “simple”, single-value OUT parameters, both in the message window with DBMS_OUTPUT checked and in the execution interface with DBMS_OUTPUT unchecked.
I’ve been able to return a REF CURSOR result from a procedure when it is the only OUT parameter of a proecedure by unchecking DBMS_OUTPUT and expanding the result in the execution console/interface.
What I’m running into now is a bit more complex.
The procedure in question returns five OUT parameters. Two are varchar2 (100), One is CLOB, one is a REF CURSOR and one is a TYPE TABLE Object (custom defined record structure).
Running the procedure with DBMS_OUTPUT returns the varchar and CLOB parameters, but doesn’t display the TYPE or REF CURSOR content. Unchecking “DBMS_OUTPUT” causes errors/failures in content return.
How do I view this complex set of outputs from a single procedure in SQL Navigator?
I think only “simple” datatypes like VARCHAR2 or NUMBER are displayed. If you want to see the content of nested or other complex types, you’ll need to write a custom “display_the_contents_of” piece of code…
What’s the error you got when you ran the procedure unchecking the DBMS_OUTPUT?
And could you provide the script of your procedure? We can try to reproduce your error here.
Hey there. I'm attaching one file and two images the file is a simplified version of the procedure in question, along with scripts for the referenced TYPE Objects.
The first image is the result of running the procedure with DBMS_OUTPUT checked...note that the CLOB, the TABLE and the REF CURSOR didn't display, even though the procedure ran successfully.
The second image is the result of un-checking DBMS_OUTPUT.
As stated before, single-output procedures return CLOB and REF CURSOR outputs, which can be "expanded" for viewing, with DBMS_OUTPUT unchecked.
Single-return procedures with only a Schema-level TABLE parameter are effectively "un-viewable" with either selection option. TABLE and Record TYPEs defined within the package will show in the DBMS_OUTPUT, but won't work with external Java classes or other external applications, so I need to know how to see custom TYPE objects defined for the Schema when testing a packaged procedure.
Any input would be welcome, as I have about six similar procedures I'll need to develop and...hopefully...unit-test directly in the data-layer prior to integration into our application. Message was edited by: kallhands
Hey there. I'm attaching one file and two images the file is a simplified version of the procedure in question, along with scripts for the referenced TYPE Objects.
The first image is the result of running the procedure with DBMS_OUTPUT checked...note that the CLOB, the TABLE and the REF CURSOR didn't display, even though the procedure ran successfully.
The second image is the result of un-checking DBMS_OUTPUT.
As stated before, single-output procedures return CLOB and REF CURSOR outputs, which can be "expanded" for viewing, with DBMS_OUTPUT unchecked.
Single-return procedures with only a Schema-level TABLE parameter are effectively "un-viewable" with either selection option. TABLE and Record TYPEs defined within the package will show in the DBMS_OUTPUT, but won't work with external Java classes or other external applications, so I need to know how to see custom TYPE objects defined for the Schema when testing a packaged procedure.
Any input would be welcome, as I have about six similar procedures I'll need to develop and...hopefully...unit-test directly in the data-layer prior to integration into our application. Message was edited by: kallhands
Hey there. I’m attaching one file and two images the file is a simplified version of the procedure in question, along with scripts for the referenced TYPE Objects.
The first image is the result of running the procedure with DBMS_OUTPUT checked…note that the CLOB, the TABLE and the REF CURSOR didn’t display, even though the procedure ran successfully.
The second image is the result of un-checking DBMS_OUTPUT.
As stated before, single-output procedures return CLOB and REF CURSOR outputs, which can be “expanded” for viewing, with DBMS_OUTPUT unchecked.
Single-return procedures with only a Schema-level TABLE parameter are effectively “un-viewable” with either selection option. TABLE and Record TYPEs defined within the package will show in the DBMS_OUTPUT, but won’t work with external Java classes or other external applications, so I need to know how to see custom TYPE objects defined for the Schema when testing a packaged procedure.
Any input would be welcome, as I have about six similar procedures I’ll need to develop and…hopefully…unit-test directly in the data-layer prior to integration into our application. Message was edited by: kallhands
Well, I’m coming back a bit humbled and sheepishly revising the nature of my problem. If the script provided is revised to eliminate the TABLE object from output, the composite set of output parameters is, in fact, viewable in SQL navigator with DBMS_OUTPUT unchecked.
This means that the issue is related to the inclusion of TABLE type objects, whether defined in-package or at the schema level.
When defined in-package, they can be seen as part of the DBMS_OUTPUT, which excludes the CLOB and the REF_CURSOR. These cannot be seen with DBMS_OUTPUT unchecked.
When defined at Schema-level, they are seemingly not viewable at all. So, the question comes down to seeing TABLE TYPE objects while also seeing CURSOR and CLOB outputs.
This may be a theoretical stretch, but could I open another REF CURSOR against my populated TABLE TYPE, thus returning similar objects that I know how to deal with?
