I have code that creates a sequence and a select statement that generates the next number in a sequence. It starts the sequence at 5 and every subsequent number is the previous number minus 3. The minimum value it can go to is 0. All I want is for my select statement to execute three times in a row. Is there any way I can do that?
create sequence MY_FIRST_SEQ
increment by -3
maxvalue 5
start with 5
minvalue 0
nocycle;
<Column name> value 1 value 2 value 3 . . . value n
Since the column is small it can fit in a page more then one time. I know how to make it print more then once: i switch the repeating frame to print down and across and i modify the frame that contains it so the horizontal elasticity is variable.
After these changes my report looks like this: <Column name> value 1-----------------------value n+1-----------------------value m+1 value 2-----------------------value n+2-----------------------value m+2 value 3-----------------------value n+3-----------------------... .------------------------------- . .------------------------------- . .------------------------------- . value n-----------------------value m
What i want is my report to look like this:
<Column name>-------<Column name>------------<Column name> value 1-----------------------value n+1-----------------------value m+1 value 2-----------------------value n+2-----------------------value m+2 value 3-----------------------value n+3-----------------------... .------------------------------- . .------------------------------- . .------------------------------- . value n-----------------------value m
we are running SAP application against oracle database. say, if I use brspace or brtools (from SAP side) to shutdown or startup database or collect stats, does this mean it not recommend to use oracle command to shutdown/start & collect stats?
I'm using Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - ProdMy problem is, There is data for IN time and OUT time data type is DATE. I need to calculate OT hour from this two field and store into a third one column. But i'm confuse what should be the data type of the third column. Is it number or else ?
I know difference of two date column is number. So, first i need to know that data type i should use ?
Secondly, how i get the sum of the all stored time ? for example data are
CREATE OR REPLACE PROCEDURE IND_MONITOR(P_tab VARCHAR2) is type ind_table is table of varchar2(20); p_ind ind_table; v_sql varchar2(2000); begin select index_name bulk collect into P_Ind from user_indexes where table_name=upper(P_tab); for i in 1..p_ind.count loop v_sql :='alter index '||p_ind(i)|| ' monitoring usage' execute immediate v_sql using p_ind(i); end loop; end;
I found this query in one of my stored procedures that updates a key for a value in a data table by reading the information from a master table.
The data table is: ITEM_INVENTORY The master table is: MASTER_SOURCE_SYSTEM
UPDATE ITEM_INVENTORY I SET I.SOURCE_SYSTEM_ID = (SELECT NVL(M.SRC_SYS_ID,-100) FROM MASTER_SOURCE_SYSTEM M WHERE M.SRC_SYS_DESC(+) = I.SOURCE_SYSTEM_CODE ) WHERE ORG_CODE = 'TNXC' AND EXISTS (SELECT 1 FROM MASTER_SOURCE_SYSTEM M WHERE M.SRC_SYS_DESC(+) = I.SOURCE_SYSTEM_CODE )
The situation here is that:
1. There are about 15000 rows that match ORG_CODE = 'TNXC'. 2. The SOURCE_SYSTEM_CODE is same for all the 15000 rows and there is a matching entry for it in the MASTER_SOURCE_SYSTEM table.
My question is: Do both the inner select statements execute 15000 times?
The statement executes within a second and updates 15000 rows. How is this made possible?
For obscure test purposes I need to modify an existing SQL query to emit the rows N times instead once. I'm aware of the possibility to "UNION ALL" the query with itself to get the all rows twice.
But as I require the resulting rows to be emitted around ten to hundred times this approach doesn't seem sensible to me. Not to speak of the missing possibility to parametrize the number of "repetitions".
I am dealing with a bunch of tables containing sales information for an New Zealand organisation. The sale datetime has been recorded as UTC.
New Zealand operates Daylight Savings, so twice a year it changes its clocks.
When New Zealand is on standard time it is UTC+12.
When New Zealand is on daylight savings time it is UTC+13.
Thus an event which actually occurred when New Zealand was on standard time at 2011-08-31 15:20:52 local time, is recorded in the database as having occurred at 2011-08-31 03:20:52. However, an event that actually occurred when New Zealand was on daylight savings time at 2011-10-06 15:20:52 local time, is recorded in the database as having occurred at 2011-10-06 02:20:52.
I want to be able to read the sales dates from my table and convert them to the actual time in New Zealand when the event occurred. The table will contain data for sales that occurred in both standard and daylight savings times.
I do not think that the data has been stored with time zone information, simply that the application writing the data to the Oracle database, calculated the event time as UTC when it occurred and wrote that time to the table.
Does Oracle only know about what UTC-offset is in force right now or is it capable of determining what offset from UTC is required for any given historical date ?
1. Make the jobname distinct, because it keeps giving me multiple entries for each jobname 2. Add the the start_time of SOD_start_data9_UAT1 to end_time fodba_MUAT1 to get the combined duration 3. CONCAT jobnames SOD_start_data9_UAT1 and end_time fodba_MUAT1 4. Generate the last seven days batch run times 5. Generate a report into .csv format and email out 6. I have access to sqlplus and plsql developer
I am trying to determine the number of times a value appears and display the count. However the value can only be counted once per 'trip' even though it may appear several times per trip.
with the above data the expected value would be two because the trip id appears twice so it was just the one trip - given a count of one. I am not sure how create a query to check this.
I need to open an explicit cursor for making a total: after I have to use the same information of that explicit cursor for dividing a column of the cursor by that total. It is not enough to open close, reopen and reclose because I just obtain one register at the same time and it is the same register two times consecutively.
I don't want to use auxiliary structures cause there are 18000 columns for 10200 rows.
FOR i IN 300..300 --18000 LOOP y:=ymax-ysize*(i+0.5);
In TOAD,I see one of my jobs is failing several times. However, I could refresh it manually.I found the following error. What is the cause of error and the way to rectify this.
ORA-23404: refresh group "ORA-23404: refresh group "schema_name"."job_name" does not exist ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: at "SYS.DBMS_REFRESH", line 23 ORA-06512: at "SYS.DBMS_REFRESH", line 195 ORA-06512: at line 1 "."" does not exist.
I have some data that I need to group to the Month, Day, Year, Hour and minute in a subquery. Then I need to summarize it to the Month and year in the parent query.If I try to group by the field itself, it is not taking it down to hour and minutes - just the day, so I am losing records.if I do a TO_char (visitdate, 'DD-MON-YY HH:MI AM') in the subquery, then the main query no longer sees it as a date, so cannot do my TO_CHAR(VISITDATE,'MON-YYYY') in the parent. I could parse out the pieces using string manipulation, but that seems rather silly.Is there a way to keep as a date in my sub query and then convert to a string?
it looks a little like this, with some other fields that I have to max, sum ...
Then I use that in a subquery where I use just the month and year TO_CHAR(VISITDATE,'MON-YYYY') AS APPT_MO_YR right now if I do a group by visitdate on the subquery it returns
provider visitdate 2 12/20/2012 3 12/21/2012
even if I do a group by to_date(visitdate, 'DD-MON-YY HH:MI AM') it is still returning :
Im trying to replicate a set of rows multiple times to create large volume. I am trying by For Loop, but got confused how to pass the parameters in any cursor i declare.
how many number of times the sql's had been executed/run in an oracle session. Executions in V$sql, is it the right place to look at? Will it give the number of times the sql has been executed in a particular session?
The SAMPLE clause in the select statement works well in most cases, but we found in some instances the result is way off - between 200% to 700% discrepancy has been observed.
For example, we have thee tables with the following results:
Table1: 495,365,317 rows (20 cols, unique primary key present), SAMPLE ( 0.002018712182064212 ) returns 41,499 (about four times off - we expected about 10,000) Table2: 3,350,864,539 rows ( 5 cols, unique primary key present), SAMPLE ( 0.00029843044634040336 ) returns 9,835 (this is good as it is close to 10,000) Table3: 6,974,724,543 rows ( 5 cols, no unique primary key present), SAMPLE ( 0.00014337483779250091 ) returns 58,789 (about six times off - we expected about 10,000)
The tables got billions of rows, and that is why we want to do sampling. The sample percentage rate is computed to return about 10,000 rows in all three tables.On Table3, we ran the sampling three times in one occasion, and we got "58,570", "24,575" and "24,561"
I expected +/- 20% of variance, but 200% to 700% seems to be way too much.Once again, I stress that it does work well in most cases (another 3.4 billion table and numerous smaller tables we tested were well within +/- 5 percent of the target).I noted the presence of a primary key above because I read an article saying that the SAMPLE function relies on the existence of a primary key (which does not quite explain the examples above).Is this kind of spread something we should expect or is it a bug? Is the sampling rate too small for such large tables?
I have a form which has a Number field. I set it's Format Mask property to 9,99,99,99,99,999 so that I can format it comma separated and to restrict the user to enter decimal values. If the user enter any format other than this for example any decimal values, it is showing the message FRM-40209:Field must be of the form 9,99,99,99,99,999 which is fine. But when the user enters a decimal value and try to save the form it pop up this message several times.
I tried to catch this error in ON-ERROR trigger and display a message. But this is also pop up several times. I tried to raise form_trigger_failure also. This is also not working. I want this error message to come only once.
How many times i can do recover (TSPITR) a tablespace ? While trying to recover tspitr tablespace on 2nd time i got error message ORA-01178 file 13 created before last create controlfile,but first time it was success.
Get the following error from Oracle ORA-24333: zero iteration count Cause: An iteration count of zero was specified for the statementAction: Specify the number of times this statement must be executed on whether is this a Oracle bug or if not what should be set to avoid this failure.
I'm looking for a way to make CRLFs show in a CLOB.I'm feeding the insert statement with a concatinated string like this:
insert into table(Data) values (MyConc)
(MyConc is a string put togheter by another application)
Because of this I can't use the "|| chr(13) || chr(10) ||" because I only have that one concatinated string.Is there a character I can set in my string that automatically translates to a CRLF?
I have a question about database fragmentation.I know that fragmentation can reduce performance in query times. The blocks are distributed in many extents and scans process takes a long time. Oracle engine have to locate the address of the next extent..
I want to know if there is any system view in which you can check if your table or index has high fragmentation. If it's needed I will have to re-create, move or rebulid the table or index, but before I want to know if the degree of fragmentation is high.
Any useful script or query to do this, any interesting oracle system view?
I have a list of strings ( like a,b ,c) that I want to loop againts. I will be creating string to execute them as dynamic sql.I could have put the string in a table and loop againt the table but this is a deployment script so I do not want to create any table.I can also do
DECLARE cursor c is SELECT 'a' FROM dual UNION SELECT 'b' FROM dual ; BEGIN for i in c loop ....... end loop. END;
But I have many strings to loop over. What is the best way to achieve it. Can i use collection to achieve this?
What I want to produce is a table in the following format
basket_ref, all_fruit 1, apple banana pear 2, apple lemon
There could theoretically be any amount of fruit in a given basket, so I will need to set up some kind of loop in order to read each record from the original table. I am not sure how it would be best to do this.
I have a cursor which I am opening and then looping through. Within this loop I am comparing attributes within this cursor with attributes from another loop that this one is within.
you will see in the IF statements (there are several distinct IF statements within the loop) that there is a check which assesses if the attributes are not equal.
If they are NOT, the value of v_mismatch is set to 1
What I need to do instead of setting this to 1, is to go to the next record in the loop. How can I achieve this?
OPEN c_distMatrix; LOOP FETCH c_distMatrix INTO r_dist; EXIT WHEN c_distMatrix%NOTFOUND; --compare each field and update the counter [code].....
