We were working on an iPhone app recently and added a Settings.bundle to allow the user to set their preferences as described here and here.  However whenever we ran the app either on the device or on the simulator the settings simply would not appear in the iPhone’s settings.

After much digging around and many hours banging heads on walls we finally tracked it down.  Settings bundles are only added to the phone the first time the application is installed. If you’ve worked on an application and deployed it to your development device or simulator before adding the settings.bundle to your XCode project then subsequent build and runs won’t add the settings unless you remove the app from the device or simulator in the usual way. i.e by pressing and holding the app icon and removing it when it starts shaking.

The next time you deploy to the device or simulator the settings should be present.

We hope this helps someone.

To me a simple, yet glaring, omission from the Oracle PL/SQL language, which is in many ways excellent, is a function to test to see if a string represents numeric data as can be found in many other languages (e.g is_numeric in PHP, isNumeric in Java and .Net).

A possible implementation is to simply try to assign the string to a number and catch any exceptions.  If something goes wrong then it’s not a number!

  create or replace function isnumeric(p_value in varchar2) return number
  as
    l_ret number;
  begin
    -- Try to cast to a number
    begin
      l_ret := to_number(p_value);
      -- if the line above worked then just reset to 1
      l_ret := 1;
    exception
      when others then
        -- Something, anything, went wrong
        l_ret := 0;
    end;
    -- Return
    return l_ret;
  end isnumeric;

e.g

  select isnumeric('10.1.1') from dual

  select isnumeric('10.1') from dual

  select isnumeric('-10.1') from dual

We hope this helps someone.

Yesterday one of our iPhones developed an intermittent fault with the call quality.  Often we could hear the other party but they couldn’t hear us, or vice versa.  It didn’t seem to matter who initiated the call or wether it was a mobile to landline call or mobile to mobile.

iPhone 3G

We took the following diagnostic steps to try and track down the problem :

1) Made a sound recording with the Voice Memos app and played it back to confirm speaker / mic working.

2) Powered off and on again

3) Reset (pressed Home and Power button simultaneously for 10 seconds)

4) Did a Restore through iTunes.

All to no effect.  A quick call to O2 resulted in a couple more things to try :

1) Remove SIM card and give it a good clean.  It was surprising how much pocket fluff was in there!

2) Try the SIM card in another phone.

With the second option above we actually found that the other phone (a Nokia) started exhibiting the same behaviour so the problem appeared to be with the SIM, not the iPhone (phew!).

A quick trip to the local O2 retail store for a SIM swap (done in the store with no temporary number or delay) and we’re back up and running again.

We hope this helps someone else.

UPDATE : the solution may not have been that simple as the problem resurfaced a few days later.  We’ve just done a complete factory reset and fingers-crossed we’ll be OK.  Just made a 6 minute call which was impossible this morning.

We just upgraded XCode to the latest release (3.1.4 as at time of writing) and noticed that after doing so a number of our iPhone projects now had their Frameworks all set to missing (i.e coloured in red) like this :

The solution wasn’t immediately obvious but just in case anyone else was wondering what was required to fix it here are the steps :

1) Open the settings for your project by right-clicking and choosing ‘Get Info’ :

2) This will display this page, which will display the root of the problem in the ‘Base SDK for all configuration’ drop down :

The problem is that the project was set to ‘iPhone Device 2.2′ which is no longer supplied with XCode.

3) To fix the problem simply change the Base SDK to whatever is appropriate for you, e.g :

and voila, the problem is solved :

Thanks for reading.

An earlier post illustrated how to extract information from XML inside PL/SQL.  This post is an example of the reverse of this, in that it shows how to create an XML document from a SQL statement.  These examples are based on creating an XML document providing details of the tables in the SCOTT schema but could easily be adapted to suit your needs.

This first example extract the list of tables names and uses a combination of the XMLRoot, XMLElement, XMLAgg, XMLAttributes and XMLForest functions to create the document :

SELECT XMLRoot(
         XMLElement("Tables",
           XMLAgg(XMLElement("Table",
             XMLAttributes(table_name),
               XMLForest(owner,
                         tablespace_name,
                         status,
                         num_rows)))

       ), VERSION '1.0', STANDALONE YES).getClobVal() as XMLDATA
  from all_tables
 where owner = 'SCOTT'

This example returns a document like so :
<?xml version="1.0" standalone="yes"?>
<Tables>
  <Table TABLE_NAME="DEPT">
    <OWNER>SCOTT</OWNER>
    <TABLESPACE_NAME>USERS</TABLESPACE_NAME>
    <STATUS>VALID</STATUS>
    <NUM_ROWS>4</NUM_ROWS>
    </Table>
etc etc.
To make this a little more useful (and complex!) we can add a nested query to return the column details in-line with the table information, like this :
SELECT XMLRoot(
         XMLElement("Tables",
          XMLAgg(XMLElement("Table",
             XMLAttributes(at.table_name),
               XMLForest(at.owner,
                         at.tablespace_name,
                         at.status,
                         at.num_rows,
                         (select XMLAgg(XMLElement("Column",
                                   XMLAttributes(atc.column_name),
                                     XMLForest(atc.data_type,
                                               atc.data_length,
                                               atc.data_scale,
                                               atc.data_precision,
                                               atc.nullable)))
                           from all_tab_columns atc
                          where atc.owner = at.owner
                            and atc.table_name = at.table_name) as columns)))
         ), VERSION '1.0', STANDALONE YES).getClobVal() as XMLDATA
  from all_tables at
 where owner = 'SCOTT'
