1 2 3 > >>   Sort: Rank

Start with a Minimum Initialization Parameter File in Oracle
How To Start Instance with a Minimal Initialization Parameter File in Oracle? The sample initialization parameter file provided by Oracle seems to be not working. But we can try to start the new instance with a minimal initialization parameter file (PFile). First you can create another PFile, $ORACL...
2018-05-08, 390👍, 0💬

Create a Server Parameter File in Oracle
How To Create a Server Parameter File in Oracle? This is Step 5. The initialization parameter file is good to get an Oracle database instance started. But it is not ideal run an instance as production. You need to convert the initialization parameter file into a Server Parameter File (SPFile) using ...
2018-05-08, 371👍, 0💬

Start a Specific Oracle Instance in Oracle
How To Start a Specific Oracle Instance in Oracle? A simple way to start a specific Oracle instance is to start the instance with the PFILE option as shown in the following example: >.\bin\sqlplus /nolog SQL> CONNECT / AS SYSDBA Connected. SQL> STARTUP NOMOUNT PFILE=$ORACLE_HOME...
2018-05-08, 359👍, 0💬

Start an Oracle Instance in Oracle
How To Start an Oracle Instance in Oracle? This is Step 6. Now you are ready to start the new Oracle Instance without any database. This instance will be used to create a database. Starting an instance without database can be done by using STARTUP NOMOUNT statement as shown below: >.\bin\sqlp...
2018-05-08, 329👍, 0💬

Run CREATE DATABASE Statement in Oracle
How To Run CREATE DATABASE Statement in Oracle? This is Step 7. Oracle Administrator Guide provided a sample CREATE DATABASE statement. But it is a long statement. You can modify and same it in a file, $ORACLE_HOME/configscripts/cre ate_database_fyi.sql,and run the file within SQL*Plus. Here is a co...
2018-05-08, 319👍, 0💬

Use an Explicit Cursor without OPEN Statements in Oracle
How To Use an Explicit Cursor without OPEN Statements in Oracle? If you want to open a cursor and loop through its data rows in quick way, you can use the FOR ... IN ... LOOP statement in the same way as the implicit cursor. The following tutorial exercise gives you a good example: CREATE OR REPLACE...
2018-04-07, 414👍, 0💬

Open Multiple Cursors at the Same Time in Oracle
Can Multiple Cursors Being Opened at the Same Time in Oracle? Yes, multiple cursors can be opened at the same time. See the following example: CREATE OR REPLACE PROCEDURE FYI_CENTER AS CURSOR emp_cur IS SELECT * FROM employees; emp_rec employees%ROWTYPE; CURSOR dpt_cur IS SELECT * FROM departments; ...
2018-04-07, 395👍, 0💬

Use FETCH Statement in a Loop in Oracle
How To Use FETCH Statement in a Loop in Oracle? If you have a cursor opened ready to use, you can also use the FETCH statement in a loop to retrieve data from the cursor more efficiently. But you need to remember to use an EXIT statement break the loop when the cursor pointer reaches the end. The sc...
2018-04-07, 371👍, 0💬

What Is a Cursor Variable in Oracle
What Is a Cursor Variable in Oracle? A cursor variable is a variable of a specific REF CURSOR data type, which is a pointer to a data structure resource connects to query statement result, similar to the CURSOR data type.. The advantage of using cursor variables is that cursor variables can be used ...
2018-04-07, 354👍, 0💬

Define a Sub Function in Oracle
How To Define a Sub Function in Oracle? 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 definition starts with the FUNCTION key word. Here is a sample sc...
2018-03-18, 425👍, 0💬

Call Procedure or Function Recursively in Oracle
Can Sub Procedure/Function Be Called Recursively in Oracle? 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 OR REPLACE PROCEDURE FACTORIAL_TEST AS 2 FUNCTION F...
2018-03-18, 373👍, 0💬

Run-Away Recursive Calls in Oracle
What Happens If Recursive Calls Get Out of Control in Oracle? What happens if your code has bug on recursive procedure calls, which causes an infinite number nested procedure calls? The answer is not so good. Oracle server seems to offer no protection calling stack limit. The script below shows you ...
2018-03-18, 360👍, 0💬

