RenDC
home
Human Resources
Tutorials
search
User
Home
Interview Q&A
News Feed
Add Post
Questions
Add Question
Tutorials
 » Interview Questions and Answers  » Questions  » News Feed  » Tutorials  » rendc.com

Oracle Database

  Home  Oracle  Oracle Database


“Learn Oracle database programming with hundreds of Interview Questions and Answers and examples and get a better job as an Oracle DBA with this basic and advance Oracle Database Interview Questions and Answers guide”



430 Oracle Database Questions And Answers

261⟩ How To Run CREATE DATABASE Statement Again?

After cleaning up the results of a previously failed CREATE DATABASE statement, you can run the CREATE DATABASE statement again as shown below:

SQL> @$ORACLE_HOMEconfigscriptscreate_database_ggl.sql;

CREATE DATABASE ggl

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

Something went wrong again. This time it might related the limitation that Oracle 10g XE only supports one database instance. With the default instance called "XE" already defined, creating another database instance might be not allowed.

Is this answer helpful?   | 

Answer This Question
 157 views

262⟩ How To Create Additional Tablespaces for an New Database?

This is Step 8. Creating additional tablespaces can be done by using the CREATE TABLESPACE statement as shown in the following sample script:

SQL> CREATE TABLESPACE users

2 DATAFILE '/oraclexe/oradata/ggl/users01.dbf' SIZE 10M;

SQL> CREATE TABLESPACE indx

2 DATAFILE '/oraclexe/oradata/ggl/indx01.dbf' SIZE 10M;

Is this answer helpful?   | 

Answer This Question
 152 views

264⟩ What Is PL/SQL?

PL/SQL is a modern, block-structured programming language. It provides several features that make developing powerful database applications very convenient. For example, PL/SQL provides procedural constructs, such as loops and conditional statements, that are not available in standard SQL.

PL/SQL code runs on the server, so using PL/SQL lets you centralize significant parts of your database applications for increased maintainability and security. It also enables you to achieve a significant reduction of network overhead in client/server applications.

Is this answer helpful?   | 

Answer This Question
 142 views

265⟩ What Are the Types PL/SQL Code Blocks?

There are 3 types of PL/SQL code blocks:

► Anonymous Block - A block of codes with no name. It may contain a declaration part, an execution part, and exception handlers.

► Stored Program Unit - A block of codes with a name. It is similar to an anonymous block. But it can take parameters and return values.

► Trigger - A block of code that can be defined to fire based an specific event.

Is this answer helpful?   | 

Answer This Question
 135 views

266⟩ How To Define an Anonymous Block?

An anonymous block must have an execution part, which is a group of other PL/SQL statements enclosed in the BEGIN ... END statement. Here is a script on how to define a simple anonymous block with SQL*Plus:

SQL> set serveroutput on;

SQL> begin

2 dbms_output.put_line('Hello world!');

3 end;

4 /

Hello world!

PL/SQL procedure successfully completed.

"set serveroutput on;" allows dbms_output.put_line() to work.

"/" runs the anonymous block, which print the "Hello world!" message.

Is this answer helpful?   | 

Answer This Question
 122 views

267⟩ How Many Anonymous Blocks Can Be Defined?

An anonymous block is stored in the user's current session without any name. So you can only define one anonymous block at any time. If you define another anonymous block, the new block will replace the previously defined block, as shown in the following script:

SQL> set serveroutput on;

SQL> begin

2 dbms_output.put_line('Hello world!');

3 end;

4 /

Hello world!

PL/SQL procedure successfully completed.

SQL> begin

2 dbms_output.put_line('This is a PL/SQL FAQ.');

3 end;

4 /

This is a PL/SQL FAQ.

PL/SQL procedure successfully completed.

Is this answer helpful?   | 

Answer This Question
 130 views

268⟩ How To Run the Anonymous Block Again?

If you have an anonymous block defined in your session, you can run it any time by using the "/" command as shown in the following script:

SQL> set serveroutput on;

SQL> begin

2 dbms_output.put_line('This is a PL/SQL FAQ.');

3 end;

4 /

This is a PL/SQL FAQ.

PL/SQL procedure successfully completed.

SQL> /

This is a PL/SQL FAQ.

PL/SQL procedure successfully completed.

What Is Stored Program Unit?

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.

Is this answer helpful?   | 

Answer This Question
 127 views

269⟩ How To Create a Stored Program Unit?

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

3 DBMS_OUTPUT.PUT_LINE('Hello world!');

4 END;

5 /

Procedure created.

Is this answer helpful?   | 

Answer This Question
 126 views

270⟩ How To Execute a Stored Program Unit?

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 DBMS_OUTPUT.PUT_LINE('Hello world!');

4 END;

5 /

Procedure created.

SQL> EXECUTE Hello;

Hello world!

Is this answer helpful?   | 

Answer This Question
 131 views

271⟩ How Many Data Types Are Supported?

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 serveroutput on;

SQL> DECLARE

2 title VARCHAR(8);

3 salary NUMBER;

4 seeking_job BOOLEAN;

5 BEGIN

6 title := 'DBA';

7 salary := 50000;

8 seeking_job := TRUE;

9 DBMS_OUTPUT.PUT_LINE('Job Title: ' || title);

10 DBMS_OUTPUT.PUT_LINE('Expected salary: '

11 || TO_CHAR(salary));

12 END;

13 /

Job Title: DBA

Expected salary: 50000

Is this answer helpful?   | 

Answer This Question
 141 views

272⟩ What Are the Execution Control Statements?

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 executes a block of statements.

* GOTO Statements - Unconditional changes the execution flow to a specified statement.

The script below shows execution control statements:

SQL> set serveroutput on;

SQL> DECLARE

2 total NUMBER;

3 BEGIN

4 total := 0;

5 LOOP

6 total := total+1;

7 IF total >= 10 THEN

8 GOTO print;

9 END IF;

10 END LOOP;

11 <>

12 DBMS_OUTPUT.PUT_LINE('Total counts: '

13 || TO_CHAR(total));

14 END;

15 /

Total counts: 10

This sample script sh

Is this answer helpful?   | 

Answer This Question
 124 views

273⟩ How To Use SQL Statements in 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) NOT NULL,

3 description VARCHAR(256) NOT NULL);

Table created.

SQL> BEGIN

2 INSERT INTO tip VALUES(1, 'PL/SQL',

3 'Good for beginners.');

4 UPDATE tip SET description = 'Good for beginners.';

5 END;

6 /

PL/SQL procedure successfully completed.

SQL> COL subject FORMAT A12;

SQL> COL description FORMAT A24;

SQL> SELECT * FROM tip;

  ID SUBJECT DESCRIPTION

---------- ------------ -------------------

  1 PL/SQL  Good for beginners.

SQL> DROP TABLE tip;

Table dropped.

This script example actually has 3 parts:

► Part 1 - A single DDL (Data Definition Language) statement, CREATE TABLE, outside the PL/SQL code block.

► Part 2 - A PL/SQL code block defined and executed to insert and updated a record.

► Part 3 - A couple of SQL statements outside the PL/SQL code block to view the record and drop the table.

Is this answer helpful?   | 

Answer This Question
 141 views

274⟩ How To Process Query Result in 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 WHERE manager_id = 101)

4 LOOP

5 DBMS_OUTPUT.PUT_LINE('Name = ' || row.last_name);

6 END LOOP;

7 END;

8 /

Name = Greenberg

Name = Whalen

Name = Mavris

Name = Baer

Name = Higgins

The loop statement in this script automatically sets a temporary variable "row" each row in the result set returned from the SELECT statement.

Is this answer helpful?   | 

Answer This Question
 144 views

275⟩ How To Create an Array in 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(3) OF VARCHAR(16);

3 domain_list list;

4 BEGIN

5 domain_list := list('google','globalguideline','myspace');

6 DBMS_OUTPUT.PUT_LINE('Second domain: '

7 || domain_list(2));

8 END;

9 /

Second domain: globalguideline

As you can see, to define an array variable, you need to create your own array type, and use that type to define array variables.

