Development

Script to get OMS ,Agent and DB Plugin Patch details

Arun Bavera - Sat, 2017-02-18 18:17

 

GETTING OMS  PATCH DETAILS

export OMS_HOME=/u01/middleware/oms
export ORACLE_HOME=$OMS_HOME
export PLUGIN_HOME=/u01/middleware/plugins/oracle.sysman.db.oms.plugin_12.1.0.8.0/

DT=$(date '+%m%d%Y')

LOG=/tmp/EM_Patch_details_${DT}.txt

echo "1.Getting the OMS system patches" >${LOG}
echo "=====================================================================================" >>${LOG}

$ORACLE_HOME/OPatch/opatch lsinventory -details >>${LOG}

echo "" >>${LOG}
echo "======================================================================================" >>${LOG}
echo "2.Getting the OMS DB PLUGIN patches" >>${LOG}
echo "======================================================================================" >>${LOG}
$ORACLE_HOME/OPatch/opatch lsinventory -oh $PLUGIN_HOME -details >>${LOG}

 

GETTING AGENT PATCH DETAILS

export AGENT_BASE=/u01/app/oracle/product/agent12c
export AGENT_HOME=${AGENT_BASE}/core/12.1.0.5.0
export ORACLE_HOME=${AGENT_HOME}
export PLUGIN_HOME=${AGENT_BASE}/plugins/oracle.sysman.db.agent.plugin_12.1.0.8.0/

DT=$(date '+%m%d%Y')

LOG=/tmp/AGENT_Patch_details_${DT}.txt

echo "1.Getting the AGENT system patches" >${LOG}
echo "=====================================================================================" >>${LOG}

$ORACLE_HOME/OPatch/opatch lsinventory -details >>${LOG}

echo "" >>${LOG}
echo "======================================================================================" >>${LOG}
echo "2.Getting the AGENT DB PLUGIN patches" >>${LOG}
echo "======================================================================================" >>${LOG}
$ORACLE_HOME/OPatch/opatch lsinventory -oh $PLUGIN_HOME -details >>${LOG}

Categories: Development

Coming Soon: Database Star Academy Membership

Complete IT Professional - Fri, 2017-02-17 05:00
The Database Star Academy membership will be launching very soon. Read on to find out more. What Is The Database Star Academy Membership? Over the last couple of years, I’ve developed several video courses and PDF guides to help database developers improve their SQL skills and their career. Up until now, I’ve made these available […]
Categories: Development

Weekly Link Roundup – Feb 17, 2017

Complete IT Professional - Fri, 2017-02-17 00:39
Here’s a collection of interesting articles I’ve read this week. Articles I’ve Read Truncate 12c https://jonathanlewis.wordpress.com/2017/02/16/truncate-12c/ Jonathan Lewis writes an article about some improvements to the TRUNCATE statement in Oracle 12c, and how it relates to “on delete cascade”. THere’s a small example and a good explanation on how it works.   Step by Step […]
Categories: Development

Comma separated search and search with checkboxes in Oracle APEX

Dimitri Gielis - Thu, 2017-02-16 16:30

When you have a classic report in Oracle Application Express (APEX) and want to make it searchable you typically add a Text Item in the region, enable Submit on Enter and add a WHERE clause to your SQL statement.

Here’s an example:

Classic Report with Search (text item)

Your SQL statement probably looks like this:

select CUSTOMER_ID,
CUST_FIRST_NAME,
CUST_LAST_NAME,
CUST_STREET_ADDRESS1,
CUST_CITY,
CUST_STATE,
CUST_POSTAL_CODE,
CUST_EMAIL,
CREDIT_LIMIT
from DEMO_CUSTOMERS
where CUSTOMER_ID = :P4_SEARCH

When you want to search for multiple customers separated by a comma, how do you do that?
So in my search field I add for example: 1,2,3 and expect to see 3 customers.

There’re a couple of options you have, I’ll list three below:

  1. INSTR

    where INSTR(','||:P4_SEARCH||',', ',' || CUSTOMER_ID || ',') > 0
  2. REGEXP_LIKE

    where REGEXP_LIKE(CUSTOMER_ID, '^('|| REPLACE(:P4_SEARCH,',','|') ||')$')
  3. REGEXP_SUBSTR

    where customer_id in to_number((
    select regexp_substr(:P4_SEARCH,'[^,]+', 1, level)
    from dual
    connect by regexp_substr(:P4_SEARCH, '[^,]+', 1, level) is not null
    ))

