Oracle Question and Answer Site
Categories: Database Administration, Information Technology, Oracle
Tom Kyte points out a new Oracle Q&A site oraqa.com. The site is young, but very active and covers many of the topics of interest to Oracle DBAs and developers.
14 Responses to “Oracle Question and Answer Site”
-
ajama Says:
January 27th, 2006 at 11:53 amI would like to ask how shall I do a sql script or a function which returns me the calendar week in a month .exemple
my week starts on sunday and ends on sturday .
saturday 1/2005 => 1
sundayday 2/2005 => 2
monday 3/2005 => 2
tuesday 4/2005 => 2
wednesday 5/2005 => 2
thursday 6/2005 => 2
friday 7/2005 => 2
Saturday 8/2005 => 2
Sunday 9/2005 => 3 –shifted
monday 10/2005 => 3
tuesday 11/2005 => 3
wednesday 12/2005 => 3
thursday 13/2005 => 3
friday 14/2005 => 3
Saturday 15/2005 => 3
Sunday 16/2005 => 4 –shifted
monday 17/2005 => 4
tuesday 18/2005 => 4
wednesday 19/2005 => 4
thursday 20/2005 => 4
friday 21/2005 => 4
Saturday 22/2005 => 4
Sunday 23/2005 => 5 –shiftedand so one for the following month february again same thing has to happen starting by 1 ;
The following is my sql which does not workselect
to_char(to_date(’2004′,’YYYY’)+rownum-1,’DD-MON-YYYY’) day_full
– ,to_char(add_months(to_date(’2004′,’YYYY’)+rownum-1,-(month_num-1)),’WW’) Week_in_month
– ,Week_number_fiscal_Q is same as Week_number_calendar_Q
,to_char(add_months(to_date(’2004′,’YYYY’)+rownum-1,-3*(round((to_number(to_char(to_date(’2004′,’YYYY’)+rownum-1,’MM’))/3)+.49)-1)),’WW’) Week_number_Q
,to_char(add_months(to_date(’2004′,’YYYY’)+rownum-1,-6*(round((to_number(to_char(to_date(’2004′,’YYYY’)+rownum-1,’MM’))/6)+.49)-1)),’WW’) Week_half_year
,to_char(add_months(to_date(’2004′,’YYYY’)+rownum-1,-(to_char(to_date(’2004′,’YYYY’)+rownum-1,’MM’)-1)),’WW’) Week_in_month
from all_objects where rownum -
Jon Says:
February 1st, 2006 at 2:30 pmAjama, I think I’ve got a solution for you. Check out my article SQL: Find the Week Number in a Month.
Thanks for the challenge!
-
Life After Coffee » SQL: Find the Week Number in a Month Says:
February 15th, 2006 at 9:37 am[...] Ajama commented on my article Oracle Question and Answer Site asking how to write a SQL function to return the calendar week in a month. [...]
-
Anisa Says:
July 29th, 2006 at 12:38 amI had done my testing. I would like to reset my autonumber back to 1. I am using Access interface with oracle as a database.
-
dharmendra Says:
September 13th, 2006 at 1:47 pmhow to rename a database name
-
dharmendra Says:
September 13th, 2006 at 1:48 pmhow to rename a database name
ex.current name is ask and modify by scroll -
Jon Says:
September 13th, 2006 at 8:43 pmDharmendra,
I think this is what you’re looking for. BE CAREFUL and never try this on your production system until you have tried it on a test system. The directions below should be considered just a broad guideline and do not cover moving files to another location.
If you need to rename a database the best thing to do is to run the sql command
alter database backup controlfile to trace;and grab the resulting sql script from the udump directory.Grab the SQL from the trace file for the RESETLOGS case and change the line that says
CREATE CONTROLFILE REUSE DATABASE "OLDNAME" RESETLOGS ARCHIVELOG
to read
CREATE CONTROLFILE SET DATABASE "NEWNAME" RESETLOGS ARCHIVELOGNow shut down the database and run your SQL script to startup and rename the database. Finally run the following SQL to change the global name of the database.
ALTER DATABASE RENAME GLOBAL_NAME TO NEWNAME;Don’t forget to update your TNSNames, listener.ora, oratab and any other configuration files on your system.
Hope this helps!
-
Pradeep Says:
October 6th, 2006 at 8:46 amI want to store some temporary data in sql
how to create a temporary table in sql
so that i do condition checks pull out values and store there
select then
i am not able to use views here -
Pradeep Says:
October 8th, 2006 at 3:01 amI have to select one particular column from a view
it is throwing error for
select registration_id from view_registration;
how can i do that -
Jon Emmons Says:
October 12th, 2006 at 6:27 pmPradeep,
I think you’re looking for something like this:
http://www.dba-oracle.com/t_temporary_tables_sql.htm
Burleson describes it here as a technique for improving performance but from what I gather you want to do the same thing.
Hope this helps, and sorry for the slow response.
-
keekys Says:
December 14th, 2006 at 11:09 pmHi,
Here is the actual query.
SELECT contract_num_ng,STATE_PERFORM, CITY_PERFORM, COUNTRY_PERFORM,order_num_ng,mod_num_ng,PIID,referenced_PIID,contract_status,
contractor_name,component_code,office_code,date_signed,contact_title,dollars_obligated,
contractor_street,contractor_city,contractor_state,contractor_country,contractor_zip
FROM contract_actions_dw
WHERE date_signed BETWEEN ’01-OCT-2004′ AND ’30-SEP-2006′
AND contract_status=’A’
AND contract_num_ng LIKE ‘HHS%’
AND city_perform IN(
SELECT c.city_code,s.state_code
FROM CITY_CODES c,state_codes s
WHERE c.city_name IN(‘FAIRBANKS’,'NORTHPOLE’,'ANCHORAGE’,'GALENA’,'MONTGOMERY’,'TUSCALOOSA’,'TUSKEGEE’,'HUNTSVILLE’,'MOBILE’,'FAYETTEVILLE’,'HOTSPRINGS’,'JONESBORO’,'ELDORADO’,'TUCSON’,'MESA’,'BARSTOW’,'MARINA’,'SEASIDE’,'DEL REY OAKS’,'YUBA CITY’,'NORCO’,'CONCORD’,'SUNNYVALE’,
‘RIVERBANK’,'PORT HUENEME’,'OXNARD’,'RIDGECREST’,'CORONADO’,'SAN DIEGO’,'PASADENA’,'LONG BEACH’,'MOUNTAIN VIEW’,'SAN JOSE’,'COLORADO SPRINGS’,'DENVER’,'MIDDLETOWN’,'WATERBURY’,
‘MILFORD’,'FAIRFIELD’,'WASHINGTON’,'WILMINGTON’,'MILTON’,'PENSACOLA’,'SPRINGFIELD’,'VALPARISO’,'JACKSONVILLE’,'CRAWFORDVILLE’,'KEY WEST’,'FORT WALTON BEACH’,
‘ST PETERSBURG’,'AUGUSTA’,'WARNER ROBBINS’,'ALBANY’,'KINGSLAND’,'HINESVILLE’,'FOREST PARK’,'ATLANTA’,'MARIETTA’,'ATHENS’,'COLUMBUS’,'ROME’,'CEDAR RAPIDS’,'MIDDLETOWN’,
‘DUBUQUE’,'MOUNTAIN HOME’,'SHILOH’,'ROCK ISLAND’,'CENTRALIA’,'FAIRFIELD’,'FOREST PARK’,
‘MARION’,'WAUKEGAN’,'MANHATTAN’,'PARSONS’,'ELIZABETHTOWN’,'PADUCAH’,'LOUISVILLE’,'NEW ORLEANS’,'BOSSIER CITY’,'BATON ROUGE’,'LINCOLN’,'SPRINGFIELD’,
‘CHICOPEE’,'HUGHESVILLE’,'FORT MEADE’,'BETHESDA’,'ABERDEEN’,'ADELPHI’,'KITTERY’,'TOWN OF BRUNSWICK’,'TOPSHAM’,'CHESTERFIELD TOWNSHIP’,'BATTLECREEK’,'CAMBRIDGE’,'FARIBAULT’,'HERMANTOWN’,'KANSAS CITY’,'CAPE GIRARDEAU’,'MERIDIAN’,'PASCAGOULA’,'HELENA’,'MISSOULA’,'HAVELOCK’,'FAYETTEVILLE’,'ALBEMARLE’,'WILMINGTON’,'ASHEVILLE’,'GRAND FORKS’,'LINCOLN’,'PORTSMOUTH’,'EATONTOWN’,'EDISON’,'EWING’,'PENNSAUKEN’,'CLOVIS’,'UNIONDALE’,'AMITYVILLE’,'NEW YORK CITY’)
AND s.state_code IN (‘GA’,'IA’,'ID’,'IL’,'IN’,'KS’,'KY’,'LA’,'MA’,'MD’,'ME’,
‘MI’,'MN’,'MO’,'MS’,'MT’,'NC’,'ND’,'NE’,'NH’,'NJ’,'NM’,
‘NY’,'OH’,'OK’,'OR’,'PA’,'PR’,'RI’,'SC’,'SD’,'TX’,'VA’,'VT’,'WA’,'WI’,'WV’)
AND c.state_num=s.state_num
GROUP BY c.city_code,s.state_codeNow the problem is I have a list of companies which exists only in one particular city in a particular state. But my query gives multiple columns for a city matching with many states. But I want only the state and city where the company exists. The company info does not exist in any table in dbase. Just by matching the city and state i have to select the compnany info. Pls advice.
-
sushil Says:
January 5th, 2007 at 3:20 amI hv migrate forms 6i into 10 and it gives the error vbx.set_property .
what is alternate . -
john Says:
April 5th, 2007 at 1:42 ami have table
c1 c2
———-
1 a
2 b
1 c
1 d
2 ei want a query forthis table which will dispaly it as
c1 c2
————
1 a
- c
- d
2 b
- e- means blank space
-
Srinivas Says:
June 13th, 2007 at 6:55 amDear John,
Run the following commands at the SQL prompt:
Break on c1
select * from temp order by c1;Hope it helps.
Rgds,
Srin
