<< < 1 2 3 4 5 >   Sort: Rank

What Do You Think about PL/SQL
What Do You Think about PL/SQL? - Oracle DBA FAQ - Introduction to PL/SQL After following through the tutorials in the FAQ collection, you probably agree that PL/SQL is indeed a general purpose database programming language. PL/SQL is a natural extension of SQL. It is very useful for DBA to automate...
2007-04-26, 4111👍, 0💬

How To Drop a Stored Procedure
How To Drop a Stored Procedure? - Oracle DBA FAQ - Creating Your Own PL/SQL Procedures and Functions If there is an existing stored procedure and you don't want it any more, you can remove it from the database by using the DROP PROCEDURE statement as shown in the following script example: SQL> CREAT...
2007-04-26, 4935👍, 0💬

How To Pass Parameters to Procedures
How To Pass Parameters to Procedures? - Oracle DBA FAQ - Creating Your Own PL/SQL Procedures and Functions Store procedures or functions can take parameters. You need to define parameters while defining the procedure, and providing values to parameters while calling the procedure. The script below s...
2007-04-26, 4461👍, 0💬

How To Manage Transaction Isolation Level
How To Manage Transaction Isolation Level? - Oracle DBA FAQ - Introduction to PL/SQL Transaction isolation level can be managed in a procedure by using the SET TRANSACTION and COMMIT statements. Here is a sample script on how to manage transaction isolation level: SQL> CREATE OR REPLACE PROCEDURE HR...
2007-04-26, 6256👍, 0💬

How To Pass Parameters to Procedures
How To Pass Parameters to Procedures? - Oracle DBA FAQ - Introduction to PL/SQL Store procedures or functions can take parameters. You need to define parameters while defining the procedure, and providing values to parameters while calling the procedure. The script below shows you how to do this: SQ...
2007-04-26, 4437👍, 0💬

How To Create a Stored Function
How To Create a Stored Function? - Oracle DBA FAQ - Creating Your Own PL/SQL Procedures and Functions A stored function is a function with a specified name and stored into the current database. If you want to create a stored function, you can use the CREATE FUNCTION statement. The example script bel...
2007-04-26, 4353👍, 0💬

How To Call a Stored Function
How To Call a Stored Function? - Oracle DBA FAQ - Creating Your Own PL/SQL Procedures and Functions A stored function can be called as part of expression in any PL/SQL statement. One simplest way to call a stored function is to a dummy SELECT statement as shown in the following tutorial script using...
2007-04-26, 4520👍, 0💬

How To Create an Array in PL/SQL
How To Create an Array in PL/SQL? - Oracle DBA FAQ - Introduction to PL/SQL If you want create an array data structure, you can use the collection type VARRAY. VARRAY stands for Variable Array. Here is a sample script on how to use VARRAY: SQL> set serveroutput on; SQL> DECLARE 2 TYPE list IS VARRAY...
2007-04-26, 6703👍, 0💬

