SQL, PL/SQL, T-SQL programming
Bogs about SQL, T-SQL and PL/SQL coding. Basic concepts and skills of the programming language, application developing, as well as advanced technology and techniques.
Oracle: Granting the Ability to Create & Execute PL/SQL Stored Programs
- Details
- Written by sepia
- Hits: 317
Problem
You want to grant someone the ability to create and execute stored PL/SQL programs.
Solution
To grant the ability for a user to create a procedure, function, or package, you must log in to the Oracle database with a privileged account and grant the CREATE PROCEDURE privilege to the user. Here's an example:
PL/SQL: Writing Initialization Code for a Package
- Details
- Written by sepia
- Hits: 219
Problem
You want to execute some code each time a particular PL/SQL package is instantiated in a session.
Solution
Create an initialization block for the package in question. By doing so, you will have the ability to execute code each time the package is initialized. The following example shows the same package that was constructed in this article. However, this time the package contains an initialization block.
PL/SQL package: Collecting Related Routines into a Single Unit
- Details
- Written by sepia
- Hits: 199
Problem
You have a number of procedures and functions that formulate an entire application when used together. Rather than defining each subprogram individually, you prefer to combine all of them into a single, logically related entity.
Solution
Create a PL/SQL package that in turn declares and defines each of the procedures together as an organized entity. You declare each of the subprograms in the package specification (otherwise known as a header) and define them in the package body.
PL/SQL procedure: Setting Default Parameter Values
- Details
- Written by sepia
- Hits: 222
Problem
You want to create a PL/SQL procedure that accepts several parameters. However, some of those parameters should be made optional and contain default values.
Solution
You can allow the procedure caller to omit the parameters if default values are declared for the variables within the procedure. The following example shows a procedure declaration that contains default values:
PL/SQL: Passing Parameters by Name
- Details
- Written by sepia
- Hits: 271
Problem
You have a PL/SQL procedure in your Oracle database that accepts a large number of parameters. When calling the procedure, you would rather not worry that the positioning of the parameters is correct.
Solution
Rather than trying to pass all the parameters to the procedure in the correct order, you can pass them by name. The code in this solution calls a procedure that accepts six parameters, and it passes the parameters by name rather than in order.
A Brief History of SQL
- Details
- Written by Александров Попков
- Hits: 351
User Rating: 5 / 5
Dr. Edgar F. Codd (1923–2003), an IBM research scientist, first conceived the relational database model in 1969. He was looking into new ways to handle large amounts of data in the late 1960s and began thinking of how to apply mathematical principles to solve the myriad problems he had been encountering.
After Dr. Codd presented the relational database model to the world in 1970, organizations such as universities and research laboratories began efforts to develop a language that could be used as the foundation of a database system that supported the relational model. Initial work led to the development of several different languages in the early to mid-1970s. One such effort occurred at IBM’s Santa Teresa Research Laboratory in San Jose, California.
Define PL/SQL Data Types: varchar2, number, date, Boolean, VARRAY
- Details
- Written by Андрей Волков
- Hits: 523
User Rating: 5 / 5
The use of local variables within a PL/SQL program is important for everyone using the language to understand. It is a basic component of each program, and as such, it is invaluable for understanding what is available and how best to use it. You can now look at how you use and define variables and working storage within your PL/SQL programs.
As with all programming languages, there are characters that you use to write your programs. Each language has its own rules and restrictions when it comes to the valid characters. In the following sections of article, we will show you:
Describe the Basic PL/SQL Program Structure
- Details
- Written by Андрей Волков
- Hits: 448
User Rating: 5 / 5
The structure you use in PL/SQL is the foundation for the language as a whole. Once you’ve mastered it, you will then be able to move forward; however, if you do not take the time to get this first step right, your journey will be difficult. Thankfully, it’s quite simple.
The structure is quite basic. You will have areas for your program parameters (these are used to pass values from outside a program to the program itself), your internal variables, the main program code and logic, and various ways to deal with problem situations. Let’s look at the basic form of a PL/SQL block:
Why Oracle DBAs learn PL/SQL and how to apply it
- Details
- Written by Андрей Волков
- Hits: 450
User Rating: 5 / 5
Oracle Database 12c is more than just a database management system - it’s also an engine for many programming languages. Not only does it serve as a Java engine with the built-in Java Virtual Machine (JVM), it’s a PL/SQL engine as well. This means that the code you write can be stored in a database and then run as required when it is needed.
The PL/SQL engine is bundled together with the database and is an integral part of the Oracle server, providing you with a powerful language to empower your logic and data. Let’s look at how PL/SQL fits into the Oracle server. Figure 1 shows you how PL/SQL works from both within and outside the Oracle database.