Tom Kyte

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

How you manage you're work-spaces!!

Thu, 2017-10-12 23:06
Greetings Team, I am very curious to know how you actually manage you're work spaces.I mean you always face different kind of questions all day along.How many laptops you use(of course you are using VM). But my question is if you guide someone fo...
Categories: DBA Blogs

SQL * Loader connection errors

Thu, 2017-10-12 23:06
To previous answer to MY SQL*Loader question I had asked, you said: Examples of SQL*Loader String <code>sqlldr.exe userid=my_user/my_password@some_string control=my_control.ctl sqlldr.exe userid=scott/tiger@//my_host:my_port/my_service_name con...
Categories: DBA Blogs

ORA-01722: invalid number error in decode

Thu, 2017-10-12 04:46
1. select decode('a',1,1,2) from dual --------> O/P Error saying Invalid Number 2. select decode(1,'a',1,2) from dual --------> O/P : 2 what is the difference in the above two cases and also please explain the execution process? Thanks I...
Categories: DBA Blogs

Using Analytical Functions to get Group Total

Thu, 2017-10-12 04:46
I need a GROUP TOTAL for EACH MONTH that SUMS up all the values in APPL column even when the query is for a particular APPL value. <code>create table test_grp_total (dttm_month date, appl varchar2(5), mins number) insert into test_grp_total va...
Categories: DBA Blogs

Convert row to matrix and save in table

Wed, 2017-10-11 10:26
Good Day I want to ask for your valuable help on a problem that I found, there is a table that stores the information in a single column in Clob format and is copied to another table of a second Oracle Schema, which separates the information with ...
Categories: DBA Blogs

ORA-12560: TNS:protocol adapter error (when running rman backup from scheduler on windows Oracle database)

Wed, 2017-10-11 10:26
Hi, I trying to setup a scheduler job on my windows 64bit Oracle database version 12C Below is the script i used where i created OS authentication and create a scheduler to run the backup_script. -- Create a credential so script is run as the co...
Categories: DBA Blogs

Numeric Overflow

Wed, 2017-10-11 10:26
I am attempting to Solve some mathematical problems using PL/SQL and I am facing a problem with huge numbers. One such question involves calculating 2 power 1000 (2 power 3 = 2*2*2 => 8). I tried assigning the values to multiple datatypes that I knew...
Categories: DBA Blogs

oracle on linux

Wed, 2017-10-11 10:26
i am not well verserd with linux ..i know that we have to write "sqlplus" on the comand prompt in linux to open sql*plus application...is there any place from where i can know how to open other (oracle) applications in linux like "oralce enterprise...
Categories: DBA Blogs

bind variables/ define confusion

Wed, 2017-10-11 10:26
Tom, Could you please clarify the DEFINE command in the example below. I am having trouble using the DEFINE command. INPUT: define naming = 'Ivan' insert into emp2 (fname) values (:naming); OUTPUT: Bind variable "NAMING" not declare...
Categories: DBA Blogs

SQLPLUS query output to *.csv or *.txt format

Wed, 2017-10-11 10:26
Hello Tom is there anyway to do a query in sql*plus, then have the result output to a file in *.csv or .*.txt format without invoking UTL_FILE, using only sql*plus command. I'm not allowed to creat any procedure. PS: this link "http://asktom.ora...
Categories: DBA Blogs

Second Highest Sal

Mon, 2017-10-09 21:46
Hello Tom, How are you, After long time i visited the site and able to find the button. Ok Here is my question how can i get from sql second highest salary record from the table but with deparment wise Ram 10 1000 Jai 10 2000 San 20 3000...
Categories: DBA Blogs

Long to Varchar2 conversion....

Fri, 2017-10-06 02:06
Hi, Thanks for your earlier responses.... See, i have one more problem, like i want to retrive the first 4000 characters of the long datatype, with out using the pl sql code. i just wrote a function like // create or replace function g...
Categories: DBA Blogs

date function in oracle: find the date of a day

Tue, 2017-10-03 00:46
can i find the date of a day that is on which dates saturdays of a specific month fall in oracle sql ??
Categories: DBA Blogs

conversion of MSACCESS query to Oracle SQL

Tue, 2017-10-03 00:46
The Goal is to convert a successful MS_ACCESS Query to Oracle SQL. Access Query ------------------ UPDATE target_table T INNER JOIN source_table S ON T.linkcolumn = S.linkColumn SET T.field1 = S.field1, T.field2 = S.field2, T.field...
Categories: DBA Blogs

Oracle Express Commercial Use

Tue, 2017-10-03 00:46
Hi - Can you please confirm that the Oracle Express Database group is free for commercial use ? The licensing implies it is but it would be good to get confirmation from Oracle (Masters). Regards Erick
Categories: DBA Blogs

remove compress basic

Sat, 2017-09-30 17:46
What is the best way to remove compress basic from tables and partitions in production environment.
Categories: DBA Blogs

FILE_ID vs RELATIVE_FNO

Sat, 2017-09-30 17:46
Hi TOM, Trying to understand the difference between FILE_ID & RELATIVE_FNO in dba_data_files and dba_extents.
Categories: DBA Blogs

Is UTL_MAIL supported in 11g EE

Sat, 2017-09-30 17:46
Hi Team, Wanted to know wherher utl_mail is supported in 11g EE.I installed the version in my local windows machine.I am able to connect to it via SQL Developer. I can see utl_smtp and utl_tcp packages are installed. I tried to install the utlmail...
Categories: DBA Blogs

What performs better NVL or DECODE for evaluating NULL values

Fri, 2017-09-29 23:26
Afternoon, Could anyone tell me which of the following statements would perform better? <code> SELECT 1 FROM DUAL WHERE NVL (NULL, '-1') = NVL (NULL, '-1') </code> OR <code> SELECT 1 FROM DUAL WHERE DECODE(NULL, NULL, '1', '0') = '...
Categories: DBA Blogs

Which Index is Better Global Or Local in Partitioned Table?

Fri, 2017-09-29 23:26
We have partitioned table based on date say startdate (Interval partition , For each day) We will use query that will generate report based on days (like report for previous 5 days) Also we use queries that will generate report based on hours (li...
Categories: DBA Blogs

Pages