Tom Kyte

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

XMLIndex on In-Memory Variables

Thu, 2016-10-06 04:06
Hi, I went through the articles on XMLIndex, which will be created on the table - with XMLType columns. If i am processing PL/SQL package - procedure or functions - with XMLType variables passed through different procedures, can i make use of XM...
Categories: DBA Blogs

XMLROOT

Thu, 2016-10-06 04:06
Hello Oracle Guruji Thanks for your support in Advance, I have one doubt on XMLROOT, this is my query ops$tkyte@ORA9IR2>select xmlroot(XMLELEMENT("DriverDescription",'$'||'$'||'s partner payout by PL '||' ='||'>' ||' L2 BU'),VERSION '1.0')as emp ...
Categories: DBA Blogs

Oracle bulk collect/ forall for dynamic usage

Thu, 2016-10-06 04:06
Hi Tom, Thanks to all the great comments provided in the below link : https://asktom.oracle.com/pls/apex/f?p=100:11:114010771330237::::P11_QUESTION_ID:3675533064673 I am currently looking for a solution to a problem in similar lines. Inste...
Categories: DBA Blogs

How to get Unlocked records from a table

Thu, 2016-10-06 04:06
Hi Tom, i am a beginner in Oracle. I want to lock the records while we update in a stored procedure. Meanwhile if any other user trying to fetch the same record which is locked, i should not allow him to fetch that record. As per my knowledge i ca...
Categories: DBA Blogs

datafile block fractured

Thu, 2016-10-06 04:06
Hi, Can you pls let me know what is the difference between - select * from v$database_block_corruption; corruption column showing - corruption or fractured What is corruption and what is fractured How to resolve corruption or fra...
Categories: DBA Blogs

View Syntax isn't working

Thu, 2016-10-06 04:06
Tom, I have an existing view that works fine. It is used to compare rows in two tables and to display which table has records that the other doesn't. I've been asked to add one more column to this view from a new table. For one reason or ano...
Categories: DBA Blogs

Temporary tables (comparision with SQLServer)

Thu, 2016-10-06 04:06
We are migrating some Stored Procedures from Sybase 11 to Oracle 8i. Most of them use temporal tables like: SELECT x, y, z INTO #Temp FROM some_table ... -OR- CREATE TABLE #Temp ( .... ) Where #Temp is a Temporal Table in a temporal area wh...
Categories: DBA Blogs

How to size the log_buffer

Thu, 2016-10-06 04:06
Hello In looking into how to tune log_buffer, I came across two different warnings. One claims that if the log buffer is too small, we will get a significant amount of "log buffer space." The solution is to "consider making the log buffer bigger...
Categories: DBA Blogs

SQL Loader- Multibyte character error

Wed, 2016-10-05 09:46
Hi Team, When I am trying to load an UTF8 characterset datafile using sqlldr, it fails with Multibyte character error. Steps to reproduce the issue: 1. Create table. create table LoaderTest( rec_no number, rec_id_no nvarchar2(30), col...
Categories: DBA Blogs

How to export .xls file with table data as a attachment from Plsql block

Wed, 2016-10-05 09:46
We are using below code for excel property p_file_name:='demo.xls'; UTL_TCP. write_line (v_tcp_connection, 'Content-Type: application/ms-excel;'); ----- 2ND BODY PART. UTL_TCP. write...
Categories: DBA Blogs

Get dates between given periods

Wed, 2016-10-05 09:46
I have table tt with columns: skey number, start date, end date Start date and end date will always be month end dates. data will be 1 31/01/2016 31/03/2016 2 29/02/2016 31/03/2016 3 29/02/2016 30/04/2...
Categories: DBA Blogs

SQL to display colours for values

Wed, 2016-10-05 09:46
Hi, create table test code number(5)); insert into text values (1); insert into text values (1); insert into text values (2); insert into text values (2); insert into text values (3); insert into text values (4); insert into text values (4...
Categories: DBA Blogs

MATCH_RECOGNIZE and matching to multiple pattern

Wed, 2016-10-05 09:46
Hi! I've been playing with MATCH_RECOGNIZE feature in 12c and I've encountered situation which I'm not able to explain myself - so I'd like to ask you for support. Please find the script here: https://livesql.oracle.com/apex/livesql/s/dyhcdwiuu...
Categories: DBA Blogs

Displaying data in timetable grid

Wed, 2016-10-05 09:46
Hi Guys Really hope you can help with an issue that I have. I use the code below to output the days of the current week (stored in a table called dates) <code>SELECT to_char(NEXT_DAY(SYSDATE,'MON') + case when to_char(SYSDATE,'D') in (6,7) t...
Categories: DBA Blogs

Scroll to Right

Wed, 2016-10-05 09:46
Hi Chris & Connor it is not a question. While reading ask tom's old question answers there are a lot of scroll to right. Very difficult to read conversation that is scrolling to long right. I stop reading when is see right scroll. Can you f...
Categories: DBA Blogs

SQL and/or PL/SQL in ODI vs in the Database

Wed, 2016-10-05 09:46
This is sort of a #ThickDB question. I can put functions, procedures, etc. in ODI for it to execute on the database or I can put the functions, procedures, etc. in the database and have ODI execute them when appropriate. I've tried this both ways and...
Categories: DBA Blogs

BLOB aggregation

Wed, 2016-10-05 09:46
Hi Tom! I've got the table: <code>create table example (id number, image varchar2(10));</code> with these two rows: <code>insert into example (id, image) values (25, 'pippo'); insert into example (id, image) values (32, 'pluto');</c...
Categories: DBA Blogs

Daywise partition automatically

Wed, 2016-10-05 09:46
Hi, 1.I have table with huge data so I have created the index, gathering stats, rebuilding index but it is very slow now I have seen the optimiser plan it is showing create partition index So can you pls help me to know what is partition index and...
Categories: DBA Blogs

PGA

Tue, 2016-10-04 15:26
Hi Tom, Could you tell me,Is PGA Resides inside instance or outside intance? Thanks in advance.
Categories: DBA Blogs

To send mail at a particular time everyday

Tue, 2016-10-04 15:26
I have a table(TBL_LOG) that store the status of all my packages. I want the contents of the table to mailed to me daily. This should happen as a part of the package that is being triggered at last. I am new learner. Please help.
Categories: DBA Blogs

Pages