Feed aggregator

TNS Issues when connecting to database with Forms Builder 12.2.1.2.0

AskTom - Hot Questions - Sun, 03/19/2017 - 00:00
I am getting the following issue when trying to connect to the database with Oracle Forms Builder (12.2.1.2.0): ORA-12154: TNS: Could not resolve the connect identifier specified. I am using: username: hr password: hrpassword database: pdb...

Daily Insert, Delete on table

AskTom - Hot Questions - Mon, 03/06/2017 - 00:00
I have a table tb_card_data CARD_NO TYPE EMAIL MODULE CREATED_ON SEQ_NO ------- ---- ----- ------ ---------- --------- 12345 C x@a.com MOD_1 06/03/2017 20170306000001 (create_on + sequence number) .. so on around 500k records for MOD...

I have to import the specific tables using DBMS_DATAPUMP PACKAGE . How to exclude the unwanted tables ?

AskTom - Hot Questions - Thu, 03/02/2017 - 00:00
Hi Tom , I have used the below import par file to exclude the tables those names are with $ DIRECTORY=FEB09_E DUMPFILE= MXPRD_01feb09_%U.dmp LOGFILE=IMP_EDRPRE.log REMAP_SCHEMA=MXPRD:MAXIMO remap_tablespace=MXPRD_DATA:MAXIMO_DATA,MXPRD_INDE...

Optimizer Dynamic Sampling at OPTIMIZER_DYAMIC_SAMPLING level 2

AskTom - Hot Questions - Wed, 02/15/2017 - 00:00
At my Oracle 12.1.0.2 Oracle Standard Edition database, at OPTIMIZER_DYNAMIC_SAMPLING = 2 which is the default, I see Dynamic Sampling is happening for some of the SQLs for which all tables already have already gathered table statistics. Due to this,...

How to extract substrings enclosed within double quotes from a string

AskTom - Hot Questions - Sun, 02/12/2017 - 00:00
I have a requirement to extract column names (there could be multiple instances) from a SQL string. The column names will always be enclosed within double quotes. e.g. "le code" = 802 AND "principal amount" > 1200 OR "branch id" = 'ABC' <b>Req...

What is your salary ?

AskTom - Hot Questions - Fri, 02/10/2017 - 00:00
Tom, WHat is your salary? Thanks

Shuffle values randomly in columns

AskTom - Hot Questions - Tue, 02/07/2017 - 00:00
Note - this is more of an academic question as I have a resolution, I am just keen to see whether my alternative approach is possible. I have a HR table with a list of names. For demo purposes I needed to shuffle these around to make them a bit mo...

Insert Into table_name Select for table with CLOB column is very slow.

AskTom - Hot Questions - Wed, 02/01/2017 - 00:00
Hi Sir, I have a activity for making non partition table to partition table, the table has clob column i was using below approach to fulfill the activity. rename existing table to _old create new partition blank table insert into new blank pa...

SQL Query Help Required

AskTom - Hot Questions - Tue, 01/31/2017 - 00:00
I'm new to Oracle and have got a requirement from my client as follows. Eventhough I have got the PL/SQL solution for this requirement, I'm interested in understanding whether a SQL based solution is possible rather than a PL/SQL approach. If yes, p...

Concat all columns

AskTom - Hot Questions - Tue, 01/31/2017 - 00:00
Hello Tom. I want to concat all columns of a row into one string. <code>select * from table</code> should bring out one colum per row, including all fieldvalues as one string. The use auf || didn't work, because I want it for different table...

Finding if record is overlap or not

AskTom - Hot Questions - Sun, 01/29/2017 - 00:00
Hi Tom, Hi Tom, Could you please help me with the SQL in finding if the record is a overlap or not. Here is the test case.. seq_val is calculated first and would like to check overlaps based on that order.. ID Start_dt end_dt Seq_val 1 01/0...

Memory usage in heavily partitioned database

AskTom - Hot Questions - Fri, 01/27/2017 - 00:00
Hi From the depths of internet I have found sql-statement which I slighly modified. Purpose was to find out what objects exists in the buffer cache and many blocks they were using. </> with t1 as ( select o.owner, o.object_name ...

Performance and commits

AskTom - Hot Questions - Fri, 01/27/2017 - 00:00
Hi team, I have a simple question. Let's say i have 20,000 txn for one session and i have multiple sessions say 10. So is it good to mention commit frequency as 300-400 in code for better performance. Please let me know as what i am follo...

Fetch Ref cursor to another ref cursor

AskTom - Hot Questions - Thu, 01/19/2017 - 00:00
Hi, First of all Thank you Gurus! for your time and effort to solve user issues. I have a requirement where I have to consume first row of the big query result set and fetch the rest of the result into a refcursor(for some reason I can't change...

Edition based redefinition- Data handelling

AskTom - Hot Questions - Tue, 01/17/2017 - 00:00
Hi, I am using EBR to upgrade from one application version to another with zero downtime. Definitely, this includes migration DB scripts to be run to migrate from one version to another. The requirement is that the migration scripts have some DMLs...

Locking issues on Table A with FKs on table X while a procedure inserts records in table B with FKs on table X too

AskTom - Hot Questions - Mon, 01/16/2017 - 00:00
Hello, we use oracle 11.2.0.3 and following topic i would like to discuss here: Setup like this: Table A (partitioned) with an article column and FK defined to masterdata (article) keytable X Table B also with article column and FK define...

Procedure accepting more than 32K text as a parameter and inserting to CLOB coumn

AskTom - Hot Questions - Sat, 01/14/2017 - 00:00
Hi Team, We have one requirement where: 1) we have to write one proceudre which will insert into a clob column meant for email body. This procedure should accept text more than 32K. 2) the application or routine will execute this procedure by p...

Use of Wrap utility for 12K LOC packages

AskTom - Hot Questions - Thu, 01/12/2017 - 00:00
I've create a SP to encrypt the source code of existing packages, my problem is that legacy packages contain more than 12K LoC (lines of code), with small packages I have no issue, but with the heavy ones I got : <i>06502. 00000 - "PL/SQL: nu...

Where clause with multiple arguments which can be null or populated

AskTom - Hot Questions - Wed, 01/11/2017 - 00:00
I have a search feature in my application where a user can type filter on 3 different columns(can be more), each of these filters can be used or null. What is the best way to select a ref cursor for the data? I can think of 2 different ways. 1st wa...

Pages