Check Difference Between Two Database Objects

Step-1. Create Two DBLINK to connect with Two Database then

Step-2.

define logindb=DBLINKONE

define remotedb=DBLINKTWO

define schema_name=SCHEMA_NAME

clear col breaks compute

break on OBJECT_TYPE skip 1 

col src_name format a40

col tgt_name format a40

col object_type format a20

col src_length format 999,999

col tgt_length format 999,999

col diff format 999,999

 

With source_tbl as

(SELECT /*+ MATERIALIZE */ type, name

, standard_hash ( LISTAGG(text, ' ' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY line) , 'SHA1') hash_value

, sum(length(text)) text_length

, max(line) source_lines

FROM     dba_source@&&logindb

where 1 = 1

and owner = '&&schema_name'

and name not like 'BIN$%'

--and type = 'PROCEDURE'

group by type, name)

, target_tbl as

(SELECT /*+ MATERIALIZE */ type, name

, standard_hash ( LISTAGG(text, ' ' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY line) , 'SHA1') hash_value

, sum(length(text)) text_length

, max(line) source_lines

FROM     dba_source@&&remotedb

where 1 = 1

and owner = '&&schema_name'

and name not like 'BIN$%'

--and type = 'PROCEDURE'

group by type, name)

select nvl(a.type, b.type) object_type, a.name src_name, b.name tgt_name

, nvl(a.text_length,0) src_length, nvl( b.text_length,0) tgt_length 

, abs ( nvl(a.text_length,0) - nvl(b.text_length,0) )  diff

, GREATEST ( a.source_lines, b.source_lines) max_source_lines

, abs ( a.source_lines -  b.source_lines) diff_source_lines

from source_tbl A full outer join target_tbl B

on (a.name = b.name

    and a.type = b.type)

where 1= 1 

--and (a.hash_value <> b.hash_value

--    or nvl(a.text_length,0) <> nvl(b.text_length,0) )

--objects that are available in both environment

and (a.name is not null and b.name is not null)

and abs ( nvl(a.text_length,0) - nvl(b.text_length,0) ) > 10

order by nvl(a.type, b.type), nvl(a.name, b.name)

/ 

Reorganize Oracle Database Table Column Position Without Recreating Table

Reorganize Column Position Without Recreating Table.

Step_1. 

Create a table:

CREATE TABLE test_table (

    a INT,

    b INT,

    d INT,

    e INT

);

Step_2.

Add a column:

ALTER TABLE test_table  ADD (c INT);

Step_3.

Move the column to the middle:

ALTER TABLE test_table  MODIFY (d INVISIBLE, e INVISIBLE);

ALTER TABLE test_table MODIFY (d VISIBLE, e VISIBLE);

Step_4.

DESCRIBE test_table;

Name

----

A

B

C

D

E

Difference Between SOAP and REST API

 

Difference Between SOAP and REST API

Restrict File Upload by File Type in Oracle Apex

If you want to restrict file upload by file type/extension/format you can follow the below steps. 

Goto File Browser Item --> Advanced --> Custom Attributes 

Then pest below code :

accept=".pdf,.csv,.xlsx"

Here it means only pdf , csv and xlsx file will be acceptable for upload. 

If you want to accept only pdf then you have to put like below code as the screenshot given:

accept=".pdf"

  Double-click on the image to view it on a big screen.

That's it. Now you can go and check. 

Database DML from frontend in Oracle Apex

Application users can able to do DML from the frontend side. 

 1. Create a region and create 7 item like below and a button call Convert 

 

P23_UPDATE_DELETE 

P23_COLUMN_NAME 

P23_CONDITION 

P23_CURRENT_DATE 

P23_UPDATE_COLUMN 

P23_TARGET_DATE 

P23_UPDATE_COUNT 

 

Convert 

 

 

 

2. P23_UPDATE_DELETE  

 

will be a radio group with Update-U and Delete-D 

 

 

 

3. P23_COLUMN_NAME 

 

select  COLUMN_NAME R, COLUMN_NAME D  

from all_tab_columns col 

WHERE  

OWNER='SCHEMA_NAME' AND  

TABLE_NAME='TABLE_NAME' AND  

COLUMN_NAME NOT IN (COLUMNS YOU WANT TO AVOID); 

 

 

 

 

 

4.P23_CONDITION 

 

Select list -> Equals = , Not Equals <> , Is Empty (IS NULL),Is No Empty (IS NOT NULL), Greater Than > , Less Than < , 

Greater Than Equals >=, Less Than Equals <= , IN , NOT IN 

 

 

5. P23_UPDATE_COLUMN 

 

select  COLUMN_NAME R, COLUMN_NAME D  

from all_tab_columns col 

WHERE  

OWNER='SCHEMA_NAME' AND  

TABLE_NAME='TABLE_NAME' AND  

COLUMN_NAME NOT IN (COLUMNS YOU WANT TO AVOID); 

 

 

6. Create a process on the Convert Button 

 

DECLARE 

V_QUERY VARCHAR2(4000):= NULL; 

BEGIN 

 

IF :P23_UPDATE_DELETE='U' THEN  

 

V_QUERY:='UPDATE SCHEMA_NAME.TABLE_NAME SET '||:P23_UPDATE_COLUMN||' = '''||:P23_TARGET_DATE||''' 

WHERE '||:P23_COLUMN_NAME||' '||:P23_CONDITION||'  

'||CASE WHEN UPPER(:P23_CONDITION) ='IS NULL' THEN NULL  

WHEN UPPER(:P23_CONDITION) ='IS NOT NULL' THEN NULL 

WHEN UPPER(:P23_CONDITION) ='IN' THEN '('||:P23_CURRENT_DATE||')' 

WHEN UPPER(:P23_CONDITION) ='NOT IN' THEN '('||:P23_CURRENT_DATE||')' 

WHEN UPPER(:P23_CONDITION) ='BETWEEN' THEN :P23_CURRENT_DATE ||' AND '|| :P23_BETWEEN 

ELSE :P23_CURRENT_DATE END ||'  

AND UPPER(UPLOADED_BY)= '''||UPPER(:APP_USER)||''''; 

 

ELSIF :P23_UPDATE_DELETE='D' THEN  

 

V_QUERY:='DELETE FROM UPDATE SCHEMA_NAME.TABLE_NAME WHERE '||:P23_COLUMN_NAME||' '||:P23_CONDITION||'  

'||CASE WHEN UPPER(:P23_CONDITION) ='IS NULL' THEN NULL  

WHEN UPPER(:P23_CONDITION) ='IS NOT NULL' THEN NULL 

WHEN UPPER(:P23_CONDITION) ='IN' THEN '('||:P23_CURRENT_DATE||')' 

WHEN UPPER(:P23_CONDITION) ='NOT IN' THEN '('||:P23_CURRENT_DATE||')' 

WHEN UPPER(:P23_CONDITION) ='BETWEEN' THEN :P23_CURRENT_DATE ||' AND '|| :P23_BETWEEN 

ELSE :P23_CURRENT_DATE END ||'  

AND UPPER(UPLOADED_BY)= '''||UPPER(:APP_USER)||''''; 

 

END IF ; 

 

 

EXECUTE IMMEDIATE V_QUERY ;  

 

 

IF SQL%ROWCOUNT > 0 THEN 

:P23_UPDATE_COUNT:=SQL%ROWCOUNT||' Records Successfully Updated!'  ; 

ELSE  

:P23_UPDATE_COUNT:=NULL; 

END IF; 

 

END ; 

 

-----> Put it &P23_UPDATE_COUNT.  on Process Success Message