Fine Tune - Trim Down SQL To Within 255 Characters?
Feb 1, 2013
trim down the following sql to within 255 characters help:
select indate
from (
select case count(inputDate)
when 1 then inputdate
end as indate
from commLeaseBut5
[code]...
This sql is check a date field in the database for record which, if the date field is blank it should be a new record. Then the sql will assigned the current timestamp and stored to the new record. Otherwise, the sql will return the record timestamp for display.
I have some trouble with a stored procedure I've created, when I run it in the server dedicated to developers it runs fine, and updates 100,000 record in 23 seconds, but when I run it in the production server it collapses and don't update anything. Here's my code.
CREATE OR REPLACE PROCEDURE cdc_sp_comp_tablas_paudit IS
TYPE t_folio_log IS TABLE OF cdc_compara_consulta_paudit.nconencfolio%TYPE; v_folio_log t_folio_log;
CURSOR c_folios_bd IS SELECT a.nconencfolio FROM cdc_compara_consulta_paudit a , cdc_consulta_encabezado b WHERE a.nconencfolio = b.nconencfolio [code].......
create table test ( name varchar2(50), descd varchar2(50) ) insert into test values ('kethlin','da,dad!tyerx'); insert into test values ('tauwatson','#$dfegr'); insert into test values ('jennybrown','fsa!!trtw$ fda'); insert into test values ('tauwatson','#$dfegr ,try');
how do I get the first three characters and last three characters from name field and remove all the junk characters from descd field?
Can we tune below mentioned query without creating any indexes :
SELECT /*+ PARALLEL(a,64) */x_abc_41, x_abc_44, CALLED_FROM_NUM, X_abc_25, created, evt_stat_cd, last_date, x_abc_number_from, x_abc_complete_date FROM table a WHERE a.todo_cd in('MNPIN','MNPOUT') AND a.x_abc_25 = 'NP RFS' AND a.created BETWEEN sysdate-180 AND sysdate
I am using oracle forms 10g and basically we have a system that takes over 300 photos on a daily basis, this all works fine and with no issues except for say maybe 2-3 photos a month. Occasionally we will get a 'corrupt photo' (it not actually corrupt, it displays in everything as it should except forms) . When we encounter these photos forms just crashes out and the user is unable to query the record with the associated photo until it is deleted and a new one is taken (or alternatively if we take the photo from the database open it in paint.net and just hit save it will then work). There is no difference that we can see in the photo which doesnt work and those that do work. I have tried using WRITE_IMAGE_FILE to save the photo to disk and Read_Image_File to read from the disk to see if that makes a difference. If i save the file as jpeg and no compression it still crashes, if i save it with low compression it works fine but we lose quality which we dont want to lose. Bitmap wont work at all. Saving as JFIF and GIF works fine without any compression but we still lose quality.
The photo will display fine if we use a javabean to display it but in this instance a javabean is not an option.
One weird thing we noticed is that when we are on the form that crashes with these photos and query back a working photo first, and THEN query the 'corrupt photo' the corrupt photo displays fine, but if we go into the form and query a corrupt photo first forms crashes as explained.
I am using dbms_sqltune package to tune some resource intensive queries. In this I am looking to know how Sql profile works
I created task for one sql using dbms_sqltune, then in Report it was recommended to accept its sql profile (which will potentially benefits 65%) then accepted that sql profile with
and re-executed same query but NO difference in time. Then, what that mentioned potentially benefit 65% is about.
1) Originally query is taking 10 sec to execute, with this 65% i thought that it will execute within 3-4 sec. Is it right? 2)Also, the query for which i have created tuning task has some hardcoded input values, what if i change input values next time, will that profile works with new input values?
FINDINGS SECTION (1 finding) ------------------------------------------------------------------------------- 1- SQL Profile Finding (see explain plans section below) -------------------------------------------------------- A potentially better execution plan was found for this statement.
I am trying to find some way how to tune and optimize the server performance in following situation. There are 100s of sessions inserting records to one table. Sessions are communication threads in java application, each thread is receiving messages that are to be stored in the table. Each message must be commited and then is ACK sent to remove client. Two problems are raising of course - much of ITLs on the table and lots of very small transactions. I can adjust the java application, but cant do much about the design.
I was thinking about some "caching" - if the messages are stored in memory and bulk-inserted to database by single thread the performance would be much higher. However, there would be possible loss of data - the message could be lost from memory cache and client already received ACK.
Tabular form works fine for first entries, but upon going back to add more records Page 55 has a button that links to the 'Approval' page which is a tabular form (Page 56).A text field at top of this tabular form receives the pass of a varchar2 field from page X which is an identifier (not PK). Tabular form query has condition WHERE ISO_NUMBER = :P56_ISO_NUMBER
When I add records on the first pass to the tabular form and click submit all is fine and dandy.When I get out of the tabular form and go back to it and add additional records I get this error:
Error in mru internal routine: ORA-20001: Error in MRU: row= 1, ORA-20001: ORA-20001: Current version of data in database has changed since user initiated update process. current checksum = "C4B43B9A17A41D287D55DEFE9B035944", item checksum = "607C07E22C9B03E6A45AF44EDC06BB31"., update "SPICE_HUNTER1"."DOC_APPRV_DOC" set "APPRV_DOC_ID" = :b1, "DOC_INFO_ID" = :b2, "ISO_NUMBER" = :b3, "DOC_APPROVER" = :b4, "DOC_APPROVED" = :b5, "DOC_APPROVAL_DT" = :b6, "DOC_COMMENT" = :b7, "APP_USER" = :b8Here's what I've done to try to figure it out:
1. Ran debug and saw it was happening in my MRU; and 2. In the past what I have cleaned out tabular form table data because I've found that when I'm in this testing mode going back and forth with changes gets fouled up, but with a clean slate it still happens. 3. I had Hidden the ID fields, and then remembered this was one of the issues with versions of this error. (I actually logged this one to remember!). So I made them Display as Text (Saves State) and unchecked their boxes.
How to avoid sort operation by an order by clause without changing the sort area size.what hints or changes should be done in query so that order by clause work faster.
I have a field "Email". The length of it is restricted to 30. But i mayget more than 30 characters. So how to trim the email address so that its max length is 30 characters.
read the data and write into the textfile. I have a target text file with one column in varchar2(8000) length size. Whenever i write into this textfile, after the first row entered, for example, the first row string is 'H2001', then it will automatically spacing 7995 spaces. What I want is, when i enter the second row, it will auto write into the file in next row without all the spaces in row 1. I had been tried using trim, rpad, substr. and still don't get the thing that i want.
Customer is sending data from legacy system (Source) with the web service which in turn calls a package lying on Oracle server (Target). Now this package is simply inserting data passed by legacy system into master staging table in Oracle database. When they started this process in Sept 2011 then 4 lack records were inserted into staging table. In Oct 11 it was 0 records Nov 11 it was 2 lack records, Dec 11 it was 1 lack records, in Jan 12 it was 1 lac records, Feb 12 73k records, Mar 12 0 records, Apr 12 52k records.
As we see that number of records inserted in the table got reduced with time.. what should be the starting point here since web service is calling that package on the fly, how can i enable trace for that package? I cannot replicate this is Dev as this process is only working in PROD.
How to write a pl/sql query to trim/remove more than one character from string.
Like the itemfield is 'Profit CY' I want it to show as 'Profit' but only for itemfields that say 'Profit CY' in the column for remaining items in column such as 'Loss CY' should stay as it is.
I have created a function based index(FBI) with trim(header_date), but when i query the table by passing the hardcoded date, it is not working and i have to manually apply trim to get the result?
my query after applying FBI is
select * from abc where header_date = '21-JUN-11', no results are returned and when i apply trim to header_date it works fine .
LOAD DATA APPEND INTO TABLE IPGITLREDATA WHEN ITL_REC_TYPE = 'D' FIELDS TERMINATED BY ',' TRAILING NULLCOLS (
[code].....
The data file might have a value of " D " instead of "D" for ITL_REC_TYPE and ITL_REC_TYPE is in the WHEN clause. How can I check for the trimmed value of ITL_REC_TYPE in the WHEN clause ?
I am calling a function in front end, from front end i am passing space, now i want to trim space in parameter itself, it's psssible, assume the below is the function defination, i want to trim the p_region_name parameter like this trim(p_region_name), is this possible?
FUNCTION add_country_region_column ( p_s_country_code_iso_2 IN varchar2_table_type, p_type IN d_country.c1_type%TYPE, p_name IN d_country.c1_name%TYPE, p_desc IN d_country.c1_desc%TYPE,
A computation after submit pl/sql function process to trim off the first part of the string (CQ..) within the list manager values. Support for example the list manager contains values such as
The computation process should trim off the first part(CQ..) and should return the list manager value as SAMPLE1..TEST1SAMPLE2..TEST2SAMPLE1..TEST2 Oracle APEX 4.0.2 is the version and Oracle 10g r2 is the database.
Below is the sample code working fine in 10g and not working now in 11g.
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "PSTest" AS import java.sql.SQLData; import java.sql.SQLException; import java.sql.SQLInput; import java.sql.SQLOutput; import java.util.List; [code]....
we got the below error: ORA-00932: inconsistent datatypes: expected an IN argument at position 1 that is an instance of an Oracle type convertible to an instance of a user defined Java class got an Oracle type that could not be converted to a java class
Current Oracle version is Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit and the version we are upgrading is Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
