1 2 3 4 5 > >>   Sort: Date

How To Assign Query Results to Variables
How To Assign Query Results to Variables? - Oracle DBA FAQ - Working with Database Objects in PL/SQL If you want to assign results from SELECT statements to variables, you can use the INTO clause, which an extension of SELECT statements for PL/SQL. The sample code below shows some good example on IN...
2007-04-27, 8788👍, 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 Use "IF" Statements on Multiple Conditions
How To Use "IF" Statements on Multiple Conditions? - Oracle DBA FAQ - Understanding PL/SQL Language Basics If you have multiple blocks of codes to be executed based on different conditions, you can use the "IF ... ELSIF" statement. Here is a sample script on IF statements: DECLARE day VARCHAR2; BEGI...
2007-04-29, 6831👍, 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, 6704👍, 0💬

Can Multiple Cursors Being Opened at the Same Time
Can Multiple Cursors Being Opened at the Same Time? - Oracle DBA FAQ - Working with Cursors in PL/SQL 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; CURSO...
2007-04-28, 6605👍, 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💬

What Are the Parameter Modes Supported by PL/SQL
What Are the Parameter Modes Supported by PL/SQL? - Oracle DBA FAQ - Creating Your Own PL/SQL Procedures and Functions PL/SQL supports 3 parameter modes on procedure/function parameters: IN: This is the default mode. IN parameters allow the calling code to pass values into the procedure or function....
2007-04-25, 6076👍, 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💬

Oracle PL/SQL Questions
1. What is normalization. 2. Difference between procedure and functions. 3. Oracle 9i Vs 10g. 4. how to improve the performance of a query. 5. %type vs %rowtype. 6. regression testing. 7. deleting the duplicate records without using rowid in oracle. 8. Row chaining. 9. types of cursors.
2008-11-05, 6065👍, 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💬

What Are Named Parameters
What Are Named Parameters? - Oracle DBA FAQ - Creating Your Own PL/SQL Procedures and Functions Named parameters are actual parameters specified not by position but by providing formal parameter names when calling the procedure or function. The main advantage of named parameters is that the caller d...
2007-04-21, 5902👍, 0💬

How To Retrieve the Count of Updated Rows
How To Retrieve the Count of Updated Rows? - Oracle DBA FAQ - Working with Database Objects in PL/SQL After running an UPDATE statement, the database server returns a count of updated rows. You can retrieve this count from a special predefined variable called SQL%ROWCOUT, as shown in the following t...
2007-04-27, 5779👍, 0💬

How To Use "IN OUT" Parameter Properly
How To Use "IN OUT" Parameter Properly? - Oracle DBA FAQ - Creating Your Own PL/SQL Procedures and Functions Here are the rules about IN OUT parameters: A formal IN OUT parameter acts like an initialized variable. An actual IN OUT parameter must be a variable. An actual IN OUT parameter passes a cop...
2007-04-21, 5711👍, 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💬

Can one execute dynamic SQL from Forms?
Can one execute dynamic SQL from Forms? Yes, use the FORMS_DDL built-in or call the DBMS_SQL database package from Forms. Eg: FORMS_DDL('INSERT INTO X VALUES (' || col_list || ')'); Just note that FORMS_DDL will force an implicit COMMIT and may de-synchronize the Oracle Forms COMMIT mechanism.
2011-04-12, 5267👍, 0💬

How To Declare a Local Variable
How To Declare a Local Variable? - Oracle DBA FAQ - Understanding PL/SQL Language Basics A local variable can be defined in the declaration part with a declaration statement, which is a variable name followed a data type identifier. Below are some examples of declaration statements: PROCEDURE proc_v...
2007-04-30, 5115👍, 0💬

How To Test NULL Values
How To Test NULL Values? - Oracle DBA FAQ - Understanding PL/SQL Language Basics There ate two special comparison operators you can use on NULL values: "variable IS NULL" - Returns TRUE if the variable value is NULL. "variable IS NOT NULL" - Return TRUE if the variable value is not NULL. The followi...
2007-04-29, 5090👍, 0💬

How To Initialize Variables with Default Values
How To Initialize Variables with Default Values? - Oracle DBA FAQ - Understanding PL/SQL Language Basics There are two ways to assign default values to variables at the time of declaration: Using key word DEFAULT - Appending "DEFAULT value" to the end of declaration statements. Using assignment oper...
2007-04-30, 4959👍, 0💬

What Is the Scope of a Local Variable
What Is the Scope of a Local Variable? - Oracle DBA FAQ - Creating Your Own PL/SQL Procedures and Functions The scope of a variable can be described with these rules: A variable is valid within the procedure or function where it is defined. A variable is also valid inside a sub procedure or function...
2007-04-21, 4954👍, 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 Run SQL Functions in PL/SQL
How To Run SQL Functions in PL/SQL? - Oracle DBA FAQ - Working with Database Objects in PL/SQL Of course, you can run SQL functions in SQL statements. But many SQL functions can also be executed in regular PL/SQL statements, as shown in the following sample script: DECLARE now DATE; id NUMBER; str V...
2007-04-27, 4934👍, 0💬

How Many Categories of Data Types
How Many Categories of Data Types? - Oracle DBA FAQ - Understanding PL/SQL Language Basics PL/SQL data types are grouped into 4 categories: Scalar Data Types: A scalar data type holds a single value. Composite Data Types: A composite data type has internal components, such as the elements of an arra...
2007-04-30, 4911👍, 0💬

How To Convert Character Types to Numeric Types
How To Convert Character Types to Numeric Types? - Oracle DBA FAQ - Understanding PL/SQL Language Basics You can convert character types to numeric types in two ways: Explicitly by using TO_NUMBER() function. Implicitly by putting character data in a numeric operation. The sample script below shows ...
2007-04-30, 4877👍, 0💬

1 2 3 4 5 > >>   Sort: Date