Archive for January, 2022

PLS-00201: identifier UTL MAIL.SEND must be declared

January 9th, 2022, posted in Oracle Queries
Share

PLS-00201: identifier ‘UTL_MAIL.SEND’ must be declared

During execution of the package use UTL Mail package for send the mail may give the error PLS-00201. It may be caused due to in configured of UTL Mail package in the database. You may configure the UTL Mail package with following script:

Error:

ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'UTL_MAIL.SEND' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Solution:

1. Set the Oracle Home Environment Variable

Set ORACLE_HOME=D:\Oracle\12.1.0\dbhome_1
set PATH=%ORACLE_HOME%\bin;%PATH%

2. Connect with sqlplus / as sysdba

@?\rdbms\admin\utlmail.sql;
@?\rdbms\admin\prvtmail.plb;

3. Set up the Parameter SMTP server.

ALTER SYSTEM SET smtp_out_server='address:25' SCOPE=BOTH;

4. Grant the user with required permission.

GRANT execute ON utl_mail TO NEWUSER;

Share

Oracle Database 20c New Features

January 2nd, 2022, posted in Oracle
Share

Oracle Database 20c New Features

Oracle Database 20c is about to release this year but Oracle has already announce some wonderful features of 20c release in OOW and 20c Webcast.

Most Important things of this year is,

1) Oracle Cloud – Free Tier :
Always Free Services- 1 ADW, 1 ATP , 2 Compute services always free

2) De-support of non-cdb architecture, facilitating 3 PDB’s with no cost :

Few new enhancements which catches my eyes on Oracle Database 20c Release.

1) In-Memory Enhancements :
– Spatial & Text
– Vector Joins
– Hybrid Scans
– Auto populate and evict in-memory

Read more here about In-Memory Enhancements
https://blogs.oracle.com/in-memory/oracle-database-20c-aim-enhancements

2) New Binary JSON data type :

Read more here,
https://blogs.oracle.com/sql/how-to-store-query-and-create-json-documents-in-oracle-database

3) Auto Machin Learning in OML4py :
Automatically build and compare Machine Learning models

Read more here,
https://blogs.oracle.com/machinelearning/automated-machine-learning-for-data-scientists

4) Native Blockchain Table :
New blockchain type table. This allows only Inserts and select.

Read more here,
https://blogs.oracle.com/blockchain/blockchain-tables-in-oracle-database:-technology-convergence

Share

Oracle Database New Features (12c to 19c)

January 1st, 2022, posted in Oracle EBS Application
Share

Oracle Database New Features (12c to 19c)

From Oracle database 12c on-wards, whole architecture was changed. And it comes with many new features. Oracle also changed the policy of releasing database patches and version. Now we are seeing new version every year from 2018 on-wards, i.e. 18c in the year of 2018, 19, in the year of 2019 and now very soon 20c is coming this year.

Here I am highlighting Oracle database new features which was introduce in different Oracle database versions. My focus is to highlight most important features from version 12c onwards up to 19c.

This will help you to understand which feature we can use in which version.

 

 

Category
DB
Versions
12.1
12.2
18c
19c
High
Availability
Comprehensive HA and disaster
recovery functionality
Sharding
Sharded RAC
Data Guard DML Redirect
Oracle Golden Gate for heterogeneous replication
Autonomous Health Framework
User-defined Sharding Methods
Sharding: Supports multiple PDB shards in a CDB
Unique sequence number generation per shard
Zero Downtime Grid Infrastructure
Patching
PDB Recovery Catalog
Improvements to Application
Continuity

 

Category
DB Versions
12.1
12.2
18c
19c
Performance and Salability
In-Memory Column Store
In Memory JSON Queries
Memory Optimized Row
Store
Fast ingest support for
IoT type workloads
Engineered Systems
In Memory Active Standby
New Approximate Functions
Quarantine for SQL Statements
Optimizer Adaptive
Feature
Optimizer Adaptive
Feature
Stats Only Queries
Approximate Query Processing
New Approximate Functions
Improvements for COUNT DISTINCT and GROUP BY queries
Optimizer Statistics
Advisor
Automatic Indexing
Realtime Statistics
Automatic Resolution of
SQL Plan Regressions

 

Category
DB Versions
12.1
12.2
18c
19c
Multitenant
Container managed
database virtualization
Online cloning &
relocation
Transportable backups
DBCA Silent mode
operations for PDB
Manage Many as one
Incremental refresh of test/dev master
Snapshot carousel
RAT and ADDM at PDB level
Patching, Backup,
Security, Online Cloning, Online Relocation
Application containers
Refreshable PDB
switchover
Automated PDB Relocation

 

Category
DB Versions
12.1
12.2
18c
19c
Security
Security In-Depth
Access Controls, Encryption, Redaction, Masking, Auditing, SQL Firewalls Key
Vault
Online encryption
Integration with Active
Directory
Data Dictionary
Encryption
Audit Vault Database Firewall
Password-less schema creation
Password-less Schema-only Oracle accounts
Security Assessment Tool
Database Vault Operations
Control
Privilege Analysis available in EE

 

Category
DB Versions
12.1
12.2
18c
19c
Application 
Development
Application Express 5.1
Long Identifiers
Improved JSON Support
SQL JSON Enhancements
Comprehensive Language support
PL/SQL, SQL , Python, Node.js, PHP, Java, C, .NET, REST
JSON
Case Insensitive Databases
C, Python, JavaScript JSON SODA API
Materialized View for JSON_TABLE
Private Temporary Tables
Partial JSON Update
support
Realtime SQL Monitoring for Developers

 

Category
DB Versions
12.1
12.2
18c
19c
Data Warehousing and Big Data
Partitioning,
Compression, SQL, Analytical Views, Analytical SQL, Data Mining
Partitioned External
Tables
In-Memory for external
tables
Hybrid Partitioned Tables
Easily analyze data held in Hadoop with Big Data SQL
Analytical Views
Automatic propagation of nologged data to standby
Stats only Queries
Big Data Appliance
Machine Learning :
Random Forrest, Neural Network

 

Category
DB Versions
12.1
12.2
18c
19c
Other 
Improvements
EZConnect Improvements
Oracle Database XE
Free to use
Includes most EE features
no RAC, Data Guard, GoldenGate
Oracle Database 19c RPM
Install, Yum install of Oracle Instant Clients
Oracle Read Only Homes
No click through client
software install
Oracle Docker images
SQLDeveloper Web
Oracle Data REST Services (ORDS)
Share