Oracle Apex, Forms, Reports

Md. Quium Hossain who I'm Oracle DBA & APEX Developer. All-rounder in building small, medium, and enterprise applications. Extensive knowledge in various areas of web-driven applications in Back-end (PL/SQL, SQL, Java), Front-end (Oracle APEX, Oracle Forms, Oracle Reports, HTML, JavaScript, CSS, jQuery, OracleJET, ReactJS), RESTful APIs, Third-party library integrations (Apex Office Print (AOP), Payment Gateways, SMS, Syncfusion, HighCharts) and APEX Plugins (HighChart, StarRating)

Tuesday, February 28, 2017

Master Detail Relation in oracle Tables

Sunday, February 26, 2017

create a foreign key

SQL> -- create a foreign key
SQL>
SQL>     CREATE TABLE supplier
  2      (      supplier_id     numeric(10)     not null,
  3             supplier_name   varchar2(50)    not null,
  4             contact_name    varchar2(50),
  5             CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
  6      );

Table created.

SQL>
SQL>
SQL>     CREATE TABLE products
  2      (      product_id      numeric(10)     not null,
  3             supplier_id     numeric(10)     not null,
  4             CONSTRAINT fk_supplier
  5               FOREIGN KEY (supplier_id)
  6               REFERENCES supplier(supplier_id)
  7      );

Table created.

SQL>
SQL>
SQL>
SQL>     desc products;
 Name                                                                                                  Null?    Type
 ----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------

 PRODUCT_ID                                                                                            NOT NULL NUMBER(10)
 SUPPLIER_ID                                                                                           NOT NULL NUMBER(10)

SQL>     desc supplier;
 Name                                                                                                  Null?    Type
 ----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------

 SUPPLIER_ID                                                                                           NOT NULL NUMBER(10)
 SUPPLIER_NAME                                                                                         NOT NULL VARCHAR2(50)
 CONTACT_NAME                                                                                           VARCHAR2(50)

SQL>
SQL>     drop table products cascade constraints;

Table dropped.

SQL>
SQL>     drop table supplier cascade constraints;

Saturday, February 25, 2017

cleanup the schema Be care full Details

I use the following script to cleanup the schema.
Since I use "CASCADE CONSTRAINTS" for table it doesn't throw me an error.

set feedback off
set heading off
set termout off
set linesize 1000
set trimspool on
set verify off
spool c:\temp\drop_objects.lst
SELECT 'DROP ' || OBJECT_TYPE || ' ' || OBJECT_NAME ||';'   FROM USER_OBJECTS
WHERE OBJECT_TYPE <> 'TABLE' AND OBJECT_TYPE <> 'INDEX' AND OBJECT_TYPE<>'PACKAGE BODY' AND OBJECT_TYPE<>'TRIGGER' AND OBJECT_TYPE<>'LOB'
UNION ALL
SELECT 'DROP ' || OBJECT_TYPE || ' ' || OBJECT_NAME ||' CASCADE CONSTRAINTS;'   FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'TABLE';
spool off;
@c:\temp\drop_objects.lst
prompt 'Purging the recycle bin...'
purge recyclebin;
host del c:\temp\drop_objects.lst
---exit;

=======================================================================
Hoe to drop all Schema Objects in Oracle

To drop all schema objects in Oracle database including tables, sequences, indices, views, materialized views, materialized view logs, and all other object like scheduled jobs run the following PL/SQL block:
SQL> create table LIMA (id varchar2(200));
table created

SQL> select * from tab;

LIMA                           TABLE

