What does Opening a cursor do ?
- It executes the query and identifies the Result set
What does Fetching a cursor do ?
- It reads the Result Set row by row.
What does Closing a cursor do ?
- It clears the private SQL area and de-allocates the memory.
What are Cursor Variables ?
- Also called REF CURSORS.
- They are not tied to a single SQL. They point to any SQL area dynamically.
- Advantage is : You can declare variables at Client side and open them Server side. You can thus centralize data retrieval.
Why use Cursor Variables?
- You can pass cursor RESULT SETS between PL/SQL stored programs and clients.
What are SQLCODE and SQLERRM ?
- Oracle Error code and detailed error message
- They are actually functions with no arguments, that can be used only in procedural statements ( not SQL)
What are Pseudocolumns ?
- They are not actual columns. They are like Functions without arguments.
- They typically give a different value for each row.
- Examples: ROWNUM, NEXTVAL, ROWID, VERSION_STARTTIME
Why use Truncate over Delete while deleting all rows ?
- Truncate is efficient. Triggers are not fired.
- It deallocates space (Unless REUSE STORAGE is given).
What is a ROWID composed of ?
- It's a hexadecimal string representing the address of a row. Prior to Oracle 8, it's a restricted rowid comprising block.row.file. Extended rowid ( the default on higher releases) comprises data object number as well ( comprising the segment number ).
What is the use of a ROWID ?
- Retrieve data faster with ROWID.
- Shows you the physical arrangement of rows in the table.
- Also unique identifier for each row.
Can rows from two different tables have the same ROWID?
- Possible, if they are in a Cluster
What is ROWNUM and ROW_NUMBER ?
- ROWNUM is a pseudocolumn which is the number assigned to each row retrieved.
- ROW_NUMBER is an analytic function which does something similar, but has all the capabilities of PARTITION BY and ORDER BY clauses..
What is an inline view?
- It's not a schema object
- It's a subquery in the FROM clause with an alias that can be used as a view within the SQL statement.
What are Nested and Correlated subqueries ?
- The subquery used in WHERE clause is a nested subquery.
- If this subquery refers to any column in the parent statement, it becomes a correlated subquery.
How do you retrieve a dropped table in 10g?
- FLASHBACK table <tabname> to BEFORE DROP
What are PSPs?
- PL/SQL Server Pages. Web pages developed in PL/SQL
What is an index-organized table?
- The physical arrangement of rows of this table changes with the indexed column.
- It's. in-short, a table stored like an index itself.
What is an implicit cursor?
- Oracle opens an implicit cursor to process each SQL statement not associated with an explicit cursor.
Name a few implicit cursor attributes.
- %FOUND, %ROWCOUNT, %NOTFOUND, %ISOPEN, %BULK_ROWCOUNT, %BULK_EXCEPTIONS
- It executes the query and identifies the Result set
What does Fetching a cursor do ?
- It reads the Result Set row by row.
What does Closing a cursor do ?
- It clears the private SQL area and de-allocates the memory.
What are Cursor Variables ?
- Also called REF CURSORS.
- They are not tied to a single SQL. They point to any SQL area dynamically.
- Advantage is : You can declare variables at Client side and open them Server side. You can thus centralize data retrieval.
Why use Cursor Variables?
- You can pass cursor RESULT SETS between PL/SQL stored programs and clients.
What are SQLCODE and SQLERRM ?
- Oracle Error code and detailed error message
- They are actually functions with no arguments, that can be used only in procedural statements ( not SQL)
What are Pseudocolumns ?
- They are not actual columns. They are like Functions without arguments.
- They typically give a different value for each row.
- Examples: ROWNUM, NEXTVAL, ROWID, VERSION_STARTTIME
Why use Truncate over Delete while deleting all rows ?
- Truncate is efficient. Triggers are not fired.
- It deallocates space (Unless REUSE STORAGE is given).
What is a ROWID composed of ?
- It's a hexadecimal string representing the address of a row. Prior to Oracle 8, it's a restricted rowid comprising block.row.file. Extended rowid ( the default on higher releases) comprises data object number as well ( comprising the segment number ).
What is the use of a ROWID ?
- Retrieve data faster with ROWID.
- Shows you the physical arrangement of rows in the table.
- Also unique identifier for each row.
Can rows from two different tables have the same ROWID?
- Possible, if they are in a Cluster
What is ROWNUM and ROW_NUMBER ?
- ROWNUM is a pseudocolumn which is the number assigned to each row retrieved.
- ROW_NUMBER is an analytic function which does something similar, but has all the capabilities of PARTITION BY and ORDER BY clauses..
What is an inline view?
- It's not a schema object
- It's a subquery in the FROM clause with an alias that can be used as a view within the SQL statement.
What are Nested and Correlated subqueries ?
- The subquery used in WHERE clause is a nested subquery.
- If this subquery refers to any column in the parent statement, it becomes a correlated subquery.
How do you retrieve a dropped table in 10g?
- FLASHBACK table <tabname> to BEFORE DROP
What are PSPs?
- PL/SQL Server Pages. Web pages developed in PL/SQL
What is an index-organized table?
- The physical arrangement of rows of this table changes with the indexed column.
- It's. in-short, a table stored like an index itself.
What is an implicit cursor?
- Oracle opens an implicit cursor to process each SQL statement not associated with an explicit cursor.
Name a few implicit cursor attributes.
- %FOUND, %ROWCOUNT, %NOTFOUND, %ISOPEN, %BULK_ROWCOUNT, %BULK_EXCEPTIONS
No comments:
Post a Comment