How To Process Query Result in PL/SQL
How To Process Query Result in PL/SQL? - Oracle DBA FAQ - Introduction to PL/SQL You can run queries (SELECT statements) in a PL/SQL code blocks, and process the results a loop as shown in the following script example: SQL> set serveroutput on; SQL> BEGIN 2 FOR row IN 3 (SELECT * FROM employees WHER...
2007-04-26, 6023👍, 0💬

How To Drop a Stored Function
How To Drop a Stored Function? - Oracle DBA FAQ - Creating Your Own PL/SQL Procedures and Functions If there is an existing stored function and you don't want it any more, you can remove it from the database by using the DROP FUNCTION statement as shown in the following script example: SQL> CREATE O...
2007-04-25, 4320👍, 0💬

How To Call a Stored Function with Parameters
How To Call a Stored Function with Parameters? - Oracle DBA FAQ - Creating Your Own PL/SQL Procedures and Functions You can define a function that takes parameters, provide values to those parameters when calling the function. Here is a good example of a function with a parameter: SQL> CREATE OR REP...
2007-04-25, 4378👍, 0💬

What Are the Execution Control Statements
What Are the Execution Control Statements? - Oracle DBA FAQ - Introduction to PL/SQL PL/SQL supports three groups of execution control statements: IF Statements - Conditionally executes a block of statements. CASE Statements - Selectively executes a block of statements. LOOP Statements - Repeatedly ...
2007-04-25, 4210👍, 0💬

How To Use SQL Statements in PL/SQL
How To Use SQL Statements in PL/SQL? - Oracle DBA FAQ - Introduction to PL/SQL SQL DML (Data Manipulation Language) statements can be included in PL/SQL code blocks directly without any changes. See the script below for examples: SQL> CREATE TABLE tip (id NUMBER(5) PRIMARY KEY, 2 subject VARCHAR(80)...
2007-04-25, 4168👍, 0💬

How To Define a Sub Procedure
How To Define a Sub Procedure? - Oracle DBA FAQ - Creating Your Own PL/SQL Procedures and Functions A sub procedure is a named procedure defined and used inside another procedure or function. You need to define a sub procedure in the declaration part of the enclosing procedure or function. Sub proce...
2007-04-25, 4741👍, 0💬

How To Call a Sub Procedure
How To Call a Sub Procedure? - Oracle DBA FAQ - Creating Your Own PL/SQL Procedures and Functions To call a sub procedure, just use the sub procedure name as a statement. Here is another example of calling a sub procedure: SQL> CREATE OR REPLACE PROCEDURE WELCOME AS 2 PROCEDURE WELCOME_PRINT(S CHAR)...
2007-04-25, 7276👍, 0💬

How To Define a Sub Function
How To Define a Sub Function? - Oracle DBA FAQ - Creating Your Own PL/SQL Procedures and Functions A sub function is a function defined and used inside another procedure or function. You need to define a sub function in the declaration part of the enclosing procedure or function. Sub function defini...
2007-04-25, 7097👍, 0💬

How To Execute a Stored Program Unit
How To Execute a Stored Program Unit? - Oracle DBA FAQ - Introduction to PL/SQL If you want to execute a stored program unit, you can use the EXECUTE statement. The example script below shows how to executes a stored program unit: SQL> set serveroutput on; SQL> CREATE PROCEDURE Hello AS 2 BEGIN 3 DB...
2007-04-25, 4369👍, 0💬

How Many Data Types Are Supported
How Many Data Types Are Supported? - Oracle DBA FAQ - Introduction to PL/SQL PL/SQL supports two groups of data types: SQL Data Types - All data types used for table columns. PL/SQL Special Data Types - Like BOOLEAN or PLS_INTEGER. The script below shows some data type examples: SQL> set serveroutpu...
2007-04-25, 4113👍, 0💬

Can Sub Procedure/Function Be Called Recursively
Can Sub Procedure/Function Be Called Recursively? - Oracle DBA FAQ - Creating Your Own PL/SQL Procedures and Functions PL/SQL allows sub procedures or functions to be called recursively. The tutorial example below shows you how to calculate factorial values with a recursive sub function: SQL> CREATE...
2007-04-25, 5658👍, 0💬

What Happens If Recursive Calls Get Out of Control
What Happens If Recursive Calls Get Out of Control? - Oracle DBA FAQ - Creating Your Own PL/SQL Procedures and Functions What happens if your code has bug on recursive procedure calls, which causes an infinite number nested procedure calls? The answer is so good. Oracle server seems to offer no prot...
2007-04-25, 4628👍, 0💬

What Is Stored Program Unit
What Is Stored Program Unit? - Oracle DBA FAQ - Introduction to PL/SQL A stored program unit is a named block of codes which: Has a name. Can take parameters, and can return values. Is stored in the data dictionary. Can be called by many users.
2007-04-25, 4436👍, 0💬

How To Create a Stored Program Unit
How To Create a Stored Program Unit? - Oracle DBA FAQ - Introduction to PL/SQL If you want to create a stored program unit, you can use the CREATE PROCEDURE or FUNTION statement. The example script below creates a stored program unit: SQL> set serveroutput on; SQL> CREATE PROCEDURE Hello AS 2 BEGIN ...
2007-04-25, 4291👍, 0💬

What Is the Difference between Formal Parameters and Actual Parameters
What Is the Difference between Formal Parameters and Actual Parameters? - Oracle DBA FAQ - Creating Your Own PL/SQL Procedures and Functions Formal parameter and actual parameter are two different terms related parameters used in the procedures and functions: A formal parameter is a term used to ref...
2007-04-25, 6071👍, 0💬

What Is the Order of Defining Local Variables and Sub Procedures/Functions
What Is the Order of Defining Local Variables and Sub Procedures/Functions? - Oracle DBA FAQ - Creating Your Own PL/SQL Procedures and Functions In the declaration part, you must define all local variables before defining any sub procedures or sub functions. See the following sample script: SQL> CRE...
2007-04-25, 4689👍, 0💬

<< < 1 2 3 4 5 >   Sort: Rank