Development

How To Turn On or Off SQL Developer Autocommit Settings

Complete IT Professional - Fri, 2015-07-10 06:00

SQL Developer AutocommitIn this article, I’ll explain what the SQL Developer Autocommit feature is, where to find it, and how to turn it on or off.

What Is SQL Developer Autocommit?

When you write an SQL statement in SQL Developer, you perform an operation on that data. If you’re just reading the data, this is pretty straightforward.

Where it acts differently is when you change data in the database. This is usually done with the DELETE, INSERT, or UPDATE statements.

If you run an SQL statement that does any of these functions, then the data will be updated, but it won’t be committed.

If you’re not sure what committed means (when we talk about databases), it just means that the data is not permanently saved. It allows you to see the changes in your session, and then you can make a conscious decision to update the changes permanently (committing) or undoing the changes (rolling back).

Now, this is the default behaviour. There are two separate steps to this – running the statement to change the data, and then a second command to commit the changes to the database.

SQL Developer allows you to change this default behaviour, so that the statement is run and the changes are saved in a single step – hence the term “auto commit”.

 

How Do I Change The Autocommit Setting in SQL Developer?

SQL Developer is pretty flexible when it comes to what you can change, and autocommit is no exception.

To find the autocommit setting, go to the Tools > Preferences.

SQL Developer Tools and Preferences

The Preferences window will appear.

SQL Developer Preferences

Click on the + icon next to Database to expand it.

SQL Developer Preferences

Then, click on Advanced.

SQL Developer Preferences Advanced

Here, you’ll see the option for Autocommit.

SQL Developer Preferences Autocommit

Click the checkbox to turn it on.

Here’s what the setting does:

ValueImpactCheckedAutocommit is on. SQL statements that change data will have their changes automatically committed to the database.UncheckedDefault. Autocommit is off. SQL statements that change data will not have their changes automatically committed to the database. A separate COMMIT action is needed.
What Are The Disadvantages of Autocommit?

Sure, turning on autocommit may save you time and confusion if you expect changes to be saved but they aren’t.

But there are some disadvantages.

You won’t be able to undo any of your changes by issuing a ROLLBACK statement, as you’d normally be able to do. If you run a DELETE or UPDATE statement, your changes are made permanently, and it’s much harder to get them back

It’s not impossible, though. If you have set up save points or database backups then you can restore from a backup, but it is a hassle.

Generally, I would prefer to have this autocommit option turned off in SQL Developer. The only exception would be if you’re running Oracle Express on your own computer and want to save some time with your SQL.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Categories: Development

Oracle RTRIM Function with Examples

Complete IT Professional - Thu, 2015-07-09 06:00

Oracle RTRIM FunctionThe Oracle RTRIM function is one of the most commonly used string manipulation functions in SQL. Learn more about it and see some examples in this article.

Purpose of the Oracle RTRIM Function

The Oracle RTRIM function removes all characters that you specify from the right side of a string.

This is most often used when you want to remove spaces from the right side of the string. However, it can be used for other characters as well, depending on the data.

 

Syntax

The syntax for the Oracle RTRIM function is:

RTRIM ( input_string, [trim_character])

 

Parameters

The parameters of the RTRIM function are:

  • input_string (mandatory): This is the string that will have the characters trimmed from it.
  • trim_string (optional): This is the value to trim or remove from the input_string. If it is omitted, a space character is used for trimming.

The R in RTRIM stands for Right, because it removes the characters from the right of the string.

Some other points to remember:

  • If the trim_string is a literal value, you need to include it inside single quotes. For example, to remove an underscore, you need to specify it as ‘_’
  • Both parameters can be any of the types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
  • The string returned is a VARCHAR2 data type if the input_string was a character, and LOB if it was a LOB data type.
  • The trim_string can be more than one character
  • The function removes each individual value inside trim_string, not the string as a whole. See the Examples section below for more information.

 

Can You Use Oracle RTRIM with Special Characters?

Yes, you can. You can use exclamation points and many kinds of special characters as input.

You can also use unicode characters, such as å, as the function supports NVARCHAR2 types.

See the Examples section for more information.

 

Can You Use Oracle RTRIM with LTRIM?

Yes, you can. It works similar to the TRIM function.

You’ll need to enclose one within the other, and it doesn’t matter which one is outside and which is inside.

So, LTRIM(RTRIM(value)) works the same as RTRIM(LTRIM(value)).

See the Examples section for more information.

 

Can You Use Oracle RTRIM with CHAR or LOB Types?

Yes, the Oracle RTRIM can handle CHAR and LOB types. It can handle CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB data types.

 

Examples of the RTRIM Function

Here are some examples of the RTRIM function. I find that examples are the best way for me to learn about code, even with the explanation above.

 

Example 1

This example demonstrates a simple RTRIM with no trim value specified.

SELECT RTRIM('Complete IT Professional   ')
AS RTRIM_EXAMPLE FROM DUAL;

Result:

RTRIM_EXAMPLEComplete IT Professional

The extra spaces are removed from the original value.

 

Example 2

This example uses a specific value to trim.

SELECT RTRIM('Complete IT Professional___', '_')
AS RTRIM_EXAMPLE FROM DUAL;

Result:

RTRIM_EXAMPLEComplete IT Professional

The underscores are removed from the original value.

 

Example 3

This example uses RTRIM with several characters as the string to trim.

SELECT RTRIM('Complete IT Professional; ; ; ; ; ', ' ; ')
AS RTRIM_EXAMPLE FROM DUAL;

Result:

RTRIM_EXAMPLEComplete IT Professional

Both the spaces and semicolons are removed from the original value.

 

Example 4

This example uses RTRIM on data in a table, instead of providing a value.

SELECT country, RTRIM(country, 'A')
AS RTRIM_EXAMPLE FROM customers;

Result:

COUNTRYRTRIM_EXAMPLEUSAUSUSAUSCanadaCanadaUKUKUSAUS(null)(null)FranceFrance(null)(null)

The capital A is removed from the “USA” values. It remains on the end of “Canada” because it is a lower-case “a”.

 

Example 5

This example uses RTRIM on data in a table with several characters in the trim parameter.

SELECT full_address, RTRIM(full_address, 'et')
AS RTRIM_EXAMPLE FROM customers;
Result:
FULL_ADDRESSRTRIM_EXAMPLE10 Long Road10 Long Road50 Market Street50 Market Str201 Flinders Lane201 Flinders Lan8 Smith Street8 Smith Str14 Wellington Road14 Wellington Road80 Victoria Street80 Victoria Str5 Johnson St5 Johnson S155 Long Road155 Long Road
The letters “e” and “t” are removed from the right of the street names. Some values have been changed to “Str” because all occurrences of these letters are removed, not just those that end in “et”.
Example 6

