Inserting Dates into Table


My homework requires me to create a booking table for a hotel and I have created the table but I'm having trouble inserting the dates.

This is my table:
Code: DROP TABLE BookingDM CASCADE CONSTRAINTS PURGE; CREATE TABLE BookingDM ( hNo NUMBER(3), gNo NUMBER(5), dFrom DATE NOT NULL, dTo DATE NULL, rNo VARCHAR(4), CONSTRAINT BookingDM_PK PRIMARY KEY (hNo, gNo, dFrom), CONSTRAINT BookingDM_FK FOREIGN KEY (hNo, rNo) REFERENCES RoomDM (hNo, rNo) ON DELETE CASCADE, CONSTRAINT BokingDM_gNo_FK FOREIGN KEY (gNo) REFERENCES GuestDM (gNo) ON DELETE SET NULL );

This is the first set I'm attempting to insert
hNo = 148
gNo = 11169
dFrom = 09/03/2009
dTo = 09/10/2009
rNo = 202

This is my attempt to insert the set:
Code: SQL> INSERT INTO BookingDM VALUES('148', '11169', '09/03/2009', '09/10/2009', '202'); INSERT INTO BookingDM VALUES('148', '11169', '09/03/2009', '09/10/2009', '202') * ERROR at line 1: ORA-01843: not a valid month

I need my dates to be in the format "MM/DD/YYYY"
Posted On: Monday 31st of December 2012 01:13:48 AM Total Views:  238
View Complete with Replies

RELATED TOPICS OF Oracle PROGRAMMING LANGUAGE




RMAN backup

I cannot find certain information about large file size RMAN backup can support. Is there 2 GB backup piece limit using RMAN backup My unix system support large file (>2GB).
VIEWS ON THIS POST

57

Posted on:

Wednesday 7th November 2012
View Replies!

Please help with a Hierarchical Query

I have a table with sample records as below Code: Parent Region Child Region ============= =========== North America USA USA CALIFORNIA USA CONNECTICUT CALIFORNIA ORANGE COUNTY CONNECTICUT FAIRFIRLD COUNTY FAIRFIELD COUNTY NORWALK I want to create a flattened table / materialized view such as below Code: Parent Region Child Region ============= =========== North America USA North America CALIFORNIA North America CONNECTICUT North America ORANGE COUNTY North America FAIRFIELD COUNTY North America NORWALK USA CALIFORNIA USA CONNECTICUT USA ORANGE COUNTY USA FAIRLFIELD COUNTY USA NORWALK CALIFORNIA ORANGE COUNTY CONNECTICUT FAIRFIELD COUNTY CONNECTICUT NORWALK FAIRFIELD COUNTY NORWALK I am at loss as to how do I write a query to do this. I can do it by writing a stored procedure but was thinking whether its poccible using connect by in a single query to achieve this.
VIEWS ON THIS POST

38

Posted on:

Wednesday 7th November 2012
View Replies!

Patching new db

, I have an existing db instance that has been patched to 9.2.0.6 awhile back. Now I need to create an additional new instance and db on that same box. Is it necessary to apply the patches on the new instance and db or will it automatically be at 9.2.0.6
VIEWS ON THIS POST

58

Posted on:

Wednesday 7th November 2012
View Replies!

urgent dataretrival

hiii I have 3 users on a same database. but one user has all tables. Remaining users just select the all tables of data . around 300 tables. how to grant these tables to other users
VIEWS ON THIS POST

47

Posted on:

Wednesday 7th November 2012
View Replies!

seeking script to reverse engineer schema

seeking script to reverse engineer schema
VIEWS ON THIS POST

64

Posted on:

Wednesday 7th November 2012
View Replies!

Scritps to create tables from an existing database

I need a script to create tables without storage parameters from existing database.
VIEWS ON THIS POST

77

Posted on:

Wednesday 7th November 2012
View Replies!

Reports? Forms? or Other?

Iam a new dba who is supporting local oracle users and remote oracle users. I need something to replace access for viewing reports gathered from the oracle database. Access eats up too much bandwith when trying to work with oracle for our remote users. Is there a simple program I can use to create simple reports for about 6 remote users The end users are non-technical and would need a gui interface with options such as date selection and currency etc.
VIEWS ON THIS POST

57

Posted on:

Wednesday 7th November 2012
View Replies!

Redo Space Wait Ratio

Guys, I am checking server statistics in TOAD which is showing Redo Space Wait Ratio = 199.09. What does it mean exactly Oracle 8.1.7 on Win NT. I have 4 redolog files of 10MB each.
VIEWS ON THIS POST

