DBA Blogs

fetch output (success/failure) status from web service

Tom Kyte - Fri, 2017-11-03 08:26
Hi, Could you please share any example to fetch web service output (i.e. success/failure) status into oracle PL/SQL procedure? The scenario is as below, we have created a stored procedure which will pass 2 input parameters from those input p...
Categories: DBA Blogs

Merge - unfold records based on conditional join

Tom Kyte - Thu, 2017-11-02 14:06
Hi Team, Need your help or suggestion on altering a merge statement. I have a below staging table A_TRANSACTION_STAGING which gets merged to main table A_TRANSACTION : A_TRANSACTION_STAGING : <code> TRANSACTION_ID NUMBER REGION_CD ...
Categories: DBA Blogs

SQL Query related to String

Tom Kyte - Thu, 2017-11-02 14:06
Hi Tom, There is a string 'ascjhsdndfdaja' i want to print only 'a' alphabet from this string there are 3-occurrence of 'a' so i want to print 'aaa' can you please help me this. Your help will be much appriciated. Thanks
Categories: DBA Blogs

trim in sql*plus

Tom Kyte - Thu, 2017-11-02 14:06
Hi Tom, I have a varchar2(30) field which when displayed on sqlplus, doesn't seem to be trimming the trailing spaces when I use rtim or trim in select stmt: set head off set colsep "," set trim on set wrap off set linesize 800 select part_id...
Categories: DBA Blogs

Two Talks Accepted for RMOUG Training Days

Bobby Durrett's DBA Blog - Thu, 2017-11-02 14:01

I got two talks accepted for RMOUG Training Days in February. I mentioned these two titles in a earlier post:

  • Python for the Oracle DBA
  • Toastmasters for the Oracle DBA

These two talks are about topics that interest me so I am glad that RMOUG thinks that they are valuable to the conference attendees.

I plan to do the two talks for my DBA coworkers and shorter versions at Toastmasters so I should get some constructive feedback and practice before the conference.

Should be fun. Hope to see you in Denver next February.

My Python posts: url

My Toastmasters posts: url

Bobby

Categories: DBA Blogs

Limitations of REGEXP functions

Tom Kyte - Wed, 2017-11-01 19:46
Tom: I am trying to use this function to validate email addresses. I am reading that the regexp functions have a limitation of 512 bytes. Is there a way around this or a better way? Thanks! Mitchell <code>CREATE OR REPLACE FUNCTION is_valid_e...
Categories: DBA Blogs

Index organized table, secondary indexes, and stale guesses

Tom Kyte - Wed, 2017-11-01 19:46
Hi, Tom Secondary indexes of IOT have physical guesses. However guesses can become stale after many inserts. It is two ways to obtain fresh guesses: 1. Alter index ... rebiuld and 2. ALTER INDEX ... UPDATE BLOCK REFERENCES. 3. What is be...
Categories: DBA Blogs

How to calculate free space in the Database

Tom Kyte - Wed, 2017-11-01 19:46
How to calculate the actual database size and the free space in the database. I tried below queries <code>select round(sum(bytes)/1024/1024/1024) size_in_gb from dba_data_files; = 1073 GB select round(sum(bytes)/1024/1024/1024) size_in_gb from dba...
Categories: DBA Blogs

Oracle Cloud Application Development Platform 17.4.2 Anncounced

We are excited to announce the new enhancements to Oracle Application Container Cloud and Oracle Java Cloud Service that were rolled out as part of the 17.4.2 release. New Language runtime Support...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Retrieve fields from clob field with envelope and HTML chacaters

Tom Kyte - Wed, 2017-11-01 01:26
Dear I have tried to parse a clob field located in a Oracle table that contains a envelope and also some HTML characters (&quot, &lt, &gt), as shown below: <?xml version="1.0" encoding="utf-8"?><soapenv:Envelope xmlns:soapenv="http://schemas.xm...
Categories: DBA Blogs

Bulk Inserts with CLOB column Slower in Oracle 12c than Oracle 11g

Tom Kyte - Wed, 2017-11-01 01:26
We are noticing that large table inserts with CLOB is taking more time on 12c than 11g. The inserts are during an online transaction processing where we copy data from one business unit to another. There is one table in particular that is taking cons...
Categories: DBA Blogs

Get "LPX-00242: invalid use of ampersand" from xmltype() even though ampersand is already escaped

Tom Kyte - Wed, 2017-11-01 01:26
Any idea why I get the error message "LPX-00242: invalid use of ampersand ('&') character (use &amp;)" even though the ampersand in the below test case is already escaped? Tested with same result both in 11g XE, 11.2.0.4 SE, and on apex.oracle.com (w...
Categories: DBA Blogs

Implementing Old style SQL outer join along with condition query to the new style

Tom Kyte - Tue, 2017-10-31 07:06
Hi, I have SQL outer join query which is written in old format with (+) notation and I am working on its migration to the new presentation and without the (+) notation. I am unable to convert the last line of the query with the outer join condition....
Categories: DBA Blogs

Need to print latin ä charater through Dbms out into apex sql developer ?

Tom Kyte - Tue, 2017-10-31 07:06
Hi gurus, I am trying to print this character a through apex sql developer but it return me ? instead of this a , any one know how can I achieve this. begin dbms_output.put_line ('http://fam.co/') ; end; if my question is not clear please...
Categories: DBA Blogs

two stage RMAN backups

Tom Kyte - Tue, 2017-10-31 07:06
Hi team. We have an Oracle instance, which I think is using ASM on SAN LUNs. The SAN is multi-path, multi-fabric with the LUNs are provisioned from a high quality H/A storage array. At the moment the DBAs use RMAN to push backups via MML directly ...
Categories: DBA Blogs

Make a simple audit of table connections

Tom Kyte - Mon, 2017-10-30 12:46
Dear Tom. I want to know which tables in my Oracle schema are impacted by any software, web application or direct queries. An provider said me : " Make an audit on all tables (and views) of your schemas is not a good idea because your DB will w...
Categories: DBA Blogs

Data Model (Design) Question

Tom Kyte - Mon, 2017-10-30 12:46
I have a around 2 million assets that send 15 min interval data for a given day (i.e) total of 96 intervals for a given day. What is best database modeling approach. My target RDBMS is Oracle. I'm partitioning by week and for each interval I need ...
Categories: DBA Blogs

ASSM and basicfile LOB

Tom Kyte - Mon, 2017-10-30 12:46
Hello Tom According to https://docs.oracle.com/database/121/ADLOB/adlob_smart.htm#ADLOB46175 basicfile lob must be stored in a tablespace with segment space manually managed. However, it seems oracle does not prevent user from creating it in ...
Categories: DBA Blogs

Install Apex on Database Vault

Tom Kyte - Mon, 2017-10-30 12:46
Dear Tom, I am trying to install oracle apex 5.1.3 on oracle database 12c 12.1 release which is vault enabled. I was complete my apex install all are done successfully. but when i call on browser a messages shown is (Connection was reset). ...
Categories: DBA Blogs

Does update query returns affected row(s) value in key holder ?

Tom Kyte - Mon, 2017-10-30 12:46
Hi TOM, Does Oracle returns affected rows for update SQL statement in key holder? For insert query we supposed to receive the auto generated key. Such a way update query returns the affected rows id ?
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs