Tom Kyte

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

How to use case statement inside where clause ?

Thu, 2016-10-20 16:06
I'm getting error-- PL/SQL: ORA-00905: missing keyword when i compile the following script create or replace procedure testing (ass_line in char, curs out sys_refcursor ) is begin open curs for select asl.Production_Group,asl.last_sequen...
Categories: DBA Blogs

Mixing 9s and 0s in format number

Thu, 2016-10-20 16:06
Hello, I'm a bit confused by SQL Refenrence Lets consider Elements 0 and 9 of "Table 2-14 Number Format Elements" of the current "SQL Reference" Lets also consider the emxamples of "Table 2-15 Results of Number Conversions" I have SO much to a...
Categories: DBA Blogs

Column level access restrictions on a table

Thu, 2016-10-20 16:06
Let's say I have a table T with columns A, B, C and D. Data in each column by itself is not considered sensitive, but a combination of columns A,B,C in the same resultset is considered sensitive. Is it possible to allow queries that select A,B,D or A...
Categories: DBA Blogs

Related to Job scheduler

Thu, 2016-10-20 16:06
Hi Tom, actually i created a job (it will run for every 5 mins)which will execute the procedure,In that procedure we are reading file list from directory and constructing a string which contains all the file names using java class. Here my prob...
Categories: DBA Blogs

Invalid identifier while merge using dynamic sql - static merge statement suceeds

Thu, 2016-10-20 16:06
Hi, I am facing a weird problem in which a merge statement using dynamic sql is failing with the below error while the static merge statement is suceeding. <b>Error:</b> ORA-00904: : invalid identifier ORA-06512: at line 60<u></u> Below is...
Categories: DBA Blogs

View with a CTE?

Wed, 2016-10-19 21:46
Can I create a view that uses a CTE?
Categories: DBA Blogs

Format for TRUNC(TIMESTAMP,<seconds>)

Wed, 2016-10-19 21:46
Is there a format specifier for TRUNCATE(timestamp,?) to seconds? The following all work: TRUNC(SYSTIMESTAMP,'MI') TRUNC(SYSTIMESTAMP,'HH') TRUNC(SYSTIMESTAMP,'MM') TRUNC(SYSTIMESTAMP,'YY') But TRUNC(SYSTIMESTAMP,'SS') and any variation I'v...
Categories: DBA Blogs

How to tackle 'ORA-14024: number of partitions of LOCAL index must equal that of the underlying table' error?

Wed, 2016-10-19 21:46
Hi, I have created a plsql program which does the following. 1) Create Backup tables using script from dbms_metadata.get_ddl utility. 2) Insert records from Main tables into backup tables. 3) Rename indexes on Main table 3) Create Indexes (W...
Categories: DBA Blogs

how to get regular updates on database space left and database used space in my table

Wed, 2016-10-19 21:46
how to get regular updates on database space left and database used space in my table . i have created a table with 3 columns , dbspacetotal & dbspaceused,dbspaceremaining. how to get data into these columns when i insert update or delete in my...
Categories: DBA Blogs

running application with an app_user instead of schema owner user

Wed, 2016-10-19 21:46
Hi I have a java application that requires access to oracle database with an app_user instead of schema owner user and this is for security purposes. - The schema owner user is the user that owns oracle objects that need to be accessed from j...
Categories: DBA Blogs

Database

Wed, 2016-10-19 21:46
i learn all sql and pl/sql concept very well. but still not working on any project. so my question is please give me project or idea to develop project in oracle(SQL and PL/SQL) which helps to improve my knowledge very well... please give me the prj...
Categories: DBA Blogs

Need help on dbms_scheduler

Wed, 2016-10-19 21:46
Hi Tom, I have a scheduler which is linked to my package. The package was running for long and hence I cancelled the task. Now when I try to run the package back, the scheduler is not running. I checked in "USER_SCHEDULER_JOB_LOG" AND THE...
Categories: DBA Blogs

SQL group by query shenanigans

Wed, 2016-10-19 03:26
Hi Chris or Connor, Saw you guys at OOW so I thought I toss a basic SQL query to you. I?ll use the HR.EMPLOYEES table to represent my problem so forgive me if it's a bit contrived. I?ll like to construct a query to sum the salaries grouped by ...
Categories: DBA Blogs

Grant Access on Table

Wed, 2016-10-19 03:26
I've given grant to one user on a table but user unable to access the table though I'm getting output as grant succeeded. The scenario is 1. There is one database A on remote location. 2. A is trying to access some table on database B(place on othe...
Categories: DBA Blogs

View that opens and runs once. The next time it's opened it hangs.

Wed, 2016-10-19 03:26
I have a view that when you open the view in say something like TOAD, or MS Access, or SMSS it opens fine the first time. The next time you open it or select from it it hangs. Creating the view I get no errors or warnings. If I open the view i...
Categories: DBA Blogs

Bulk Collection Save Exception

Wed, 2016-10-19 03:26
Dear Tom, Please help me on the below... we are having BULK COLLECT option which will save exception like below FORALL i in 1tab.count SAVE EXCEPTIONS INSERT INTO table values(obj(i)); exception when excep_bulk_err then ...
Categories: DBA Blogs

Oracle RAC without ASM

Wed, 2016-10-19 03:26
Hi My aim is to install oracle rac 11gR2 without ASM. What's required steps to achieve this and this system (rac installation) and what's pros / cons for this kind of installation . Regards, Oussema
Categories: DBA Blogs

DBLink for Local Tables

Wed, 2016-10-19 03:26
Nice day i'm from peru so my english is not the best. The database is a: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production "CORE 11.2.0.3.0 Production" TNS for Linux: Version 11.2.0.3.0 - Product...
Categories: DBA Blogs

Database PL/SQL developer

Wed, 2016-10-19 03:26
what are the roles and responsibilities of PL/SQL developer? what kind of knowledge required for oracle PL/SQL developer? what will be the future of PL/SQL developer?
Categories: DBA Blogs

PL/SQL Database Programming Question

Wed, 2016-10-19 03:26
I am struggling to figure out which LOOP statement to use. Here's the question: Each day, starting on Monday, the price will drop 5% from the previous day?s price. Monday?s sale price will be 5% less than what is stored in the database in the BB...
Categories: DBA Blogs

Pages