Posts

All About PL/SQL Compilation Settings

Image
A recent Twitter thread delved into the topic of the best way to enable PL/SQL warnings for program units, including this recommendation from Bryn Llewellyn, Distinguished Product Manager for PL/SQL:


which then led to Bryn volunteering me to delve into the details of PL/SQL compiler settings in an AskTOM PL/SQL Office Hours session. 


Which I will do. But I figured I could start right off by writing this post. So let's explore how to set and modify PL/SQL compiler settings.

First, you might wonder what those settings are or could be. The best way to check is by examining the USER_PLSQL_OBJECT_SETTINGS view (and of course the ALL* version to examine attributes of code you do not own but can execute):



The values that are stored for a PL/SQL unit are set every time it is compiled—in response to "create", "create or replace", "alter", invoking a utility like Utl_Recomp, or implicitly as a side effect of trying to execute an invalid PL/SQL unit.

A spec…

How to debug your dynamic SQL code

Image
Got this plea for help via our AskTOM PL/SQL Office Hours program:

Dear Experts, I have below written below code: ---------------------------------------------- Declare v_Table all_tables.TABLE_NAME%type; v_Mnt varchar2(2):='08'; Type Cur_type is Ref Cursor; C Cur_type; Begin v_Table:='ddi_ticket_10_1018'; Open C for 'SELECT * from bill.'||v_Table||v_Mnt||'Where called_nbr=123'; End; ------------------------------------------------------------------- When executing this code, I face this Error message. ORA-00933-SQL Command not properly ended ORA-06512: At Line 9. Please check the above code and modify for syntax correction
I could, at a glance, pretty well guess what the problem is.

Can you?

I am not trying to boast. I just encourage you to not read further and instead examine the code. What could be causing his problem?

Dy…

Code You Should Never See in PL/SQL

Image
If you ever run across any of the following, apply the suggested cleanup, or contact the owner of the code, or run for the hills.

And I am pretty sure many of my readers will have suggestions for other code that should never appear in your PL/SQL programs. Let me know in the comments and I will add them to the post (giving me all the credit of course - no, just joking! YOUR NAME will be bright lights. :-) ).

Here's the list of things to avoid:

DON'T set default values of variables to NULL.DON'T select from dual for....just about anything.DON'T declare the FOR loop iterator.DON'T trap NO_DATA_FOUND for inserts, updates and deletes.DON'T execute the same DML statement inside a loop.DON'T hide errors.DON'T re-raise an error without logging first. See below for the gory details.

Set default value to NULL

Whenever you declare a variable it is assigned a default value of NULL. So you should not explicitly provide NULL as a default value. It won't do any ha…

The PL/SQL Collection Resource Center

Image
Collections (Oracle PL/SQL's data structure to implement arrays, lists, stacks, queues, etc.) are not only handy in and of themselves, but are used for many key features of this powerful database programming language, including:
High performance querying with BULK COLLECTSuper-fast, bulk non-query DML operations with FORALLTable functions (functions that can be treated like a table in a SELECT's FROM clause) PL/SQL offers three types of collections - associative arrays, nested tables, and varrays - each with their own characteristics and ideal use cases.
If you are not already using collections on a regular basis in PL/SQL, you are really missing out.
Use this article as starting point for accessing a number of useful resources for getting up to speed on collections, and putting them to use in your programs.
Documentation
The PL/SQL User Guide offers detailed coverage of collection features here. It starts by reviewing the differences between collections types.


Articles

ORACLE-BAS…

The PL/Scope Resource Center

Image
PL/Scope is a compiler-driven tool that collects PL/SQL and SQL identifiers as well as SQL statements usage in PL/SQL source code.  PL/Scope collects PL/SQL identifiers, SQL identifiers, and SQL statements metadata at program-unit compilation time and makes it available in static data dictionary views. The collected data includes information about identifier types, usages (DECLARATION, DEFINITION, REFERENCE, CALL, ASSIGNMENT) and the location of each usage in the source code.

Starting with Oracle Database 12cRelease 2 (12.2), PL/Scope has been enhanced to report on the occurrences of static SQL, and dynamic SQL call sites in PL/SQL units. The call site of the native dynamic SQL (EXECUTE IMMEDIATE, OPEN CURSOR FOR) and DBMS_SQL calls are collected. Dynamic SQL statements are generated at execution time, so only the call sites can be collected at compilation time. The collected data in the new DBA_STATEMENTS view can be queried along with the other data dictionary views to help answer …

Class on PL/SQL Table Functions at the Oracle Dev Gym

Image
http://bit.ly/dg-tf
A table function is a function that can act like a table inside a SELECT statement. The function returns a collection, and the SQL engine converts that collection into rows and columns that can be manipulated with standard SQL operations (joins, unions, where clauses, etc.).

Far and away the most popular post on this blog is an introduction to a series of articles on table functions:














Given that level of interest in a very interesting feature of PL/SQL, I thought it would be a good thing to give you even more resources to learn about table functions.

So I put together a FREE class at the Oracle Dev Gym on PL/SQL table functions. It consists of four modules and gives you a solid grounding in table function fundamentals:



Each modules consists of a video that covers the basics, followed by a LiveSQL tutorial that dives into more of the details, and gives you an opportunity to run and play with the code. We then finish up the module with quizzes to reinforce and deepen …

How to avoid spamming users from your applications

Image
Does your application send out emails? Lots of emails?

Did you ever get that feeling like someone punched you in the stomach when you realize that you mistakenly sent out hundreds or thousands of emails to your users when you didn't mean to?

I have. It's a terrible feeling. And these days, in the age of GDPR, there can be real consequences for invading the privacy of your users. This post explores how to make sure that, at least when you are developing and testing your code, you do not inadvertently spam your users.

The Oracle Dev Gym sends out lots of different kinds of emails to those players who have opted-in for them, such as:

Results of the quiz you just completedConfirmation of sign-up in a classReminder to take our weekly tournament quizzesHourly reports to site admins with any new errors in our logWeekly activity summaries to quizmasters The Dev Gym is an Oracle Application Express app, so we are able to happily and easily take advantage of the APEX_MAIL package, and it…