![]() ![]() Note that this is not likely to please certain control freaks, so keep it to yourself. If you need PL/SQL and face a limitation where you cannot get the direct grants, this might be a way around it. ![]() Granted that the function doesn’t do anything that we could not have done directly in the query, but the test was merely to prove that we can avoid the issue with grants through roles versus direct grants to the schema owner. If I attempted to create a function that read from the table HR.job_history, the create would fail. I have the role privilege HR_SELECT, and that role has been granted SELECT on HR.job_history. Role Access for Inline PL/SQLįor this test my schema has not been granted SELECT on HR.job_history. Or perhaps your access to the database elements you need is through roles, not direct grants, so you cannot deploy PL/SQL that uses those elements. Maybe you have a real need for PL/SQL, like calling procedures with OUT parameters then selecting the value in a query, but you have query-only access on a system. ![]() You can deploy PL/SQL to that system after going through the formal process, but not today. The best example I have is doing adhoc queries on a production system. Consider the case where you cannot deploy schema level PL/SQL. I would also add a caveat that if the procedure is big and gnarly, I would rather compile it in the schema than trying to debug it in the middle of a giant query. There are not that many tasks that are so difficult in SQL that this condition exists, but there are some where procedural code is just a better answer. The best case I have for deploying inline methods in production code is when the logic is specific to that single query, and is not easily and cleanly done directly in SQL. Yet in the same release we were given PRAGMA UDF which allows optimizing schema level PL/SQL functions as inline. The primary reason (allegedly) that Oracle provided for defining the PL/SQL code inline is to improve performance by avoiding context switching. Tim Hall of Oracle Base fame has a good primer on it WITH Clause Enhancements in Oracle Database 12c Release 1 (12.1). The Oracle documentation for it that I have found so far is sparse. The capability to define PL/SQL functions and procedures inside an Oracle SQL query (and even the query portion of DML statements) was added in Oracle version 12.1.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |