Explore topic-wise InterviewSolutions in .

There are two WAYS to create a NEW database:

• Use the Database Configuration ASSISTANT (DBCA) to create a database interactively.
• Use the CREATE DATABASE statement to create a database MANUALLY.

There are two ways to create a new database:

To START ORACLE SQL Developer, go to sqldeveloper and click sqldeveloper.exe. The Oracle SQL Developer window shows up. Your copy of Oracle SQL Developer is running now. There will nothing under the Connections icon, because you haven't defined any connections YET.

You should visit different menu items to explore the user interface.

To start Oracle SQL Developer, go to sqldeveloper and click sqldeveloper.exe. The Oracle SQL Developer window shows up. Your copy of Oracle SQL Developer is running now. There will nothing under the Connections icon, because you haven't defined any connections yet.

You should visit different menu items to explore the user interface.

Oracel SQL DEVELOPER is written in JAVA. It requires JDK 1.5, which is already included in your download file.

Oracel SQL Developer is written in Java. It requires JDK 1.5, which is already included in your download file.

The Reports view lets you browse database information that organized by the server as special VIEWS. Information can be BROWSED include:

• Database PARAMETERS
• Storage information
• Session information
• Cursors
• DATA objects
• User ACCOUNTS
• Security informaiton

The Reports view lets you browse database information that organized by the server as special views. Information can be browsed include:

To conclude this introductory FAQ collection, you should think about ORACLE SQL Developer in comparison with other client tools like SQL*Plus and OracleWeb INTERFACE. SQL Developer is definitely better than the other tools, more functionality, and MUCH easier to use. The SCRIPT debugging MODE is very useful.

To conclude this introductory FAQ collection, you should think about Oracle SQL Developer in comparison with other client tools like SQL*Plus and OracleWeb interface. SQL Developer is definitely better than the other tools, more functionality, and much easier to use. The script debugging mode is very useful.

An anonymous block is a PL/SQL code block with no name. It consists of three parts:

• Declaration Part - Defining local variables and local procedures. Declaration part is optional.
• EXECUTION Part - Defining execution logic with executable statements. Execution part is required.
• Exception Part - Defining error handling logics. Exception part is optional.

Here how a complete anonymous block should LOOK like:

DECLARE // Declaration statements
BEGIN // Executable statements
EXCEPTION // Error handling statements
END;

An anonymous block is a PL/SQL code block with no name. It consists of three parts:

Here how a complete anonymous block should look like:

DECLARE // Declaration statements
BEGIN // Executable statements
EXCEPTION // Error handling statements
END;

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:

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:

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 operator - Appending ":= value" to the end of declaration statements.

The SCRIPT below show you some examples of declaration statements with default values:

There are two ways to assign default values to variables at the time of declaration:

The script below show you some examples of declaration statements with default values:

You can use assignment statements to ASSIGN values to VARIABLES. An assignment statement contains an assignment operator ":=", which takes the value specified on the right to the VARIABLE on LEFT. The script below show you some examples of assignment statements:

You can use assignment statements to assign values to variables. An assignment statement contains an assignment operator ":=", which takes the value specified on the right to the variable on left. The script below show you some examples of assignment statements:

There are 4 basic arithmetic OPERATIONS on numeric values as shown in the following sample script:

There are 4 basic arithmetic operations on numeric values as shown in the following sample script:

PL/SQL supports 6 basic numeric comparison OPERATIONS as shown in the following SAMPLE SCRIPT:

PL/SQL supports 6 basic numeric comparison operations as shown in the following sample script:

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 array.
• LOB Data Types: A LOB data type holds a lob locator that specify the LOCATION of a large object.
• Reference Data Types: A reference data type holds a pointer that points to another data object.

PL/SQL data types are grouped into 4 categories:

PL/SQL supports 3 logical OPERATIONS as SHOWN in the FOLLOWING sample script:

PL/SQL supports 3 logical operations as shown in the following sample script:

PL/SQL supports many scalar data types divided into 4 GROUPS:

Numeric Types: BINARY_DOUBLE, BINARY_FLOAT, BINARY_INTEGER, DEC, DECIMAL, DOUBLE PRECISION, FLOAT, INT, INTEGER, NATURAL, NATURALN, NUMBER, NUMERIC, PLS_INTEGER, POSITIVE, POSITIVEN,REAL, SIGNTYPE, SMALLINT.

• Character Types: CHAR, CHARACTER, LONG, LONG RAW, NCHAR, NVARCHAR2, RAW, ROWID, STRING,UROWID, VARCHAR, VARCHAR2.
• Boolean Types: BOOLEAN.
• Date Types: DATE, TIMESTAMP, TIMESTAMP WITH TIMEZONE, TIMESTAMP WITH LOCAL TIMEZONE,INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND.

PL/SQL supports many scalar data types divided into 4 groups:

Numeric Types: BINARY_DOUBLE, BINARY_FLOAT, BINARY_INTEGER, DEC, DECIMAL, DOUBLE PRECISION, FLOAT, INT, INTEGER, NATURAL, NATURALN, NUMBER, NUMERIC, PLS_INTEGER, POSITIVE, POSITIVEN,REAL, SIGNTYPE, SMALLINT.

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:

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:

If you have a block of codes to be executed repeatedly based a condition, you can use the "WHILE ... LOOP" statement. Here is a SAMPLE script on WHILE STATEMENTS:

This script should print this: Total counts: 10

If you have a block of codes to be executed repeatedly based a condition, you can use the "WHILE ... LOOP" statement. Here is a sample script on WHILE statements:

This script should print this: Total counts: 10

If you have a block of CODES to be EXECUTED REPEATEDLY over a RANGE of values, you can use the "FOR ... LOOP" STATEMENT. Here is a sample script on FOR statements:

If you have a block of codes to be executed repeatedly over a range of values, you can use the "FOR ... LOOP" statement. Here is a sample script on FOR statements:

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. Execution part is required.
• Exception Part - Defining error handling LOGICS. Exception part is OPTIONAL.

Here how a complete procedure should look like:

A procedure is a named program unit. It consists of three parts:

Here how a complete procedure should look like:

A stored procedure is a procedure with a specified name and stored into the current database. If you want to create a stored procedure, you can use the CREATE PROCEDURE statement. The EXAMPLE script below CREATES a stored procedure:

Procedure created.

A stored procedure is a procedure with a specified name and stored into the current database. If you want to create a stored procedure, you can use the CREATE PROCEDURE statement. The example script below creates a stored procedure:

Procedure created.

If you WANT to execute a STORED procedure, you can use the EXECUTE statement. The EXAMPLE script below shows how to executes a stored procedure:

Procedure created. SQL> EXECUTE Greeting; Welcome to wisdom!

If you want to execute a stored procedure, you can use the EXECUTE statement. The example script below shows how to executes a stored procedure:

Procedure created. SQL> EXECUTE Greeting; Welcome to wisdom!

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:

Procedure created. SQL> DROP PROCEDURE Greeting; Procedure dropped.

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:

Procedure created. SQL> DROP PROCEDURE Greeting; Procedure dropped.

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 below creates a stored PROCEDURE:

Function created.

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 below creates a stored procedure:

Function created.

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:

Function created. SQL> DROP FUNCTION GET_SITE; Function DROPPED.

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:

Function created. SQL> DROP FUNCTION GET_SITE; Function dropped.

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:

Function created. SQL> SELECT GET_DOUBLE(36) FROM DUAL;

GET_DOUBLE(36)
--------------
72

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:

Function created. SQL> SELECT GET_DOUBLE(36) FROM DUAL;

GET_DOUBLE(36)
--------------
72

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 procedure definition starts with the PROCEDURE key WORD. Here is a sample script showing you how to define and use a sub procedure:

SQL> EXECUTE DBA_WEEK; Checking log files. Reading some papers.

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 procedure definition starts with the PROCEDURE key word. Here is a sample script showing you how to define and use a sub procedure:

SQL> EXECUTE DBA_WEEK; Checking log files. Reading some papers.

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 protection calling stack limit. The script below shows you a badly coded recursive procedure. If you run it on an Oracle 10g XE server on Windows, your server will out of control and keep using virtual memory to satisfy the growing calling stack. You have to reboot your server to control back.

SQL> EXECUTE STACK_TEST; (your server keep running with 100% CPU and memory usage)

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 protection calling stack limit. The script below shows you a badly coded recursive procedure. If you run it on an Oracle 10g XE server on Windows, your server will out of control and keep using virtual memory to satisfy the growing calling stack. You have to reboot your server to control back.

SQL> EXECUTE STACK_TEST; (your server keep running with 100% CPU and memory usage)

No, you can not run any DDL STATEMENTS is PL/SQL DIRECTLY. If you try to use the DROP TABLE STATEMENT inside PL/SQL, you will get a compilation error as shown below:

No, you can not run any DDL statements is PL/SQL directly. If you try to use the DROP TABLE statement inside PL/SQL, you will get a compilation error as shown below:

The best way to resolve name CONFLICTS is to avoid USING COLUMN names for variables.

The best way to resolve name conflicts is to avoid using column names for variables.

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 INTO clause:

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 INTO clause:

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 TUTORIAL:

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 tutorial:

If a DELETE statement is deleting a SINGLE row, you can assign column values of the deleted row to variables by using the RETURNING clause, which an extension of DELETE statements for PL/SQL. The tutorial script SHOWS you how to do this:

Name deleted = Steven King Similar to SELECT ... INTO, RETURNING ... INTO will not work if multiple rows are deleted.

If a DELETE statement is deleting a single row, you can assign column values of the deleted row to variables by using the RETURNING clause, which an extension of DELETE statements for PL/SQL. The tutorial script shows you how to do this:

Name deleted = Steven King Similar to SELECT ... INTO, RETURNING ... INTO will not work if multiple rows are deleted.

RECORD is a composite data TYPE in PL/SQL. It can have MANY fields REPRESENTING data elements with different data types. Variables of RECORD type can be designed to hold data from database table ROWS. To use RECORD data type, you need to DEFINE a specific RECORD type with each field defined with a name and a data type. Then you can use your specific RECORD type to define your RECORD type variables.

RECORD is a composite data type in PL/SQL. It can have many fields representing data elements with different data types. Variables of RECORD type can be designed to hold data from database table rows. To use RECORD data type, you need to define a specific RECORD type with each field defined with a name and a data type. Then you can use your specific RECORD type to define your RECORD type variables.

If you WANT to define a specific RECORD type, you need to use the TYPE ... IS RECORD statement in the declaration PART of any procedure or FUNCTION. The following example script defines a RECORD type called STUDENT:

If you want to define a specific RECORD type, you need to use the TYPE ... IS RECORD statement in the declaration part of any procedure or function. The following example script defines a RECORD type called STUDENT:

Once you have your specific RECORD type DEFINED, you can define NEW variables with this specific RECORD type like any other data type. In the sample script below, several variables are defined with a regular data type and a specific RECORD type:

Once you have your specific RECORD type defined, you can define new variables with this specific RECORD type like any other data type. In the sample script below, several variables are defined with a regular data type and a specific RECORD type:

If a variable is a RECORD variable, you can assign values to its data fields by using fields names prefixed with variable name as "variable.field_name". Here is a sample script assigning values to data fields of RECORD variables:

If a variable is a RECORD variable, you can assign values to its data fields by using fields names prefixed with variable name as "variable.field_name". Here is a sample script assigning values to data fields of RECORD variables:

If a variable is a RECORD variable with data fields assigned values, you can retrieve those values out of its data fields by using fields names PREFIXED with variable name as "variable.field_name". Here is a SAMPLE script showing you how to retrieve field values from RECORD variables:

If a variable is a RECORD variable with data fields assigned values, you can retrieve those values out of its data fields by using fields names prefixed with variable name as "variable.field_name". Here is a sample script showing you how to retrieve field values from RECORD variables:

When defining a specific RECORD type, you can define a data field as NOT NULL to make sure variables with this RECORD type to always have values in this field. A field defined as NOT NULL must have a default value. Here is a tutorial script showing you how to define a data field as NOT NULL:

When defining a specific RECORD type, you can define a data field as NOT NULL to make sure variables with this RECORD type to always have values in this field. A field defined as NOT NULL must have a default value. Here is a tutorial script showing you how to define a data field as NOT NULL:

If you have a table, and want to define a RECORD variable to store all the data elements of a row from that table, you can use table_name%ROWTYPE to define the RECORD variable as shown in the following sample script:

If you have a table, and want to define a RECORD variable to store all the data elements of a row from that table, you can use table_name%ROWTYPE to define the RECORD variable as shown in the following sample script:

If you have a table, and want to assign a data row of that table to a RECORD variable, you need to DEFINE this RECORD variable to MATCH the table column structure, then use the SELECT ... INTO statement to assign a data row that RECORD variable. The script below shows you how to do this:

If you have a table, and want to assign a data row of that table to a RECORD variable, you need to define this RECORD variable to match the table column structure, then use the SELECT ... INTO statement to assign a data row that RECORD variable. The script below shows you how to do this:

If you have a RECORD VARIABLE with data fields matching a table structure, you can update a row in this table with this RECORD variable using the UPDATE ... SET ROW statement as shown in the SAMPLE script below:

If you have a RECORD variable with data fields matching a table structure, you can update a row in this table with this RECORD variable using the UPDATE ... SET ROW statement as shown in the sample script below:

If you have a table, and want to define some variables to have exactly the same DATA types as some columns in that table, you can use table_name.column_name%TYPE as data types to define those variables. The TUTORIAL sample below SHOWS you how to do this:

If you have a table, and want to define some variables to have exactly the same data types as some columns in that table, you can use table_name.column_name%TYPE as data types to define those variables. The tutorial sample below shows you how to do this:

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.

PL/SQL supports two types of cursors:

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 called SQL. It has MANY attributes representing some GOOD INFORMATION about the execution like:

• SQL%FOUND - True, if the SQL statement has changed any rows.
• SQL%NOTFOUND - True, if the SQL statement has not changed any rows.
• %ISOPEN - True, if there is a SQL statement being associated to the cursor.

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 called SQL. It has many attributes representing some good information about the execution like:

An explicit CURSOR must be defined in the declaration part of a procedure or function with the CURSOR ... IS STATEMENT as shown in the following sample script:

An explicit cursor must be defined in the declaration part of a procedure or function with the CURSOR ... IS statement as shown in the following sample script:

An EXISTING cursor can be opened or CLOSED by the OPEN or CLOSE STATEMENT as shown in the following SAMPLE script:

An existing cursor can be opened or closed by the OPEN or CLOSE statement as shown in the following sample script:

If you have a cursor opened ready to use, you can also use the FETCH statement to retrieve data from the cursor into a RECORD variable as shown in the tutorial exercise below:

If you have a cursor opened ready to use, you can also use the FETCH statement to retrieve data from the cursor into a RECORD variable as shown in the tutorial exercise below:

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 script below gives you a good example:

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 script below gives you a good example:

f 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:

f 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:

Yes, multiple cursors can be opened at the same time. See the following EXAMPLE:

Yes, multiple cursors can be opened at the same time. See the following example:

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 as variables to PASS between procedures and functions.

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 as variables to pass between procedures and functions.