This example uses RTRIM with 0 as the parameter.

SELECT RTRIM('Complete IT Professional00', 0)
AS RTRIM_EXAMPLE FROM DUAL;

Result:

RTRIM_EXAMPLEComplete IT Professional

The zeroes are removed from the original value.

 

Example 7

This example uses both LTRIM and RTRIM in the one expression.

SELECT LTRIM(RTRIM('___Complete IT Professional__', '_'), '_')
AS RTRIM_EXAMPLE FROM DUAL;

Result:

RTRIM_EXAMPLEComplete IT Professional

The underscores are removed from both sides of the original value.

 

Example 8

This example uses Unicode characers as the trim parameter.

SELECT RTRIM('Complete IT Professionalé', 'é')
AS RTRIM_EXAMPLE FROM DUAL;

Result:

RTRIM_EXAMPLEComplete IT Professional

The accented “e” character is removed from the original value.

 

Similar Functions

Some functions which are similar to the RTRIM function are:

  • TRIM – This function trims characters from both the left and the right of the specified value.
  • LTRIM – This function trims characters from the left of the specified value only.
  • SUBSTR – Extracts one value from a larger value. Not really a TRIM function but does something similar.
  • REPLACE – Replaces occurrences of one text value with another.

You can find a full list of Oracle functions here.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Image courtesy of digitalart / FreeDigitalPhotos.net

Categories: Development

12c: New SQL PLAN OPERATIONS and HINTS

XTended Oracle SQL - Wed, 2015-07-08 08:27

This post is just a compilation of the links to other people’s articles and short descriptions about new SQL PLAN OPERATIONS and HINTS with a couple little additions from me.

th.c_operation_name { min-width:100px; max-width:100px; } th.c_description { min-width:200px } .c_links { min-width:150px; max-width:220px; } .c_links ul { margin: 0 0 5px 0 !important; -webkit-padding-start: 5px; } .c_links ul li { margin-left: 0px; -webkit-padding-start: 0px; } td.c_operation_name { font-size:12px;} td.c_description { font-size:12px;} td.c_links { font-size:10px;} .c_body td { vertical-align: text-top; } div.hints_wrapper { border-style: solid; border-width: 1px; padding: 2px; overflow: scroll !important; } div.hints_content { width: 1175px; min-width:1175px; padding: 2px; }


OPERATION_NAME Description Links JSONTABLE EVALUATION JSON_TABLE execution XMLTABLE EVALUATION This is new name for “COLLECTION ITERATOR PICKLER FETCH [XQSEQUENCEFROMXMLTYPE]”. XPATH EVALUATION still exists. MATCH RECOGNIZE New feature “PATTERN MATCHING” STATISTICS COLLECTOR Optimizer statistics collector OPTIMIZER STATISTICS GATHERING Automatic Optimizer statistics gathering during the following types of bulk loads:

  • CREATE TABLE … AS SELECT
  • INSERT INTO … SELECT into an empty table using a direct-path insert
CUBE JOIN Joining Cubes to Tables and Views EXPRESSION EVALUATION Each parallel slave executes scalar correllated subqueries from SELECT-list. parallel “FILTER” Each parallel slave executes own FILTER operation

Example
SQL> explain plan for
  2  select--+ parallel
  3      owner,object_name
  4  from xt_test l
  5  where exists(select/*+ no_unnest */ 0 from dual where dummy=object_name);

Explained.

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
Plan hash value: 2189761709

-------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Rows  | Bytes | Cost (%CPU)|   TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |     2 |    62 |   177K  (1)|       |      |            |
|   1 |  PX COORDINATOR          |           |       |       |            |       |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10000  | 91060 |  2756K|   113   (0)| Q1,00 | P->S | QC (RAND)  |
|*  3 |    FILTER                |           |       |       |            | Q1,00 | PCWC |            |
|   4 |     PX BLOCK ITERATOR    |           | 91060 |  2756K|   113   (0)| Q1,00 | PCWC |            |
|   5 |      INDEX FAST FULL SCAN| IX_TEST_1 | 91060 |  2756K|   113   (0)| Q1,00 | PCWP |            |
|*  6 |     TABLE ACCESS FULL    | DUAL      |     1 |     2 |     2   (0)|       |      |            |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "SYS"."DUAL" "DUAL" WHERE "DUMMY"=:B1))
   6 - filter("DUMMY"=:B1)
                