Is this answer helpful?   | 

Answer This Question
 141 views

276⟩ How To Manage Transaction Isolation Level?

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.TOTAL_SALARY AS

 2 total_salary NUMBER(12,2);

 3 BEGIN

 4 SET TRANSACTION READ ONLY;

 5 SELECT SUM (salary) INTO total_salary FROM employees;

 6 DBMS_OUTPUT.PUT_LINE('Total salary 1: ' 

 7  || total_salary); 

 8 -- Other sessions may change salaries of some records

 9 SELECT SUM (salary) INTO total_salary FROM employees;

 10 DBMS_OUTPUT.PUT_LINE('Total salary 2: ' 

 11  || total_salary);

 12 COMMIT;

 13 END;

 14 /

SQL> EXECUTE TOTAL_SALARY;

Total salary 1: 691400

Total salary 2: 691400

"READ ONLY" transaction level takes a read only snapshot of the database. This allows other sessions to update the database without any locks. All queries in the session will produces identical results. So both SELECT statements in this script will return the same value guaranteed.

Is this answer helpful?   | 

Answer This Question
 136 views

277⟩ How To Pass Parameters to Procedures?

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:

SQL> CREATE OR REPLACE PROCEDURE DBA_TASK (day VARCHAR2) AS

2 BEGIN

3 IF day = 'MONDAY' THEN

4 DBMS_OUTPUT.PUT_LINE('Checking log files.');

5 ELSIF day = 'FRIDAY' THEN

6 DBMS_OUTPUT.PUT_LINE('Rebuild indexes.');

7 ELSE

8 DBMS_OUTPUT.PUT_LINE('Reading some papers.');

9 END IF;

10 END;

11 /

SQL> EXECUTE DBA_TASK('MONDAY');

Checking log files.

SQL> EXECUTE DBA_TASK('SUNDAY');

Reading some papers.

As you can see, procedures with parameters can make procedures more flexible.

Is this answer helpful?   | 

Answer This Question
 129 views

278⟩ How To Define a Procedure inside Another Procedure?

Define a procedure inside another procedure is supported by PL/SQL. The following tutorial script shows you an example:

SQL> CREATE OR REPLACE PROCEDURE HR.DBA_WEEK AS

2 PROCEDURE DBA_TASK (day VARCHAR2) AS

3 BEGIN

4 IF day = 'MONDAY' THEN

5 DBMS_OUTPUT.PUT_LINE('Checking log files.');

6 ELSIF day = 'FRIDAY' THEN

7 DBMS_OUTPUT.PUT_LINE('Rebuild indexes.');

8 ELSE

9 DBMS_OUTPUT.PUT_LINE('Reading some papers.');

10 END IF;

11 END;

12 BEGIN

13 DBA_TASK('MONDAY');

14 DBA_TASK('TUESDAY');

15 END;

16 /

SQL> EXECUTE DBA_WEEK;

Checking log files.

Reading some papers.

Remember that procedures used inside a procedure must be defined in the declaration block.

Is this answer helpful?   | 

Answer This Question
 144 views

279⟩ What Do You Think about 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 specific administration tasks or for developers to developer simple business backend applications.

Is this answer helpful?   | 

Answer This Question
 144 views

280⟩ What Is Oracle SQL Developer?

Oracle SQL Developer is a new, free graphical tool that enhances productivity and simplifies database development tasks. With SQL Developer, you can browse database objects, run SQL statements and SQL scripts, and edit and debug PL/SQL statements. You can also run any number of provided reports, as well as create and save your own.

Oracle SQL Developer is formerly called Project Raptor.

Is this answer helpful?   | 

Answer This Question
 152 views

rendc.org

rendc.org

Whether it is technological solutions or user-oriented innovations, We have put together for you the best sources of HR Interview Questions and Answers, Tech Ideas, and Programming Tutorials."

Quick Links:
Interview Question and Answer Questions Add Questions News-Feed Add Posts HR Hacks Tutorials Profile rendc.com memeph.com

© 2022 | All rights reserved.


News Letter   RENDC

welcome to www.rendc.org