38

Posted on:

Wednesday 7th November 2012
View Replies!

RMAN Catalog

Upgraded RMAN catalog from 8.0.5.3 to 8.1.6.3.4 and recreated RMAN catalog as 8.0.5 rman has so many issues including the datafile growing to 9 GB. Now the initial datafile attached is 300 MB and has grown to 880 MB with out there being any backup done.. why is it eating so much of space
VIEWS ON THIS POST

28

Posted on:

Wednesday 7th November 2012
View Replies!

Reorganizing Tablespace

I am in Oracle 8.1.7 with Dictionary Managed Tablespaces in my development. I am trying to regain some space by moving some objects off of one tablespace to another by useing the move command for tables and the rebuild command for indexes. I am having a problem with the partitioned tables. I was able to move the partitions but the table itself has a default tablespace of the tablespace I want to move it off of. What is the command to move the table to another tablespace when it is partitioned
VIEWS ON THIS POST

56

Posted on:

Wednesday 7th November 2012
View Replies!

Tablespaces on Different Drives

Hi.., I have created separate Tablespaces for my Static(lookup tables) & Dynamic Table(data growing tables), but my database is not implemented either S/W or H/W RAID, in this case if we implement more than one DBWR(i.e db_writer_processes=2 in my pfile/spfile) and keeping those datafiles in different drives(if my Hard Disk is partitioned) will it gives the more performance comparatively normal mode. OR if I kept in my Server more than one Hard Disk at least in this case will I achieve more performance..
VIEWS ON THIS POST

58

Posted on:

Wednesday 7th November 2012
View Replies!

pfile

SCO Unixware 711 Orcle 8.1.6 Hi freinds.. When I start an instance, mount and open without specifying where the pfile is.. how does oracle know where it is
VIEWS ON THIS POST

51

Posted on:

Wednesday 7th November 2012
View Replies!

Path to becoming a DBA (Questions from a student)

, I am currently a university student majoring in management information systems. My goal is to get involved with Oracle upon graduation, either as a developer or a DBA. From my research, it is abundantly clear that there are some difficulties with getting into the DBA scene. No employer really is interested in someone with no experience. This leaves people like me in a difficult situation, as all I can offer a potential employer is my education. I do, however, intend on getting internships as a developer of some sort as soon as I am comfortable with SQL, Java, and C#. My question to the people of this forum is in regards to certificate programs, and the OCP programs. I am debating to whether it is a good idea to invest my time and money into one of these programs before entering the job market. _________ UCLA Extension offers the following certification: https://www.uclaextension.edu/r/Prog...aspxreg=CF518 It costs about $10,000, and you can see the list of classes if you click on the link above. UCI Extension offers the following certification, and they guarantee OCP certification after you complete the coursework. I also believe that this program is cheaper. http://unex.uci.edu/certificates/it/...b/courses.aspx I believe that the actual classes between the two programs are very similar, however in California UCLA is a more reputable/well known school. I do believe that the program offered by UCI is better, however I wanted to hear what you have to say as far what difference the reputation of the school might have. __________ in all, is this something I should consider starting before graduating university I definitely have the time to complete the program, so it wouldn't be taking away from my education. Money wise, I would be taking out a student loan to pay for the certificate. Thank you in advance.
VIEWS ON THIS POST

81

Posted on:

Wednesday 7th November 2012
View Replies!

String conversion

What function enables me to know whether a string is convertable to number using TO_NUMBER function Something like ... DECODE(IS_STRING('string'), 0, TO_NUMBER('string')) ...
VIEWS ON THIS POST

56

Posted on:

Wednesday 7th November 2012
View Replies!

Statspack/spauto.sql

, Happy new year to all. I am exploring the tuning techniques and tools available on Oracle 9i and there is this script called spauto.sql that one can use to automate the statistics gathering. My problem is that, it collects the statistics every hour. Is there a way to change it to say...every 15 mins or ... every 4 hrs
VIEWS ON THIS POST

50

Posted on:

Wednesday 7th November 2012
View Replies!

outer join bug in 9i

