Top PL/SQL Tips In Just One Hour

Here's a video I recorded of a webcast to a team of developers who had one hour to spare to learn about some PL/SQL best practices. I offer tips on using compile time warnings, avoiding hard-coding/repetition, using nested subprograms and in the performance arena, lightning fast coverage of FORALL, function result cache, NOCOPY and PRAGMA UDF. Fun stuff and here's the guide:

03:00 Compile-time warnings
16:25 Don’t repeat anything
21:15 Tips on writing SQL in PL/SQL
24:50 Avoid hard-coding of formulas
28:35 Keep executable sections tiny
35:15 Avoid row-by-row processing
42:10 Function result cache
54:28 NOCOPY parameter hint
56:02 Optimizing user-defined function execution in SQL

========================================
Practically Perfect PL/SQL with Steven Feuerstein

Copyright © 2018 Oracle and/or its affiliates. Oracle is a registered trademark of Oracle and/or its affiliates. All rights reserved. Other names may be registered trademarks of their respective owners. Oracle disclaims any warranties or representations as to the accuracy or completeness of this recording, demonstration, and/or written materials (the “Materials”). The Materials are provided “as is” without any warranty of any kind, either express or implied, including without limitation warranties or merchantability, fitness for a particular purpose, and non-infringement.

[TheRealBigYang]

22:00 Even in my university projects I always have a separate file with just my sql statements, safely stored into functions, making a little library of database manipulation code. I don't need to change every single line of code, I just change that little database functions library.

[dhanukajayasinghe]

Steven, thanks champ. You just helped me clean up my immature code. Keep up the good work...

[zexalex]

bulks are meant for performance. but in the vast majority of use cases, simple and clear code is always a better option, despite the performance drawback.

[letsvisualizedata]

It is one of the best PL/SQL tutorials I have found in the youtube form the point of view of performance. Can you please create a tutorial on AWR report?

[bbujjin]

Always loved this channel my favourite you tube channel

[yotuberudy]

result_cache is what called memoization in many other languages

[SinskariBoithree]

MI was caught in the trap here, it is still unclear to me. Sorry, I started to feel silly. When I executed in Toad it returned Count=4. But still when I read it, I don't get it how it returned 4. Can you elaborate it or can you make an other post on this please.

In the function: store_and_return_date

--#1. First call to insert into the Collection: dates with parameter's SYSDATE
add_date (date_in);


IF call_in_plsql_in = 'YES'
THEN
--#2.Second call to insert into the Collection: dates, with SYSDATE current
add_date (SYSDATE);
END IF;

Since the table: tab has got 4 rows, what impact does it make while calling functions with parameter (call_in_plsql_in=YES/NO).

[Empire_Earth]

Are you gonna reupload this videos with the screen fix?