[collapse]
PX SELECTOR Execution of the serial plan parts in the one of the parallel slaves PX SEND 1 SLAVE Execution of the serial plan parts in the one of the parallel slaves(single DFO tree) PX TASK Parallel access to fixed tables(x$) by each node in RAC HYBRID HASH DISTRIBUTION Adaptive parallel data distribution that does not decide the final data distribution(HASH, BROADCAST or SKEW) method until execution time. PQ_DISTRIBUTE_WINDOW In addition to “PX SEND” HASH-distribution for WINDOW functions, “PX SEND RANGE” was added
Example
-- TESTPART - list-partitiioned table:
-------------------------------------------------------------------------------------------------
| Operation               | Name     | Rows  | Cost | Pstart| Pstop |   TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------
| SELECT STATEMENT        |          | 74384 |   102|       |       |       |      |            |
|  PX COORDINATOR         |          |       |      |       |       |       |      |            |
|   PX SEND QC (RANDOM)   | :TQ10001 | 74384 |   102|       |       | Q1,01 | P->S | QC (RAND)  |
|    WINDOW SORT          |          | 74384 |   102|       |       | Q1,01 | PCWP |            |
|     PX RECEIVE          |          | 74384 |   100|       |       | Q1,01 | PCWP |            |
|      PX SEND RANGE      | :TQ10000 | 74384 |   100|       |       | Q1,00 | P->P | RANGE      |
|       PX BLOCK ITERATOR |          | 74384 |   100|     1 |     3 | Q1,00 | PCWC |            |
|        TABLE ACCESS FULL| TESTPART | 74384 |   100|     1 |     3 | Q1,00 | PCWP |            |
-------------------------------------------------------------------------------------------------
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      PQ_DISTRIBUTE_WINDOW(@"SEL$1" 3)
      FULL(@"SEL$1" “TESTPART"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('12.1.0.2')
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

[collapse]

Hint PQ_DISTRIBUTE_WINDOW(@Query_block N), where N=1 for hash, N=2 for range, N=3 for list VECTOR
KEY VECTOR Inmemory aggregation RECURSIVE ITERATION Unknown WINDOW CONSOLIDATOR WINDOW CONSOLIDATOR BUFFER for parallel execution of analyrical WINDOW aggregation functions

Example
SQL> explain plan for select/*+ parallel(t 4) PQ_DISTRIBUTE_WINDOW(2) */ count(*) over(partition by owner) cnt,owner from xt_test t;
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 3410952625
---------------------------------------------------------------------------------------------------
| Id | Operation                    |Name    |Rows |Cost |Pstart|Pstop|   TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |        |91060|  124|      |     |       |      |            |
|  1 |  PX COORDINATOR              |        |     |     |      |     |       |      |            |
|  2 |   PX SEND QC (RANDOM)        |:TQ10001|91060|  124|      |     | Q1,01 | P->S | QC (RAND)  |
|  3 |    WINDOW CONSOLIDATOR BUFFER|        |91060|  124|      |     | Q1,01 | PCWP |            |
|  4 |     PX RECEIVE               |        |91060|  124|      |     | Q1,01 | PCWP |            |
|  5 |      PX SEND HASH            |:TQ10000|91060|  124|      |     | Q1,00 | P->P | HASH       |
|  6 |       WINDOW SORT            |        |91060|  124|      |     | Q1,00 | PCWP |            |
|  7 |        PX BLOCK ITERATOR     |        |91060|  122|    1 |    4| Q1,00 | PCWC |            |
|  8 |         TABLE ACCESS FULL    |XT_TEST |91060|  122|    1 |    4| Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------

Note
-----
   - Degree of Parallelism is 4 because of table property
                

[collapse]
DETECT END Unknown DM EXP MAX AGGR Unknown DM EXP MAX PAR Unknown FAULT-TOLERANCE BUFFER The fault-tolerance for parallel statement.
Patent #US8572051: Making parallel execution of structured query language statements fault-tolerant
  • PX_FAULT_TOLERANCE / NO_PX_FAULT_TOLERANCE hints


See also:

  1. Randolf Geist “12c New Optimizer Features”
  2. Randolf Geist “Parallel Execution 12c New Features Overview”


HINTS:

sup {color: red} table.HINTS{ font-size:12px; } .HINTS td {vertical-align: text-top;}

PATH HINT_CLASS HINT_NAME VERSION VERSION_OUTLINE ALL WITH_PLSQL WITH_PLSQL 12.1.0.1 ALL -> ANSI_REARCH ANSI_REARCH 1 ANSI_REARCH
NO_ANSI_REARCH 12.1.0.2 12.1.0.2 ALL -> EXECUTION BATCH_TABLE_ACCESS_BY_ROWID 2 BATCH_TABLE_ACCESS_BY_ROWID
NO_BATCH_TABLE_ACCESS_BY_ROWID 12.1.0.1 12.1.0.1 INMEMORY INMEMORY
NO_INMEMORY 12.1.0.2 12.1.0.2 INMEMORY_PRUNING INMEMORY_PRUNING
NO_INMEMORY_PRUNING 12.1.0.2 12.1.0.2 ALL -> COMPILATION -> ZONEMAP ZONEMAP ZONEMAP
NO_ZONEMAP 12.1.0.1 12.1.0.1 ALL -> COMPILATION -> DATA_SECURITY_REWRITE DATA_SECURITY_REWRITE_LIMIT DATA_SECURITY_REWRITE_LIMIT

NO_DATA_SECURITY_REWRITE 12.1.0.1 12.1.0.1 ALL -> COMPILATION -> CBO CLUSTER_BY_ROWID CLUSTER_BY_ROWID
CLUSTER_BY_ROWID 12.1.0.1(11.2.0.4) 12.1.0.1 ALL -> COMPILATION -> CBO -> ACCESS_PATH -> BITMAP_TREE BITMAP_AND BITMAP_AND 12.1.0.1 12.1.0.1 ALL -> COMPILATION -> CBO -> ADAPTIVE_PLAN ADAPTIVE_PLAN ADAPTIVE_PLAN
NO_ADAPTIVE_PLAN 12.1.0.2 12.1.0.2 ALL -> COMPILATION -> CBO -> AUTO_REOPT AUTO_REOPTIMIZE 2 AUTO_REOPTIMIZE
NO_AUTO_REOPTIMIZE 12.1.0.1 ALL -> COMPILATION -> CBO -> JOIN_METHOD ANTIJOIN CUBE_AJ 12.1.0.1 12.1.0.1 SEMIJOIN CUBE_SJ 12.1.0.1 12.1.0.1 ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_CUBE JOIN USE_CUBE

NO_USE_CUBE 12.1.0.1 12.1.0.1 ALL -> COMPILATION -> CBO -> PARTIAL_JOIN PARTIAL_JOIN PARTIAL_JOIN
NO_PARTIAL_JOIN 12.1.0.1 12.1.0.1 ALL -> COMPILATION -> CBO -> PARTITION USE_HIDDEN_PARTITIONS USE_HIDDEN_PARTITIONS 12.1.0.1 ALL -> COMPILATION -> CBO -> PQ PARTIAL_ROLLUP_PUSHDOWN PARTIAL_ROLLUP_PUSHDOWN

NO_PARTIAL_ROLLUP_PUSHDOWN 12.1.0.1 12.1.0.1 PQ_CONCURRENT_UNION PQ_CONCURRENT_UNION

NO_PQ_CONCURRENT_UNION 12.1.0.1 12.1.0.1 PQ_DISTRIBUTE_WINDOW PQ_DISTRIBUTE_WINDOW 12.1.0.1 12.1.0.1 PQ_FILTER PQ_FILTER 12.1.0.1 12.1.0.1 PQ_SKEW PQ_SKEW

NO_PQ_SKEW 12.1.0.1 12.1.0.1 PX_FAULT_TOLERANCE PX_FAULT_TOLERANCE
NO_PX_FAULT_TOLERANCE 12.1.0.1 12.1.0.1 ALL -> COMPILATION -> CBO -> PQ -> PQ_REPLICATE PQ_REPLICATE PQ_REPLICATE
NO_PQ_REPLICATE 12.1.0.1 12.1.0.1 ALL -> COMPILATION -> CBO -> STATS -> DBMS_STATS GATHER_OPTIMIZER_STATISTICS GATHER_OPTIMIZER_STATISTICS

NO_GATHER_OPTIMIZER_STATISTICS 12.1.0.1 ALL -> COMPILATION -> TRANSFORMATION ELIM_GROUPBY ? ELIM_GROUPBY

NO_ELIM_GROUPBY ALL -> COMPILATION -> CBO -> CBQT -> VECTOR_AGG
and

ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> VECTOR_AGG USE_VECTOR_AGGREGATION USE_VECTOR_AGGREGATION
NO_USE_VECTOR_AGGREGATION 12.1.0.2 12.1.0.2 VECTOR_TRANSFORM VECTOR_TRANSFORM

NO_VECTOR_TRANSFORM 12.1.0.2 12.1.0.2 VECTOR_TRANSFORM_DIMS VECTOR_TRANSFORM_DIMS

NO_VECTOR_TRANSFORM_DIMS 12.1.0.2 12.1.0.2 VECTOR_TRANSFORM_FACT VECTOR_TRANSFORM_FACT

NO_VECTOR_TRANSFORM_FACT 12.1.0.2 12.1.0.2

ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> DECORRELATE DECORRELATE DECORRELATE

NO_DECORRELATE 12.1.0.1 12.1.0.1

See also:
Fuyuncat(Wei Huang) – “Oracle 12c new SQL Hints”

Categories: Development

APEX 5.0 New Features Training September 2015

Denes Kubicek - Wed, 2015-07-08 07:12




Oracle Application Express 5.0 wurde am 15.04.2015 freigegeben und ist als Download verfügbar.

Wir haben jetzt alle über zwei Jahre auf das neue Release gewartet ... und das Warten hat sich mehr als gelohnt ... die neuen Möglichkeiten werden Sie umhauen!

Wir haben die neuen Möglichkeiten genau unter die Lupe genommen und sind selbst total begeistert, weil sie das tägliche Arbeiten extrem vereinfachen. Wir haben schon einige Anwendungen in APEX 5.0 neu programmiert und einige ältere Versionan auf 5.0 umgestellt.

Mit dem neuen Pagedesigner sind wir so produktiv wie noch nie, das Universal Theme erlaubt auf einfache Weise, richtig elegante Applikationen zu entwickeln. Damit ist wirklich ein Durchbruch gelungen, die Konfiguration (über Template Optionen) und selbst farbliche Anpassungen sind ein Kinderspiel, der integrierte Theme Roller ist einfach genial!

Modale Dialoge, multiple Interaktive Berichte pro Seite, Erweiterungen für die mobilen Endgeräte, ein komplett neues File-Handling und viele neue Security - Features ... dieses Release ist wirklich umfangreich!

Neben diesen großen Features wurden auch wieder über 100 kleinere oder auch größere Verbesserungen implementiert.

In unseren Kursen haben wir schon über 200 APEX-Fans die besten Herangehensweisen, Tipps und Tricks beigebracht. Durch die Hands-On Übungen vertiefen wir diese und Sie können diese sofort einsetzen ... oder Sie schlagen sie nach ... wenn Sie sie später brauchen ;). Aber auf jeden Fall wissen Sie nach dem Kurs, was möglich ist!