Hi create table tabA( bankcode varchar(10), bankname varchar2(15)); create table tabB( bbankcode varchar(10), bbankname varchar(15)); Insert data into tabA(Transaction table) insert into tabA(bankcode,bankname) values ('001','firstbank'); insert into tabA(bankcode,bankname) values ('002','secondbank'); insert into tabA(bankcode,bankname) values ('003','thirdbank'); insert into tabA(bankcode,bankname) values ('004','fourthbank'); insert into tabA(bankcode,bankname) values ('004','Firstunionbank'); Insert data into tabB(Master table) insert into tabB(bbankcode,bbankname) values ('001','firstbank'); insert into tabB(bbankcode,bbankname) values ('003','thirdbank'); disply the current data from from tabA select * from tabA; BANKCODE BANKNAME -------------------- ------------------------------ 001 firstbank 002 secondbank 003 thirdbank 004 fourthbank 004 Firstunionbank SQL> select * from tabB; BBANKCODE BBANKNAME -------------------- ------------------------------ 001 firstbank 003 thirdbank now when i do a full outer join i get different results Code: select a.BANKCODE,b.BBANKNAME from tabA a full outer join tabB b on a.BANKCODE=b.BBANKCODE BANKCODE BBANKNAME ---------- --------------- 001 firstbank 003 thirdbank 002 004 004 with 8i syntax SELECT a.bankcode,a.bankname FROM taba a,tabb b where a.bankcode=b.bbankcode(+) union SELECT b.bbankcode,b.bbankname FROM taba a,tabb b where b.bbankcode(+)=a.bankcode BANKCODE BANKNAME ---------- --------------- 001 firstbank 002 secondbank 003 thirdbank 004 Firstunionbank 004 fourthbank Note the null values have been filled up with banknames is this a bug How do i rewrite the above query without using the union clause bankname has to come from tabb table only. regards Hrishy
VIEWS ON THIS POST

152

Posted on:

Wednesday 7th November 2012
View Replies!

Oracle User Missing

Hi! I'm facing a strange situation with a user. I'm using Oracle 10g. The first time I was creating a user y putted it in SYSTEM tablespace. The user got SYSDBA privileges. My problema is that I can log with this user in SQLPlus, but when I log with with SYS as SYSDBA the user doesn't appear... I want to delete this user but I don't know how.
VIEWS ON THIS POST

29

Posted on:

Wednesday 7th November 2012
View Replies!

Select result

I heared that it is possible to utilize 8i features so you can select from database and get back the result in XML format. Can you put some light in this subject and provide me with some example and links Cheers
VIEWS ON THIS POST

44

Posted on:

Wednesday 7th November 2012
View Replies!

Rman Split block re-reads relationship to busy i/o

, I have a system that reports corruption during backup but structure validations don't indicate that any corruption exists which is further confirmed by exports and fast full index scans. The theory is that rman is running during peak I/O batch jobs and is not able to get a read consistent image of the header and footer SCNs in a timely manner and gives up and goes ahead and populates the corruption views. Information in the Rman advanced backup manual does not indicate how to force Rman to retry a certain number of times before reporting the corruption. Thoughts anyone
VIEWS ON THIS POST

86

Posted on:

Wednesday 7th November 2012
View Replies!

RMAN script

hi, I have taken full backup yesterday including archivelog files upto sequence# 2848.Now i need to take incremental backup of database plus archivelog files from that sequence#2848 to till now. Oracle version :10.2.0 WindowsNT I tried using this script : Run { allocate channel c1 device type disk format='f:/backup/increment/Increment_bkp_Jul31/db_%U'; backup incremental level 1 database; backup archivelog from sequence 2849; } Is there any other way if guide me plz... TIA,
VIEWS ON THIS POST

33

Posted on:

Wednesday 7th November 2012
View Replies!

pls 801

A package in our instance which was running fine has suddenly become invalid.. and We are getting this error when we compile/recreate this package: PLS-00801: INTERNAL ERROR(1002) Any idea what does this error mean
VIEWS ON THIS POST

60

Posted on:

Wednesday 7th November 2012
View Replies!

Oracle User Authenitcation and Windows AD

There seems to be a lot of information on setting up users accounts and integrating with Windows Active Directory. I am hoping someone has some simple and easy to understand information on setting up users to authenticate to an oracle database from windows AD. Basically we have a few users that currently have an account in Oracle setup for reporting purposes, we manage the creation of the accounts in Oracle and use the tnsnames.ora file to point to each database. Each account has been assigned a role, which has been assigned Select rights to particular tables in each database. the users use ODBC or OLE to connect to the database, either using Crystal Reports or MSAccess with Linked Tables. We would like to setup authentication using Windows AD, basically allowing the user to use their windows account/password as the authentication to each database. It just seems that there is a ton of information for somthing that should be simple to setup. Do I need to use OID, Wallet, is there something simple
VIEWS ON THIS POST

22

Posted on:

Wednesday 7th November 2012
View Replies!

Oracle RAC 10g and NAS Failover

