Oracle PLSql Interview Online Test
Oracle PLSQL technical interview questions and answers help candidates understand advanced database programming concepts used in enterprise applications. PL/SQL is Oracle’s procedural language extension that supports functions, stored procedures, packages, exceptions, loops, and triggers. Because PL/SQL developers play an important role in backend development, companies like TCS, Wipro, Infosys, Capgemini, Cognizant, and Accenture frequently ask PL/SQL interview questions during technical rounds and placement tests. This guide provides clear explanations of the most commonly asked PL/SQL queries and coding questions. Whether you are a fresher or an experienced developer, mastering PL/SQL helps you solve real-world problems during interviews. You can also download interview PDFs and practice mock tests to boost your confidence.
4. Oracle cursor : Implicit & Explicit cursors
Answer: Oracle uses work areas called private SQL areas to create SQL statements.
PL/SQL construct to identify each and every work are used, is called as Cursor.
For SQL queries returning a single row, PL/SQL declares all implicit cursors.
For queries that returning more than one row, the cursor needs to be explicitly declared.
Show Answer
Hide Answer
5. Explicit Cursor attributes
Answer: There are four cursor attributes used in Oracle
cursor_name%Found, cursor_name%NOTFOUND, cursor_name%ROWCOUNT, cursor_name%ISOPEN
Show Answer
Hide Answer
6. To view installed Oracle version information
Answer: SQL> select banner from v$version;
Show Answer
Hide Answer
8. Any three PL/SQL Exceptions?
Answer: Too_many_rows, No_Data_Found, Value_Error, Zero_Error, Others
Show Answer
Hide Answer
10. What are the more common pseudo-columns?
Answer: SYSDATE, USER , UID, CURVAL, NEXTVAL, ROWID, ROWNUM
Show Answer
Hide Answer
11. What is the output of SIGN function?
Answer: 1 for positive value,
0 for Zero,
-1 for Negative value.
Show Answer
Hide Answer
12. What is the maximum number of triggers, can apply to a single table?
Answer: 12 triggers.
Show Answer
Hide Answer
13. Which is the subset of SQL commands used to manipulate Oracle Database structures, including tables?
Answer: Data Definition Language (DDL)
Show Answer
Hide Answer
17. What is the parameter substitution symbol used with INSERT INTO command?
Answer: &
Show Answer
Hide Answer
18. Which command displays the SQL command in the SQL buffer, and then executes it?
Answer: RUN
Show Answer
Hide Answer
19. What are the wildcards used for pattern matching?
Answer: _ for single character substitution and % for multi-character substitution
Show Answer
Hide Answer
22. What are the privileges that can be granted on a table by a user to others?
Answer: Insert, update, delete, select, references, index, execute, alter, all
Show Answer
Hide Answer
23. What command is used to get back the privileges offered by the GRANT command?
Answer: REVOKE
Show Answer
Hide Answer
24. Which system tables contain information on privileges granted and privileges obtained?
Answer: USER_TAB_PRIVS_MADE, USER_TAB_PRIVS_RECD
Show Answer
Hide Answer
25. Which system table contains information on constraints on all the tables created?
Answer: USER_CONSTRAINTS
Show Answer
Hide Answer
26. TRUNCATE TABLE EMP;
DELETE FROM EMP;
Will the outputs of the above two commands differ?
Answer: Both will result in deleting all the rows in the table EMP.
Show Answer
Hide Answer
27. What is the difference between TRUNCATE and DELETE commands?
Answer: TRUNCATE is a DDL command whereas DELETE is a DML command. Hence
DELETE operation can be rolled back, but TRUNCATE operation cannot be rolled back.
WHERE clause can be used with DELETE and not with TRUNCATE.
Show Answer
Hide Answer
28. What command is used to create a table by copying the structure of another table?
Answer: CREATE TABLE .. AS SELECT command
Show Answer
Hide Answer
29. Which date function is used to find the difference between two dates?
Answer: MONTHS_BETWEEN
Show Answer
Hide Answer
30. Why does the following command give a compilation error?
Answer: DROP TABLE &TABLE_NAME;
Variable names should start with an alphabet. Here the table name starts with an
'&' symbol.
Show Answer
Hide Answer
31. What is the advantage of specifying WITH GRANT OPTION in the GRANT command?
Answer: The privilege receiver can further grant the privileges he/she has obtained from the owner to any other user.
Show Answer
Hide Answer
32. What is the use of the DROP option in the ALTER TABLE command?
Answer: It is used to drop constraints specified on the table.
Show Answer
Hide Answer
33. What is the use of DESC in SQL?
Answer: DESC has two purposes. It is used to describe a schema as well as to retrieve rows from table in descending order.
Show Answer
Hide Answer
34. What is the use of CASCADE CONSTRAINTS?
Answer: When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.
Show Answer
Hide Answer
35. Which function is used to find the largest integer less than or equal to a specific value?
Answer: FLOOR
Show Answer
Hide Answer