![]() ![]() loadblobfromfile ( dest_lob => l_blob, src_bfile => l_bfile, amount => DBMS_LOB. createtemporary ( l_blob, TRUE ) DBMS_LOB. WITH FUNCTION get_blob ( p_directory VARCHAR2, p_filename VARCHAR2 ) RETURN BLOB AS l_bfile BFILE l_blob BLOB l_blob_out BLOB l_dest_offset INTEGER := 1 l_src_offset INTEGER := 1 BEGIN l_bfile := BFILENAME ( p_directory, p_filename ) DBMS_LOB. I want to SELECT that BLOB value and save it to my local drive (which I can do easily from Toad or SqlDeveloper).Īs of Oracle 12.2 you can do this in straight SQL (see TO_CLOB and TO_BLOB enhancements), but I’m going to use the example anyway. The DBMS_LOB package has a procedure LOADBLOBFROMFILE that puts the result in an OUT parameter. In this system we cannot deploy schema level PL/SQL today, so we will use an inline function to provide the result. You cannot call that directly from a SQL select. Selecting a Value from a Procedure OUT ParameterĬonsider a scenario where the only methods available to you for retrieving some value is through a procedure with an OUT parameter. I like the inline PL/SQL method for it though. I admit to not having a complete grasp of all of the intricacies of the privilege stack where dynamic sql is involved, but I think it can be done. You might also be able to get around this limitation by using dynamic sql (EXECUTE IMMEDIATE) and AUTHID CURRENT_USER. 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. Declaring PL/SQL in an Oracle SQL WITH Clause ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |