Function to calculate count using dynamic SQL, for an input table name
create or replace function
count_rows (p_tablename in varchar2)
from ' || p_tablename
USER_TABLES has all the table statistics for the same schema. How to invoke above functions:
SQL> select table_name
2 , count_rows(table_name) num_rows
3 from user_tables;
4 rows selected.
© 2015, www.techkatak.com. All rights reserved.
What is PL/SQL? PL/SQL stands for Procedural Language extension of SQL. PL/SQL is a combination of SQL along with the procedural features of programming languages. It was developed by Oracle Corporation in the early 90’s to enhance the capabilities of SQL. The PL/SQL Engine: Oracle uses a PL/SQL engine to processes the PL/SQL statements. A […]
When we write any program in Oracle, it includes both PL/SQL statements and SQL statements. PL/SQL statements are run by the PL/SQL statement executor and SQL statements are run by the SQL statement executor. When the PL/SQL runtime engine sees a SQL statement, it stops and passes the SQL statement over to the SQL engine. […]
Ref Cursor is a data type of Cursor variable. It is not limited to one query. You can open a cursor variable for a query, process the result set, and then use the cursor variable for another query. You can assign a value to it. You can use it in an expression. It can be […]
Dynamic SQL is a programming methodology for generating and running SQL at runtime. This is mostly useful to perform DDL operations at runtime. There are 2 ways to write Dynamic SQL. Native Dynamic SQL PL/SQL language for building,running Dynamic SQL DBMS_SQL package A package for building,running Dynamic SQL When to write Dynamic SQL? SQL that […]
PRAGMA is a compiler directive. AUTONOMOUS_TRANSACTION is a transaction where PL/SQL suspends the current transaction temporarily and performs a fully independent transaction. A subprogram marked with PRAGMA can do SQL operations and commit/rollback these operations without rolling back the main transaction. There are scenarios when you might want to have an transaction to commit or […]
Packages are schema objects that groups Pl/sql types,variables and subprograms logically. Package has 2 parts. Package specification Package body Oracle package specification can be created without body but Package body can’t be created without specification. Package Specification: It contains all the information about content of the package. It DECLARES the types, variables, constants, exceptions, cursors, […]