Aspire Systems Technical Architect Interview Questions and Answers

Functions return a value while procedures do not in Oracle.

• Functions are used to perform a specific task and return a value.

• Procedures are used to perform a specific task but do not return a value.

• Functions can be used in SQL statements while procedures cannot.

• Functions can be called from within procedures while procedures cannot be called from within functions.

Cursor is a pointer to a private SQL area that stores information about processing a specific SELECT or DML statement.

• Attributes include %FOUND, %NOTFOUND, %ROWCOUNT, %ISOPEN, %BULK_ROWCOUNT, %BULK_EXCEPTIONS

• Used to fetch and manipulate data from a result set

• Can be declared and used in PL/SQL blocks

• Example: OPEN cursor_name FOR SELECT * FROM table_name WHERE column_name = value;

Removing duplicate records in SQL

• Use SELECT DISTINCT to retrieve unique records

• Use GROUP BY to group records by a specific column

• Use HAVING to filter out groups with more than one record

• Use DELETE to remove duplicate records from the table

Packages in Oracle provide modularity, encapsulation, and reusability of code.

• Packages group related procedures, functions, and variables together.

• Packages provide a namespace for the objects they contain, avoiding naming conflicts.

• Packages can be compiled and stored in the database for efficient execution.

• Packages can be granted privileges, allowing controlled access to their contents.

• Packages can be versioned and upg...read more