Which one to choose? It depends what you need… if you need readability, maybe you find INSTR easier to understand. If you need performance, maybe the last option is the better choice… so as always it depends. If you want to measure the performance you can look at the Explain Plan (just copy the SQL in SQL Workshop and hit the Explain tab).

The Explain Plan for the first SQL looks like this:

Explain Plan INSTR

The Explain Plan for the last SQL looks like this:

Explain Plan REGEXP_SUBSTR

The above technique is also useful when you use want checkboxes above your report, so people can make a selection. For example we select the customers we want to see:

Classic Report with checkbox selection

The where clause would be identical, but instead of a comma (,) you would use a colon (:), so the first statement would be:

where INSTR(':'||:P4_SEARCH||':', ':' || CUSTOMER_ID || ':') > 0

Happy searching your Classic Report :)

Categories: Development

ORA-00936: missing expression Solution

Complete IT Professional - Wed, 2017-02-15 05:00
Did you get an ORA-00936: missing expression error? Learn what it means and how to resolve it in this article. ORA-00936 Cause The error you’ve gotten is this: ORA_00936: missing expression Oracle’s official “cause and action” that appears along with the error is: Cause: A required part of a clause or expression has been omitted. […]
Categories: Development

Where do you specify the Date Format Mask

Dimitri Gielis - Mon, 2017-02-13 10:32

When reviewing Oracle APEX applications I often see hardcoded date or timestamp formats.
You can define your date formats in multiple places in your application. In your item or column attributes, as part of your code e.g.TO_CHAR(sysdate, ‘DD-MON-YYYY HH24:MI’) or if you want to make it more reusable you might create a substitution string. That will all work, but you can make your life easier and for the ones looking or needing to maintain your code…

APEX itself provides in the Globalization attributes (Shared Components) a place where you can define your default date and format masks for your entire applications. I consider this a best practice to use those fields, as it’s defined in one logical place, so when you need to change your format, you do it once and you’re done. In your custom SQL and PL/SQL code you can also reference those format masks by predefined substitution strings:

  • APP_NLS_DATE_FORMAT
  • APP_DATE_TIME_FORMAT
  • APP_NLS_TIMESTAMP_FORMAT
  • APP_NLS_TIMESTAMP_TZ_FORMAT

e.g. TO_CHAR(sysdate, :APP_NLS_DATE_FORMAT)

Here’s a screenshot which shows which substitution string corresponds with which field:

Application Attributes - Globalization

You can define the format mask you want, or you can click the arrow to see most used format masks represented with an example. To make it a bit easier, I put the format mask (in red) next to it, so you see the underlying format mask more easily:

Possible date format masks defined in the pop-up

If you need to make the format mask dynamic, for example using different format masks for different language, APEX doesn’t allow you to translate that substitution string through Text Messages, but you can work around it by using your own substitution string and have that dynamically filled. In the Globalization Attributes you would add instead of a hardcoded format mask your own substitution string e.g. &MY_TRANSLATED_DATE.FORMAT.

Categories: Development

UNION vs UNION ALL: What’s The Difference?

Complete IT Professional - Mon, 2017-02-13 05:00
What’s the difference between UNION and UNION ALL in Oracle SQL? There are a few. Learn what they are in this article. What Is UNION and UNION ALL? First of all, let’s explain what they are. UNION and UNION ALL are both “set operators”. They are keywords you can use to combine two sets of […]
Categories: Development

row pieces, 255 columns, intra-block row chaining in details

XTended Oracle SQL - Sat, 2017-02-11 20:54

You may know about Intra-block row chaining which may occur when the number of columns in a table are more than 255 columns.
But do you know that intra-block chaining works with inserts only? not updates!

Documentation says:

When a table has more than 255 columns, rows that have data after the 255th column are likely to be chained within the same block. This is called intra-block chaining. A chained row’s pieces are chained together using the rowids of the pieces. With intra-block chaining, users receive all the data in the same block. If the row fits in the block, users do not see an effect in I/O performance, because no extra I/O operation is required to retrieve the rest of the row.

A bit more details:
1. One row piece can store up to 255 columns
2. Oracle splits fields by row pieces in reverse order
3. Oracle doesn’t store trailing null fields in a row (not in row piece)
4. Next row piece can be stored in the same block only with inserts. When you run update, oracle will place new row piece into another block.