Wir beide (Denes und Dietmar) entwickeln seit 2006 fast jeden Tag mit APEX Applikationen für unsere Kunden, wir haben mit APEX schon alles ausprobiert.

Nehmen Sie die Abkürzung und lernen Sie von den Besten, was für die Praxis am wichtigsten sein wird.

Um die Vorteile des neuen Releases möglichst schnell nutzen zu können, melden Sie sich am besten sofort an und sichern Sie sich Ihren Platz!
  • Klicken Sie auf den Link "Anmeldung zum Kurs".
  • Tragen Sie Ihre Anmeldedaten ein und klicken Sie auf den Button "Anmelden".
  • Sie bekommen sofort eine Bestätigungs-Email zugeschickt.
  • Sobald Sie in der Email auf den Link zur Bestätigung klicken, haben Sie Ihren Platz gesichert und sind auf jeden Fall dabei!


Anmeldung zum Kurs

P.S.: Die vollständige Agenda und weitere Infos zum Kurs gibt es online in der Kursbeschreibung.
Categories: Development

How To Turn On Line Numbers in SQL Developer

Complete IT Professional - Wed, 2015-07-08 06:00

SQL Developer Line NumbersIn this article, I’ll show you what line numbers are in SQL Developer and how to turn this setting on.

What Are Line Numbers in SQL Developer?

Line numbers are numbers that are next to each line in the Code Editor. They indicate which line of code you’re working on.

They are helpful for developers for many reasons:

  • They let you see how many lines of code you’ve written
  • They help with debugging errors, as error messages usually refer to a line number (related: 7 Debugging Tips To Rapidly Improve Your Programming)
  • They let you refer to a specific line from another place (e.g. when talking to another developer)

SQL Developer - no line numbersBy default, the line numbers are turned off in SQL Developer. Many IDEs have them on by default. I much prefer to have them on, and I’m sure you do too.

Let’s take a look at how to toggle the SQL Developer line numbers setting.

 

 

 

How To Turn Line Numbers On in SQL Developer

So, the first step is to go to Tools > Preferences.

SQL Developer - Tools and Preferences

The Preferences window is now shown.

SQL Developer - Preferences

Then, click on the + next to Code Editor to expand that section.

SQL Developer - Code Editor Preferences

 

Then, click on Line Gutter.

SQL Developer - Line Gutter Preferences

The Show Line Numbers option is then shown.

SQL Developer - Show Line Numbers option

If you check this, the line numbers will appear in your code.

SQL Developer - Show Line Numbers

 

 

SQL Developer Line Numbers Possible Values

So, the Line Numbers setting can be changed to:

ValueImpactCheckedLine numbers are shown in the Code Editor windowUncheckedDefault. Line numbers are not shown in the Code Editor window.
If you’re wondering how to turn on SQL Developer line numbers, then this is how it’s done, and this is the impact.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Categories: Development

RESULT_CACHE: run-time dependency tracking

XTended Oracle SQL - Sun, 2015-07-05 14:20

As you know, since 11.2 “relies_on” clause was deprecated and oracle tracks dependencies at runtime now.

Test function and tables
create or replace function f_without_deps(p_tab varchar2) return varchar2
as
   res varchar2(30);