SQL> DECLARE
2    CURSOR c1 IS
3      SELECT 'DROP ' || OBJECT_TYPE || ' ' || OBJECT_NAME || DECODE(OBJECT_TYPE,'TYPE',' FORCE') CODE
4        FROM USER_OBJECTS
5       WHERE OBJECT_TYPE NOT IN ('INDEX','LOB','TRIGGER','TABLE','PACKAGE BODY','JOB')
6       ORDER BY 1;
7    CURSOR c2 IS
8      SELECT 'DROP TABLE ' || TABLE_NAME || ' CASCADE CONSTRAINTS' CODE
9        FROM USER_TABLES
10       WHERE NESTED = 'NO' AND TABLE_NAME NOT LIKE 'MLOG$%'
11       ORDER BY 1;
12    CURSOR c3 IS
13      SELECT OBJECT_NAME
14        FROM USER_OBJECTS
15       WHERE OBJECT_TYPE = 'JOB'
16       ORDER BY 1;
17 BEGIN
18    FOR X IN c3 LOOP
19      DBMS_SCHEDULER.DROP_JOB (job_name => X.OBJECT_NAME);
20    END LOOP;
21    FOR X IN c1 LOOP
22      EXECUTE IMMEDIATE (X.CODE);
23    END LOOP;
24    FOR X IN c2 LOOP
25      EXECUTE IMMEDIATE (X.CODE);
26    END LOOP;
27    EXECUTE IMMEDIATE 'PURGE RECYCLEBIN';
28 END;
29 /
SQL> select * from tab;
SQL> /
SQL>

=======================================================================
Dangerous XXXXX Dangerous Do Things
How to truncate all tables in oracle schema

SQL> select * from lima;

333333
333333
333333
333333
SQL> create table Kaium (id varchar2(200));
SQL>
SQL>
SQL>
SQL> insert into kaium s('333333')
2
SQL>
SQL> insert into kaium    values    ('333333')
2 /
SQL> /
SQL> /
SQL> /
SQL> /
SQL> select * from kaium;

333333
333333
333333
333333
333333
SQL> begin
2 for r in (select table_name from user_tables order by table_name)
3    loop
4    begin
5     execute immediate 'truncate table '||r.table_name;
6     exception when others then null;
7    end;
8    end loop;
9 end;
10 /
SQL> select * from kaium;
SQL> select * from lima;
SQL>
SQL>
SQL>
SQL>

               Warning The script below will remove all tables 
                       under the current schema

begin
for r in (select table_name from user_tables order by table_name)
  loop
  begin
   execute immediate 'drop table '||r.table_name
                     ||' cascade constraints purge';
   exception when others then null;
  end;
  end loop;
end;
/

=======================================================================

How to truncate all user tables

declare

begin

for c1 in (select table_name, constraint_name from user_constraints) loop
    begin
        execute immediate ('alter table '||c1.table_name||' disable constraint '||c1.constraint_name);
    end;
end loop;

for t1 in (select table_name from user_tables) loop
    begin
        execute immediate ('truncate table '||t1.table_name);
    end;
end loop;

for c2 in (select table_name, constraint_name from user_constraints) loop
    begin
        execute immediate ('alter table '||c2.table_name||' enable constraint '||c2.constraint_name);
    end;
end loop;

end;
/

Create table by Select Statement in oracle

Enter user-name: test/test@fstar

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> desc fhrd.emp_shft
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
EMP_ID                                             VARCHAR2(6)
AT_DT                                              DATE
SHFT                                               VARCHAR2(1)

SQL> create table emp_shft as select * from fhrd.emp_shft;

Table created.

Friday, February 24, 2017

drop table detail purge

SQL> create table detail
2 (id varchar2(1), QTY NUMBER(10,2));

Table created.
SQL> select * from tab;

TNAME                          TABTYPE CLUSTERID
------------------------------ ------- ----------
COUNTRIES                      TABLE
DEPARTMENTS                    TABLE
DEPT1                          TABLE
EMPLOYEES                      TABLE
EMP_DETAILS_VIEW               VIEW
JOBS                           TABLE
JOB_HISTORY                    TABLE
LOCATIONS                      TABLE
MYTABLE                        TABLE
PAGER                          TABLE

TNAME                          TABTYPE CLUSTERID
------------------------------ ------- ----------
REGIONS                        TABLE
DETAIL    TABLE

12 rows selected.
SQL> drop table detail;

Table dropped.

SQL> select * from tab;

TNAME                          TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$kRC9qcb8QN6c+iscYyVIJA==$0 TABLE
COUNTRIES                      TABLE
DEPARTMENTS                    TABLE
DEPT1                          TABLE
EMPLOYEES                      TABLE
EMP_DETAILS_VIEW               VIEW
JOBS                           TABLE
JOB_HISTORY                    TABLE
LOCATIONS                      TABLE
MYTABLE                        TABLE
PAGER                          TABLE