Formal Parameters and Actual Parameters in Oracle
What Is the Difference between Formal Parameters and Actual Parameters in Oracle? 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 refer to a parameter defined in the procedure or function decl...
2018-03-18, 356👍, 0💬

Define Variables before Procedures and Functions in Oracle
What Is the Order of Defining Local Variables and Sub Procedures/Functions in Oracle? In the declaration part, you must define all local variables before defining any sub procedures or sub functions. See the following sample script: SQL> CREATE OR REPLACE PROCEDURE WELCOME AS 2 SITE CHAR(80) ...
2018-03-18, 350👍, 0💬

Working with Cursors in Oracle PL/SQL
Where to find answers to frequently asked questions on Working with Cursors in Oracle PL/SQL? Here is a list of frequently asked questions and their answers compiled by FYIcenter.com DBA team on Working with Cursors in Oracle PL/SQL. Clear answers are provided with tutorial exercises on defining, op...
2018-02-01, 508👍, 0💬

Attributes of the Implicit Cursor in Oracle
How To Use Attributes of the Implicit Cursor in Oracle? Right after executing a DML statement, you retrieve any attribute of the implicit cursor by using SQL%attribute_name, as shown in the following tutorial exercise: CREATE TABLE student (id NUMBER(5) PRIMARY KEY, first_name VARCHAR(80) NOT NULL, ...
2018-02-01, 479👍, 0💬

What Is a Cursor in Oracle
What Is a Cursor in Oracle? A cursor looks like a variable, but it is not a variable. A cursor looks like a procedure, but it is not a procedure. A cursor is a cursor. It is a logical representation of a resource connects to a set of data rows related to a DML statement. A cursor is consists of: A D...
2018-02-01, 429👍, 0💬

What Is the Implicit Cursor in Oracle
What Is the Implicit Cursor in Oracle? There is only one implicitly cursor in a session. The implicit cursor is the cursor automatically defined by PL/SQL for you. Whenever a SQL statement is executed, this cursor will be assigned to represent the execution of this statement. This implicit cursor is...
2018-02-01, 426👍, 0💬

Types of Cursors Supported in PL/SQL in Oracle
How Many Types of Cursors Supported in PL/SQL in Oracle? PL/SQL supports two types of cursors: The implicit cursor - A single default cursor that automatically connects to the last DML statement executed. Explicit cursors - User defined cursors with specific DML statements and execution statuses.   ...
2018-02-01, 388👍, 0💬

Creating Oracle PL/SQL Procedures and Functions
Where to find answers to frequently asked questions on Creating Oracle PL/SQL Procedures and Functions? Here is a list of frequently asked questions and their answers compiled by FYIcenter.com DBA team on Creating Oracle PL/SQL Procedures and Functions. It can also be used as learning tutorials on c...
2018-01-27, 466👍, 0💬

Define Anonymous Procedures with Variables in Oracle
How To Define an Anonymous Procedure with Variables in Oracle? Anonymous procedure is a procedure without any name. If you have some variables to declare, you can define an anonymous procedure by using the DECLARE keyword in SQL*Plus as shown in the following tutorial script: SQL> set servero...
2018-01-27, 419👍, 0💬

What Is a Function in Oracle
What Is a Function in Oracle? A function is a named program unit. It consists of three parts: Declaration Part - Defining the function name, calling parameters, return value type, local variables and local procedures. Declaration part is required. Execution Part - Defining execution logic with execu...
2018-01-27, 369👍, 0💬

What Is a Procedure in Oracle
What Is a Procedure in Oracle? A procedure is a named program unit. It consists of three parts: Declaration Part - Defining the procedure name, calling parameters, local variables and local procedures. Declaration part is required. Execution Part - Defining execution logic with executable statements...
2018-01-27, 361👍, 0💬

Define Anonymous Procedures without Variables in Oracle
How To Define an Anonymous Procedure without Variables in Oracle? Anonymous procedure is a procedure without any name. If you don't have any variables to declare, you can define an anonymous procedure by using the BEGIN keyword directly in SQL*Plus as shown in the following tutorial script: SQL&...
2018-01-27, 358👍, 0💬

1 2 3 > >>   Sort: Rank