ORACLE Find host name of a currently connected Schema/Instance

This query would give you the server host name of ORACLE machine


 SELECT sys_context('USERENV', 'SERVER_HOST') FROM DUAL;

Oracle: Merge Date between tales OR copy from table to table

Ref:
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5646797700346972949

You Asked

Hello,

I have a requirement to update 2 columns (A, B) in a arbitrarily (very, very) large table. The values that needs to be updated relies on values from another table and must be consistent with the values from the other table. Since the values in A differ from B, currently, i have to do 2 updates to the table for each record in the cursor. This is obviously inefficient. It can take longer than 12 hours to complete (and has been confirmed as such). There surely must be some way to improve the process. All my reading and trawling the web seems to point to using FETCH, BULK COLLECT and LIMIT etc. Is this the way to go? Can you please perhaps furnish an example?

See the code example below:

declare
CURSOR tran
IS
  SELECT oid, user_account_number FROM user_account001;
BEGIN
  FOR t IN tran
  LOOP
    UPDATE transaction001
    SET payer_account_number = t.user_account_number
    WHERE payer_account_oid  = t.oid;
    UPDATE transaction001
    SET payee_account_number = t.user_account_number
    WHERE payee_account_oid  = t.oid;
  END LOOP;
END;

Thanks!

and we said...

why is this anything more than:

begin

merge into transaction001 t
using user_account001 ua
on ( t.payer_account_oid = ua.oid )
when matched then update set payer_account_number = ua.user_account_number;

merge into transaction001 t
using user_account001 ua
on ( t.payee_account_oid = ua.oid )
when matched then update set payee_account_number = ua.user_account_number;

end;

Oracle: varying elements in IN list(comma separated string)

Ref:  http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:110612348061

You can use a bind variable in an IN but I don't think it'll do what you want.  I believe 
you want a string that has the value:

    1, 2, 3

for example (a single variable) to be used in a query like:
  
   select * from t where x in ( :that_variable );

and have all rows returned such that x is in the set of 1, 2, 3.  What would happen 
though is that every row in T such that x = "1, 2, 3" would be returned (if it worked the 
other way -- you would never be able to IN on something with commas in it). 

One 'trick' that works very well on Oracle8.0 and up is to use an object type that is a 
nested table type and a simple parse routine that returns this nested table type given a 
string input.  What I mean is best explained via an example:

ops$tkyte@8i> create or replace type myTableType as table of number;
  2  /
Type created.

ops$tkyte@8i> create or replace function str2tbl( p_str in varchar2 ) return myTableType
  2  as
  3      l_str   long default p_str || ',';
  4      l_n        number;
  5      l_data    myTableType := myTabletype();
  6  begin
  7      loop
  8          l_n := instr( l_str, ',' );
  9          exit when (nvl(l_n,0) = 0);
 10          l_data.extend;
 11          l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
 12          l_str := substr( l_str, l_n+1 );
 13      end loop;
 14      return l_data;
 15  end;
 16  /

Function created.

ops$tkyte@8i> 
ops$tkyte@8i> select * from all_users
  2  where user_id in ( select *
  3    from THE ( select cast( str2tbl( '1, 3, 5, 7, 99' ) as mytableType ) from dual ) )
  4  /

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
SYSTEM                                  5 20-APR-99

Understand Classloaders

http://zeroturnaround.com/labs/rebel-labs-tutorial-do-you-really-get-classloaders/

XML to database RDMS table mapping

Ref: http://www.xml.com/pub/a/2001/05/09/dtdtodbs.html