Advantages of PL/SQL
Tight Integration with SQL
Better Performance
Higher Productivity
Full Portability
Tight Security
Access to Pre-defined Packages
Support for Object-Oriented Programming
Support for Developing Web Applications and Pages
Support for Developing Web Applications and Pages
You can use PL/SQL to develop Web applications and Server Pages (PSPs).
PL/SQL Block
PL/SQL is a block structured language
It is composed of one or more blocks
Types of Blocks
Anonymous Blocks: constructed dynamically and executed only once
Named Blocks: Subprograms, Triggers, etc.
Subprograms: are named PL/SQL blocks that are stored in the database and can be invoked explicitly as and when required; e.g. Procedures, Functions and Packages
Triggers: are named blocks that are also stored in the database; Invoked implicitly whenever the triggering event occurs; e.g. Database Triggers
PL/SQL Block Structure
A PL/SQL block has the following structure
DECLARE
Variables, cursors, user-defined exceptions, etc.
BEGIN
SQL and PL/SQL statements
EXCEPTION
Trapping Errors occurred in executable section
END;
Note: BEGIN & END are compulsory statements
PL/SQL Block consists of three sections
Declarative: Contains declarations of variables, constants, cursors, user-defined exceptions and types (Optional)
Executable: Contains SQL statements to manipulate data in the database and PL/SQL statements to manipulate data in the block
Exception Handling: Specifies the actions to perform when errors and abnormal conditions arise in the executable section (Optional)
Greetings Mate,
ReplyDeleteGreat info! I recently came across your blog and have been reading along.
I thought I would leave my first comment. I don’t know what to say except that I have
We have a weird issue with MSSQL 2012 on AMAZON (RDS), at one point the CPU spikes to 20 % and the 20% is now the new 0% until I change the threshold for parallelism. This means the cpu stays constant > 20% during the day. After the modification of the parameters the cpu drops and the sql server shows normal CPU signs going from 5% to 10% with some higher spikes. I have no idea why this happens and why this resolves itself when I change the max dop or the threshold for parallelism.
Excellent tutorials - very easy to understand with all the details. I hope you will continue to provide more such tutorials.
Regards,
Ajeeth Kapoor
Halo,
ReplyDeleteFully agree on PL/SQL Introduction. We’re seeing a lot of projects tackle big complex problems but few seem to have taken into consideration and in particular reasons to adopt.
Currently I'm trying to make a test using create job as a way to have multiple process on database starting in a loop.
Basically I'm getting different behaviors when testing on different database machines. I'll define the machine names are A and B.
If I started running the anonymous block below in the machine A for a loop to create 3 multiple jobs, is working fine, i.e, the create job using enabled parameter, created the job and automatically start:
DECLARE
L_job_name VARCHAR2(100);
L_comments VARCHAR2(240);
L_pls_block VARCHAR2(32000);
L_thread NUMBER := 1; -- count of jobs
L_max_threads NUMBER := 3; -- max number of jobs to be started
L_lot_id NUMBER := 1234; -- add any number just to represent a lot
BEGIN
while L_thread <= L_max_threads loop
---
L_job_name := 'job_' || L_lot_id || L_thread;
L_comments := 'Job test ' || L_lot_id || ' and thread ' || L_thread;
L_pls_block := 'BEGIN logger.log(''job running of thread' || L_thread || '''); END;';
---
dbms_scheduler.create_job(job_name => L_job_name,
job_type => 'PLSQL_BLOCK',
job_action => L_pls_block,
start_date => SYSTIMESTAMP,
comments => L_comments,
enabled => true);
--DBMS_SCHEDULER.RUN_JOB(L_job_name, TRUE);
L_thread := L_thread + 1;
end loop;
END;
/
but if I start the same code in the machine B, nothing is happening and I need to uncomment the line about DBMS_SCHEDULER.RUN_JOB(L_job_name, TRUE);
I did another test in the same machine B using enabled FALSE when creating the job and during the call from DBMS_SCHEDULER.RUN_JOB changed to false in the user_credentials but only one job runned, i.e, the result was logged from just one job, instead of the 3 jobs that had most be created.
Do you have an idea about what I need to look? Is there something related about database setup, credentials that I need to take a look?
It was cool to see your article pop up in my google search for the process yesterday. Great Guide.
Keep up the good work!
Thank you,
Radhey
Thanks and Regards. Oracle Apps R12 Training Videos at affordable cost. please check oracleappstechnical.com
ReplyDeleteOracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check https://www.oracleappstechnical.com for Never Before Offers and Discount Coupon Codes.
ReplyDeleteOracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check https://www.oracleappstechnical.com for Never Before Offers and Discount Coupon Codes.
ReplyDelete