SAVE EXCEPTIONS and SQL%BULK_EXCEPTION
We saw how the FORALL syntax allows us to perform bulk DML operations.
But what happens if one of those individual operations results in an exception? If there is no exception handler, all the work done by the current bulk operation is rolled back.
If there is an exception handler, the work done prior to the exception is kept,...
Tuesday, 30 May 2017
Utility / Math related queries
Convert number to words
More info: Converting number into words in Oracle
SELECT TO_CHAR (TO_DATE (1526, 'j'), 'jsp') FROM DUAL;
Output:
one thousand five hundred twenty-six
Find string in package source code
Below query will search for string ‘FOO_SOMETHING’ in all package source. This query comes handy when you want to find a particular procedure or function...
Database administration queries
Database version informationReturns the Oracle database version.
SELECT * FROM v$version;
Database default information
Some system default information.
SELECT username,profile,default_tablespace,temporary_tablespaceFROM dba_users;
Database Character Set information
Display the character set information of database.
SELECT * FROM nls_database_parameters;
Get...
Friday, 26 May 2017
SQL%BULK_ROWCOUNT
The SQL%BULK_ROWCOUNT cursor attribute gives granular information about the rows affected by each iteration of the FORALL statement. Every row in the driving collection has a corresponding row in the SQL%BULK_ROWCOUNT cursor attribute.
Lets see example you will clearly understand.
create one table.
CREATE TABLE bulk_rowcount_test AS
SELECT *
FROM all_users;
Below...
FORALL in Bulk Collection.
The FORALL syntax allows us to bind the contents of a collection to a single DML statement, allowing the DML to be run for each row in the collection without requiring a context switch each time. To test bulk binds using records we first create a test table.
lets try to understand with example.
create one table to test how forall function will work and performance...
Data dictionary queries
Check if a table exists in the current database schema
A simple query that can be used to check if a table exists before you create it. This way you can make your create table script rerunnable. Just replace table_name with actual table you want to check. This query will check if table exists for current user (from where the query is executed).
SELECT table_nameFROM user_tablesWHERE...
Thursday, 25 May 2017
Subscribe to:
Posts (Atom)