begin
   execute immediate 'select '''||p_tab||''' from '||p_tab||' where rownum=1' into res;
   return res;
end;
/
create table a as select 'a' a from dual;
create table b as select 'b' b from dual;
create view v_ab as select a,b from a,b;

[collapse]

And it works fine with normal tables:
v_ab
SQL> exec :p_tab:='v_ab';

PL/SQL procedure successfully completed.

SQL> call DBMS_RESULT_CACHE.flush();

Call completed.

SQL> select/*+ result_cache */ f_without_deps(:p_tab) result from dual;

RESULT
----------
v_ab

1 row selected.

SQL> select name,bucket_no, id,type,status,pin_count,scan_count,invalidations from v$result_cache_objects o order by type,id;

NAME                                                               BUCKET_NO  ID TYPE       STATUS     PIN_COUNT SCAN_COUNT INVALID
----------------------------------------------------------------- ---------- --- ---------- --------- ---------- ---------- -------
XTENDER.F_WITHOUT_DEPS                                                  1579   0 Dependency Published          0          0       0
XTENDER.V_AB                                                            3127   2 Dependency Published          0          0       0
XTENDER.B                                                                778   3 Dependency Published          0          0       0
XTENDER.A                                                                464   4 Dependency Published          0          0       0
select/*+ result_cache */ f_without_deps(:p_tab) result from dual       1749   1 Result     Published          0          0       0

[collapse]

But don’t forget that the result_cache also caches such functions with the objects, that usually should not be cached, and such objects will not be listed in the result_cache dependencies list:
v$database
SQL> exec :p_tab:='v$database';

PL/SQL procedure successfully completed.

SQL> call DBMS_RESULT_CACHE.flush();

Call completed.

SQL> select/*+ result_cache */ f_without_deps(:p_tab) result from dual;

RESULT
----------
v$database

1 row selected.

SQL> select name,bucket_no, id,type,status,pin_count,scan_count,invalidations from v$result_cache_objects o order by type,id;

NAME                                                               BUCKET_NO  ID TYPE       STATUS     PIN_COUNT SCAN_COUNT INVALID
----------------------------------------------------------------- ---------- --- ---------- --------- ---------- ---------- -------
XTENDER.F_WITHOUT_DEPS                                                   772   0 Dependency Published          0          0       0
PUBLIC.V$DATABASE                                                       1363   2 Dependency Published          0          0       0
select/*+ result_cache */ f_without_deps(:p_tab) result from dual       2283   1 Result     Published          0          0       0

3 rows selected.

[collapse]
As you can see, there is only dependency on public synonym V$DATABASE, but not real base fixed X$-tables.
SYS.OBJ$
SQL> exec :p_tab:='sys.obj$';

PL/SQL procedure successfully completed.

SQL> call DBMS_RESULT_CACHE.flush();

Call completed.

SQL> select/*+ result_cache */ f_without_deps(:p_tab) result from dual;

RESULT
----------
sys.obj$

1 row selected.

SQL> select name,bucket_no, id,type,status,pin_count,scan_count,invalidations from v$result_cache_objects o order by type,id;

NAME                                                               BUCKET_NO ID TYPE       STATUS     PIN_COUNT SCAN_COUNT INVALID
----------------------------------------------------------------- ---------- -- ---------- --------- ---------- ---------- -------
XTENDER.F_WITHOUT_DEPS                                                  3922  0 Dependency Published          0          0       0
select/*+ result_cache */ f_without_deps(:p_tab) result from dual       3753  1 Result     Published          0          0       0

2 rows selected.

[collapse]
The results were cached and the dependencies do not include system objects.
We easily check that the queries with any table in SYS schema or with sysdate,systimestamp,current_date,current_timestamp,dbms_random will not be cached:
SYS tables
SQL> select/*+ result_cache */ current_scn result from v$database;

    RESULT
----------
##########

1 row selected.

SQL> select name,bucket_no, id,type,status,pin_count,scan_count,invalidations from v$result_cache_objects o order by type,id;

no rows selected

SQL> explain plan for select/*+ result_cache */ * from sys.obj$;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 2311451600

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 87256 |  7328K|   296   (1)| 00:00:04 |
|   1 |  TABLE ACCESS FULL| OBJ$ | 87256 |  7328K|   296   (1)| 00:00:04 |
--------------------------------------------------------------------------

[collapse]
Note that there is no “RESULT CACHE” line.
And even if create own tables in SYS schema(don’t do it :)), they will not be cached :
SYS.V_AB
SYS> create table a as select 'a' a from dual;
SYS> create table b as select 'b' b from dual;
SYS> create view v_ab as select a,b from a,b;
SYS> grant select on v_ab to xtender;

XTENDER> explain plan for select/*+ result_cache */ * from sys.v_ab;
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Plan hash value: 215283502

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |     6 |     4   (0)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN|      |     1 |     6 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | A    |     1 |     3 |     2   (0)| 00:00:01 |
|   3 |   BUFFER SORT        |      |     1 |     3 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL | B    |     1 |     3 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

[collapse]

But sys_context and userenv will be cached successbully:
sys_context
SQL> explain plan for select/*+ result_cache */ sys_context('userenv','os_user')  from dual;

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 1388734953

---------------------------------------------------------------------------------------
| Id  | Operation        | Name                       | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                            |     1 |     2   (0)| 00:00:01 |
|   1 |  RESULT CACHE    | 267m2hcwj08nq5kwxcb0nb2ka8 |       |            |          |
|   2 |   FAST DUAL      |                            |     1 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 - column-count=1; attributes=(single-row); parameters=(sys_context);
        name="select/*+ result_cache */ sys_context('userenv','os_user')  from dual"

14 rows selected.

[collapse]
userenv
SQL> explain plan for select/*+ result_cache */ userenv('instance')  from dual;

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 1388734953

---------------------------------------------------------------------------------------
| Id  | Operation        | Name                       | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                            |     1 |     2   (0)| 00:00:01 |
|   1 |  RESULT CACHE    | dxzj3fks1sqfy35shbbst4332h |       |            |          |
|   2 |   FAST DUAL      |                            |     1 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 - column-count=1; attributes=(single-row); parameters=(sys_context); 
       name="select/*+ result_cache */ userenv('instance')  from dual"

[collapse]

Categories: Development

A function gets called twice if the result_cache is used

XTended Oracle SQL - Sun, 2015-07-05 08:37

Recently I showed simple example how result_cache works with non-deterministic functions and observed strange behaviour: a function gets fired once in the normal query, but twice with the result_cache hint.
Moreover, only third and subsequent query executions return same cached results as second query execution.
I didn’t want to investigate such behavior, just because 1) we should not cache the results of non-deterministic functions and 2) it doesn’t matter if we use deterministic functions.
But later I was asked to explain this, so this post is just a short description with test case.

Look at the simple function that just returns random values:

create or replace function f_nondeterministic(p int:=100) return int
as
  res number;
begin
   res:=round(dbms_random.value(0,p));
   return res;
end;
SQL> exec dbms_result_cache.flush;
PL/SQL procedure successfully completed.

SQL> select/*+ result_cache */ f_nondeterministic(1000) nondeter from dual;

  NONDETER
----------
       481

SQL> select/*+ result_cache */ f_nondeterministic(1000) nondeter from dual;

  NONDETER
----------
       689

SQL> select/*+ result_cache */ f_nondeterministic(1000) nondeter from dual;

  NONDETER
----------
       689

with result_cache_statistics
SQL> exec dbms_result_cache.flush;

PL/SQL procedure successfully completed.

SQL> select/*+ result_cache */ f_nondeterministic(1000) nondeter from dual;

  NONDETER
----------
       481

SQL> select name,value from v$result_cache_statistics where name in ( 'Create Count Success', 'Find Count');

NAME                                               VALUE
-------------------------------------------------- ----------
Create Count Success                               1
Find Count                                         0

SQL> select/*+ result_cache */ f_nondeterministic(1000) nondeter from dual;

  NONDETER
----------
       689

SQL> select name,value from v$result_cache_statistics where name in ( 'Create Count Success', 'Find Count');

NAME                                               VALUE
-------------------------------------------------- ----------
Create Count Success                               1
Find Count                                         1

SQL> select/*+ result_cache */ f_nondeterministic(1000) nondeter from dual;

  NONDETER
----------
       689

SQL> select name,value from v$result_cache_statistics where name in ( 'Create Count Success', 'Find Count');

NAME                                               VALUE
-------------------------------------------------- ----------
Create Count Success                               1
Find Count                                         2

SQL> select name,bucket_no, id,type,status,pin_count,scan_count,invalidations from v$result_cache_objects o;

NAME                                                BUCKET_NO         ID TYPE       STATUS     PIN_COUNT SCAN_COUNT INVALIDATIONS
-------------------------------------------------- ---------- ---------- ---------- --------- ---------- ---------- -------------
XTENDER.F_NONDETERMINISTIC                                552          0 Dependency Published          0          0             0
select/*+ result_cache */ f_nondeterministic(1000)       2102          1 Result     Published          0          2             0
 nondeter from dual

[collapse]

As you can see, second execution returns different result than first one.
If we change this function:

create or replace function f_nondeterministic(p int:=100) return int
as
  res number;
begin
   res:=round(dbms_random.value(0,p));
   dbms_output.put_line('fired! ('||res||')');
   return res;
end;

and repeat this test-case:

SQL> select/*+ result_cache */ f_nondeterministic(1000) nondeter from dual;

  NONDETER
----------
       943    -- << (2)

1 row selected.

fired! (607)    -- << (1)
fired! (943)    -- << (2)
SQL> /

  NONDETER
----------
       607    -- << (1)

1 row selected.

SQL> /

  NONDETER
----------
       607    -- << (1)

1 row selected.

SQL> /

  NONDETER
----------
       607    -- << (1)

1 row selected.

we will see that there were 2 function executions: first result was cached, and the second was fetched!

Categories: Development

Make even more of UKOUG Tech15: APEX 5.0 UI Training - Dec 10th in Birmingham

Dimitri Gielis - Tue, 2015-06-30 01:48

APEX 5.0 has been released this spring. People who have already spent some time on this new version know this version is packed with new features aimed to make APEX developers even more productive, like the Page Designer.
Another striking new subset of features is aimed at creating better looking user interfaces for your APEX applications in an easy and maintainable way. 
The definition of user interface components in APEX 5.0 is very different to what we're used to. For example there is a new Universal Theme with Template Options and a Theme Roller. To get you up and running with this new toolset as quickly as possible, Dimitri Gielis of APEX R&D and Roel Hartman of APEX Consulting have joined forces and set up a one day course fully aimed at APEX 5.0 UI. So if you want to know not only how to use the new Theme, but also how to modify it to fit your needs, this is the event you should attend!

The training will be at the Jury’s Inn in Birmingham (UK) on Thursday Dec 10 - so conveniently immediately after the UKOUG Tech15 conference.
More information and registration see www.apextraining.eu

If you are from another country and think this training should be available in your country as well, please contact us - then we'll see what we can do!
Categories: Development

SQL*Plus tips #7: How to find the current script directory

XTended Oracle SQL - Fri, 2015-06-26 14:06

You know that if we want to execute another script from the current script directory, we can call it through @@, but sometimes we want to know the current path exactly, for example if we want to spool something into the file in the same directory.
Unfortunately we cannot use “spool @spoolfile”, but it is easy to find this path, because we know that SQL*Plus shows this path in the error when it can’t to find @@filename.

So we can simply get this path from the error text:

rem Simple example how to get path (@@) of the current script.
rem This script will set "cur_path" variable, so we can use &cur_path later.
 
set termout off
spool _cur_path.remove
@@notfound
spool off;
 
var cur_path varchar2(100);
declare 
  v varchar2(100);
  m varchar2(100):='SP2-0310: unable to open file "';
begin v :=rtrim(ltrim( 
                        q'[
                            @_cur_path.remove
                        ]',' '||chr(10)),' '||chr(10));
  v:=substr(v,instr(v,m)+length(m));
  v:=substr(v,1,instr(v,'notfound.')-1);
  :cur_path:=v;
end;
/
set scan off;
ho (rm _cur_path.remove 2>&1  | echo .)
ho (del _cur_path.remove 2>&1 | echo .)
col cur_path new_val cur_path noprint;
select :cur_path cur_path from dual;
set scan on;
set termout on;
 
prompt Current path: &cur_path

I used here the reading file content into variable, that I already showed in the “SQL*Plus tips. #1″.
UPDATE: I’ve replaced this script with a cross platform version.

Also I did it with SED and rtrim+ltrim, because 1) I have sed even on windows; and 2) I’m too lazy to write big PL/SQL script that will support 9i-12c, i.e. without regexp_substr/regexp_replace, etc.
But of course you can rewrite it without depending on sed, if you use windows without cygwin.

PS. Note that “host pwd” returns only directory where SQL*Plus was started, but not executed script directory.

Download latest version

Categories: Development

Quick and Dirty - Refreshing a Select List of Values

Denes Kubicek - Tue, 2015-06-23 06:14
This is a quick and dirty solution but it works. It shows hot to update a select list of values on demand without a lot of coding.

Categories: Development

AJAX Callback and jQuery creating Page Items Dynamically

Denes Kubicek - Tue, 2015-06-23 05:40
This example shows a couple of interesting techniques:
  • 1. How to create and use an AJAX Callback process instead of an Application Process on Demand,
  • 2. how to create a dynamic page item,
  • 3. how to modify the item display settings and reference Font Awsome icon set and finaly,
  • 4. it shows how to call an AJAX process from javascript.
This question is based on a recent forum posting where this question was asked.

Have a look and enjoy.

Categories: Development

Aporias

Greg Pavlik - Sat, 2015-06-20 11:03
1.
Actually
He likes word games
   Diffident
In the way they circle about
   Starlings in flight
   Or Seraphim.

2.
With sweeping gesture
   Left to right
Hands hang with head
Under the pressing of the sun:
   Weight of doubt
   Or will.

3.
Against open air, tumultuous sea
   Turtle green
The division is nowhere more evident
Where sand meets froth
   Stark, blinding glare
Wind balmed
   Until night.

4.
Cleanliness,
   Next to godliness
The echo of countless schoolmarms
Chiding, chilling - without regret
   Yeah, rather,
   Motherhood.

What The World Needs More Of

Greg Pavlik - Fri, 2015-06-19 15:07
The interview with these two kids - Chris and Camryn Singleton - is available on BBC, but I wanted to pull out this remarkable commentary in a related article:

"People are hurting in Charleston. But for the hundreds who packed into the gymnasium at the Goose Creek High School, it was also a reminder of the importance of love.

Sharonda Singleton coached the girls' athletics team here. As her photo rested on an easel on the polished floors in the vast sports hall, her friends and family paid tribute. Speaking for the first time since the deadly attack on the AME church where she worshipped, Sharonda's two children, Chris and Camryn, told me they forgive the man who killed her.

 "We already forgive him and there's nothing but love from our side of the family," Chris told me.

Many will find this incomprehensible. Charleston is often called the Holy City for the number of churches it is home to, and the role religion plays here. For some, like Chris and Camryn, unwavering faith is the only way to turn such a devastating loss into something positive."

This immediately brought to mind the sayings of Fr Zosima in Dostoevsky's Brothers Karamazov, which not to many years ago were the source of a kind of epiphany for me that in a sense reoriented by own thinking:

"Strive to love your neighbor actively and indefatigably. In as far as you advance in love you will grow surer of the reality of God and of the immort
ality of your soul. If you attain to perfect self-forgetfulness in the love of your neighbor, then you will believe without doubt, and no doubt can possibly enter your soul. This has been tried. This is certain.

Above all, avoid falsehood, every kind of falsehood, especially falseness to yourself. Watch over your own deceitfulness and look into it every hour, every minute. Avoid being scornful, both to others and to yourself. What seems to you bad within you will grow purer from the very fact of your observing it in yourself. Avoid fear, too, though fear is only the consequence of every sort of falsehood. Never be frightened at your own faint-heartedness in attaining love. Don't be frightened overmuch even at your evil actions. I am sorry I can say nothing more consoling to you, for love in action is a harsh and dreadful thing compared with love in dreams. Love in dreams is greedy for immediate action, rapidly performed and in the sight of all. Men will even give their lives if only the ordeal does not last long but is soon over, with all looking on and applauding as though on the stage. But active love is labor and fortitude, and for some people too, perhaps, a complete science. But I predict that just when you see with horror that in spite of all your efforts you are getting farther from your goal instead of nearer to it—at that very moment I predict that you will reach it and behold clearly the miraculous power of the Lord who has been all the time loving and mysteriously guiding you."

....


"At some thoughts one stands perplexed, especially at the sight of men's sin, and wonders whether one should use force or humble love. Always decide to use humble love. If you resolve on that once for all, you may subdue the whole world. Loving humility is marvelously strong, the strongest of all things, and there is nothing else like it." 

....

"“Remember particularly that you cannot be a judge of anyone. For no one can judge a criminal until he recognizes that he is just such a criminal as the man standing before him, and that he perhaps is more than all men to blame for that crime. When he understands that, he will be able to be a judge. Though that sounds absurd, it is true. If I had been righteous myself, perhaps there would have been no criminal standing before me. If you can take upon yourself the crime of the criminal your heart is judging, take it at once, suffer for him yourself, and let him go without reproach. And even if the law itself makes you his judge, act in the same spirit so far as possible, for he will go away and condemn himself more bitterly than you have done. If, after your kiss, he goes away untouched, mocking at you, do not let that be a stumbling-block to you. It shows his time has not yet come, but it will come in due course. And if it come not, no matter; if not he, then another in his place will understand and suffer, and judge and condemn himself, and the truth will be fulfilled. Believe that, believe it without doubt; for in that lies all the hope and faith of the saints.”


This time, Chris and Camryn have moved me beyond words by living this reality.

Addendum/edit: more of this humbling love on display

A natural born tree right in my own backyard!

FeuerThoughts - Wed, 2015-06-17 07:33
As some of my readers may have noticed, I spend a lot of time these days among trees, paying attention to trees, cutting back invasive trees to save native trees, etc.

And one thing that I came to realize is that at least in an area like Chicagoland, humans tightly control the reproduction of trees. 

I live on a lovely tree-lined street. Big trees - 100 ft tall or more. Maples, oaks, ash....but there are no baby trees, except for smallish trees that the city plants when they have to remove diseased trees (such as all the ash trees, under assault from ash borers).

It makes me sad to think of how impoverished my immediate surroundings are, how unnatural. We don't even let trees - majestic living things that make our lives possible, that live through many of our own generations - live out natural life cycles. 

In fact, I have come to accept that trees planted singly along streets to enhance our lives are really just ornaments. If "a man is not an island" then certainly a tree is not a forest. And very few trees live naturally outside of forests of many, many trees.

Well, enough of sadness. Veva and I were sitting on our patio last week, enjoying the (finally) warm weather and our lovely garden (thanks to Veva), when she pointed out something truly wonderful:


Can you see it? We planted the birch trees years ago. They are now 40 feet tall, but nestled in between? A natural born baby birch tree! Can't see it? Here maybe this will help:


I feel so much better now. The (minimal) wildness of our garden (as in: no grass) made it possible for a birch seed to take hold and grow. A tree that humans did not plant and hopefully will allow to grow to maturity.

Humans love to debate things like "Do plants feel?" Of course, it is terribly difficult for us to imagine such a thing - because the way that plants would think and feel would be so different from us. So we will likely never really be able to answer the question.

Which means it would make a lot more sense to err on the side of caution and assume that trees and plants and creatures do feel, do think in their own way, do take joy in life.

And watching this natural born tree grow, it is certainly easy to believe that it is joyful. I sure am.



Categories: Development

APEX Meetup Frankfurt

Denes Kubicek - Sun, 2015-06-14 04:47
Am 26.06.2015 17.00 treffen wir uns in Frankfurt a. M. bei einem weiteren APEX Meetup. Danke an Sabine Heimsath und Moritz Klein für die Organisation. Ich zeige dort, wie man lokal eine XE Datenbank zusammen mit ORDS und Glassfish konfigurieren kann. Danach kann sehr leicht jede beliebige Version von APEX nachinstalliert werden. Der Vorteil ist, ich kann alle Features einsetzen (RESTful Services, XLS Upload) und der Austausch vo Images bzw. Konfiguration für die neue Version von APEX sind kein Problem mehr.

Die Adresse ist:

Ericsson Telekommunikation GmbH
Herriotstr. 1
Frankfurt

Our next meetup is in Frankfurt on 26th of June. Thanks to Sabine Heimsath and Moritz Klein we will meet at Ericsson Telekommunikation GmbH Herriotstr. 1 Frankfurt. I will demonstrate how to install XE with ORDS and Glassfish and how to upgrade to APEX 5.0 on a local virtual machine.

Categories: Development

EM12c 12.1.0.4 Summary of Installation Steps

Arun Bavera - Fri, 2015-06-12 10:08
1. Install EM12c using Advanced without choosing the “Download Update”

2. Install Latest JDK 1.6 (Note: 1944044.1) JDK 1.6.0.95.. Updated to 1.6.0.95

3. Install Latest PSU for EM12cR4 Note: 1995267.1

4. Install latest Plugins

5. Install Monthly Plugin patches for OMS (Doc ID 1900943.1 & Doc ID 1664074.1)

6. Install Monthly Plugin patches for Agent (Doc ID 1900943.1 & Doc ID 1664074.1)

7. Install WebLogic latest PSU (1470197.1)

8. Configure Load Balancer

9. Configure additional OMS

10. Configure LDAP

11. Configure External Roles for AD/LDAP

12. Import Templates and Reports if you have

13. Resize OMS Heap according to need

14. Install & Configure JVMD Servers

15. Install & Configure BI Publisher

16. Brand your login page with custom logos and Cloud login
 
Reference:
http://docs.oracle.com/cd/E24628_01/install.121/e22624/toc.htm
http://docs.oracle.com/cd/E24628_01/install.121/e24089/toc.htm
Get ready DB12c as per Note: 12c Database has been certified as a 12cR4 Repository with Certain Patchset Restrictions (Doc ID 1987905.1)
Latest plug-ins can be downloaded from :
http://www.oracle.com/technetwork/oem/grid-control/downloads/oem-upgrade-console-502238.html

















Categories: Development

Leveraging Oracle Developer Cloud Service in SQL and PL/SQL Projects - lifecycle and team collaboration

Shay Shmeltzer - Tue, 2015-05-26 12:37

Usually my demos are targeted at Java developers, but I realize that a lot of developers out there are not using Java, for example in the Oracle install base there is a huge section of PLSQL developers. This however doesn't change their requirements from a development platform. They can still benefit from version management and code review functionality. They still need to track bugs/issues and requirements from their users, and they still need to collaborate in a team environment. 

So I decided to try out and see what would be the development lifecycle experience for a PL/SQL developer if they'll leverage the services provided by the Oracle Developer Cloud Service - here is a demo that shows a potential experience. 

What you'll see in the demo:

  • Using JDeveloper to create DB Diagrams, Tables and PL/SQL code
  • Version manage PL/SQL and SQL with Git
  • Defining a cloud project and adding users
  • Check code in, and branch PL/SQL functions
  • Tracking tasks for developers
  • Code review by team members
  • Build automation (with Ant) - and almost a deploy to the DB

As you can see it is quite a nice complete solution that is very quick to setup and use.

It seems that the concepts of continuous integration in the world of PL/SQL development are not yet a common thing. In the demo I use the Ant SQL command to show how you could run a SQL script you created to create the objects directly in the database - which is probably the equivalent of doing a deployment in the world of Java. However if you prefer you can use Ant for example to copy files, zip them, or do many other tasks such as run automated testing frameworks.

The Ant task I used is this:

  <path id="antclasspath">
    <fileset dir=".">
      <include name="ojdbc7.jar"/>
    </fileset>
  </path>
   <target name="deploy">
    <sql driver="oracle.jdbc.OracleDriver" userid="hr2" password="hr"
         url="jdbc:oracle:thin:@//server:1521/sid" src="./script1.sql" 
	classpathref="antclasspath"/>
  </target> 

I had both the ojdbc7.jar file and the script file at the root of the project for convenience. 

While my demo uses JDeveloper - you should be able to achieve similar functionality with any tool that supports Git. In fact if you rather not use a tool you can simply use command lines to check your files directly into the cloud.

Categories: Development

Fixing Super LOV in Universal Theme

Dimitri Gielis - Thu, 2015-05-21 15:08
When you migrate to APEX 5.0 and the Universal Theme you might see that some plugins are not behaving correctly anymore. In this post I'll discuss the Enkitec Modal LOV plugin.

When I ran the plugin in my APEX 5.0 app with the Universal Theme it looked like this:


There's too much space in the search bar and the close button is not showing up with an icon.

Here're the steps I did to fix it. First you need to download the css file of the style you use and the js file from the plugin in Shared Components. I use the smoothness.css style most of the times, so I'll use that as an example.

To fix the close icon, add !important to the png:

.ek-ml .ui-state-default .ui-icon {
background-image: url(ui-icons_888888_256x240.png) !important;
}

Note: you can do that for all those png on line 467 till 489.

To fix the height, add following css to smoothness.css:

.superlov-button-container {
  height:50px;
}

And finally in enkitec_modal_lov.min.js change the height of the searchContainer from a dynamic height (r) to 0px:

$searchContainer.css("height","0px")

Next upload those files again to the plugin.

When you run the plugin it should give you this result:


Now the bigger question is; do we still need that plugin? In APEX 5.0 there're native Modal Pages, so you could create an Interactive Report and set the page as a Modal Page. Next you can hook that up to a button or link and you've just build your own Modal LOV.

I still like to use the plugin at the moment (as it's just one item on the page), but it could use a refresh to make it look nicer and more inline with Universal Theme.

Wonder what you think - would you build your own Modal LOV in APEX 5.0 or would you still prefer to use a plugin? 

Categories: Development

2 minute Tech Tip: Working with JSON in APEX

Dimitri Gielis - Tue, 2015-05-19 17:30
On Monday Bob Rhubart did a video call with me in his series of 2MTT (2 Minute Tech Tip) on YouTube. You find my 2MMT here.

I talked about using JSON and APEX and gave two examples were we use it.

In previous blog posts I gave more details on those techniques. Here's a quick overview:
Categories: Development

Pages

Subscribe to Oracle FAQ aggregator - Development