1) you can use the primary key -- but rowid is faster then primary key, rowid is always there, rowid is a known length, rowid is reliable.

htf.escape_sc() is used to escape special characters, things that will break HTML if you don't escape them.  If you are programming web stuff with HTP and don't know about this -- you are in BIG trouble. 

For example, insert a row into a table that has this in a string:

  insert into t values ( 'This is what a sqlplus prompt looks like "SQL>" ' );

Now, create a form to edit that!  The > is going to mess things up horribly.  If we htf.escape_sc before we print out using htp:

ps$tkyte@ORA817DEV.US.ORACLE.COM> select htf.escape_sc( 'This is what a sqlplus prompt looks like "SQL>"' )
  2  from dual
/  3  

HTF.ESCAPE_SC('THISISWHATASQLPLUSPROMPTLOOKSLIKE"SQL>"')
-----------------------------------------------------------------------------------------------------------------------------------
This is what a sqlplus prompt looks like "SQL>"


it ESCAPES things that will mess up HTML -- quotes, >, +, whatever...


You need to revisit ALL of your code to make sure you are escaping text before putting it in HTML else you run the risk of having a very bad thing happen to your data someday (lost data, incorrect data, etc)

2) I fail to see where an if statement comes into play.  We don't have any name, dob variables to compare to???

again, you would need to add the OR condition for NULLs as well.

3) In an HTML plsql application - the second you updated that record, you locked it.  Its locked until your transaction ends.  There is no difference here.  What you cannot do in a STATELESS environment is lock a record in one page, send the page back to the browser and expect that record to remain locked.

You need a where clause in an update to avoid updating records you don't mean to update.

There is no "locking feature for the web", locking is NO DIFFERENT on the web then anywhere else.  It is just that on the web -- you typically do not maintain state, hence you do not maintain locks, hence you must use a technique such as above when updating to ensure the data did not change.

If you have my book -- i do go into great detail on this.  This technique you are doing is known as OPTIMISTIC locking.  In a client server environment with a stated connection we would typically use  PESSIMISTIC locking instead.  I cover how to do it, what it is and more in the book. 

Arg, I hate IE for this reason (i use netscape).

IE is hiding your error at the expense of every other browser.

YOU are responsible for URL encoding YOUR strings.  YOU have a bug in your code!!!

Here is a handy little function you can use:


ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace function urlencode( p_str in varchar2 ) return varchar2
  2  as
  3          l_tmp   varchar2(12000);
  4          l_len   number default length(p_str);
  5          l_bad   varchar2(100) default ' >%}\~];?@&<#{|^[`/:=$+''"' || chr(10);
  6          l_char  char(1);
  7  begin
  8          if ( p_str is NULL ) then
  9                  return NULL;
 10          end if;
 11  
 12          for i in 1 .. l_len loop
 13                  l_char :=  substr(p_str,i,1);
 14                  if ( instr( l_bad, l_char ) > 0 )
 15                  then
 16                          l_tmp := l_tmp || '%' || to_char(ascii(l_char), 'fm0X');
 17                  else
 18                          l_tmp := l_tmp || l_char;
 19                  end if;
 20          end loop;
 21  
 22          return l_tmp;
 23  end;
 24  /

Function created.



Use that on all fields you pass as data to links and it'll do the right thing:

ops$tkyte@ORA817DEV.US.ORACLE.COM> select urlencode( 'user name' ) from dual;

URLENCODE('USERNAME')
-----------------------------------------------------------------------------------------------------------------------------------
user%20name


This is not a Netscap'ism -- this is the way the HTTP protocol is defined to work (and MS is "breaking" that protocol, again, as usual)
 

ops$tkyte%ORA10GR2> create table t
  2  as
  3  select 1 cust_id, q'|how's this for a string with 'quotes' galore|' descript
  4  from dual;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from t;

   CUST_ID DESCRIPT
---------- --------------------------------------------
         1 how's this for a string with 'quotes' galore

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> @owainit
ops$tkyte%ORA10GR2> declare
  2          nm      owa.vc_arr;
  3          vl      owa.vc_arr;
  4  begin
  5          nm(1) := 'WEB_AUTHENT_PREFIX';
  6          vl(1) := 'WEB$';
  7          owa.init_cgi_env( nm.count, nm, vl );
  8  end;
  9  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> begin
  2          for x in ( select * from t )
  3          loop
  4                  htp.p('<?xml version="1.0" encoding="ISO-8859-1"?>');
  5                  htp.p('<ROW>');
  6                  htp.p('<CUST_ID>'||x.cust_id||'</CUST_ID>');
  7                  htp.p('<COMMENTS>'||htf.escape_sc(x.descript)||'</COMMENTS>');
  8                  htp.p('</ROW>');
  9          end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec owa_util.showpage
Content-type: text/html
Content-length: 144
<?xml version="1.0" encoding="ISO-8859-1"?>
<ROW>
<CUST_ID>1</CUST_ID>
<COMMENTS>how's this for a string with 'quotes' galore</COMMENTS>
</ROW>

PL/SQL procedure successfully completed.