oracle db - how to identify compilation errors

1.

select * from user_errors;

2.

SELECT RTRIM(INITCAP(e.type))||' '||e.name||': Line '||
      TO_CHAR(e.line)||' Column '||TO_CHAR(e.position) linecol,
      s.text sourceline,RPAD(' ',(e.position - 1))||'^' errpos,
      e.text error
FROM   user_source s,user_errors e
WHERE  e.type = s.type
AND    e.name = s.name
AND    e.line = s.line
ORDER BY e.name,e.sequence

The latter one will give you the exact line where the error occcurs

ddl over database link in oracle 10gR2

recently I encountered a requirement to create a table on remote database DB_TARGET (working in DB_SOURCE)

for example:

create table table_a@dblink_to_db_target; --> does not work
-- ORA-02021: DDL operations are not allowed on a remote database

whereas

select 'hello' from dual@dblink_to_db_target; --> works

This is a workaround I used:
on TARGET_DB:

CREATE OR REPLACE 
FUNCTION prepare_table 
   ( table_name in varchar2) return varchar2 
   IS 
    err_num NUMBER; 
    err_msg VARCHAR2(100); 
    sqlstmt varchar2(4000) := ''; 
    tbl varchar2(30) := ''; 
BEGIN 
    if instr(table_name,'.') > 0 then 
        tbl := substr(table_name,instr(table_name,'.')+1); 
        sqlstmt := 'CREATE TABLE ' || tbl || ' as select * from ' || 
table_name; 
        execute immediate sqlstmt; 
        return 'table ' || tbl || ' successfully created'; 
    else 
        return 'table in owners'' schema'; 
    end if; 
EXCEPTION 
    WHEN others THEN 
        err_msg := SUBSTR(SQLERRM, 1, 100); 
        return 'error when creating ' || tbl || '-' || err_msg || '-' 
|| sqlstmt; 
END; 
/ 

this function creates a table specified as a table_name parameter only
if the "." is in the name => goal is to create a table in my_schema
from other user's schema.
this function works fine when calling this on DB_TARGET:

SQL> set serveroutput on; 
SQL> declare 
  2  result varchar2(300) := ''; 
  3  begin 
  4  result := prepare_table('other_user.table_a'); 
  5  dbms_output.put_line(result); 
  6  end; 
  7  / 
table table_a successfully created 

PL/SQL procedure successfully completed. 

but then surprisingly to me:
when I try to call this function from DB_SOURCE:

SQL> set serveroutput on; 
SQL> declare 
  2  result varchar2(300) := ''; 
  3  begin 
  4  result := prepare_table@dblink_to_db_target('other_user.table_a'); 
  5  dbms_output.put_line(result); 
  6  end; 
  7  / 
error when creating gcd_countries-ORA-02064: distributed operation not 
supported-CREATE TABLE table_a as select * from other_user.table_a 

PL/SQL procedure successfully completed. 

I found a workaround with a wrapper procedure on DB_TARGET:

CREATE OR REPLACE 
PROCEDURE prepare_table_proc(table_name in varchar2) 
   IS 
   result varchar2(300) := ''; 
BEGIN 
    result := prepare_table(table_name); 
    dbms_output.put_line('success'); 
EXCEPTION 
    WHEN others THEN 
        dbms_output.put_line('err'); 
END; 
/ 

when I call it from DB_SOURCE like this:

SQL> call prepare_table_proc@dblink_to_db_target('other_user.table_a'); 
Call completed. 

And the table_a is created on DB_TARGET.

when we look closer why the function itself did not work we see in the description of the error message:
ORA-02064: distributed operation not supported
Cause: One of the following unsupported operations was attempted:
1. array execute of a remote update with a subquery that
references a dblink, or
2. an update of a long column with bind variable and an update of
a second column with a subquery that both references a dblink and a
bind variable, or
3. a commit is issued in a coordinated session from an RPC
procedure call with OUT parameters or function call.

in my case point 3. yields and hence the error message.

Oracle documentation comes with help in:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sql.htm#sthref6148

it works only as a procedure and we cannot return a value to the invoking DB_SOURCE anyway.

maybe there's a better way...
right now I am happy with the workaround

why you should always use aliases in SQL (oracle)

let's create for this example following 2 tables (a and b):

create table a (org_id number, org_name varchar2(1)); 
insert into a values(1,'a'); 
insert into a values(2,'b'); 
insert into a values(3,'c');
commit;

create table b (name varchar2(1)); 
insert into b values('a'); 
insert into b values('b');
commit;

Now let's run this:

select * from a 
where a.org_id in (select org_id from b); 
-- returns all 3 rows from a 

select * from a 
where a.org_id not in (select org_id from b); 
-- returns 0 rows

The tricky part is, you might want the org_id in the subquery to be taken from b (which does not exist) and hence expect an INVALID IDENTIFIER error message as running only subquery:

select org_id from b 
        * 
ORA-00904: "ORG_ID": invalid identifier

But the initial query is correlated and actually the org_id in the subquery comes from outer table a and not from b.

That's why a good practice would be always to use aliases as prefixes to the columns.

In that way we can rewrite the query that will now raise an exception as expected:

select * from a 
where a.org_id not in (select b.org_id from b); 
-- ORA-00904: "B"."ORG_ID": invalid identifier

oracle archiver hangs because space_used = space_limit

Here is how to solve the issue without extending DB_RECOVERY_FILE_DEST_SIZE:

rman target /
RMAN> delete archivelog until time 'SYSDATE - 1';
or
RMAN> delete archive all;

RMAN> delete expired archivelog all;

sql loader and tab separated multibyte fields

when you use following part in your control file:

fields terminated by X'0009' optionally enclosed by X'0022'

you probably notice that the multi tab characters got swelled to one tab character, which causes that null value fields are gone, and the next non-null fields got shifted in that way, that there are no null values between 2 non-null value fields in db.

The solution to this issue is removing the "optionally enclosed ..." statement.

sqlplus enabling arrow keys for command history

It is very annoying when you in sqlplus under Linux cannot use ARROW keys to choose between executed commands.
there is a workaround for that, it's name is rlwrap:

sudo apt-get install rlwrap

then you can execute:

rlwrap sqlplus /nolog

or create an alias on sqlplus to be executed as rlwrap sqlplus.

thanks,
chris

Oracle - table changes how to notice

Couple of days ago I had a real problem how to provide always the most recent report with the products hierarchy. The first thought was to write a kind of a hash function to get just a hash stamp of the whole table and compare against the new one. The problem arose when it came to implementing the idea.
There is no built way to get a hash value of a whole table.

Suddenly I thought: "OK, let's check if the number of rows is different, then it would mean the content of the table changed".

I walked that way.
Replacing the table I always create first the new one with some different name and if everything goes fine, I replace the name to the right one at the end.

I have a production table called prods_hier and the new one in the background called prods_hier_bak

the following lines of code do the comparing:

changed:=false
select count(*) into no_of_rows_new from prods_hier;
select count(*) into no_of_rows_bak from prods_hier_bak;
if no_of_rows_new <> no_of_rows_bak then
    changed := true;
else -- the same no_of_rows
    select count(*) into no_of_changed_rows from
    ( select * from prods_hier
      minus
      select * from prods_hier_bak
    );
    if no_of_changed_rows <> 0 then
        changed := true;
    end if;
end if;