which returns an XML document like so :
<?xml version="1.0" standalone="yes"?>
<Tables>
  <Table TABLE_NAME="DEPT">
    <OWNER>SCOTT</OWNER>
    <TABLESPACE_NAME>USERS</TABLESPACE_NAME>
    <STATUS>VALID</STATUS>
    <NUM_ROWS>4</NUM_ROWS>
    <COLUMNS>
      <Column COLUMN_NAME="DEPTNO">
        <DATA_TYPE>NUMBER</DATA_TYPE>
        <DATA_LENGTH>22</DATA_LENGTH>
        <DATA_SCALE>0</DATA_SCALE>
        <DATA_PRECISION>2</DATA_PRECISION>
        <NULLABLE>N</NULLABLE>
      </Column>
      <Column COLUMN_NAME="DNAME">
        <DATA_TYPE>VARCHAR2</DATA_TYPE>
        <DATA_LENGTH>14</DATA_LENGTH>
        <NULLABLE>Y</NULLABLE>
      </Column>
      <Column COLUMN_NAME="LOC">
       <DATA_TYPE>VARCHAR2</DATA_TYPE>
       <DATA_LENGTH>13</DATA_LENGTH>
       <NULLABLE>Y</NULLABLE>
     </Column>
  </COLUMNS>
</TABLE>
etc etc.
I hope this is helpful to someone.  Most of the documentation that supports this post can be found here.

Occasionally it may be useful to have a primary / foreign key relationship between a view and table, where the view is the primary key referenced by a column in the table.

Oracle doesn’t let you do this quite as ‘completely’ as we’d probably like, but it at least let’s you put in the building blocks which may help the CBO and make your database design more concise or elegant in places.

Here is a quick example :

SQL> create view pk_view

(item_id, item_name,

constraint pk_test_view primary key (item_id) rely disable novalidate) 

as

select 1, 'One' from dual

union

select 2, 'Two' from dual

union

select 3, 'Three' from dual;

View created.

SQL> create table fk_view (

item_id number references pk_view disable novalidate);

Table created.

SQL>

There, that’s it.  Hope this helps someone as the create view syntax can be a little tricky.

Just a simple one but a syntax that I can never remember when I need to so a quick post may help jog mine, and your, memory.  This grep syntax is for matching lines from a text file that begin with a particular word or expression :

$ grep -w '^Word' input_file.txt > output_file.txt

After much digging it turns out that the problem is simply that the path from which the setup.exe was ran had a space in it (as it was just on the Desktop so was actually in C:\Documents and Settings\<user>\Desktop.  Simply moving the file to a folder with no spaces (e.g C:\temp) fixed the issue and the upgrade proceeded as you would expect.

Often it can be useful to return the result of a simple SQL*Plus query to a shell script variable, for example to check for a value before deciding wether or not to proceed.  The syntax is actually pretty simple :

#!/bin/sh

count=$(sqlplus -s 'scott/tiger@xe'<<EOF

  set pages 0 feed off

  select count(*) from tab;
EOF
)
echo $count

Enjoy!

Imagine this scenario :

drop table t;

drop table s;

create table t (id number, name varchar2(32));

create table s (id number, name varchar2(32));

insert into t values (1,'1');

insert into t values (2,'2');

insert into t values (3,'3');

insert into t values (4,'4');

insert into t values (5,'5');

insert into s values (3,'three');

insert into s values (5,'five');

select * from t;

update t

   set name = (select name

                 from s

                where t.id = s.id);

select * from t;

Our first table (t) simply has the numbers 1-5 in the ‘id’ and ‘name’ column but we want to update some of the names based on the contents of the second table (s) but this table only has values for two of the rows.  This is OK so long as we can preserve the ‘default’ numeric values.  However the script above gives the following output :

drop table t succeeded.

drop table s succeeded.

create table succeeded.

create table succeeded.

1 rows inserted

1 rows inserted

1 rows inserted

1 rows inserted

1 rows inserted

1 rows inserted

1 rows inserted

ID                     NAME                             
---------------------- -------------------------------- 
1                      1                                
2                      2                                
3                      3                                
4                      4                                
5                      5                                

5 rows selected

5 rows updated

ID                     NAME                             
---------------------- -------------------------------- 
1                                                       
2                                                       
3                      three                            
4                                                       
5                      five                             
 
5 rows selected

The problem here is that the update isn’t a true ‘join’ as every row is going to be updated and if the ‘join’ fails then a null is written.  The traditional solution to this is to rewrite the SQL to be something like :

update t

   set name = (select name

                 from s

                where t.id = s.id)

 where exists (select 1

                 from s

                where t.id = s.id);

select * from t;

which does the job correctly :

2 rows updated

ID                     NAME 

---------------------- -------------------------------- 

1                      1 

2                      2 

3                      three 

4                      4 

5                      five

5 rows selected

However this isn’t a very good solution for updates involving very large tables as it can significantly slow things down.  One alternative is the MERGE statement :

merge into t
 using (select id, name
          from s) s
    on (t.id = s.id)
 when matched then
   update set t.name = s.name;

select * from t;

2 rows merged

ID                     NAME 

---------------------- -------------------------------- 

1                      1 

2                      2 

3                      three 

4                      4 

5                      five

5 rows selected

An other option is to use the NVL() function which allows you to deal with the problem of the nulls above :

update t
   set name = nvl((select name
                    from s
                   where t.id = s.id), t.name);

select * from t;

which also works :

5 rows updated

ID                     NAME 

---------------------- -------------------------------- 

1                      1 

2                      2 

3                      three 

4                      4 

5                      five

5 rows selected