Monthly Archives: May 2008

Hey Scott, where have you been ?

6 Replies

Today I missed Scott in my emp table. When selecting from EMP, Scott is not there. Gone…

Ok, let’s recreate the scott schema.

C:> sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production 
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
SYS@lsc02> spool scott.txt
SYS@lsc02> drop user scott cascade;
User dropped.
SYS@lsc02> @?/rdbms/admin/utlsampl
Disconnected from Oracle Database 10g Enterprise Edition

C:> sqlplus scott/tiger
SQL*Plus: Release 10.2.0.3.0 - Production 
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
SCOTT@lsc02> select ename from emp;
ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
KING
TURNER
JAMES
FORD
MILLER

12 rows selected.

Hey Scott, I miss you!

Let’s see if I can dig out something in the log file I created with spool scott.txt


(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87',
  'dd-mm-rr')-85,3000,NULL,20)
ERROR at line 2:
ORA-01858: a non-numeric character was found 
  where a numeric was expected

Hey! the UTLSAMPL script is not supposed to work where JUL is not a month ?
reg query HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE /s /f NLS_LANG

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1
NLS_LANG REG_SZ FRENCH_SWITZERLAND.WE8MSWIN1252

Since FRENCH has no month called JUL the UTLSAMPL failed to insert SCOTT. Let’s fix the issue :
C:>reg add HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1
/v NLS_LANG /t reg_sz /d AMERICAN_AMERICA.WE8MSWIN1252
Value NLS_LANG exists, overwrite(Yes/No)? yes
The operation completed successfully.
C:>reg query HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1 /v NLS_LANG

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1
NLS_LANG REG_SZ AMERICAN_AMERICA.WE8MSWIN1252

and retry

C:>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production 
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
SYS@lsc02> @?/rdbms/admin/utlsampl
Disconnected from Oracle Database 10g Enterprise Edition

C:>sqlplus scott/tiger
SQL*Plus: Release 10.2.0.3.0 - Production 
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
SCOTT@lsc02> select ename from emp;
ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

14 rows selected.

Welcome back Scott!

DITO

3 Replies

I never used Oracle Web Conference OWC, for security reason. Today I created one SR and read first time about Demo It To Oracle (DITO). This is basically a link CamStudio.org, which is a tool that records AVI file and converts AVI to flash SWF file. This is nice to have because you do need to grant access to your desktop to Oracle Support and you can record what’s happening on your screen !

Let’s have a look

defaultsysdate.swf

How to cron?

3 Replies

RTFM is not the best answer … 

man crontab
SunOS 5.10          Last change: 10 Nov 2005

User Commands                                          crontab(1)
     A crontab file consists of lines of  six  fields  each.  The
     fields  are  separated by spaces or tabs. The first five are
     integer patterns that specify the following:

     minute (0-59),
     hour (0-23),
     day of the month (1-31),
     month of the year (1-12),
     day of the week (0-6 with 0=Sunday).

so far so good. But read this


     Each of these patterns can be either  an  asterisk  (meaning
     all legal values) or a list of elements separated by commas.
     An element is either a number or two numbers separated by  a
     minus sign (meaning an inclusive range). Time specified here
     is interpreted in the timezone of the cron(1M) daemon, which
     is  set system-wide in /etc/default/init. Entries do not use
     the invoking user's timezone. The specification of days  can
     be  made  by  two  fields  (day  of the month and day of the
     week). Both are adhered to if specified as a  list  of  ele-
     ments. See .

See .

Let’s imagine you want to run a job today, Thursday May 15, 2008 at 2:15pm
You write:
15 14 15 05 4 /tmp/run-my-job

The job will be run Today at 2:15, next year Friday May 15, 2009 at 2:15pm and next week Thursday May 22, 2008 at 2:15pm…

The correct method is to use
15 14 15 05 * /tmp/run-my-job

Specifying both day of week and day of month is not what I expected :twisted:

drop all objects

2 Replies

warning: the script below is destructive and not 100% safe

update: it is unsafe to drop SYS_ objects, check for instance note 579399.1

A question was posted on the french forums of developez.net about how to drop all objects of an user. The drop user toto cascade; followed by create user toto identified by tott; was suggested as an easy answer, but I commented that create user must re-grant quotas, roles, system privileges, table privileges. Create user must also set the correct security status of the account (password, case-sensitive password, lock status, expiration status, profile), and the password history and failed login history is lost. Also it must set the default and temporary tablespaces.

It is also important to note that “drop user” requires dba privileges, when dropping the own object does not require privileges

To drop all objects I tried with plsql and a dictionary loop.

select object_type, count(*) 
from user_objects 
group by object_type;

OBJECT_TYPE           COUNT(*)
------------------- ----------
INDEX                        6
TYPE BODY                    1
INDEXTYPE                    1
PROCEDURE                    1
JAVA CLASS                   2
JAVA RESOURCE                1
JAVA SOURCE                  1
FUNCTION                     1
TABLE                       10
TRIGGER                      1
TYPE                         1
MATERIALIZED VIEW            1
DATABASE LINK                1
PACKAGE BODY                 1
CLUSTER                      1
DIMENSION                    1
OPERATOR                     1
SEQUENCE                     1
PACKAGE                      1

19 rows selected.

purge recyclebin;

Purge successfully completed.

