Here is an SQL, which demonstrates the use of Row_Number() function:with places_table as (select 'Africa' continent, 'Nigeria' country, 'Lagos' city from dualunionselect 'Africa' continent, 'Nigeria' country, 'Abuja' city from dualunionselect…
Here are 2 examples of how to delete or replace xml part: Supposing that we have a variable l_xmltype of type XMLTYPE containing an XML: --delete XML;select xmltype(deletexml(xmltype('<s:a xmlns:s="qw"><s:b><return></return></s:b></s:a>'),…
Here is how to program a scheduled job in Oracle DB to run at specific date and time:The following job will run at 17:00 every day:SET DEFINE OFF;DECLARE X NUMBER;BEGIN …
Here is how to query the dependencies of an object in Oracle DB:(in this example, which tables a package specification is depended on)select /*+ NO_PARALLEL */ * from (SELECT DISTINCTb.object_name…
Find out which objects are locked using the following query: select substr(a.os_user_name,1,15) "OS User", substr(a.oracle_username,1,8) "DB User", substr(b.owner,1,8) "Schema", substr(b.object_name,1,20) "Object Name", substr(b.object_type,1,10) "Type", substr(c.segment_name,1,15) "RBS", substr(d.used_urec,1,12) "# of Records", …
You can find the number of uncommited transactions using the following query:SELECT ROWS_PROCESSED,sql_textFROM v$sqlareaWHERE (address, hash_value) IN(SELECT sql_address, sql_hash_valueFROM v$sessionWHERE sid in (select sid from v$session where schemaname='VAG'))or using v$transaction…
Some sample commands for Add/Drop/Rename objects:--Dropping the primary key:ALTER TABLE "VAG"."TEST_TABLE" drop primary key;drop index "VAG"."TEST_TABLE_PK";--Adding primary key:CREATE UNIQUE INDEX "VAG"."TEST_TABLE_PK" ON "VAG"."TEST_TABLE" ("TEST_TABLE_ID");ALTER TABLE "VAG"."TEST_TABLE" ADD CONSTRAINT "TEST_TABLE_PK" PRIMARY…
For exporting the DDL of data objects without the storage specifications, this is what we should do:EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);select DBMS_METADATA.GET_DDL('TABLE','PROJECTS') from DUAL;EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT');
This is how to get the size of a BLOB in SQL query:select file_id, doc_xml, dbms_lob.getlength(doc_xml) from my_files order by dbms_lob.getlength(doc_xml) desc
The following examples are applied on Oracle DB, but they can be used also with another language, e.g. Java.The last examples illustrate in particular SOAP envelope xml extraction.Simple Examles:select xmltype('<a><b><return>vag</return></b></a>').extract('/a/b/text()').getstringval()…