I’ll show in examples with dumps:

Example 1:
1. Create table TEST with 355 columns (c_1, c_2, …, c_355)
2. insert into test(c_300) values(2)
3. dump data blocks

test code

drop table test purge;
set serverout on;
alter session set tracefile_identifier='test1';
declare
   c varchar2(32000);
   v varchar2(32000);
   cols varchar2(32000):='c_1 number(1,0)';
   vals varchar2(32000):='null';
   
   ndf int;
   nbl int;
begin
   for i in 2..355 loop
      cols:=cols||',c_'||i||' number(1,0)';
      vals:=vals||','||case 
                         when i = 300 then '2'
                         else 'null'
                       end;
   end loop;
   c:='create table test('||cols||')';
   v:='insert into test values('||vals||')';
   dbms_output.put_line(c);
   dbms_output.put_line(v);
   execute immediate (c);
   execute immediate (v);
   
   for r in (select 'alter system dump datafile '||file#||' block '||block# cmd 
             from (
                   select distinct file#,block# 
                   from v$bh 
                   where class#=1 
                   and objd in (select o.data_object_id from user_objects o where object_name='TEST') 
                   order by 1,2
                  )
            )
   loop 
       execute immediate r.cmd;
   end loop;
end;
/
disc;

[collapse]
grep -P “^(bdba|block_row_dump|tl: |col )” test1.trc

bdba: 0x018019f3
block_row_dump:

bdba: 0x018019f4
block_row_dump:

bdba: 0x018019f5
block_row_dump:

bdba: 0x018019f6
block_row_dump:

bdba: 0x018019f7
    block_row_dump:
        tl: 260 fb: -----L-- lb: 0x1  cc: 255
            col  0: *NULL*
            col  1: *NULL*
            col  2: *NULL*
            col  3: *NULL*
               ...
            col 252: *NULL*
            col 253: *NULL*
            col 254: [ 2]  c1 03
        tl: 54 fb: --H-F--- lb: 0x1  cc: 45
            col  0: *NULL*
            col  1: *NULL*
            col  2: *NULL*
            col  3: *NULL*
               ...
            col 42: *NULL*
            col 43: *NULL*
            col 44: *NULL*

The resulted dump file shows us:
1. Both row pieces are in the same block 0x018019f4
2. They contain only first 300 columns, (trailing 55 columns are NULLs)
3. First row piece contains columns c_46 – c_300,
4. Second row piece contains columns c_1 – c_45 (they all are NULLs)

Example 2.
But let’s test an update with the same table:
1. Create table TEST with 355 columns (c_1, c_2, …, c_355)
2. insert into test(c_1) values(null)
3. update test set c_300=2
4. dump data blocks

Test code 2

drop table test purge;
set serverout on;
alter session set tracefile_identifier='test2';
declare
   c varchar2(32000);
   cols varchar2(32000):='c_1 number(1,0)';
begin
   for i in 2..355 loop
      cols:=cols||',c_'||i||' number(1,0)';
   end loop;
   c:='create table test('||cols||')';
   execute immediate (c);
   execute immediate ('insert into test(c_1) values(null)');
   execute immediate 'update test set c_300=3';
   commit;
   for r in (select 'alter system dump datafile '||file#||' block '||block# cmd 
             from (
                   select distinct file#,block# 
                   from v$bh 
                   where class#=1 
                   and objd in (select o.data_object_id from user_objects o where object_name='TEST') 
                   order by 1,2
                  )
            )
   loop 
       execute immediate r.cmd;
   end loop;
end;
/
disc;

[collapse]
Dump:

bdba: 0x018019f3
    block_row_dump:
        tl: 260 fb: -----L-- lb: 0x1  cc: 255
            col  0: *NULL*
            col  1: *NULL*
            col  2: *NULL*
               ...
            col 251: *NULL*
            col 252: *NULL*
            col 253: *NULL*
            col 254: [ 2]  c1 04

bdba: 0x018019f4
block_row_dump:

bdba: 0x018019f5
block_row_dump:

bdba: 0x018019f6
block_row_dump:

bdba: 0x018019f7
    block_row_dump:
        tl: 54 fb: --H-F--- lb: 0x1  cc: 45
            col  0: *NULL*
            col  1: *NULL*
            col  2: *NULL*
               ...
            col 42: *NULL*
            col 43: *NULL*
            col 44: *NULL*

