tag:blogger.com,1999:blog-8677649049588007585.post1686292390440097009..comments2023-06-18T16:15:22.432+01:00Comments on PL/SQL Challenge: ALL_PROCEDURES Evolves Over Versions (5980)Steven Feuersteinhttp://www.blogger.com/profile/16619706770920320550noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-8677649049588007585.post-32349188896224265042011-08-19T19:54:14.923+01:002011-08-19T19:54:14.923+01:00We have already learned how to find out if a progr...We have already learned how to find out if a program unit is a procedure or a function in the quiz from 2011-01-12: if a record exists where "POSITION = 0" in the all_arguments-view then it is a function else it is a procedure.<br />Greetings, NielsAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-58708223024842312272011-08-19T12:42:05.653+01:002011-08-19T12:42:05.653+01:00Thanks for pointing that out, Syed. The problem wi...Thanks for pointing that out, Syed. The problem with not specifying 11g is that a person might be using 10.2.0.1 (the free download) and the column is not present in that release. We don't specify sub-release numbers for our assumptions, so we need to "move it up" to 11.Steven Feuersteinhttps://www.blogger.com/profile/16619706770920320550noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-74178715825475291492011-08-19T06:11:18.724+01:002011-08-19T06:11:18.724+01:00Excusme All,
I am objecting about the titled issue...Excusme All,<br />I am objecting about the titled issue, because in oracle db 10g version (10.2.0.3.0)include the column OBJECT_TYPE in the view ALL_PROCEDURES.<br />Please take a look--<br /><br />SQL> select * from v$version;<br /><br />BANNER<br />----------------------------------------------<br />Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod<br />PL/SQL Release 10.2.0.3.0 - Production<br />CORE 10.2.0.3.0 Production<br />TNS for 32-bit Windows: Version 10.2.0.3.0 - Production<br />NLSRTL Version 10.2.0.3.0 - Production<br /><br />SQL> desc all_procedures<br /> Name Null? Type<br /> ----------------------- -------- -------------<br /> OWNER VARCHAR2(30)<br /> OBJECT_NAME VARCHAR2(30)<br /> PROCEDURE_NAME VARCHAR2(30)<br /> OBJECT_ID NUMBER<br /> SUBPROGRAM_ID NUMBER<br /> OVERLOAD VARCHAR2(40)<br /> OBJECT_TYPE VARCHAR2(19)<br /> AGGREGATE VARCHAR2(3)<br /> PIPELINED VARCHAR2(3)<br /> IMPLTYPEOWNER VARCHAR2(30)<br /> IMPLTYPENAME VARCHAR2(30)<br /> PARALLEL VARCHAR2(3)<br /> INTERFACE VARCHAR2(3)<br /> DETERMINISTIC VARCHAR2(3)<br /> AUTHID VARCHAR2(12)<br /><br />I agreed with the earlier versions argument but within 10g we get the output as above. So, we do not need to go for oracle 11g right now for this <br />quiz.<br /><br />Thanks All<br /><br />Syed Ariful Bari<br />A quiz perticipantSyed Ariful Barinoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-37612341064363193802011-08-18T17:41:43.398+01:002011-08-18T17:41:43.398+01:00Hello All,
Though the issue of determining whether...Hello All,<br />Though the issue of determining whether a packaged subprogram is a procedure or a function has already supplied us much fun and lots of creative action and learning since we play the PL/SQL Challenge, maybe time has indeed arrived to have this information explicitly included in the *_PROCEDURES views.<br /><br />I am almost sure that this will come in some future version, maybe as a PROCEDURE_TYPE column, that will accompany the PROCEDURE_NAME.<br />It's strange enough that this was not done yet.<br /><br />Thanks & Best Regards,<br />Iudithiudithhttps://www.blogger.com/profile/04905902445036068357noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-16049190694501011522011-08-18T14:57:15.164+01:002011-08-18T14:57:15.164+01:00Ah, yes, I did miss something else. Another player...Ah, yes, I did miss something else. Another player wrote with the following observation:<br /><br />"The answer about using the all_procedures to determine which package routines are functions and which are procedures is confusing. I chose that to be false, because it can't be used reliably. However it can be used sometimes, procedures can't be declared deterministic, so if deterministic is YES then the routine must be a function. Same with pipelined or aggregate. So, my guess is you'll get players answering both ways based on either argument."<br /><br />Very interesting! Yes, certainly, for some entries in ALL_PROCEDURES you could deduce that it must be a function and could not be a procedure. But you cannot determine the subprogram type for all rows in the view.Steven Feuersteinhttps://www.blogger.com/profile/16619706770920320550noreply@blogger.com