TNAME                          TABTYPE CLUSTERID
------------------------------ ------- ----------
REGIONS                        TABLE

12 rows selected.
SQL> drop table detail purge;

Table dropped.

SQL> select * from tab;

TNAME                          TABTYPE CLUSTERID
------------------------------ ------- ----------
COUNTRIES                      TABLE
DEPARTMENTS                    TABLE
DEPT1                          TABLE
EMPLOYEES                      TABLE
EMP_DETAILS_VIEW               VIEW
JOBS                           TABLE
JOB_HISTORY                    TABLE
LOCATIONS                      TABLE
MYTABLE                        TABLE
PAGER                          TABLE
REGIONS                        TABLE

11 rows selected.

Decimal & number Confusion

SQL> create table detail
2 (id varchar2(1), QTY NUMBER(10,2));
Table created.

memorize: (10-2)=8 digit must be then (dot.) fraction more insert but forms not insert more then 2 digit and sql plus show 2 digits

SQL> insert into detail values('1',55555555555555.444);
insert into detail values('1',55555555555555.444)
                              *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SQL> insert into detail values('1',55555555.444);

1 row created.

SQL> insert into detail values('1',5555555555.444);
insert into detail values('1',5555555555.444)
                              *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SQL> insert into detail values('1',5555555555.44);
insert into detail values('1',5555555555.44)
                              *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SQL> insert into detail values('1',5555555555.4);
insert into detail values('1',5555555555.4)
                              *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SQL> insert into detail values('1',555555555.4);
insert into detail values('1',555555555.4)
                              *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SQL> insert into detail values('1',55555555.4);

1 row created.

SQL> insert into detail values('1',555555555.4);
insert into detail values('1',555555555.4)
                              *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SQL> insert into detail values('1',55555555.44);

1 row created.

SQL> insert into detail values('1',55555555.444);

1 row created.

SQL> insert into detail values('1',55555555.4444);

1 row created.

SQL> insert into detail values('1',55555555.444444);

1 row created.

SQL> insert into detail values('1',55555555.444444444);

1 row created.

