Thanks to Oracle Dev Gym for awarding me Database Innovator Badge 2021 for participating in the 2021 Oracle Database 21c Competition.
Category Archives: PL/SQL
Last updated on October 25th, 2021 at 03:56 pm
Excited to announce the launch of Mahawar Vijay & Co website – http://www.vijaymahawar.com
#newproject #newwebsitelaunch #datascience #plsql #sql #python #transitionplanning
Last updated on October 14th, 2021 at 05:47 pm
I am glad to share with you all that I completed the training on demand on “Oracle Database 12c R2: Advanced PL/SQL”.
Thanks to Oracle University and my faculty Brent Dayley for providing this training on demand.
The training covered important features in Oracle PL/SQL in 12c R2 version. It was well designed and gave a good head-start from the technical perspective.
I would also like to thank Oracle University which provides wonderful platform for such courses.
My detailed resume can be found here.
Last updated on June 3rd, 2018 at 11:11 pm
Its pleasure to inform you all that I completed Oracle Apps R12 Technical Training. It was an online training session by Sridevi Koduru.
The training material and sessions were very clear and to the point! It was well designed and gave a good head-start in Oracle Apps Financials from the technical perspective.
I would also like to thank Udemy which provides wonderful platform for such courses. #BeAble
My detailed resume can be found here.
I am delighted to share with you that I have secured 27th position in Annual Logic Championship for 2015 🙂
The championship was organised by Oracle PL/SQL Challenge. The championship was held on invitation only basis. There were 41 participants from across the world. Below is the result of the championship.
The number next to the name represents the number of times the player has participated in a championship. Thanks Oracle and PL/SQL Challenge for the public recognition of my accomplishment.
I am ecstatic to share with you that I have been awarded by Oracle PL/SQL Challenge, an online website which offers a daily quiz on Oracle PL/SQL. The website has quickly attracted over 1,000 daily players, making it one of the most active PL/SQL-related websites on the Internet.
I am awarded for “High Ranking in Month” for September 2015 and November 2015 in their competition “I Love Logic“.
High Ranking in Month for November 2015
High Ranking in Month for September 2015
Public recognition of accomplishment on PLSQL Challenge website
Its an honour to win this award among 1400 active participants from across the globe 🙂 Thanks Oracle and PL/SQL Challenge for the public recognition of my accomplishment 🙂
Diary on Sangam’15
21st, 22nd Nov 2015 at Hyderabad International Convention Centre, HITEC City, Hyderabad, India
In the recently concluded two day Oracle conference – Sangam 2015, there were more number of attendees than last year (Sangam 2014). Sangam – An annual conference for Oracle Technologist across India is organised by AIOUG (All India Oracle Users Group) and aptly has tagline of ‘meeting of minds…’.
It was a privilege to attend such a large scale event for third consecutive year. It was an honour to get in touch with the Oracle ACE and Oracle ACE Directors and hear them speak in front of august gathering. There were numerous sessions lined-up as always. I am sharing sessions attended by me during the two days conference in brief.
Day 1: 21st November 2015
Kamran Agayev – Oracle Certified Master – His session on “Oracle 12c ASM new features” with webetorial was very crisp and provided lot of insights into the world of Oracle 12c for ASM.
Aman Sharma – Oracle ACE – His session on “Oracle RAC Node Eviction” was overwhelming and demonstrated his expertise in the RAC yet again. It was very useful.
Day 2: 22nd November 2015
Oracle VM and Oracle Linux – Kamal Dodeja (Sales Consulting Manager) and Ritesh Kumar (Senior Technical Consultant) from Oracle India Pvt. Ltd. – Their session on Oracle Linux and Oracle VM was primarily revolving around the Technical features offered by Oracle Linux and Oracle VM and concluded with the pricing details.
I had previously blogged about installation of Oracle VM, Oracle Linux and Installation of Oracle 12c on Oracle Enterprise Linux which is available here.
One interesting takeaway from this session of theirs was ksplice – which actually provides high availability and reduces downtime. Overview, Downloads and Documentation on ksplice is available at OTN here. It was a strategic acquisition made by Oracle in 2011. This enables important kernel patches to be applied without reboot or without bringing down any application services in Oracle Linux.
Debaditya Chatterjee – Product Manager Oracle USA – Oracle Database In-Memory Options – It was a good review of Oracle In-Memory database which was delivered by Maria Colgan in the last edition of Sangam. His session focused on the what’s and why’s of Oracle In-Memory database.
Satyendra Pasalpudi – Oracle ACE Director – His session on “Oracle Cloud DBA” showcased the next thing from Oracle – Oracle Cloud. The session was mainly for DBAs.
The conference closed with a motivational speech by “Dr. Rajdeep Manwani” – who shared his priceless life experiences about hard-work, patience, perseverance, failure, success and imaginary finishing line in marathon when most of the contestant gives up.
Thanks to AIOUG and Oracle Technology Network (OTN) for organising such events and providing Oracle fraternity in India a splendid opportunity to hear the speakers from elite Oracle community 🙂
Last updated on November 26th, 2014 at 07:07 am
Comments are text embedded within SQL statements and PL/SQL code which describes the purpose of the code. Comments improve the readability, usability and maintenance of the code. Proper comment and formatting can save lot of hours of unproductive work for developers and teams.
Oracle developers can use two types of comments – In Line and Multiline. Both Inline and Multiline comments can be used in SQL statements and PL/SQL.
In-Line comments starts with — (two hyphens) and is only limited to one line.
Multiline comments start with /* and ends with */ and spans multiple lines.
Oracle uses following comment styles for its standard scripts (scripts under – $oracle_home/rdbms/admin directory) which are shipped with oracle database.
-- -- Copyright (c) Oracle Corporation 1988, 1999. All Rights Reserved. -- -- NAME -- demobld.sql -- -- DESCRIPTION -- This script creates the SQL*Plus demonstration tables in the -- current schema. It should be STARTed by each user wishing to -- access the tables. To remove the tables use the demodrop.sql -- script. -- -- USAGE -- SQL> START demobld.sql -- --
In many oracle shipped sql scripts you will see REM commands (REMARK command). This can also be used to enhance the readability and maintenance of the sql scripts. A sample snippet from the mksample.sql is shown below:
Rem Rem $Header: mksample.sql.sbs 02-apr-2003.14:55:17 $ Rem Rem mksample.sql Rem Rem Copyright (c) 2001, 2003, Oracle Corporation. All rights reserved. Rem Rem NAME Rem mksample.sql - creates all 5 Sample Schemas Rem Rem DESCRIPTION Rem This script rees and creates all Schemas belonging Rem to the Oracle Database 10g Sample Schemas. Rem If you are unsure about the prerequisites for the Sample Schemas, Rem please use the Database Configuration Assistant DBCA to Rem configure the Sample Schemas. Rem Rem NOTES Rem - OUI instantiates this script during install and saves it Rem as mksample.sql. The instantiated scripts matches Rem the directory structure on your system Rem - Tablespace EXAMPLE created with: Rem CREATE TABLESPACE example Rem NOLOGGING Rem DATAFILE '<filename>' SIZE 150M REUSE Rem AUTOEXTEND ON NEXT 640k Rem MAXSIZE UNLIMITED Rem EXTENT MANAGEMENT LOCAL Rem SEGMENT SPACE MANAGEMENT AUTO;
In my PL/SQL programs I use multi-line comment style like below:
/************************************************************************ ** Purpose: Script to demonstrate the top 10 features in Oracle 12c for developers ** Date: 1st Feb 2014, Saturday ** Author: Vijay Mahawar ** Website: https://www.mahawar.net/blog ** Version: 1.0 *************************************************************************/
Oracle allows to store comments even for schema objects like table and views and on columns of table, views and materialized views. These comments provide useful information about the table and columns to other developers.
To insert an explanatory remark on the
notes column of the EMP table, you might issue the following statement:
COMMENT ON COLUMN EMP.EMPNO IS 'Employee Number assigned to Employees';
The comments defined in this way are stored in data dictionary views.
Object comments are stored in USER_TAB_COMMENTS
Column comments are stored in USER_COL_COMMENTS
To drop this comment from the database, issue the following statement:
COMMENT ON COLUMN EMP.EMPNO IS '';
Note: Few minutes spent on proper comments can save hours of efforts in code maintenance and debug.
Last updated on November 26th, 2014 at 07:07 am
There has been paradigm shift in the technological and scientific innovations that are happening around us. From First generation Vaccum Tubes to Latest generation of Nanotechnology, From Machine Language to High Level Programming Language, Technological innovation has made the computing devices – cheaper, faster, smaller.
The communication between the devices has seen a phenomenal growth. The volume of data produced by all this is rising exponentially. Today technology touches human lives more than ever before.
With all the technological and scientific progress happening around us we have kept our rich cultural heritage and values intact. We as humans are evolving so is the Oracle Database Product.
Over the years there have been several Oracle Database releases and version upgrades. What has remained mostly same are the best practices.
Here is the list of best practices which you can expect in my next few blog posts.
SQL and PL/SQL:
- Comments – In Line and Multi Line
- Replace Hard coded literals with Constants and move all related constants into a common package.
- Modular Approach – Split big program into generic and standalone sub-programs.
- Instrumentation whereever possible – ON/OFF as required.
- Achieve as much as poosible with SQL, to avoid context switching between SQL and PLSQLengine.
- Exception Handling – Try to handle all possible exceptions
- Avoid “Select * from”, Use fully qualified names when selecting from multiple tables
- Mention AUTHID clause, BEQUEATH clause,
- Avoid default Oracle implicit type conversion.
- Use %ROWTYPE for record types and %TYPE for variables. Avoid hardcoding VARCHAR2 length.
- Exit the program gracefully. Make sure to free up resources and memory before exiting.
- Familiarize with in-built oracle packages – Don’t write routine which is already provided by oracle.
- Show user, con_id – set timing on and many more, use spool in SQL scripts
- Ensure that the rollback scripts are ready.
- Compile objects in the order of precedene.
- Ensure you use paranthesis in expressions to override the default precedence order.
- Start with the data model and then proceed with the coding for complex SQL queries.
- Maintain test scripts/harness to test the logic and test performance.
- Use GET and SET to modify package variables.
- Use NOCOPY to copy variables by reference for performance benefits.
- Use control tables to make the code customized and generic.
- Group related privileges and grant it to a role for better manageability.
- Lock all unused user accounts and change default passwords for all default accounts
- Use seperate disks for system, sysaux; application data and Index tablespaces for better performance.
- Use comments whenever a parameter is changed – Record – date, time and changed by.
- Use seperate disks for multiplexed redo logs files in same redo log groups.
- Gather statistics using DBMS_STATS after BULK DML on tables and partitions.
- Schedule the housekeeping jobs to runs during off peak time.
- Keep oracle documents and useful links ready and handy for reference just like a bible.
In my next few blog posts, I shall showcase some of the best practices both for Developers and DBAs with the help of demo scripts.
This list is not complete and is ever evolving. It is outcome of my years of association with Oracle technology and the learnings I had taken from Oracle pundits. Its kind of survival kit for both Developers and DBAs.