We are running iSCSI and a clustered NAS with Oracle RAC 10gR2 Standard, Solaris 10. While doing NAS failover testing from each controller we seem to lose connectivity and RAC reboots. The problem occurs when we fail back the NAS and RAC will reboot. Any ideas The OS see's the iSCSI LUN's but RAC has some issues.
VIEWS ON THIS POST

57

Posted on:

Wednesday 7th November 2012
View Replies!

MYSql with ASP.NET (C#) Tutorials Please

I have worked with SQL Server before, but not MySql. I have created stored procedure in MySql which gets data from the table based on the month entered as the input. I have an aspx page with input control for month and a Gridview to display the output. And in the Web.Config file, I have defined the connection as: I am not sure how to proceed from here. Can anyone please help me out further
VIEWS ON THIS POST

43

Posted on:

Wednesday 7th November 2012
View Replies!

What happens if I don't close the reader, but close the connection?

Below is the code I use everywhere in my web app. I've been told that the DB sessions are not closing, and that they are maxing out causing the application to hang after a while. Could it be that I'm not closing the reader I tried putting "pooling=false" in the connection string, but it affects performance too much. Also tried "Min Pool Size=0;Connection Lifetime=0;" in the connection string, but I have no way of knowing if that works. Would someone please tell me if one of my solutions is correct and best one available, and if not what can I do In summary here's what I thought could possibly solve the problem of oracle sessions not closing: - add to connection string "pooling=false", but it slows down performance ALOT - add to connection string "Min Pool Size=0;Connection Lifetime=0;", not sure if it works - close the reader in EVERY single one of the stored procedure calls, which are about 100+ , not sure, will this really work ---------------------------------------------------- using (OracleConnection objConn = new OracleConnection(ClaseSuperGlobal.conString)) \t\t\t { \t\t\t\t\t OracleCommand objCmd = new OracleCommand(); \t\t\t\t\t objCmd.Connection = objConn; \t\t\t\t\t objCmd.CommandText = "PROCEDURE_NAME"; \t\t\t\t\t objCmd.CommandType = CommandType.StoredProcedure; \t\t\t\t\t objCmd.Parameters.Add("MYCURSOR_OUTPUT", OracleType.Cursor).Direction = ParameterDirection.Output; \t\t\t\t\t objConn.Open(); \t\t\t\t\t OracleDataReader objReader = objCmd.ExecuteReader(); \t\t\t\t\t while (objReader.Read()) \t\t\t\t\t { \t\t\t\t\t\t\t returnList.Add(objReader.GetValue(objReader.GetOrdinal("NAME")).ToString()); \t\t\t\t\t } \t\t\t\t\t objConn.Close(); \t\t\t }
VIEWS ON THIS POST

90

Posted on:

Wednesday 7th November 2012
View Replies!

DetailsView + MySQL paging

all, I'm beginner in ASP.NET using MySQL... I have a SELECT statement like this... \t\t\t MySqlCommand cmd = new MySqlCommand();//Create new MySqlCommand \t\t\t //select statement \t\t\t cmd.CommandText = "SELECT * FROM messagetodoctor WHERE ICDoctor = '" + LoginName + "'"; \t\t\t cmd.Connection = conn; \t\t\t //create data adapter and using select statement \t\t\t MySqlDataAdapter MyDataAdapter = new MySqlDataAdapter(cmd); \t\t\t DataSet datasettKu = new DataSet();//create new dataset \t\t\t MyDataAdapter.Fill(datasettKu, "messagetodoctor");//fill into dataset using data adapter \t\t\t DetailsView1.DataSource = datasettKu; \t\t\t DetailsView1.DataBind(); \t\t\t conn.Close();//close the connection how can i navigate to next item though paging \t what must i put inside \t\t protected void DetailsView1_PageIndexChanging(object sender, DetailsViewPageEventArgs e) \t { \t\t \t } i'm using #using MySql.Data.MySqlClient; which is .NET MySql Provider... please help me!!!...
VIEWS ON THIS POST

70

Posted on:

Wednesday 7th November 2012
View Replies!

Problem with data access to an AS/400 system from an ASP page

I'm talking to a database running on an AS/400 machine. I'm handling a simple query from an ASP page. The first time the query works fine. Second time I get an error reporting my cursor is not closed. Third time the query works again. Fourth time I get the same error about the cursor. And so on... Apparantly the error closes the cursor so the application works. Does anyone know how I can close the cursor on an AS/400 machine from my ASP page after running the query The ASP page has to close the cursor and I don't know how.
VIEWS ON THIS POST

87

Posted on:

Wednesday 7th November 2012
View Replies!

Oracle Stored Procedure, OLEDB, and ASP.NET

i wanna try this SELECT from stored procedure of database and give this error: String[1]:theSizepropertyhasaninvalidsizeof0. That is the code:\t\t\t\t\t \t\t\t\t\t Dim cmdIVR_1A As OleDbCommand = New OleDbCommand cmdIVR_1A.Connection = ConnOle cmdIVR_1A.CommandText = "BUSQUEDA_IVR" cmdIVR_1A.CommandType = CommandType.StoredProcedure cmdIVR_1A.Parameters.Add(New OleDbParameter("NIC", OracleType.VarChar)) cmdIVR_1A.Parameters("NIC").Value = Trim(txtCriteria.Text) cmdIVR_1A.Parameters.Add(New OleDbParameter("C_REFERENCIA", OleDbType.VarChar)) cmdIVR_1A.Parameters("C_REFERENCIA").Direction = ParameterDirection.Output cmdIVR_1A.Parameters.Add(New OleDbParameter("COD_CLIENT", OleDbType.VarChar)) cmdIVR_1A.Parameters("COD_CLIENT").Direction = ParameterDirection.Output cmdIVR_1A.Parameters.Add(New OleDbParameter("APELLIDO_1", OleDbType.VarChar)) cmdIVR_1A.Parameters("APELLIDO_1").Direction = ParameterDirection.Output cmdIVR_1A.Parameters.Add(New OleDbParameter("APELLIDO_2", OleDbType.VarChar)) cmdIVR_1A.Parameters("APELLIDO_2").Direction = ParameterDirection.Output cmdIVR_1A.Parameters.Add(New OleDbParameter("NOMBRE", OleDbType.VarChar)) cmdIVR_1A.Parameters("NOMBRE").Direction = ParameterDirection.Output If ConnOle.State = ConnectionState.Closed Then ConnOle.Open() End If Dim Reader As OleDbDataReader Reader = cmdIVR_1A.ExecuteReader If ConnOle.State = ConnectionState.Open Then ConnOle.Close() End If And this is my Stored Procedure:\tCREATE OR REPLACE PROCEDURE "BUSQUEDA_IVR" ( NIC IN VARCHAR2, C_REFERENCIA OUT VARCHAR2, COD_CLIENT OUT VARCHAR2, APELLIDO_1 OUT VARCHAR2, APELLIDO_2 OUT VARCHAR2, NOMBRE OUT VARCHAR2) AS CLIENTE NUMBER(10); BEGIN C_REFERENCIA := NIC; BEGIN SELECT s.cod_cli,c.ape1_cli,c.ape2_cli, c.nom_cli INTO CLIENTE, APELLIDO_1, APELLIDO_2, NOMBRE FROM t_ivr_users t, gcp82e.sumcon s, gcp82e.clientes c WHERE t.c_referencia = TRIM(NIC) AND s.nic = t.c_referencia AND c.cod_cli = s.cod_cli; EXCEPTION WHEN NO_DATA_FOUND THEN CLIENTE := 0; APELLIDO_1 := NULL; APELLIDO_2 := NULL; NOMBRE := NULL; END; IF CLIENTE = 0 THEN COD_CLIENT := NULL; ELSE COD_CLIENT := TRIM(TO_CHAR(CLIENTE)); END IF; END; Please help me\t\t
VIEWS ON THIS POST

64

Posted on:

Wednesday 7th November 2012
View Replies!

Can't enter CLOB data in oracle.

Dear Friends, I am very thankful to all of for my replies and hopping u all will reply this one. I have one problem for entering CLOB data in oracle thru stored procedure in C#. I am getting an error for inserting CLOB data. I am not doing any extra thing to insert CLOB data. The way I am using to enter nvarchar data with the same way I am entering CLOB data. I am not setting any Entery point or not using Empty_CLOB(), such kind of function to enter CLOB data. waiting for your reply. Hardik Mehta. Software Engineer.
VIEWS ON THIS POST

133

Posted on:

Wednesday 7th November 2012
View Replies!

Customizing Query Output when displaying the result

HI, I have a PL/SQL Query Now I want that When the QUERY Returns '0' THe value under column appear to be Excellent . if it return 1 Then values under the displayed column is 'Good" and if 2 the value under the column should be 'Bad'. Does anybody know how to do this.
VIEWS ON THIS POST

85

Posted on:

Wednesday 7th November 2012
View Replies!

Pulling garbage characters from Orcale Dabatase.

I have my a concole application which queries an Oracle database and populates an SQL database with the data pulled. Problem is that when I query the Oracle database, it doesn't recognize certains characters such as m-dashes and curly quotation marks and replaces them with the garbage characters (the question mark). How can I query the database in Order to pull the exact characters without any tranformation
VIEWS ON THIS POST

106

Posted on:

Wednesday 7th November 2012
View Replies!

Write excel file on server to transfer to user, including colours

I want to export a large amount of data to an excel file (30,000 plus). This is no problem. The problem is that, one of the columns needs to be coloured green according to the data within it. For example, the colour contains percentages, and when the percentage is over 70%, then the cell needs to be green. I can do this easily using a Datagrid/Gridview. However, because of the amount of data, my server is crashing with a SystemOutOfMemory exception. What I would like to do is use a tool to write the data to an excel file, and then transfer the file. Is this possible , Just to update this thread, I am trying out EPPlus - an open source Excel docx builder. http://epplus.codeplex.com/releases/view/42439
VIEWS ON THIS POST

141

Posted on:

Wednesday 7th November 2012
View Replies!

Import data from excel and making every record unique

I am developing a website admin control page....I have to import data from an excel sheet to a table...the table same columns as excel sheet....how do i import data from excel sheet....also i have to make every record unique by giving a unique ID...please help...how do i do this...
VIEWS ON THIS POST

72

Posted on:

Wednesday 7th November 2012
View Replies!

SQL Inner Join

I have three tables as follows with Role and Permission exhibiting many to many through Role Permission Table. 1.Role 2.Permission 3.RolePermission I write a query to get all the permissions for Roleid =3 which can be written in the following two ways to produce the same result. MyQusetion is are the following two queries are the same and will they have the same impact. 1 . Select PermissionName from Permission InnerJoin RolePermission On Permission.PermissionID= RolePermission.PermissionID 2. Select PermissionName From RolePermission InnerJoin Permission on RolePermission.PermissionID = Permission.PermissionID
VIEWS ON THIS POST

32

Posted on:

Wednesday 7th November 2012
View Replies!

how to insert 'EH&S' into table in a varchar field

I am trying to update Varchar field and want to insert EH&S insted of EHS here is my code Set define off; update table1 set name='EH&S' where name='EHS' when i run this query itpopup the window andask for enter value for S. Can anyone help me for this issue
VIEWS ON THIS POST

135

Posted on:

Wednesday 7th November 2012
View Replies!

Howto execute several sql statements in one command of the DataAdapter Class -> Error: ORA-00911: invalid character

Hi! When working with SQL Server it's possible to execute several sql statements in one command of the DataAdapter Class. The Statements are seperated from each other with a semikolon. Now I try the same thing with an oracle database and recieve error ORA-00911: invalid character\n. The statement I want to execute is the insert statement of the DataAdapter and is the following: "INSERT INTO Benutzer(changedate, createdate, changeuser, createuser, Name, Vorname, AdressenID ) VALUES (sysdate, sysdate, 'dummyuser', 'dummyuser', :name, :surname, :adressid ) returning Benutzerid into :IDENTITY; SELECT Benutzer.BenutzerID AS userid, Benutzer.changedate AS Benutzer_changedate, Benutzer.changeuser AS Benutzer_changeuser, Benutzer.createuser AS Benutzer_createuser, Benutzer.createdate AS Benutzer_createdate, Benutzer.Name AS name, Benutzer.Vorname AS surname, Benutzer.AdressenID AS adressid FROM Benutzer WHERE Benutzer.BenutzerID = :IDENTITY" Problem is the semikolon between the insert and select statement - both statements work fine when executed alone. Is there a different delimiter in oracle than on sql server Is there a way to make that work on oracle thanx in advance Andreas
VIEWS ON THIS POST

113

Posted on:

Wednesday 7th November 2012
View Replies!

Using CLOBs with OracleClient and without DataSet/DataAdapter

I thought I would post this solution here as I spent some 3 hours searching for a way to do this and it wasn't until I found this article that I managed to get it to work. I hope that it may save other people a lot of searching. I wanted to store XML documents into a CLOB in Oracle v8.1.7, but ran into the problem of them being over 4K in size and could therefore not be stored. I found many samples that showed how to handle binary files, but non demonstrated using a simple string of XML. Anyway, the solution can be found at this link: http://groups.google.com/groupsq=vosges+group:microsoft.public.dotnet.framework.adonet&hl=fr&lr=&ie=UTF-8&selm=d0193002.0302190622.7e8ac283@posting.google.com&rnum=1 credit goes to Philippe Vosges for the solution. Cheers Alex
VIEWS ON THIS POST

44

Posted on:

Wednesday 7th November 2012
View Replies!

Steps for upgrading a standby database in RAC

o/s: RHEL 4 live: 10.2.0.1 using ASM in 2 node RAC cluster drs: 10.2.0.1 using ASM in 2 node RAC cluster with dataguard Hi all, We need to upgrade our databases to 10.2.0.4 in the abover enviroment and I wanted to know if anyone had an outline of steps to take to upgrade the above. I know of the steps taken to upgrade a database in a RAC enviroment. However am a bit confused as how to approach this scenario due to the added dataguard configuration we have. Can any suggest a brief outline pls
VIEWS ON THIS POST

52

Posted on:

Thursday 15th November 2012
View Replies!

SQL Error in Stored Proc.

if i run this query from SQL prompt it is working fine. if i put it in stored procedure i am getting error. especialley if i put the ORDER BY clause i am getting. _______________________________________ INSERT INTO north_east SELECT min_num,configfile_id,correct_file,event_dt FROM sub_status WHERE rownum < v_trashhold AND event_dt < (sysdate - 5) AND event != 'D' AND active_file != correct_file AND market_num = v_market_num AND region_id = 2 ORDER BY event_dt; SQL> show errors; Errors for PROCEDURE SP_MASS_PUSH: LINE/COL ERROR -------- ----------------------------------------------------------------- 39/6 PLS-00103: Encountered the symbol "ORDER" when expecting one of the following: * & - + ; / at mod rem return returning and or group having intersect minus start union where connect || The symbol "group was inserted before "ORDER" to continue. __________________________
VIEWS ON THIS POST

126

Posted on:

Thursday 15th November 2012
View Replies!

services Monitoring Dashboard in OEM

when I tried to run Service Monitoring Dashboard in OEM, one of the screen ask for the "Web Application to included in this report" so I was trying to picl the web site "EM WEbsite" (the only one target type that I have in my GRID), two questions 1. what is this EM Website for 2. right now it show as down, do I need for it to be up everything in my GRID seems to work ok but this guy is down and I don't know what it's for.
VIEWS ON THIS POST

32

Posted on:

Thursday 15th November 2012
View Replies!

Search within a PL/SQL Table

I have a procedure that populates a PL/SQL Table. I want to search the table for specific values and not use loop from table.FIRST to table.LAST. Is there a quick way to search the structure \t Rotem.
VIEWS ON THIS POST

39

Posted on:

Thursday 15th November 2012
View Replies!

reset logs question

, I was wondering how I can reset my logs without having the database in recover mode. I want to reset the logs so that I can resync my rman instance with the arc logs on mountpoints. My co-dba worker accidently erased the arch mountpoints and I lost all archive logs. ever since rman isnt synced correctly. please help Jigar
VIEWS ON THIS POST

84

Posted on:

Friday 16th November 2012
View Replies!

Relationship between V$Session and V_$Session

With the following SQL statement I know that V$SESSION is a synonym of V_$SESSION Code: SQL> SELECT owner, table_name, table_owner FROM dba_synonyms WHERE synonym_name = 'V$SESSION'; OWNER TABLE_NAME TABLE_OWNER -------------- ---------------- --------------- PUBLIC V_$SESSION SYS but when I connect to SYS and run the following SQL statements Code: SELECT COUNT(*) FROM v$session; COUNT(*) -------- 17 and SELECT COUNT(*) FROM v_$session; COUNT(*) -------- 4 how can it be possible
VIEWS ON THIS POST

50

Posted on:

Friday 16th November 2012
View Replies!

parameter must be defined error on mysql with c#

the below code gives Parameter '@row' must be defined error on filling data adaptor what is wrong with my code ... best regards \t\t\t StringBuilder sql = new StringBuilder(); \t\t\t sql.Append("SET @row:=0;"); \t\t\t sql.Append("SELECT * From ("); \t\t\t sql.Append("Select @row:=@row+1 As Rec_No, _Col1, _Col2"); \t\t\t sql.Append("From _Tab1) As _T1"); \t\t\t sql.Append("WHERE Rec_No > 1 And Rec_No
VIEWS ON THIS POST

505

Posted on:

Tuesday 20th November 2012
View Replies!

Reading from Excel Problem?

I am trying to read from excel file (xls) and the problem is there is header and only one row of information in this excel but it gets 3 rows! Where is the problem My connection string is like below: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\Book1.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'; Here is the sample code: DimconnectionStringAsString="Provider=Microsoft.Jet.OLEDB.4.0;DataSource=d:\Book1.xls;ExtendedProperties='Excel8.0;HDR=Yes;IMEX=1';"DimfactoryAsDbProviderFactory=DbProviderFactories.GetFactory("System.Data.OleDb")DimadapterAsDbDataAdapter=factory.CreateDataAdapter DimselectCommandAsDbCommand=factory.CreateCommandselectCommand.CommandText="SELECTVENDORNO,VENDORNAME,DEBITFROM[Sheet1$]" DimconnectionAsDbConnection=factory.CreateConnectionconnection.ConnectionString=connectionStringselectCommand.Connection=connectionadapter.SelectCommand=selectCommandDimdebitsAsDataSet=NewDataSetadapter.Fill(debits) 'apppathConsole.WriteLine(System.Reflection.Assembly.GetExecutingAssembly.Location())removeapplicaitonfilefromfolderDimpathAsString=System.Reflection.Assembly.GetExecutingAssembly.Location()DimiAsIntegeri=path.Length-1DoUntilpath.Substring(i,1)="\"\t i=i-1Loop path=path.Substring(0,i)If(NotSystem.IO.Directory.Exists(path+"\Debit"))Then\t\t\t\t\t\tSystem.IO.Directory.CreateDirectory(path+"\Debit")EndIfDimdtAsDate=Date.NowDimdAsString=Format(dt,"yyMMdd")DimtxtnameAsString="boa_"+d+"01_debit_w_interim.txt"DimswAsStreamWriter=NewStreamWriter(path+"\Debit\"+txtname) Dimtotal_amountAsDouble=NothingDimtotal_numAsInteger=Nothing 'Writealineoftextsw.WriteLine("H"+Format(dt,"ddMMyyyy")+"0640",True)DimrowAsDataRowForEachrowIndebits.Tables(0).Rows -----------------------> 3 rows!!! But it is 1 in the excel file\t Console.WriteLine(row(0).ToString)\t Console.WriteLine(row(1).ToString)\t Console.WriteLine(row(2).ToString)\t DimamountAsDouble=Convert.ToDouble(row(2).ToString.Replace(".",String.Empty))\t DimlengthAsInteger=Nothing\t length=amount.ToString.Length\t DimtoLimitAsInteger=15-length\t Dimstr2AsString=Nothing\t ForvalueAsInteger=0TotoLimit-1\t\t\t str2=str2+"0" Next Dimtemp_textAsString=vbTab&vbTab&Format(dt,"yyyyMMdd")+str2+amount.ToString sw.WriteLine("D00"+row(0).ToString()+row(1).ToString+temp_text,True) 'Totals total_amount=total_amount+row(2) total_num=total_num+1Next Here is the sample excel: VENDORNO VENDORNAME DEBIT 102005 Spartus-S MMC 1.87
VIEWS ON THIS POST

120

Posted on:

Tuesday 20th November 2012
View Replies!

Proper Syntax


VIEWS ON THIS POST

31

Posted on:

Tuesday 20th November 2012
View Replies!

Oracle Sequences

I'm using a sequence to generate a reference number for a record. It works for a while but then starts incrementing by 20 instead of 1. I'm doing is using select reference.nextval from DUAL. Has anyone else had this problem and know how to fix it
VIEWS ON THIS POST

65

Posted on:

Wednesday 21st November 2012
View Replies!

Installing ODP.net Docs into .Net Framework Help

Does anyone know how to go about installing the ODP.Net Help files into the .Net Framework help system I know it just works with Visual studio. It seems i may need to generate a HxI file for the system. I have no idea how to go about this.
VIEWS ON THIS POST

119

Posted on:

Wednesday 21st November 2012
View Replies!

Simple question

Hi guyz, I have a simple question, the question is that what ever i am doing or making in sql*plus (creating triggers, functions, settings, changing the nls_lang and etc) they are working in sql*plus but as i want them to work from the oracle form i am developing, they are not working i mean oracle form can't find them somehow. Now i want to know is there any link between the sql*plus and oracle form that they should know each others events, functions, triggers, settings, configurations ..... or no we cant do this please help meeee i need for help. Kind
VIEWS ON THIS POST

78

Posted on:

Saturday 29th December 2012
View Replies!

Execute Sp

Basic question how to you execute a stored procedure in ORACLE For example in SQL SERVER its just EXEC Proc_Name ParameterValues How the hell do you do this in oracle i just want to test if my stored procedure works. Many
VIEWS ON THIS POST

196

Posted on:

Monday 31st December 2012
View Replies!