SQL> insert into detail values('1',55555555.4444444444444444444444444444444444444444444444444444

1 row created.

SQL> select * from detail
2 ;

I        QTY
- ----------
1 55555555.4
1 55555555.4
1 55555555.4
1 55555555.4
1 55555555.4
1 55555555.4
1 55555555.4
1 55555555.4

8 rows selected.

SQL> insert into detail values('1',55555555.456);

1 row created.

SQL> select * from detail;

I        QTY
- ----------
1 55555555.4
1 55555555.4
1 55555555.4
1 55555555.4
1 55555555.4
1 55555555.4
1 55555555.4
1 55555555.4
1 55555555.5

9 rows selected.

SQL> insert into detail values('1',55555555.056);

1 row created.

SQL> select * from detail;

I        QTY
- ----------
1 55555555.4
1 55555555.4
1 55555555.4
1 55555555.4
1 55555555.4
1 55555555.4
1 55555555.4
1 55555555.4
1 55555555.5
1 55555555.1

10 rows selected.

SQL> insert into detail values('1',55555555.406);

1 row created.

SQL> select * from detail;

I        QTY
- ----------
1 55555555.4
1 55555555.4
1 55555555.4
1 55555555.4
1 55555555.4
1 55555555.4
1 55555555.4
1 55555555.4
1 55555555.5
1 55555555.1
1 55555555.4

11 rows selected.
Different
SQL> alter table detail modify qty number(10,5);
alter table detail modify qty number(10,5)
                           *
ERROR at line 1:
ORA-01440: column to be modified must be empty to decrease precision or scale

SQL> truncate table detail;

Table truncated.

SQL> select * from detail;

no rows selected

SQL> alter table detail modify qty number(10.5);
alter table detail modify qty number(10.5)
                                      *
ERROR at line 1:
ORA-02017: integer value required

SQL> alter table detail modify qty number(10,5);

Table altered.

SQL> desc detail
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ID                                                 VARCHAR2(1)
QTY                                                NUMBER(10,5)

memorize: (10-5)=5 digit must be then (dot.) fraction more insert
SQL> insert into detail values('1',55555555.406);
insert into detail values('1',55555555.406)
                              *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SQL> insert into detail values('1',55555.40);

1 row created.

SQL> insert into detail values('1',555555.40);
insert into detail values('1',555555.40)
                              *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SQL> insert into detail values('1',55555.4585858585858558585);

1 row created.

KP automatically create by LPAD /RPAD

varcahr2(30)

SELECT NVL(MAX(TO_NUMBER(ISSUE_NO)),0)+1 into :br_issue_master.ISSUE_NO from br_issue_master;

SQL> create table TTT
(col varchar2(30));

SQL> select LPAD(NVL(MAX(TO_NUMBER(col))+1,0),6,'0')+1 ggggggggggggggggg from TTT;

---number wise insert
GGGGGGGGGGGGGGGGG
-----------------
                1

SQL> select   'QQQ'|| LPAD(NVL(MAX(TO_NUMBER(col))+1,0),6,'0') ggggggggggggggggg from TTT;
---varcahr2 wise insert

GGGGGGGGG
---------
QQQ000000
====================================================

create table TTT (col number(30));

SQL> select   LPAD(NVL(max(to_number(col)),0),6,'0')+1 from TTT;

LPAD(NVL(MAX(TO_NUMBER(CAL)),0),6,'0')+1
----------------------------------------
                                       1

SQL> select   LPAD(NVL(max(to_char(cal)),0),6,'0')+1 from RRR;

LPAD(NVL(MAX(TO_CHAR(CAL)),0),6,'0')+1
--------------------------------------
                                     1

different union all and join and Oracle more

1. what is different union all and join
UNION combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union. By using JOINs, you can retrieve data from two or more tables based on logical relationships between the tables.

2. What is the difference between union and union all?

The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table. A UNION statement effectively does a SELECT DISTINCT on the results set.

3. What is a union in SQL?

The SQL UNION Operator. The UNION operator is used to combine the result-set of two or more SELECT statements. Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types.

4. What are the set operators in SQL?

Set operators combine the results of two component queries into a single result. Queries containing set operators are called compound queries. Table 4-4 lists SQL set operators. They are fully described, including examples and restrictions on these operators, in "The UNION [ALL], INTERSECT, MINUS Operators"

Operator	Returns
`UNION`	All distinct rows selected by either query
`UNION ALL`	All rows selected by either query, including all duplicates
`INTERSECT`	All distinct rows selected by both queries
`MINUS`	All distinct rows selected by the first query but not the second

oracle lock system account

SQL> show user
USER is "SYS"
SQL> alter user sys account lock;

User altered.

SQL> select account_status from dba_users where username = 'SYS';

ACCOUNT_STATUS
--------------------------------
LOCKED

SQL> conn / as sysdba
Connected.
SQL> show user
USER is "SYS"

SQL> connect sys@ORCL as sysdba
Enter password:
Connected.

SQL> show user
USER is "SYS"
SQL> select account_status from dba_users where username = 'SYS';

ACCOUNT_STATUS
--------------------------------
LOCKED

Oracle Direct DMP Back up

SQL> conn test2/test2@orcl
Connected.
SQL> host imp

Import: Release 11.2.0.1.0 - Production on Fri Feb 24 17:25:10 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Username: test2/test2@orcl

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Import data only (yes/no): no >

Import file: EXPDAT.DMP >

IMP-00002: failed to open EXPDAT.DMP for read
Import file: EXPDAT.DMP > E:\backup\HRM_190915.DMP

Enter insert buffer size (minimum is 8192) 30720>

Export file created by EXPORT:V11.02.00 via conventional path

Warning: the objects were exported by INVFGNSW, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
List contents of import file only (yes/no): no >

Ignore create error due to object existence (yes/no): no >

Import grants (yes/no): yes >

Import table data (yes/no): yes >

Import entire export file (yes/no): no > y

Creating a User

CREATE USER LIMA IDENTIFIED BY LIMA;
GRANT CONNECT TO LIMA;
GRANT CONNECT, RESOURCE, DBA TO LIMA;
GRANT CREATE SESSION GRANT ANY PRIVILEGE TO LIMA;
GRANT UNLIMITED TABLESPACE TO LIMA;
GRANT SELECT, INSERT, UPDATE, DELETE ON schema.emp_mast TO LIMA;

Find all privileges a user

SQL>conn sys as sysdba
SQL> grant all privileges to LIMA;

Grant succeeded.

SQL> select count(*),grantee             ------------------------------see all privileges
2       from dba_sys_privs
3       where grantee in ('MDSYS','LIMA','SYS')
4       group by grantee;

COUNT(*) GRANTEE
---------- ------------------------------
        19 MDSYS
       200 LIMA
       200 SYS
SQL>conn LIMA/lima@orcl
SQL> create user kaium identified by kaium;

User Created

Thursday, February 23, 2017

Column Format

SQL> column slcode format a10
SQL> select * from supplier;

CODE   SLNAME
-------- ------------
111       kkkkkkkkk
1          rrr
3          gggggggggggggggggg

SQL>
SQL> delete from supplier where length(code) <'3'
2 /

2 rows deleted.

SQL> select * from supplier;

CODE   SLNAME
-------- ------------
111       kkkkkkkkk

SQL>

id & date wise update

1015	1/29/2017 4:26:55 PM	0006013149
1015	1/29/2017 4:39:02 PM	0006013149
1017	1/29/2017 9:56:31 AM	0002566302
1017	1/29/2017 10:28:42 AM	0002566302
update at_proc_mast a set in_time = (select min(TO_CHAR(To_Date(IN_TIME,'HH24:MI:SS'),'HH24MISS')) from access_log b a.emp_id=b.emp_id and b.at_dt=a.at_dt ) where at_dt='29-jan-17'

Wednesday, February 22, 2017

How to calculate no of days in an year

select

trunc(input_date, 'YYYY') as the_year,

add_months(trunc(input_date, 'YYYY'), 12)

-

trunc(input_date, 'YYYY') as days_in_year

from

(

select

add_months(sysdate, -12 * level) as input_date

from dual

connect by level <= 20

12 );

THE_YEAR DAYS_IN_YEAR

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

01-JAN-2009 12 00:00 365

01-JAN-2008 12 00:00 366

or

select to_date('01-JAN-'||to_char(to_number(to_char(sysdate,'YYYY'))+1),'DD-MM-YYYY')-trunc(sysdate,'YYYY') cnt

 from dual
/

or

select add_months(trunc(sysdate,'YYYY'),12) -trunc(sysdate,'YYYY') cnt from dual

select

    to_char(trunc(input_date, 'YYYY'), 'YYYY')  as "Year",
   add_months(trunc(input_date, 'YYYY'), 12)
   -
   trunc(input_date, 'YYYY')  as days_in_year
from
(
   select
      add_months(sysdate, -12 * level) as input_date
   from dual
   connect by level <= 20
)
order by "Year" desc
;

Year DAYS_IN_YEAR
---- ------------
2009          365
2008          366

Date details

SQL> select to_char(to_date(cir_dt,'DD-MON-RRRR'),'fmDay,Month,DD,RRRR') from cir_info;

TO_CHAR(TO_DATE(CIR_DT,'DD-
---------------------------
Saturday,February,25,2017

SQL> select to_char(to_date(cir_dt,'DD-MON-RRRR'),'Day,Month,DD,RRRR') from cir_info;

TO_CHAR(TO_DATE(CIR_DT,'DD-
---------------------------
Saturday ,February ,25,2017

SQL> select to_char(to_date(cir_dt,'DD-MON-RRRR'),'fmDay,Month,DD,RRRR') from cir_info;

TO_CHAR(TO_DATE(CIR_DT,'DD-
---------------------------
Saturday,February,25,2017

SQL> select to_char(to_date(cir_dt,'DD-MON-RRRR'),'fmDay,Month DD,RRRR') from cir_info;

TO_CHAR(TO_DATE(CIR_DT,'DD-
---------------------------
Saturday,February 25,2017

Friday, February 17, 2017

Oracle Privilages

SQL> SELECT username, privilege FROM USER_SYS_PRIVS;

USERNAME                       PRIVILEGE
------------------------------ --------------------------------
OE                             QUERY REWRITE
OE                             UNLIMITED TABLESPACE
OE                             CREATE SYNONYM
OE                             CREATE DATABASE LINK
OE                             CREATE MATERIALIZED VIEW
OE                             CREATE SESSION
OE                             CREATE VIEW

7 rows selected.

SQL>  SELECT GRANTEE, PRIVILEGE FROM DBA_SYS_PRIVS
  2    WHERE GRANTEE = 'SH';

GRANTEE                        PRIVILEGE
------------------------------ ----------------------------------------
SH                             CREATE SEQUENCE
SH                             ALTER SESSION
SH                             CREATE SYNONYM
SH                             CREATE TABLE
SH                             UNLIMITED TABLESPACE
SH                             CREATE SESSION
SH                             CREATE DATABASE LINK
SH                             CREATE CLUSTER
SH                             CREATE DIMENSION
SH                             CREATE VIEW
SH                             QUERY REWRITE

--Example: List all privilege that have “TABLE” keyword in it.

SQL> select * from system_privilege_map where NAME like '%TABLE%';

 PRIVILEGE NAME                                       PROPERTY
---------- ---------------------------------------- ----------
       -10 CREATE TABLESPACE                                 0
       -11 ALTER TABLESPACE                                  0
       -12 MANAGE TABLESPACE                                 0
       -13 DROP TABLESPACE                                   0
       -15 UNLIMITED TABLESPACE                              0
       -40 CREATE TABLE                                      0
       -41 CREATE ANY TABLE                                  0
       -42 ALTER ANY TABLE                                   0
       -43 BACKUP ANY TABLE                                  0
       -44 DROP ANY TABLE                                    0
       -45 LOCK ANY TABLE                                    0

 PRIVILEGE NAME                                       PROPERTY
---------- ---------------------------------------- ----------
       -46 COMMENT ANY TABLE                                 0
       -47 SELECT ANY TABLE                                  0
       -48 INSERT ANY TABLE                                  0
       -49 UPDATE ANY TABLE                                  0
       -50 DELETE ANY TABLE                                  0
      -213 UNDER ANY TABLE                                   0
      -243 FLASHBACK ANY TABLE                               0

18 rows selected.

---Users to roles and system privileges

select
  lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
  (
  /* THE USERS */
    select 
      null     grantee, 
      username granted_role
    from 
      dba_users
    where
      username like upper('%&enter_username%')
  /* THE ROLES TO ROLES RELATIONS */ 
  union
    select 
      grantee,
      granted_role
    from
      dba_role_privs
  /* THE ROLES TO PRIVILEGE RELATIONS */ 
  union
    select
      grantee,
      privilege
    from
      dba_sys_privs
  )
start with grantee is null
connect by grantee = prior granted_role;

---System privileges to roles and users

select
  lpad(' ', 2*level) || c "Privilege, Roles and Users"
from
  (
  /* THE PRIVILEGES */
    select 
      null   p, 
      name   c
    from 
      system_privilege_map
    where
      name like upper('%&enter_privliege%')
  /* THE ROLES TO ROLES RELATIONS */ 
  union
    select 
      granted_role  p,
      grantee       c
    from
      dba_role_privs
  /* THE ROLES TO PRIVILEGE RELATIONS */ 
  union
    select
      privilege     p,
      grantee       c
    from
      dba_sys_privs
  )
start with p is null
connect by p = prior c;

---OBJECT

select
  case when level = 1 then own || '.' || obj || ' (' || typ || ')' else
  lpad (' ', 2*(level-1)) || obj || nvl2 (typ, ' (' || typ || ')', null)
  end
from
  (
  /* THE OBJECTS */
    select 
      null          p1, 
      null          p2,
      object_name   obj,
      owner         own,
      object_type   typ
    from 
      dba_objects
    where
       owner not in 
        ('SYS', 'SYSTEM', 'WMSYS', 'SYSMAN','MDSYS','ORDSYS','XDB', 'WKSYS', 'EXFSYS', 
         'OLAPSYS', 'DBSNMP', 'DMSYS','CTXSYS','WK_TEST', 'ORDPLUGINS', 'OUTLN')
      and object_type not in ('SYNONYM', 'INDEX')
  /* THE OBJECT TO PRIVILEGE RELATIONS */ 
  union
    select
      table_name p1,
      owner      p2,
      grantee,
      grantee,
      privilege
    from
      dba_tab_privs
  /* THE ROLES TO ROLES/USERS RELATIONS */ 
  union
    select 
      granted_role  p1,
      granted_role  p2,
      grantee,
      grantee,
      null
    from
      dba_role_privs
  )
start with p1 is null and p2 is null
connect by p1 = prior obj and p2 = prior own;

list all privileges given to a user

select

  lpad(' ', 2*level) || granted_role "User, his roles and privileges"

from

  /* THE USERS */

    select

      null     grantee,

      username granted_role

    from

      dba_users

    where

      username like upper('%&enter_username%')

  /* THE ROLES TO ROLES RELATIONS */

  union

    select

      grantee,

      granted_role

    from

      dba_role_privs

  /* THE ROLES TO PRIVILEGE RELATIONS */

  union

    select

      grantee,

      privilege

    from

      dba_sys_privs

start with grantee is null

connect by grantee = prior granted_role;

display users with sysdba

--display users with sysdba & sysoper tips

select * from v$pwfile_users; --- sys power in oracle 11g 2

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE TRUE FALSE
HR                             TRUE TRUE FALSE
SYSDBA1                        TRUE FALSE FALSE
SYSOPER1                       FALSE TRUE FALSE

SQL> SELECT * FROM DBA_ROLES;

ROLE                           PASSWORD AUTHENTICAT
------------------------------ -------- -----------
CONNECT                        NO       NONE
RESOURCE                       NO       NONE
DBA                            NO       NONE
SELECT_CATALOG_ROLE            NO       NONE
EXECUTE_CATALOG_ROLE           NO       NONE
DELETE_CATALOG_ROLE            NO       NONE
EXP_FULL_DATABASE              NO       NONE
IMP_FULL_DATABASE              NO       NONE
LOGSTDBY_ADMINISTRATOR         NO       NONE
DBFS_ROLE                      NO       NONE
AQ_ADMINISTRATOR_ROLE          NO       NONE

ORA-01476: divisor is equal to zero

-- Correct your code so that you do not divide a number by zero.

To calculate percentage between field1 and field 2

1	`SELECT` `((FIELD1/FIELD2) *100)` `as` `Percentage` `from` `TableA;`

Now since the value of FIELD2 contains zero and any number divided by zero is infinity. So this will throw the exception:

ERROR at line 1:
ORA-01476: divisor is equal to zero

So how do you solve this. There are many ways to handle this error in Oracle.
1. The first and foremost way is to enforce the business logic and try to ensure that the field doesn’t contain a 0 in the first place.
2. Use the DECODE function

1	`DECODE(FIELD2,0,0,((FIELD1/FIELD2)*100))`

 This will return 0 in case the divisor is set to 0
3.User ZERO_DIVIDE to handle a zero divisor error
In PL/SQL you can trap the error using ZERO_DIVIDE option. The best way  to do it is replace the zero with a very small value like 0.00001
EXCEPTION
WHEN ZERO_DIVIDE THEN
:field2_var := 0.00001;
END;
Or alternately you can replace the output of the divide by zero equation with a zero return value.
EXCEPTION
WHEN ZERO_DIVIDE THEN
return 0;
END;

SELECT 
((COUNT(DECODE(SUBSTR(A.ASSETNUM,6,3),'ACS','ACS',0,null))/COUNT(DECODE(SUBSTR(A.PMNUM,1,3),'ACS','ACS',0,null)))*100)
FROM WORKORDER A 
WHERE TO_CHAR(A.REPORTDATE,'MON-YYYY') = :WO_DATE; 

GRANTEE, PRIVILEGE, ACCOUNT_STATUS

SELECT GRANTEE,

PRIVILEGE,

ACCOUNT_STATUS

FROM (SELECT GRANTEE,

LTRIM(MAX(SYS_CONNECT_BY_PATH(PRIVILEGE, ', ')), ', ') PRIVILEGE

FROM (SELECT GRANTEE,

PRIVILEGE,

ROW_NUMBER() OVER(PARTITION BY GRANTEE ORDER BY PRIVILEGE) RN

FROM (SELECT DISTINCT NVL(A.GRANTEE, B.GRANTEE) GRANTEE,

NVL(A.PRIVILEGE, B.PRIVILEGE) PRIVILEGE

FROM (SELECT A.GRANTEE,

A.PATH PRIVILEGE

FROM (SELECT A.GRANTEE,

A.GRANTED_ROLE_ROOT PRIVILEGE,

A.PATH,

A.NIVEL,

RANK() OVER(PARTITION BY A.GRANTEE, A.FIRST_ROLE ORDER BY NIVEL ASC) RANK

FROM (SELECT A.GRANTEE,

GRANTED_ROLE FIRST_ROLE,

CONNECT_BY_ROOT GRANTED_ROLE GRANTED_ROLE_ROOT,

'(' || LTRIM(SYS_CONNECT_BY_PATH(GRANTED_ROLE, '->'), '->') || ')' PATH,

LEVEL NIVEL

FROM DBA_ROLE_PRIVS A

CONNECT BY PRIOR GRANTEE = GRANTED_ROLE) A,

DBA_SYS_PRIVS B

WHERE A.GRANTEE NOT IN (SELECT ROLE

FROM DBA_ROLES)

AND A.GRANTED_ROLE_ROOT = B.GRANTEE

AND (B.PRIVILEGE LIKE 'DROP ANY%' OR B.PRIVILEGE LIKE 'GRANT%' OR B.PRIVILEGE IN ('ADMINISTER DATABASE TRIGGER'))) A

WHERE A.RANK = 1) A

FULL OUTER JOIN (SELECT GRANTEE,

PRIVILEGE

FROM DBA_SYS_PRIVS

WHERE (PRIVILEGE LIKE 'DROP ANY%' OR PRIVILEGE LIKE 'GRANT%' OR PRIVILEGE IN ('ADMINISTER DATABASE TRIGGER'))

AND GRANTEE NOT IN (SELECT ROLE

FROM DBA_ROLES)) B ON B.GRANTEE = A.GRANTEE))

START WITH RN = 1

CONNECT BY PRIOR RN = RN - 1

AND PRIOR GRANTEE = GRANTEE

GROUP BY GRANTEE) A,

DBA_USERS B

WHERE A.GRANTEE = B.USERNAME

ORDER BY 1;

Oracle Apex, Forms, Reports

Tuesday, February 28, 2017

Master Detail Relation in oracle Tables

Sunday, February 26, 2017

create a foreign key

Saturday, February 25, 2017

cleanup the schema Be care full Details

How to truncate all user tables

Create table by Select Statement in oracle

Friday, February 24, 2017

drop table detail purge

Decimal & number Confusion

KP automatically create by LPAD /RPAD

different union all and join and Oracle more

oracle lock system account

Oracle Direct DMP Back up

Creating a User

Find all privileges a user

Thursday, February 23, 2017

Column Format

id & date wise update

Wednesday, February 22, 2017

How to calculate no of days in an year

Date details

Friday, February 17, 2017

Oracle Privilages

list all privileges given to a user

display users with sysdba

--display users with sysdba & sysoper tips

select * from v$pwfile_users; --- sys power in oracle 11g 2

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE TRUE FALSE
HR                             TRUE TRUE FALSE
SYSDBA1                        TRUE FALSE FALSE
SYSOPER1                       FALSE TRUE FALSE

ORA-01476: divisor is equal to zero

GRANTEE, PRIVILEGE, ACCOUNT_STATUS

How to install and configure Oracle Apex 24.1 with ORDS 22, Tomcat 9 and Jasper Report 7 on Oracle Linux 8.10

Total Pageviews

Pages

Tuesday, February 28, 2017

Sunday, February 26, 2017

Saturday, February 25, 2017

How to truncate all user tables

Friday, February 24, 2017

Thursday, February 23, 2017

Wednesday, February 22, 2017

Friday, February 17, 2017

--display users with sysdba & sysoper tips

select * from v$pwfile_users; --- sys power in oracle 11g 2

USERNAME SYSDB SYSOP SYSAS------------------------------ ----- ----- -----SYS TRUE TRUE FALSEHR TRUE TRUE FALSESYSDBA1 TRUE FALSE FALSESYSOPER1 FALSE TRUE FALSE

USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE
HR TRUE TRUE FALSE
SYSDBA1 TRUE FALSE FALSE
SYSOPER1 FALSE TRUE FALSE