Senthil Murugan's Blog

Home » Oracle

Category Archives: Oracle

Top 10 reason to upgrade oracle 12c from old version

While your database may be running “fine” and your DBAs could be understandably skittish about rocking the boat by upgrading, in truth there are many tangible benefits to gain from upgrading to Oracle Database 12c.

In this expert e-guide Brian Peasland, a DBA with over 20 years of experience, breaks down 10 reasons you should upgrade. Here are the first 3:

  1. Increased Oracle support
  2. Updated hardware and operating systems
  3. Security patches

Learn the remaining 7 inside now.

Thanks to TechTarget

http://www.bitpipe.com/data/demandEngage.action?resId=1486401659_132

Advertisements

Oracle Application Express 5.2

Thanks to Oracle

http://www.oracle.com/technetwork/developer-tools/apex/application-express/oracle-application-express-sod-1596338.pdf

Oracle Application Express 5.2 will focus on both new features and enhancements to existing functionality, and it is planned to incorporate the following:

• Remote SQL Data Access – Extend common components such as Interactive Grids, Classic Reports and Charts to interface with data stored in remote databases using ORDS and REST.

• REST Service Consumption – Provide declarative methods to define references to external REST APIs and generic JSON data feeds and to use these references as data sources for Interactive Grids, Reports and Forms.

• Declarative App Features – Introduce a new Create App Wizard that allows for adding components and app features to new and existing applications

• Interactive Grid enhancements – Add additional reporting capabilities such as Group-By and Pivot, support for subscriptions and computations, flexible row height and general UI improvements.

• Page Designer – Provide client-side wizards for the creation of more complex components like Dynamic Actions, Forms and Shared Components, User interface improvements.

• Upgrade Oracle JET and jQuery – Adopt the most recent versions of the integrated JavaScript libraries to take advantage of new Data Visualizations such as Gantt charts and new Form widgets and controls.

• New REST Workshop – Provide declarative methods to support the development of ORDS enabled REST web services, taking advantage of the latest features and functionality of ORDS.

• Packaged Applications – Improved framework and enhancements to the packaged applications

How to Create an Execution Plan–SQL Tuning

 

Autotrace in SQL Developer

This is a step up over the SQL*Plus version. It displays the stats for every step of the plan. So for each operation you can see metrics such as:

  • How many buffer gets it used
  • How long it took to run
  • How many disk reads and writes it did

For more information, please check the below.

Thanks to Oracle  – https://blogs.oracle.com/sql/entry/how_to_create_an_execution

Oracle JavaScript Extension Toolkit (Oracle JET)

Dear Friends,

Hope the below is useful for your new developments, please check and take care.

Oracle JavaScript Extension Toolkit (Oracle JET) is a complete yet modular JavaScript development toolkit helping developers build engaging user interfaces. Based on industry standards and popular open-source frameworks, Oracle JET further adds advanced functionality and services to help developer build better applications faster.

http://www.oracle.com/technetwork/developer-tools/jet/overview/index.html

Thanks to Oracle for introducing new open source products like this.

Oracle Forms 12c installation document for Windows

Greetings,

Thanks to Mr.Shiva and Author Mr.Abdellatif Abugharbieh, hope this will be useful for everyone.

Please use the below link to download/install at your own risk.

Wish you all the best.

https://csenthilmurugan.files.wordpress.com/2015/12/oracle_forms_12c_installation_for_windows.pdf

Oracle Forms and Reports 12c is available

 

Dear Friends,

Long waited Oracle forms and reports 12c is available, check and enjoy with the new features.

http://www.oracle.com/technetwork/developer-tools/forms/overview/index.html

 

Thanks to Oracle

Materialized Views–NO DATA FOUND

Thanks to Alex Nuijten

http://nuijten.blogspot.ae/2015/04/refresh-multiple-materialized-views-in.html

To refresh multiple Materialized Views you can use the DBMS_MVIEW package, with the procedure aptly named Refresh. One method is to provide a comma-separated list of Materialized View names, or you can use an DBMS_UTILITY.UNCL_ARRAY to achieve the same objective.
When using the latter method, I stumbled upon this oddity (which is not in the current documentation, or at least I couldn’t find it).

The procedure that I initially wrote was the following:

1
2
3
4
5
6
7
8
9
10
11
create or replace
procedure refresh_mviews
is
   l_mviews dbms_utility.uncl_array;
begin
   l_mviews(1) := 'ABC_MV';
   l_mviews(2) := 'DEF_MV';
   l_mviews(3) := 'GHI_MV';  
   dbms_mview.refresh (tab => l_mviews);
end refresh_mviews;
/

On line 4 a local variable is declared on the type DBMS_UTILITY.UNCL_ARRAY. The declaration of this type is

1
TYPE uncl_array IS TABLE OF VARCHAR2(227) INDEX BY BINARY_INTEGER;

On lines 6 through 8 the array is filled with the names of the Materialized Views that I want to refresh.
The actual refresh is done on line 9.

When executing the code above, the following exception is raised:

1
2
3
4
5
6
7
8
9
Error report -
ORA-01403: Geen gegevens gevonden.
ORA-06512: in "SYS.DBMS_SNAPSHOT", regel 2809
ORA-06512: in "SYS.DBMS_SNAPSHOT", regel 3025
ORA-06512: in "ALEX.REFRESH_MVIEWS", regel 13
ORA-06512: in regel 2
01403. 00000 -  "no data found"
*Cause:    No data was found from the objects.
*Action:   There was no data from the objects which may be due to end of fetch.

Strange…

After some googling I found some old documentation (from Oracle 9i) describing the functionality of the REFRESH procedure in the DBMS_MVIEW pacakge:

If the table contains the names of n materialized views, then the first materialized view should be in position 1 and the n + 1 position should be set to NULL.

This explains the exception that is being raised.

Adding line 9 in the code below fixes this problem:

1
2
3
4
5
6
7
8
9
10
11
12
create or replace
procedure refresh_mviews
is
   l_mviews dbms_utility.uncl_array;
begin
   l_mviews(1) := 'ABC_MV';
   l_mviews(2) := 'DEF_MV';
   l_mviews(3) := 'GHI_MV';  
   l_mviews(4) := null;
   dbms_mview.refresh (tab => l_mviews);
end refresh_mviews;
/