As you can see, there is no intra-block chaining – second row piece was created in another block.

Example 3.
Now I want to show how oracle splits already chained rows:
1. Create table TEST with 355 columns (c_1, c_2, …, c_355)
2. insert into test(c_1) values(1)
3. update test set c_300=2
4. update test set c_301=3
5. update test set c_302=4
6. dump data blocks

Test code 3

drop table test purge;
set serverout on
alter session set tracefile_identifier='test3';

declare
   cols varchar2(32000):='c_1 number(1,0)';
   
   procedure print_and_exec(c varchar2) as
   begin
      dbms_output.put_line(c);
      execute immediate c;
   end;
begin
   for i in 2..355 loop
      cols:=cols||',c_'||i||' number(1,0)';
   end loop;
   print_and_exec ('create table test('||cols||')');
   print_and_exec ('insert into test(c_1) values(1)');
   print_and_exec ('update test set c_300=2');
   print_and_exec ('update test set c_301=3');
   print_and_exec ('update test set c_302=4');
   commit;
   execute immediate 'alter system flush buffer_cache';
   execute immediate 'select count(*) from test';
   for r in (select 'alter system dump datafile '||file#||' block '||block# cmd 
             from (
                   select distinct file#,block# 
                   from v$bh 
                   where class#=1 
                   and objd in (select o.data_object_id from user_objects o where object_name='TEST') 
                   order by 1,2
                  )
            )
   loop 
       execute immediate r.cmd;
   end loop;
end;
/
disc;

[collapse]

bdba: 0x018019f3
    block_row_dump:
        tl: 10 fb: -------- lb: 0x1  cc: 1
            col  0: *NULL*

bdba: 0x018019f4
    block_row_dump:
        tl: 264 fb: -----L-- lb: 0x1  cc: 255
            col  0: *NULL*
            col  1: *NULL*
               ...
            col 249: *NULL*
            col 250: *NULL*
            col 251: *NULL*
            col 252: [ 2]  c1 03
            col 253: [ 2]  c1 04
            col 254: [ 2]  c1 05

bdba: 0x018019f5
block_row_dump:

bdba: 0x018019f6
    block_row_dump:
        tl: 10 fb: -------- lb: 0x1  cc: 1
            col  0: *NULL*

bdba: 0x018019f7
    block_row_dump:
        tl: 56 fb: --H-F--- lb: 0x1  cc: 45
            col  0: [ 2]  c1 02
            col  1: *NULL*
            col  2: *NULL*
            col  3: *NULL*
               ...
            col 42: *NULL*
            col 43: *NULL*
            col 44: *NULL*

This dump shows us 4 row pieces: First row piece contains 255 columns, second – 45, and 2 row pieces – just by one row.
So we can analyze it step-by-step:
2. insert into test(c_1) values(1)
After insert we have just one row piece with 1 field.

3. update test set c_300=2
After this update, we have 2 row pieces:
1) c_1-c_45
2) c_46-c_300

4. update test set c_301=3
This update split row piece c_46-c_300 into 2 row pieces:
1) c_46
2) c_47-c_301
So we have 3 row pieces now: c_1-c_45, c_46, c_47-c_301

5. update test set c_302=4
This update split row piece c_47-c_301 into 2 row pieces:
1) c_47
2) c_48-c_302
And we’ve got 4 row pieces: c_1-c_45, c_46, c_47, c_48-c_302

You can try Example 4 and see how many blocks you can get, and all of them (except last one) will have only 1 column each:

Test code 4

drop table test purge;
set serverout on
alter session set tracefile_identifier='test4';

declare
   cols varchar2(32000):='c_1 char(3)';
   
   procedure print_and_exec(c varchar2) as
   begin
      dbms_output.put_line(c);
      execute immediate c;
   end;
begin
   for i in 2..355 loop
      cols:=cols||',c_'||i||' char(3)';
   end loop;
   print_and_exec ('create table test('||cols||')');
   print_and_exec ('insert into test(c_1) values(null)');
   commit;
   for i in 256..355 loop
      execute immediate 'update test set c_'||i||'='||i;
   end loop;
   commit;
   execute immediate 'alter system flush buffer_cache';
   for r in (select 'alter system dump datafile '||file#||' block '||block# cmd 
             from (
                   select distinct file#,block# 
                   from v$bh 
                   where class#=1 
                   and objd in (select o.data_object_id from user_objects o where object_name='TEST') 
                   order by 1,2
                  )
            )
   loop 
       execute immediate r.cmd;
   end loop;