begin
for f in (
select object_type, object_name from user_objects
where object_type in (
‘MATERIALIZED VIEW’)) loop
execute immediate
‘drop materialized view “‘||f.object_name||’” preserve table’;
end loop;
for f in (
select table_name from user_tables) loop
execute immediate
‘drop table “‘||f.table_name||’” cascade constraints’;
end loop;
for f in (
select object_type, object_name from user_objects
where object_type in (
‘DIMENSION’,'CLUSTER’,'SEQUENCE’,
‘VIEW’,'FUNCTION’,'PROCEDURE’,
‘PACKAGE’,'SYNONYM’,'DATABASE LINK’,
‘INDEXTYPE’)
and object_name like ‘SYS_%$’) loop
execute immediate ‘drop ‘||
f.object_type||’ “‘||f.object_name||’”‘;
end loop
for f in (
select object_type, object_name from user_objects
where object_type in (
‘JAVA SOURCE’)) loop
execute immediate ‘drop ‘||
f.object_type||’ “‘||f.object_name||’”‘;
end loop;
for f in (
select object_type, object_name from user_objects
where object_type in (
‘JAVA RESOURCE’)) loop
execute immediate ‘drop ‘||
f.object_type||’ “‘||f.object_name||’”‘;
end loop;
for f in (
select object_type, object_name from user_objects
where object_type in (
‘JAVA CLASS’)) loop
execute immediate ‘drop ‘||
f.object_type||’ “‘||f.object_name||’”‘;
end loop;
for f in (
select object_type, object_name from user_objects
where object_type in (
‘TYPE’,'OPERATOR’)) loop
execute immediate ‘drop ‘||
f.object_type||’ “‘||
f.object_name||’” force’;
end loop;
end;
/

PL/SQL procedure successfully completed.


select * from user_objects

no rows selected.

All objects of the current schema disappeared, do not try this as sys, this script is destructive with no confirmation

select distinct collect

7 Replies

I answered a question on otn today about distinct.

Reprased, how to select distinct collection?


select job, collect(distinct deptno) deptnos 
from emp group by job;

JOB       DEPTNOS
--------- -------------------------------------------
ANALYST   SYSTPTJCzBffh0AjgQ59n0o3QCA==(20)
CLERK     SYSTPTJCzBffh0AjgQ59n0o3QCA==(10, 20, 30)
MANAGER   SYSTPTJCzBffh0AjgQ59n0o3QCA==(10, 20, 30)
PRESIDENT SYSTPTJCzBffh0AjgQ59n0o3QCA==(10)
SALESMAN  SYSTPTJCzBffh0AjgQ59n0o3QCA==(30)

5 rows selected.

select distinct collect(distinct deptno) deptnos 
from emp group by job;

ERROR at line 1:
ORA-22950: cannot ORDER objects without MAP or ORDER method

the message is clear, i need a MAP or ORDER method. Ok, I can do this 


create type tt_n as table of number(2);
/

Type created.

create type tt_n_ord as object(
  c tt_n,
  order member function 
    eq (tc tt_n_ord) return integer);
/

Type created.

create type body tt_n_ord as
  order member function 
    eq (tc tt_n_ord) return integer
  is
  begin 
    if c=tc.c 
    then return 0; 
    else return 1; 
    end if;
  end;
end;
/

Type body created.

select distinct 
  tt_n_ord(cast(collect(distinct deptno) as tt_n))
from emp group by job;

TT_N_ORD(CAST(COLLECT(DISTINCTDEPTNO)ASTT_N))(C)
------------------------------------------------
TT_N_ORD(TT_N(30))
TT_N_ORD(TT_N(10))
TT_N_ORD(TT_N(10, 20, 30))
TT_N_ORD(TT_N(20))

4 rows selected.

Preserve code formatting

12 Replies

Hey,
This plugin seems to do exactly what I am looking for, enable users to write code in comments.
Preserve Code Formatting

Please test here and give feedback

Warning: this changed the behavior of old posts too, so I have to reformat all older posts to no longer use < in code :(

Select bottom rows

2 Replies

Today I answered a question on otn regarding order by.

Let’s build a different test case.

You have a view that contain an order by. You want to retrieve the last 5 records. However you do not have access to the sort column. 

SYS@LSC01> GRANT CREATE VIEW TO USER_A IDENTIFIED BY A;

Grant succeeded.

SYS@LSC01> GRANT SELECT ON SCOTT.EMP TO USER_A WITH GRANT OPTION;

Grant succeeded.

SYS@LSC01> CREATE VIEW USER_A.V AS SELECT ENAME FROM SCOTT.EMP 
ORDER BY HIREDATE;

View created.

SYS@LSC01> GRANT CREATE SESSION TO USER_B IDENTIFIED BY B;

Grant succeeded.

SYS@LSC01> GRANT SELECT ON USER_A.V TO USER_B;

Grant succeeded.

SYS@LSC01> CONNECT USER_B/B
Connected.
USER_B@LSC01> SELECT ROWNUM, ENAME FROM USER_A.V WHERE ROWNUM<4;
    ROWNUM ENAME
---------- ----------
         1 SMITH
         2 ALLEN
         3 WARD

To get top 3, that was easy 8)

USER_B@LSC01> SELECT R, ENAME FROM
  2    (SELECT ROWNUM R,ENAME
  3    FROM USER_A.V
  4    ORDER BY ROWNUM DESC)
  5  WHERE ROWNUM<4 ORDER BY R;
         R ENAME
---------- ----------
        12 MILLER
        13 SCOTT
        14 ADAMS

Here we have a sorted view. The OP had an external file. It could be an XML input, or any table or collection that is sorted but does not have a sort key