Welcome to HTML DB

email in PL/SQL Tuesday February 21st 2012 12:54 AM
Ask Tom MOST RECENT ARTICLES
<code>Tom: 1. If you want to write something in PL/SQL application server that send email automatically to an administrator requesting approval after a user create a record, which oracle package would be using. Would you also use an after inse...

Strings of Zero Length Not Equivalent To NULL Tuesday February 21st 2012 12:54 AM
Ask Tom MOST RECENT ARTICLES
<code>Tom, As we are planning to upgrade to 9i version, I am reviewing the Oracle 9i migration documents and in one of the topic, I read "Strings of Zero Length Not Equivalent To NULL". "A string of zero length ('') is not equivalent to a NUL...

FLASHBACK_TRANSACTION_QUERY Monday February 20th 2012 10:54 PM
Ask Tom MOST RECENT ARTICLES
<code>Tom, Could you please explain the logic behind the FLASHBACK_TRANSACTION_QUERY view, or even better could you please point me to the documentation page with explanation ? I wasn?t able to find any. I would like to know how exactly oracle g...

Assign return value to Unix Variable Monday February 20th 2012 10:54 PM
Ask Tom MOST RECENT ARTICLES
<code>Hi Tom, How to assign value from SQL Plus to Unix Shell Variable? Ex: $ SAL=`sqlplus scott/tiger -s << select max(sal) from emp; exit; EOF Thanks, Bhaskara </code>

Tablespace Coalesce Monday February 20th 2012 11:54 AM
Ask Tom MOST RECENT ARTICLES
<code>Hi Tom, I've entered the following command to coalesce my 'users' tablespace but it returned without any coalescing: alter tablespace users coalesce; What is the reason? </code>

Caching a table in Memory Monday February 20th 2012 11:54 AM
Ask Tom MOST RECENT ARTICLES
<code>Hi, Is it enough if we use ALTER TABLE <table_name> CACHE; to push the table to cache ?. Do I need to do anything else to have the table in the Memory ? Thx, VJ </code>

10g shrink space with compressed table Monday February 20th 2012 11:54 AM
Ask Tom MOST RECENT ARTICLES
<code>SQL> SQL> select tablespace_name, def_tab_compression, segment_space_management 2 from dba_tablespaces; TABLESPACE_NAME DEF_TAB_ SEGMEN ---------...

Removing deadlock Monday February 20th 2012 11:54 AM
Ask Tom MOST RECENT ARTICLES
<code>Hi Tom, How can I clear deadlock without killing a session? thanks in advance. john </code>

Key-preserved table concept in join view Friday February 17th 2012 07:03 PM
Ask Tom MOST RECENT ARTICLES
<code>Could you explain me about Key-preserved table concept in join views.i know that a table is ket preserved if every key of the table can also be a key of result of the join. but i not understand meaning of this. Thanks </code>

Problem to perform TSPITR Friday February 17th 2012 07:03 PM
Ask Tom MOST RECENT ARTICLES
<code>I was performing TSPITR with RMAN but got the following error during compiling command "restore". RMAN-06026: some targets not found - aborting restore RMAN-06023: no backup or copy of datafile 3 found to restore RMAN-06023: no backup or c...

external table and date format Friday February 17th 2012 07:03 PM
Ask Tom MOST RECENT ARTICLES
<code>Hi Tom, What i am trying to do is load in bank transactions ( downloaded in a comma delimited format from the bank ) into my database. My approach is to create an external table from the file and then create a regular table from the external ...

Cloud Computing Friday February 17th 2012 06:04 PM
Ask Tom MOST RECENT ARTICLES
What's your view on cloud computing and how will it affect the future of relational database, especially Oracle? Will cloud render most of the DBA jobs obsolete?

How to avoid the full table scan Wednesday February 15th 2012 06:01 AM
Ask Tom MOST RECENT ARTICLES
<code>Hi Tom, I have the situation as defined below: I have created two tables create table I1(n number primary key, v varchar2(10)); create table I2(n number primary key, v varchar2(10)); and a map table create table MAP(n number prim...

How can I DO PATTERN MATCHING from within PL-SQL using REGULAR Expression Wednesday February 15th 2012 06:01 AM
Ask Tom MOST RECENT ARTICLES
<code>Hi Tom, How r u doing.. I must be quick on this because today is the first time i am able to put my Qnand hope that u will see it.. My problem is : 1. I need to search for matches in a table which has a bunch of Mobile phone number...

Code Management: SQL versus PL/SQL Tuesday February 14th 2012 09:00 AM
Ask Tom MOST RECENT ARTICLES
What best practices do you use for code management? The code below explores two options; the comments explain how I think about them. I know this question is somewhat broad and less specific than most you get here, but I would value your insight. The...

execute shell script from stored procedure Tuesday February 14th 2012 08:00 AM
Ask Tom MOST RECENT ARTICLES
<code>Is it possible to execute shell script from store procedure in async. mode? If yes then how and if not is there any work around to doit? </code>

Business Logic - PL/SQL Vs Java - Reg Monday February 13th 2012 07:59 PM
Ask Tom MOST RECENT ARTICLES
<code>Hi Tom With oracle 9i where do u suggest to write the business logic PL/SQL or Java. Which is faster/flexible/portable. Is oracle going to support PL/SQL in future. Regards </code>

Exception handling for junck character Monday February 13th 2012 04:59 PM
Ask Tom MOST RECENT ARTICLES
Hi Tom, i have a function which decrypts values which are encrypted for pin column, wherein for some reason this pin column has some junck characters which decrypt function fails to read and throws error, i want to handle this inside my function a...

Partition Monday February 13th 2012 04:59 PM
Ask Tom MOST RECENT ARTICLES
Hello Tom, I have the following table partitioned by IMPORT_ID that contains millions of rows: CREATE TABLE CUSTOMERS ( SIGNING_ACCOUNT VARCHAR2(30 CHAR), COMPANY_NAME VARCHAR2(255 CHAR), IMPORT_ID ...

Keep aggregates in materialized view when purging base data? Monday February 13th 2012 03:59 PM
Ask Tom MOST RECENT ARTICLES
Hi Tom, Hope there isn't a similar question, but I couldn't find one. Let me get right to it. I have a table with a date column and some statistical columns. The current day's data, potentially hundreds of thousands of rows, are inserted as par...

Passing Parameters from a WEB page to PL/SQL Monday February 13th 2012 01:59 PM
Ask Tom MOST RECENT ARTICLES
<code>Hi Tom, Thanks for reading this e-mail. I'm new at programming in PL/SQL and have a couple of design questions for you. I have some forms that I'm working on for a future WEB site of my school. I'm hoping to use JavaScript with some of th...

Safely Comparing NULL Columns as Equal Monday February 13th 2012 11:58 AM
Ask Tom MOST RECENT ARTICLES
Hi. I have code that checks for a "material change" via... <code> (col1 is null and col2 is not null) or (col1 is not null and col2 is null) or (col1 != col2) </code> This works fine. But when I need to do the opposite-...

ORA-24338: statement handle not executed Monday February 13th 2012 11:58 AM
Ask Tom MOST RECENT ARTICLES
I called store procedure with out parameter refcursor from remote database. After sucessful execute when I try to open the cursor there was error 'ORA-24338: Statement handle not executed' SQL> var v_cursor refcursor; SQL><b>execute transform.usp...

How to use db_link dinamically Monday February 13th 2012 11:58 AM
Ask Tom MOST RECENT ARTICLES
Hi Tom, Hope everything is O.K. for you ...... You know, I am extracting segment_name information for several databases and I am inserting information in a repository table. I am using next cursor to look for every database: FOR x in (SEL...

Is there any way to call a procedure or function from FORALL statement Monday February 13th 2012 09:59 AM
Ask Tom MOST RECENT ARTICLES
Hi Tom, 1st of all regrads and Thank you very much for your answers to questions put by aspirants. I often look into your articles to find related answer whenever I need an unusual requirement. My requirement is to call a procedure/fun...

Is PGA memory allocated from SGA ? Monday February 13th 2012 08:58 AM
Ask Tom MOST RECENT ARTICLES
Hi Tom, Is PGA memory allocated from SGA? In my database SGA_MAX_SIZE is 1504M SQL> sho parameter sga_max_size NAME TYPE VALUE ------------------------------------ ----------- ----------------------...

IN (vs) EXISTS and NOT IN (vs) NOT EXISTS Sunday February 12th 2012 10:58 PM
Ask Tom MOST POPULAR
<code>Hi Tom, Can you pls explain the diff between IN and EXISTS and NOT IN and NOT EXISTS. Because I have read that EXISTS will work better than IN and NOT EXISTS will work better than NOT IN (read this is Oracle server tunning). ...

UPDATE TABLE IF THEN Sunday February 12th 2012 02:58 PM
Ask Tom MOST RECENT ARTICLES
Hello , I have a little problem about find a solution to this problem . I have a table T1 in which i add a column FULL(YES/NO) . The idea is that T1 is the classes and a T2 table which contains the students.ID and the class.ID . The two tables j...

Removing field from select clause changes results Sunday February 12th 2012 02:58 PM
Ask Tom MOST RECENT ARTICLES
As far as I understood it, changing the fields in the select clause should have zero impact on the result set that is returned. The following puzzles me: Create a test table with 10,000 rows: create table test( a int); begin for i in 1....

how can I find the session(s) which generated so much undo and redo data? Sunday February 12th 2012 01:57 PM
Ask Tom MOST RECENT ARTICLES
Hi Tom, I am trying to find out how to monitor UNDO generation and REDO generation. On some databases I noticed some dangeros peak in undo generation and redo generation and I need to find out the culprit. Unfortunatly AWR and STATSPACK do not...

Import table with a LOB have not sound perdormance Sunday February 12th 2012 01:57 PM
Ask Tom MOST RECENT ARTICLES
Hello Tom, We are trying to make an export of a table with a LOB column and then make a import of this, in order to change the chunk size of the LOB column (from 32K to 8K). The procedure is the following: - We make the exp only of the table, ...

How to load this to oracle table Sunday February 12th 2012 01:57 PM
Ask Tom MOST RECENT ARTICLES
Hi Experts, I have the requirement to load excel to oracle table.The Excel is of the format Category Subcategory Jan-11 Feb-11 Mar-11 Apr-11 1 1 100 52 45 70 1 2 50 25 0 40 1 ...

physical/Logical standby database Sunday February 12th 2012 01:57 PM
Ask Tom MOST RECENT ARTICLES
Hi, I want to configure physical/logical standby database using 11gR2 on Linux system.However, I want to restrict, delete and drop statement on standby database.For example, if I delete rows or drop partitions from the primary database, then no ...

do you consider "on delete cascade" to be a bad practice ? Sunday February 12th 2012 01:57 PM
Ask Tom MOST RECENT ARTICLES
rumours say "on delete cascade" could be a bad practice: things happen automagically like for example in triggers: evil, don't override an atomic action see for example http://harmfultriggers.blogspot.com/2012/02/where-tk-agrees-with-tk-or-why-a...

do DMLs on temporary tables generate redo? Sunday February 12th 2012 01:57 PM
Ask Tom MOST RECENT ARTICLES
Hi Tom, could you please tell me if temporary tables will generate redo for standard DML operations? I think that changes on global temporary tables' blocks will not generate redo, but since every DML generates undo, every DML on global tempora...

Column Masking Sunday February 12th 2012 12:57 PM
Ask Tom MOST RECENT ARTICLES
Hello Tom, I have a table,and a column in it with the SSN's. So I need to restrict this column for a user when he performs only SELECT statement. How can be this done? [column masking OR any other primivite method] Can you provide the complete pr...

UTL_HTTP package usage Sunday February 12th 2012 12:57 PM
Ask Tom MOST RECENT ARTICLES
<code>Hi Tom, I want to know the usage of UTL_HTTP package. Can u explain it with an example? Thanks Siva </code>

Deadlocks caused by REFRESH FAST ON COMMIT? Sunday February 12th 2012 12:57 PM
Ask Tom MOST RECENT ARTICLES
Tom, A few years ago, I was considering whether I could speed up a process I had by using a materialized view with REFRESH FAST ON COMMIT. In testing, I noticed I starting getting deadlock errors, which had never happened in that process befo...

sqlplus command line usage Sunday February 12th 2012 08:57 AM
Ask Tom MOST RECENT ARTICLES
<code>Hi Tom, I am calling sqlplus from a batch file .. I want to call sqlplus and execute a packaged procedure in 1 line.. Is it possible ? currently i am calling a .sql file as : sqlplus scott/tiger @upd1.sql wht i want to do is somethin...

Query not using the index Sunday February 12th 2012 08:57 AM
Ask Tom MOST RECENT ARTICLES
<Code> SELECT COUNT(1) FROM APPS.OE_ORDER_LINES_ALL OOLA, APPS.OE_ORDER_HEADERS_ALL OOHA, APPS.WSH_DELIVERABLES_V WDV, APPS.WSH_DLVB_DLVY_V WDLVB, APPS.OE_TRANSACTION_TYPES_Tl OTT1, APPS.OE_TRANSACTION_TYPES_Tl OTT2,...

reading files in a directory -- how to get a list of available files. Saturday February 11th 2012 04:56 PM
Ask Tom MOST RECENT ARTICLES
<code>How do I read files from a certain directory with PL/SQL, without knowing the exact name ? My program must interface with another system which puts files in a directory on the server. UTL_FILE only reads a file when you know the name of...

CPU Utilization Saturday February 11th 2012 04:56 PM
Ask Tom MOST RECENT ARTICLES
Hi Tom, We run a multi-user OLTP system on Exadata Quarter Rack (Linux version). Though the system response time is consistent and is performing well. We observed Run queues with CPU utilization at 70% on both the nodes. What could be the reason? ...

using CONTAINS() with wildcard only Saturday February 11th 2012 04:56 PM
Ask Tom MOST RECENT ARTICLES
Hi Tom, The documentation says that wildcards with the Oracle Text CONTAINS() predicate are % and _ just like the LIKE predicate. And my expectations are that the following query: SELECT * FROM test WHERE CONTAINS(c1, '%' ,1) > 0 ...

Redo writes vs Redo sync writes Saturday February 11th 2012 03:56 PM
Ask Tom MOST RECENT ARTICLES
Hi Tom, What is the difference between redo writes and redo sync writes. I am referring to Instance Activity Statistics section in AWR report. Which of these is equals number of commits in the application?

How best to handle ever-increasing values that consistently grow beyond their high_values Saturday February 11th 2012 12:56 PM
Ask Tom MOST RECENT ARTICLES
I was wondering what your advice might be on how to handle values that are ever-increasing and therefore consistently larger than their corresponding high_value values. Metalink item 877645.1 discusses the issue, but I have been unable to find any...

TSPITR Saturday February 11th 2012 12:56 PM
Ask Tom MOST RECENT ARTICLES
<code>Tom: i am practice manually TSPITR(without using rman) i tried several times, but it always stuck at some point, after i issue recover database using backup controlfile until cancel. it works fine until i issue cancel. it gives ...

How to refresh Materialized View when the changes are made in the B tables ! Saturday February 11th 2012 09:56 AM
Ask Tom MOST RECENT ARTICLES
<code>Dear Tom, Hi ! How are you doing ! Thanks very much for your Kind help again and again. Sometimes I wonder if you were not there what would have happened to my role. My sincere thanks and Regards to you. Ques) This Quest is ba...

Shrink and coalesce Saturday February 11th 2012 08:56 AM
Ask Tom MOST RECENT ARTICLES
Tom, What is the difference between shrink and coalesce and under what conditions should we use each of these options? Do these commands lock the objects? Thanks...

9i Oracle Text searching multiple tables Wednesday February 08th 2012 05:52 PM
Ask Tom MOST RECENT ARTICLES
<code>Hi Tom, I currently have a simple search page that incorporates 9i Oracle Text by using the Context index on a CLOB column. What I'd like to know is if its possible to use a single query with CONTAINS that searches a CLOB column in Table A ...

Nologging does not work (yes it does...) Tuesday February 07th 2012 07:51 PM
Ask Tom MOST RECENT ARTICLES
<code>This is reported by Surya Rao <surya.rao@reuters.com> in a mailing list and I reproduce it here: SQL> create table test (a number) nologging; Table created. SQL> select table_name,logging 2 FROM USER_TABLES 3 where table_name =...

1 - 50

iStore

WIP Workbench

Web Telesales