end;
/
disc;

[collapse]

grep

bdba: 0x01801281
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801282
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801283
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801284
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801285
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801286
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801287
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801288
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801289
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x0180128a
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x0180128b
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x0180128c
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x0180128d
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x0180128e
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x0180128f
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801291
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801292
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801293
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801294
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801295
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801296
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801297
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801298
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801299
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x0180129a
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x0180129b
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x0180129c
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x0180129d
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x0180129e
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x0180129f
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012a1
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012a2
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012a3
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012a4
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012a5
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012a6
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012a7
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012a8
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012a9
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012aa
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012ab
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012ac
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012ad
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012ae
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012af
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012b1
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012b2
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012b3
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012b4
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012b5
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012b6
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012b7
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012b8
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012b9
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012ba
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012bb
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012bc
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012bd
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012be
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012bf
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012c1
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012c2
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012c3
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012c4
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012c5
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012c6
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012c7
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012c8
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012c9
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012ca
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012cb
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012cc
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012cd
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012ce
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012cf
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012d1
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012d2
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012d3
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012d4
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012d5
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012d6
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012d7
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012d8
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012d9
block_row_dump:
bdba: 0x018012da
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012db
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012dc
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012dd
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012de
block_row_dump:
tl: 558 fb: -----L-- lb: 0x1  cc: 255
col  0: *NULL*
col  1: *NULL*
col  2: *NULL*
   ...
col 152: *NULL*
col 153: *NULL*
col 154: *NULL*
col 155: [ 3]  32 35 36
col 156: [ 3]  32 35 37
col 157: [ 3]  32 35 38
col 158: [ 3]  32 35 39
  ...
col 251: [ 3]  33 35 32
col 252: [ 3]  33 35 33
col 253: [ 3]  33 35 34
col 254: [ 3]  33 35 35
bdba: 0x018012df
block_row_dump:
bdba: 0x018019f3
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018019f4
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018019f5
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018019f6
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018019f7
block_row_dump:
tl: 10 fb: --H-F--- lb: 0x2  cc: 1
col  0: *NULL*
bdba: 0x018019f8
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018019f9
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018019fa
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018019fb
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018019fc
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018019fd
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018019fe
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018019ff
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*

[collapse]

Categories: Development

Automating DevOps for the Oracle Database with Developer Cloud Service and SQLcl

Shay Shmeltzer - Fri, 2017-02-10 13:58

In the previous blog entry I showed how you can leverage Oracle Developer Cloud Service (DevCS) to manage the lifecycle of your database creation scripts (version management, branching, code reviews etc).

But how do you make sure that your actual database is in synch with the changes that you make in your scripts?

This is another place where DevCS can come to the rescue with the built-in continuous integration functionality it provides. Specifically with the new features for database integration including secure DB connection specification, and leveraging the powerful SQL Command Line (SQLcl) - the new command line interface to the Oracle DB - which is built-in in the DevCS build servers.

In the video below I go through a process where a check-in of SQL script code change automatically initiate a build process that modifies a running database.

A few points to note:

  • For the sake of simplicity, the demo doesn't follow the recommended step of a code review before merging changes into the master branch (you can see how to do that here).
  • The demo automates running the build whenever a change to the scripts is done. You could also define a scenario where the build runs at a specific time every day - for example at 1am - and synch the DB to today's scripts.
  • You can further extend the scenario shown here of dropping and re-creating objects to add steps to populate the DB with new data and even run tests on the new database.

As you can see Developer Cloud Service can be a very powerful engine for your database DevOps - and it is included for free with your Oracle Database Cloud Services - so give it a try

DB Build

Categories: Development

Automating DevOps for the Oracle Database with Developer Cloud Service and SQLcl

Shay Shmeltzer - Fri, 2017-02-10 13:58

In the previous blog entry I showed how you can leverage Oracle Developer Cloud Service (DevCS) to manage the lifecycle of your database creation scripts (version management, branching, code reviews etc).

But how do you make sure that your actual database is in synch with the changes that you make in your scripts?

