Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 12 hours 37 min ago

Anyway to Access Data in a Table That Has Been Made Invalid By Dropping an XMLSchema

Fri, 2016-07-08 03:26
Is here anyway to make a table valid again if it has an XMLType column with an XMLSchema associated it which has been deleted via dbms_xmlschema.deleteSchema with a DELETE_INVALIDATE parameter? I cannot even successfully drop the XMLType column from...
Categories: DBA Blogs

LEFT JOIN on nested table and TABLE function: unexpected results

Fri, 2016-07-08 03:26
Hi! This is my first question here, so don't hesitate to tell me if I am unclear or if key information is missing. I have been getting unexpected results while trying a LEFT JOIN on a nested table. Here's my tables: <code>CREATE OR REPLACE ...
Categories: DBA Blogs

utl_http results to ORA-12541: TNS:no listener

Fri, 2016-07-08 03:26
Hi, I have 11g DB and I have url below that I can open in the browser displaying pdf. This is pdf report generated from Jasper server. http://serv-ora11g:8081/jasperserver/flow.html?_flowId=viewReportFlow&j_username=jasperadmin&j_password=jaspe...
Categories: DBA Blogs

utl_htp is begin_request is resulting to ORA-29263: HTTP protocol error

Fri, 2016-07-08 03:26
Hi, I have URL that I can access with a browser and this displays the PDF http://serv-ora11g:8081/jasperserver/flow.html?_flowId=viewReportFlow&j_username=jasperadmin&j_password=jasperadmin&reportUnit=/KVS/Collection/col1190&output=pdf I c...
Categories: DBA Blogs

Cross Platform "Endian" Conversion from Solaris to Linux Intel-Based Exadata -- Mounting NFS to Exadata Machine

Fri, 2016-07-08 03:26
Hi, Is it possible for a Solaris NFS to be mounted on a Linux Intel-Based Exadata machine? This approach seems (at least to me) to be alluded to in this section of Oracle Database Backup and User's Guide: https://docs.oracle.com/cd/E11882_01/back...
Categories: DBA Blogs

Creating tables (maybe)

Fri, 2016-07-08 03:26
How to create a table for this one. AAA BBB CCC RED 1 2 3 BLUE 4 5 6 GREEN 7 8 9 I tried on SQL DEVELOPER.It's gave me an error. And converted into an out put like.. RED BLUE GREEN AAA 1 4 ...
Categories: DBA Blogs

cursor for loop

Fri, 2016-07-08 03:26
Hi, I have a cursor for loop which does some calculation in the cursor query. With in the loop I have insert with commit for every iteration. There are not more statement apart from INSERT & COMMIT inside the loop. Example: DECLARE cursor...
Categories: DBA Blogs

Dba vs Apps DBA

Fri, 2016-07-08 03:26
What is different between Oracle DBA and Oracle apps DBA? I so many websites where some of them used term Oracle DBA and some of them Oracle apps DBA?
Categories: DBA Blogs

Undo Table space

Fri, 2016-07-08 03:26
Hi Tom, Following are the scenarios and please let me know what will happen in the listed scenarios : 1. I have a Undo table space with least size, i do a deleting of records in a table which is huge in size(a size which undo tablespace ca...
Categories: DBA Blogs

Website validation using regexp_like.

Thu, 2016-07-07 09:06
Hi, Could you please help to get website validation query using regexp_like. e.g. www.google.com https//:www.google.com both should get validated. Thanks in advance.
Categories: DBA Blogs

Datatype issue.

Thu, 2016-07-07 09:06
Hi all, I have an issue with column datatype.Can you please look at this issue. I have two tables emp1 and emp2 with empno column in both tables in table emp1 column empno is nvarchar2 and in table emp2 column empno is number datatype I am tryin...
Categories: DBA Blogs

how to debug the SQL logic execution ?

Thu, 2016-07-07 09:06
AskTom, I have a question about the 'SQL logic execution': Can Oracle Database log the logic of the SQL execution ? (which condition of the WHERE clause failed ?) example: select 1 from Dual WHERE 1 = 1 AND 1 = 1 + 1 --line 5 ; C...
Categories: DBA Blogs

Fuzzy Name Match Stored Procedure Optimization

Thu, 2016-07-07 09:06
Hello, Have written PL/SQL stored proc 'FuzzyNameMatch' that interrogates first, middle, last names from a single column in two distinct tables, ie source and compare columns. The algo parses shorter strings through longer and increments counter v...
Categories: DBA Blogs

Need to generate numbers between a given range for each record.

Thu, 2016-07-07 09:06
Hi Team, I have below sample table as input : ID MIN MAX 1 5 10 2 3 5 And I want output as follows: ID value 1 5 1 6 1 7 1 8 1 9 2 3 2 4 Id column is a p...
Categories: DBA Blogs

Performance & features of OCCI vs OCI

Thu, 2016-07-07 09:06
I'm going to develop an application that needs max performance. I will need: - batch inserts of BLOB - batch updates of BLOB - batch select of BLOB I consider to use OCCI but I'm not sure if it supports all optimizations that are done in OCI. ...
Categories: DBA Blogs

Materialized view logs and unique indexes

Thu, 2016-07-07 09:06
I have a table with moderate DML activity, a materialized view log on that table, and one materialized view on a remote database. After some activity in the source app, I can see multiple rows in the MV log with the same m_row$$, with a DMLTYPE$ v...
Categories: DBA Blogs

Best Data Type To use

Thu, 2016-07-07 09:06
I've a Table with columns EMPCODE,DAY1,DAY2...DAY31 I've to loop from Day1 to Day31 so what is the best way of doing this,If I should use collections then how should I assign Day1--31 to a Collection and what Collection Should I use. Can anyone p...
Categories: DBA Blogs

Oracle doubts

Wed, 2016-07-06 14:46
c d f ---- ---- ---- 1 a 100 1 b 200 Output: 1 a 100 b 200
Categories: DBA Blogs

cannot fetch plan for SQL_ID: 9m7787camwh4m, CHILD_NUMBER: 0

Wed, 2016-07-06 14:46
I am using PL/SQL in front end and Oracle 11gR2 in back end to developing a db app. In the middle of time, I am going to check hard parsed execution plan of one sql statment. What make me surprised is that the PL/SQL Developer seems has no capability...
Categories: DBA Blogs

Do bind variables in APEX trigger row by row processing?

Wed, 2016-07-06 14:46
To start this off, I kind of have a solution for my problem, I'm just trying to understand the mechanics behind it. Ok, here's the set-up: - I have a very simple APEX page: a report on a view + 2 date pickers to filter the data; - The query is a...
Categories: DBA Blogs

Pages