This is another place where DevCS can come to the rescue with the built-in continuous integration functionality it provides. Specifically with the new features for database integration including secure DB connection specification, and leveraging the powerful SQL Command Line (SQLcl) - the new command line interface to the Oracle DB - which is built-in in the DevCS build servers.

In the video below I go through a process where a check-in of SQL script code change automatically initiate a build process that modifies a running database.

A few points to note:

  • For the sake of simplicity, the demo doesn't follow the recommended step of a code review before merging changes into the master branch (you can see how to do that here).
  • The demo automates running the build whenever a change to the scripts is done. You could also define a scenario where the build runs at a specific time every day - for example at 1am - and synch the DB to today's scripts.
  • You can further extend the scenario shown here of dropping and re-creating objects to add steps to populate the DB with new data and even run tests on the new database.

As you can see Developer Cloud Service can be a very powerful engine for your database DevOps - and it is included for free with your Oracle Database Cloud Services - so give it a try

DB Build

Categories: Development

Weekly Link Roundup – Feb 10, 2017

Complete IT Professional - Fri, 2017-02-10 12:36
Here’s a collection of interesting articles I’ve read this week Articles I’ve Read DBA Productivity and Oracle Database 12.2 https://juliandontcheff.wordpress.com/2017/02/09/dba-productivity-and-oracle-database-12-2/ Julian writes about automating the tasks a DBA does, and shows a chart demonstrating which tasks are performed manually vs automated. He also covers some of the new automation features for DBAs in releace 12cR2. […]
Categories: Development

ORA-00911 invalid character Solution

Complete IT Professional - Fri, 2017-02-10 05:00
Are you getting an “ORA-00911 invalid character” error when running an SQL statement? Find out what causes it and how to resolve it in this article. ORA-00911 Cause So, you’ve tried to run an SQL statement, such as INSERT or SELECT, and gotten this error: ORA-00911: invalid character Why did this happen? According to the […]
Categories: Development

How to Upgrade an Oracle-based Application without Downtime

Gerger Consulting - Wed, 2017-02-08 11:32
One of most common reasons IT departments avoid database development is the belief that an application upgrade in the database causes downtime. However, nothing can be further from the truth. On the contrary, Oracle Database provides one of the most bullet proof ways to upgrade an application without any downtime: Edition Based Redefinition (EBR)

EBR is a powerful and fascinating feature of Oracle (added in version 11.2), that enables application upgrades with zero downtime, while the application is actively used and operational.

Attend the free webinar by Oren Nakdimon on February 16th to learn how to use EBR, see many live examples, and get tips from real-life experience in a production site using EBR extensively.

The webinar is free but space is limited.

Sign up for the free webinar.


Categories: Development

ORA-06512 At Line Solution

Complete IT Professional - Wed, 2017-02-08 05:00
Did you get an ORA-06512 error when running an SQL query? Learn what this error is and how to resolve it in this article. ORA-06512 Cause The error message you get will look similar to this: ORA-06512: at line n. Where n is a line number. This error message is a generic PL/SQL error message […]
Categories: Development

Changing the label of an item in Oracle APEX dynamically

Dimitri Gielis - Tue, 2017-02-07 16:37

Today I got the question how to change the label of an item in Oracle Application Express (APEX) based on some condition. I actually had this requirement myself a couple of times, so maybe other people too.

Here’s an example; whenever we change the Source item, we want the Affected Item to change it’s label:

 after change of the source item, the label of the affected item changes

The first thing that comes to mind (if you already know a little bit of APEX); lets use a Dynamic Action: on change of the Source item we will fire (in this example we will only fire when the value is A):

Dynamic Action in APEX

Now which action should we use when the dynamic action fires?

Default possibility of actions

Set Value will typically set the value of an Item, but what about the Label?
If I don’t find the option, I typically look for a plugin or write some code myself. In this case I wrote a bit of JavaScript, for example:

var newLabel = 'My new label for ' + $v('P2_SOURCE_ITEM');
$('#'+$(this.affectedElements).attr('id')+'_LABEL').html(newLabel);

This will set the label to "My new label for " and then the value of the item, at least if you select in the Affected Elements the item that needs the label change.

Whenever I think about writing custom code, my mind says “you should create a plugin for that”.
So I actually started to write an Oracle APEX Plug-in called “Set Label” (https://github.com/dgielis/orclapex-plugin-set-label)

While I was trying the plugin and writing up the things I needed to do, I guess something happend in my mind. I missed the obvious, it suddenly came to my mind there’s a much simpler solution to this…

You can actually use the Set Value action… just add after your item _LABEL, that’s it.

Use the Set Value dynamic action but add _LABEL to change the label of the item

Here’s the result:

enter image description here

Sometimes developing is much more simple than initially thought, you just have to see it :)

Categories: Development

Difference Between CHAR, VARCHAR, and VARCHAR2 Data Types in Oracle

Complete IT Professional - Mon, 2017-02-06 05:00
Oracle has several character data types that are commonly used and can also be confusing if you don’t know the differences between them. Learn what they are and the differences between them in this article. The Oracle character data types that are used most often are CHAR and VARCHAR2. There is also a VARCHAR data […]
Categories: Development

Weekly Link Roundup – Feb 3, 2017

Complete IT Professional - Fri, 2017-02-03 13:31
Here’s a collection of articles I’ve read over the last week that I found interesting. A shorter list than usual, but still some quality articles! Articles I’ve Read Oracle’s Cloud Licensing Change : Be Warned! https://oracle-base.com/blog/2017/01/28/oracles-cloud-licensing-change-be-warned/ Tim Hall from Oracle-Base shared with the community that Oracle’s cloud licensing costs have doubled due to the way […]
Categories: Development

ORA-01722 invalid number Solution

Complete IT Professional - Fri, 2017-02-03 05:00
Have you gotten an “ORA-01722 invalid number” error? I’ll explain what this error is and how you can resolve it in this article. ORA-01722 Cause So, you’ve run an SQL query (which can be SELECT, INSERT, UPDATE, for example), and you’ve gotten this error: ORA-01722: invalid number The reason for this error is that Oracle […]
Categories: Development

Managing Oracle Database Code with SQL Developer, Git, and Developer Cloud Service

Shay Shmeltzer - Thu, 2017-02-02 17:55

Are you coding SQL and PL/SQL code? Need to manage versions & branches? Want to track your to-do tasks? Need to conduct code reviews and peer programming? 

Developer Cloud Service can help you!

And now it comes free with your Oracle Database Cloud Service trial or license - check your service dashboard to see if you got one.  Note that even if your database is not in the cloud, but rather on-premises, you can use the same process shown in the video below.

In the demo you'll learn how to:

  • Provision a new cloud project for your team
  • Check SQL scripts into the Git Repository in DevCS
  • Track tasks and to-do items
  • Branch your SQL script code
  • Conduct code review with members of your team
  • Merge branches of code

Check it out:

I blogged about this topic in the past using JDeveloper, but figured out that most of the Oracle database developers actually use SQL Developer - so I thought it would be good to give them a quick 10 minute demo of what they can do by combining the power of SQL Developer and Developer Cloud Service. (The video can also be useful to just get a basic understanding of how SQLDeveloper works with any Git repo).  

More about Developer Cloud Service here.

Stay tune for more blog entries on features for Database developers in Oracle Developer Cloud Service - coming soon!

Categories: Development

Managing Oracle Database Code with SQL Developer, Git, and Developer Cloud Service

Shay Shmeltzer - Thu, 2017-02-02 17:55

Are you coding SQL and PL/SQL code? Need to manage versions & branches? Want to track your to-do tasks? Need to conduct code reviews and peer programming? 

Developer Cloud Service can help you!

And now it comes free with your Oracle Database Cloud Service trial or license - check your service dashboard to see if you got one.  Note that even if your database is not in the cloud, but rather on-premises, you can use the same process shown in the video below.

In the demo you'll learn how to:

  • Provision a new cloud project for your team
  • Check SQL scripts into the Git Repository in DevCS
  • Track tasks and to-do items
  • Branch your SQL script code
  • Conduct code review with members of your team
  • Merge branches of code

Check it out:

I blogged about this topic in the past using JDeveloper, but figured out that most of the Oracle database developers actually use SQL Developer - so I thought it would be good to give them a quick 10 minute demo of what they can do by combining the power of SQL Developer and Developer Cloud Service. (The video can also be useful to just get a basic understanding of how SQLDeveloper works with any Git repo).  

More about Developer Cloud Service here.

Stay tune for more blog entries on features for Database developers in Oracle Developer Cloud Service - coming soon!

Categories: Development

Pages

Subscribe to Oracle FAQ aggregator - Development