Your personal experience with "1Z0-047" Oracle Database SQL Expert


Hi

Please write your personal experience (and Oracle background) with
1Z0-047 and give safe bets on passing exam. I am writing since 3 years
on a weekly basis SQL scripts on *NIX. Failed 1Z0-047 twice, attended
official training and studied with all available 1Z0-047 study guides
(lab environment). Is the Oracle Database SQL Expert exam really that tough
Posted On: Monday 31st of December 2012 01:48:31 AM Total Views:  305
View Complete with Replies

RELATED TOPICS OF Oracle PROGRAMMING LANGUAGE




Regarding Oracle software cost

Recently I read some where that cost of Oracle enterprise edition costs $40,000 per processor. Does it mean that if there are 2 processors then a separate software is required which costs about $80,000 Please clarify.
VIEWS ON THIS POST

88

Posted on:

Wednesday 7th November 2012
View Replies!

To find objects belonging to a tablespace

I have just noted that today, one of the datafiles that was yesterday, 484 Meg full - is today 250 Meg full. Each datafile relates directly to a tablespace for simplicity so. Can someone help me discover every object that exists in a tablespace please, tables - indexes and anything else. I assume I need a query of some sort.
VIEWS ON THIS POST

158

Posted on:

Wednesday 7th November 2012
View Replies!

Questions about constraints(3)

From the procedures how Oracle server implement unique and primary key constraints, there is one saying : - If the constraint is enabled and the columns in the constraint form the leading part of an index, the index will be used to enforce the constraint. Can someone tell me what the leading part of the index means Maybe I do not understand well because English is not my native language.
VIEWS ON THIS POST

105

Posted on:

Wednesday 7th November 2012
View Replies!

Question to DBAs

I know this may sound silly, but in the past I have noticed a number of opinions to come up on this. So I'm curious to think how this forum would respond to this. So what is it What would you do, if you have been provided with the following things: 2 powerfull servers 8i latest EE 8x2 GB RAM GBit Eathernet connection Storage Filer (for performance lets throw NETAPPs Filer) You would have to support the following things: 3 Heavy OLTP application schemas using jdbc/weblogic/appache/php and etc (MISSION CRITICAL, 100-1000 concurrent users) 5 bactch operation schemas executed every (5Hrs) Now coming to the question How would you go about seting up things Would you put all the mission critical things on one system and all the others on the other Would you put things spread across Would you set up standby option/parallel option Would you think of creating multiple instances and putting all accross them or one instance for all the OLTP and one for all the batch job schemas Any one willing to flush some thoughts or questions Just keen to see how would each individuals approach would be. Also, I would like you to backup you suggestions such that the others could agree or refute to your opinions. Lets see Sam
VIEWS ON THIS POST

47

Posted on:

Wednesday 7th November 2012
View Replies!

Oracle V8.i using the Fail Over option

Hi Could anybody tell me what what is fail Over option in 8i. Any link or documentation\t
VIEWS ON THIS POST

96

Posted on:

Wednesday 7th November 2012
View Replies!

mysql: how do i go about retrieving data from a mysql database?

Hi all, Using vb.net, whats the best way to get data from a mysql database I want to write a class that does this for me
VIEWS ON THIS POST

187

Posted on:

Wednesday 7th November 2012
View Replies!

cannot expand named range in excel

, I am writing a small application which has to open up an exisitng excel file and write into the excel file using system.data.oledb . I have actually created a named range inside the excel file called as "one" and that has just two columns. here is my codestringconnectionString=@"Provider=Microsoft.Jet.OleDb.4.0;DataSource=C:/test.xls;ExtendedProperties=""Excel8.0;HDR=YES;"""; System.Data.OleDb.OleDbConnectionconn= newSystem.Data.OleDb.OleDbConnection(connectionString); try { conn.Open(); OleDbCommandcmd=newOleDbCommand("Insertintoonevalues('a','b')"); cmd.Connection=conn; cmd.ExecuteNonQuery(); Response.Write("success"); } catch(Exceptionex) { Response.Write(ex.Message); } The whole process works well until i convert one of the columns of the named range into a calculated column say i convert the second column to =LEN(A1) and drag the formula column to span multiple rows in the same column. when i run the application, it throws a error saying cannot expand named range. in short oledb is having problems writing data to a calculated column in excel. Please help me regarding this problem. I would have posted the sample excel file along with this post but when i try doing so, i get the error saying "you dont have permissions to upload files. Please contact administrator". neways, please help me with a solution
VIEWS ON THIS POST

220

Posted on:

Wednesday 7th November 2012
View Replies!

MySQL connector does not show up in Add Reference .NET list

Win 7 I have installed MySQL connector 6.3.6. Inside VS 2010, tried to Add Reference to my project, this MySQL.Data does not appear under .NET tab but need to use Browse tab instead. Any reason It appear in Win XP machine though.
VIEWS ON THIS POST

165

Posted on:

Wednesday 7th November 2012
View Replies!

Getting data from oracle sp

Hi i have a sp in oracle which has 3 input parameters and 3 output parameters( 1 ref cursor, i number, 1 varchar2) Command.CommandText = sp name; Command.CommandType = CommandType.StoredProcedure; i am doing cmd.ExecuteNonQuery(); I am able to get the number and varchar2 values from output parameters. But i am unable to get output from Ref Cursor. (How can i get this...) If i use dataadaptor then i am getting the datatable in the dataset(but i am not getting the other two parameters(where will get the other two\t)... Please help i am new to oracle...
VIEWS ON THIS POST

142

Posted on:

Wednesday 7th November 2012
View Replies!

re: sqlplus directory .. error reading file

hi!, I have a sql file in directory c:\dbscripts directory called abc.sql abc.sql calls file def.sql in c:\dbscripts directory and xyz.sql in c:\dbscripts\wmi subdirectory when I open the file to run "abc.sql" from sql*plus using file -> open it is erroring out saying that the ORA-00933: SQL command not properly ended I suspect it has to do with the buffer size ..!! or with recognizing the subdirectory "wmi" ... Can anybody help me out and tell me how to rectify it or point me to the relevant documentation I do not have a metalink id ..
VIEWS ON THIS POST

350

Posted on:

Friday 16th November 2012
View Replies!

Connecting to Oracle

My .net 2.0 application, accssing Oracle,that works fine on my development machine does not work on my production server. The error messgae says: Exception Details: System.Exception: System.Data.OracleClient requires Oracle client software version 8.1.7 or greater. Can anyone tell me what exactly do I have to install on the production server and what the url for downloading it is
VIEWS ON THIS POST

81

Posted on:

Wednesday 21st November 2012
View Replies!

[HYT00] [MySQL][ODBC 3.51 Driver]Access denied for user

Hi Expert I have connection problems with my database when running my local computer in IIS server. I installed mySQL 4.0, MySQL Front and MYODBC 3.5 and configure everything. I ran in several other computers and it works fine, but not this particular computer. When I tried to retrieve the item, there is error: ERROR [HYT00] [MySQL][ODBC 3.51 Driver]Access denied for user 'XXX@'localhost' (using password: YES) Exception Details: System.Data.Odbc.OdbcException: ERROR [HYT00] [MySQL][ODBC 3.51 Driver]Access denied for user 'XXX'@'localhost' (using password: YES) Appreciate for your help!
VIEWS ON THIS POST

255

Posted on:

Wednesday 21st November 2012
View Replies!

Updating using Oracle

Imports System.Data.OracleClient Dim OraCON As New OracleConnection(ConfigurationManager.ConnectionStrings("Oraconn").ConnectionString) Dim OUTcmd As New OracleCommand("UPDATE Login_status SET date_out = sysdate WHERE date_out IS NULL AND date_visit=(SELECT MAX(date_visit) from login_status where id_number='" & Session("IDnumber")& "')", OraCON) OraCON.Open() OUTcmd.ExecuteNonQuery() OraCON.Close() My Question is, is this the right way of updating a record without using the SQLdatasource, or are there any alternatives scripts/codesI could use
VIEWS ON THIS POST

100

Posted on:

Wednesday 21st November 2012
View Replies!

UDT parameters in ODP.NET

We use Oracle Designer to create DB, packages, procedures etc. Thats all fine. The problem arises when we want to use the procedures from ODP.NET because the packages contain pl/sql specific code, that is not directly compatible with .NET. For instance tha package includes: TYPE cg$ind_type IS RECORD( ... ); PROCEDURE slct(cg$sel_rec IN OUT cg$row_type); So the question is how do we pass the cg$sel_rec parameter to the procedure from .NET. How do i create an OracleParameter that is compatible with cg$ind_type
VIEWS ON THIS POST

83

Posted on:

Wednesday 21st November 2012
View Replies!

Npgsql provider (PostgreSQL) ...

all... Id like to know if it is posible to use npgsql provider to use a login system components(roles,membership provider....) Anyone could help-me ty
VIEWS ON THIS POST

162

Posted on:

Wednesday 21st November 2012
View Replies!

Problem with reading excel data

Hi i Need some help in my application,to read data from excel sheet uploaded by user in my application. i'm using following code. this works fine onmy local machine, but giving error for the deployed version on the server (windows server 2003). Error: The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data. one more thing is do we need excel installed on the server too (My server doesn't have it) Code i'm using: private void readExcelData() { string ss = FileUpload1.PostedFile.FileName; String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + ss + ";" + "Extended Properties=Excel 8.0;"; // Create connection object by using the preceding connection string. OleDbConnection objConn = new OleDbConnection(sConnectionString); objConn.Open(); // SELECT command to display the data from the worksheet. OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [Sheet1$]", objConn); OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(); objAdapter1.SelectCommand = objCmdSelect; excelDataSet = new DataSet(); objAdapter1.Fill(excelDataSet, "XLData"); objConn.Close(); } can someone help me how to come out of this situation.
VIEWS ON THIS POST

199

Posted on:

Wednesday 21st November 2012
View Replies!

Select specified row of record?

Hi all, I wish to know if there any way I can select a specified rows of record from a SELECT statement e.g. I have a 1000 record, but all I want is the 101th to 150th
VIEWS ON THIS POST

143

Posted on:

Wednesday 21st November 2012
View Replies!

How to use Oracle as backend for .Net appliations

Hi Guys If I want to use Oracle as my backend, wat configuration settings I need\t System.Data.SqlClient is there but not OracleClient. what namespace is required for my appliation
VIEWS ON THIS POST

98

Posted on:

Wednesday 21st November 2012
View Replies!

Copy table's data!!!

How can i copy data from table to another in same or different database!
VIEWS ON THIS POST

100

Posted on:

Wednesday 21st November 2012
View Replies!

getting the insert id

Hi Can anyone tell me, after an INSERT, how do I get the row's id In mssql it is select @@identity or similar and in php there is a function call to get it. what is it in .NET Also, does this work with REPLACE as well as INSERT
VIEWS ON THIS POST

119

Posted on:

Wednesday 21st November 2012
View Replies!

Looking for tutorial on Login + SQL

I have an SQL DB with a table called Users, inside is a bunch of columns but the two I need to work with are loginID and loginPwd. Basically I'm trying to do a login .aspx page that checks to see if the login name and password match. If so, redirect to a "Welcome member" page. If no matches, redirect to "Not registered\t Sign up". Have been pulling my hair out trying to find tutorials on Google and reading through thi forum. I'm having no luck. Please note I'm new to Visual Studio.net so any sort of walkthrough or tutorial geared more towards a newbie would be great. Perhaps a book suggestion even
VIEWS ON THIS POST

43

Posted on:

Wednesday 21st November 2012
View Replies!

Help connecting to remote MySQL database in ASP.NET (using VB.NET)

well, it would help if you read the documentation on System.Data.Odbc. Odbc Doesnt use named parameters Like SqlServer does. W/ Odbc, '' is the offically blessed placeholder.
VIEWS ON THIS POST

196

Posted on:

Wednesday 21st November 2012
View Replies!

For a MySql Expert

As you can see I am trying to make a bussiness object with an ODBC Connection and I am using MySQL. Where the problem comes in though is the "objCmd.Parameter.Add(objParam)" Now that actually works with an oledbConnection, but not odbc, can anyone else tell me how to fix this. ImportsSystem ImportsSystem.Data ImportsSystem.Data.Odbc NameSpaceMiller PublicClassUserDetails PublicUserIDasInteger PublicFirstNameAsString PublicLastNameASString PublicUserNameASString PublicPasswordASString PublicEmailASString PublicAddressASString PublicCityASString PublicStateAsString PublicZipASInteger EndClass PublicClassUser DimMyConnectionStringAsString="DRIVER={MySQLODBC3.51Driver};"&_ "SERVER=localhost;"&_ "DATABASE=LoveShare;"&_ "UID=user;"&_ "PASSWORD=mypassword;"&_ "OPTION=3;" PrivateobjConnAsNewodbcConnection(MyConnectionString) PublicFunctionLogin(StrUserNameAsString,strPasswordASString)ASInteger DimIntIDASInteger DimstrSQLLogin="SelectUserIDfromtblUsers"&_ "WhereUserName=@UserName"&_ "AndPassword=@Password" DimobjCmdAsNewodbcCommand(strSQLLogin,objConn) DimobjParamasnewodbcParameter("@UserName",odbctype.Char) objParam.Value=strUserName objCmd.Paramater.Add(objParam) objParamasnewodbcParameter("@Password",odbctype.Char) objParam.Value=strPassword objCmd.Parameter.Add(objParam) Try objConn.Open intID=CType(objCmd.ExecuteScalar,Integer) objConn.Close Catcheasexception Throwe Endtry ifintid.ToString=""then return0 endif returnintID EndFunction PublicSubAddUser(objUserASUserDetails) DimIntIDAsodbcDataReader DimobjReaderASobdcReader DIMsqlMaxstrASString=\t"SELECTMAX(userID)FromtblUsers" DimobjcmdIDASNewodbcCommand(sqlMaxstr,objConn) DimsqlStringAsString="INSERTINTOtblUsers(UserName,Password,FirstName,LastName,Address,City,State,Zip,Email)"_& "Values(@UserName,@Password,@FirstName,@LastName,@Address,@City,@State,@Zip,@Email)" DimobjCmdAsNewodbcCommand(sqlString,objConn) DimobjParamAsNewodbcparameter("@UserName",odbcType.Char) DimobjParam.Value=objUser.UserName objCmd.Parameter.Add(objParam) objParam=NewodbcParameter("@Password",odbcType.Char) objParam.Value=objUser.Password objCmd.Parameter.Add(objParam) objParam=NewodbcParameter("@FirstName",odbcType.Char) objParam.Value=objUser.FirstName objCmd.Parameter.Add(objParam) objParam=NewodbcParameter("@LastName",odbcType.Char) objParam.Value=objUser.LastName objCmd.Parameter.Add(objParam) objParam=NewodbcParameter("@Address",odbcType.Char) objParam.Value=objUser.Address objCmd.Parameter.Add(objParam) objParam=NewodbcParameter("@City",odbcType.Char) objParam.Value=objUser.City objCmd.Parameter.Add(objParam) objParam=NewodbcParameter("@State",odbcType.Char) objParam.Value=objUser.State objCmd.Parameter.Add(objParam) objParam=NewodbcParameter("@Zip",odbcType.Int) objParam.Value=objUser.Zip objCmd.Parameter.Add(objParam) objParam=NewodbcParameter("@Email",odbcType.Char) objParam.Value=objUser.Email objCmd.Parameter.Add(objParam) Try objConn.Open ObjCmd.ExecuteNonQuery objUser.UserID=CType(objCmdID.ExecuteScalar,Integer) objConn.Close Catcheasexception Throwe EndTry ifobjUser.UserID.ToString=""Then objUser.UserID="0" EndIF EndSub EndClass EndNameSpace
VIEWS ON THIS POST

100

Posted on:

Wednesday 21st November 2012
View Replies!

Problem -> can't find table using ODBC

, I'm using an ODBC connection to my database. This connection works and is set correctly. In my program i'm using IAnywhere from Sybase. The problem is that when I'm trying to get data from de database it doesn't work. Can't find table. The code I wrote is correctly because it worked on another database. Yes it worked because when i want to use my other database there also can't be found any table. The connection to the database works, only no tables can't be found. So my ODBC is correct, my code is correct and the database is online and working. Where can the problem be Thanx,
VIEWS ON THIS POST

69

Posted on:

Wednesday 21st November 2012
View Replies!

can't load oci.dll

I use ado.net connect oracle database in my asp.net application, but cant connect the database,it reports can't load oci.dll, but I use sql plus to connect the database ,it works well, who can give me a idea
VIEWS ON THIS POST

177

Posted on:

Wednesday 21st November 2012
View Replies!

DATEFORMAT Q

I have a table containing a DATETIME field. How can I in my SELECT use DATEFORMAT to retrieve only the parts of this DATETIME field that I need, i.e. just month and day with a "/" between, or just hour and minutes with a ":" inbetween /Raymond
VIEWS ON THIS POST

101

Posted on:

Wednesday 21st November 2012
View Replies!

'{MySQL ODBC 3.51 Driver}' provider is not registered

G'day, No error information available: REGDB_E_CLASSNOTREG(0x80040154). The '{MySQL ODBC 3.51 Driver}' provider is not registered on the local machine is the the basis of my error essage despite having installed it (twice). Am I missing something There are so many potential pitfalls that I'd love to see a real simple example of working code.
VIEWS ON THIS POST

161

Posted on:

Wednesday 21st November 2012
View Replies!

MySql Date Comparison?

Hi all, How can I get the fields where dateField = this; For example select * from tbl_customers where DOB=11/21/1980; Is this a valid way to compare the dates.I'm getting the EOF. Thanking in anticipation. M.Shahzad
VIEWS ON THIS POST

111

Posted on:

Wednesday 21st November 2012
View Replies!

why can't insert data into mysql database?

b4 i install mysql5, when i run in mysql4, it's ok. but after i install mysql5 store it in /mysql5 folder but not /mysql, i cannot insert record but can get record from the tables. i just wonder why\t\t
VIEWS ON THIS POST

390

Posted on:

Wednesday 21st November 2012
View Replies!

mySQL 5.0 - release dates?

Hi . Seeing that the alpha version of mySQL 5.0 is out (http://www.mysql.com/downloads/mysql-5.0.html) I'm just wondering if anyone knows when they plan to release the beta version and when they plan to release (finally) the stable (non Beta) version I've looked at mySQL's roadmaps but they don't mention dates (http://www.mysql.com/doc/en/Roadmap.html) Cheers Andrew
VIEWS ON THIS POST

216

Posted on:

Wednesday 21st November 2012
View Replies!

Connection problems

I am having problems connecting to an Oracle database, I have set everything up running against an Oracle 9i db with the client and ODP.Net data provider. I seem to however have problems establlishing connections, they take about 6 seconds to connect before data is retreived. Has anybody had this problem before and can anyone tell me how to speed up the intial connection process.
VIEWS ON THIS POST

261

Posted on:

Wednesday 21st November 2012
View Replies!

Please can u detect this error?

i have 2 comboboxes and i want 2 select form the first and make the other details 4 each selected item of the first such that when in select teh first value it insists on this value and can not be changed to whatever(The case i have) iwant to be static on the selected value(current) here is the code: This is the master: Private Sub DropDownList2_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownList2.Load Dim ds As DataSet Dim conn As OracleConnection Dim daAuthors As OracleDataAdapter conn = New OracleConnection _ ("user id=isas;data source=orarep1;password=isas") Dim cmdCities As New OracleCommand("SELECT distinct title_code,title_name from PUBL_title", conn) conn.Open() Dim drCities As OracleDataReader drCities = cmdCities.ExecuteReader() DropDownList2.DataSource = drCities DropDownList2.DataTextField = "title_name" DropDownList2.DataValueField = "title_Code" DropDownList2.DataBind() DropDownList2.Items.Add("[\t\t]") DropDownList2.SelectedIndex = DropDownList2.Items.Count - 1 End Sub This is the details: Private Sub DropDownList2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DropDownList2.SelectedIndexChanged Dim ds As DataSet Dim conn As OracleConnection Dim daAuthors As OracleDataAdapter conn = New OracleConnection _ ("user id=isas;data source=orarep1;password=isas") Dim sql As String sql = "SELECT PUBL_book.book_name,PUBL_book.book_code," & _ "publ_title.title_code,publ_title.title_name from PUBL_book," & _ "publ_title where publ_title.title_code=publ_book.title_code " & _ "and publ_title.title_code = " & DropDownList2.SelectedValue Dim cmdCities As New OracleCommand(sql, conn) conn.Open() Dim drCities As OracleDataReader drCities = cmdCities.ExecuteReader() DropDownList3.DataSource = drCities DropDownList3.DataTextField = "book_name" DropDownList3.DataValueField = "book_code" DropDownList3.DataBind() If DropDownList2.SelectedValue = "[all]" Then ' DropDownList2.DataSource = drCities Else 'Dim dvDocs As New DataView(DsDoctors1.Tables(0)) End If ' dgDoctors.DataBind() End Sub End Class so please what is the problem\t
VIEWS ON THIS POST

150

Posted on:

Wednesday 21st November 2012
View Replies!

ORACLE ODBC DRIVER error with UNION statement

Hi I am trying to run the following statement in my access database that has links to tables in an oracle 8i database: (SELECT DISTINCT PRIME_TRS_LOG.INDEXNO, PRIME_TRS_LOG.DTE, PRIME_TRS_LOG.STATUS, PRIME_TRS_LOG.TEXT FROM PRIME_TRS_LOG WHERE (PRIME_TRS_LOG.INDEXNO=80 AND PRIME_TRS_LOG.DTE=(SELECT Max(PRIME_TRS_LOG.DTE) AS MaxOfDTE FROM PRIME_TRS_LOG WHERE PRIME_TRS_LOG.INDEXNO=80 AND PRIME_TRS_LOG.DTEDate()-7+6/24))); Each select statement on its own works fine in access and returns what i need it to return. However I need to union the statements because i need both results in one datatable to be able to create a graph. But, i get the following error: ODB--Call Failed [Oracle][ODBC Oracle Driver] Invalid Cursor State. (#0) So i am thinking the statement is okay for access but as soon as goes to the oracle odbc driver it is sent right back with the error. If anyone knows how to fix please let me know. Thank you
VIEWS ON THIS POST

159

Posted on:

Wednesday 21st November 2012
View Replies!

inserting a date

How in gods name do I insert a date into MySQL. If I try to insert "11/10/2003" , European, it inserts it in "2003-11-10" format. If i insert "31/10/2003", it inserts "0000-00-00". Whats happening \t
VIEWS ON THIS POST

205

Posted on:

Wednesday 21st November 2012
View Replies!

Sql and coolmenu

Hi Is there any won that now a program I can use to make cool menu and speak sql. Per-Anders
VIEWS ON THIS POST

163

Posted on:

Wednesday 21st November 2012
View Replies!

Oracle Query

Hi I am new to oracle, was pretty much involved in developing in sql server. Can someone please help me in converting the below query to oracle. Declare @lowyear int Declare @highyear int Declare @TotalRecords int Set @highyear = @Year Set @lowyear = 2001 while @highyear >= @lowyear begin Set @Year = @highyear Set @TotalRecords = (select count(*) from .....) DECLARE @TempTable TABLE (YearID char(45), TotalCount char(30), test1 char(30), test2 char(30), test3 char(30), test4 char(30), test5 char(30), test6 char(30), test7 char(30)) INSERT INTO @TempTable (YearID, ToalCount, test1, test2, test3, test4, test5, test6, test7) Select @Year, @TotalRecords, convert(decimal(4,1),round(convert(decimal(15,5),(select count(*) ....) * 100) / convert(decimal(15,5),(@TotalRecords)),2)), convert(decimal(4,1),round(convert(decimal(15,5),(select count(*) ....) * 100) / convert(decimal(15,5),(@TotalRecords)),2)), convert(decimal(4,1),round(convert(decimal(15,5),(select count(*) ....) * 100) / convert(decimal(15,5),(@TotalRecords)),2)), convert(decimal(4,1),round(convert(decimal(15,5),(select COUNT(*) ....) * 100) / convert(decimal(15,5),(@TotalRecords)),2)), convert(decimal(4,1),round(convert(decimal(15,5),(select COUNT(*) ....) * 100) / convert(decimal(15,5),(@TotalRecords)),2)), convert(decimal(4,1),round(convert(decimal(15,5),(select COUNT(*) ....) * 100) / convert(decimal(15,5),(@TotalRecords)),2)), convert(decimal(4,1),round(convert(decimal(15,5),(select COUNT(*) ....) * 100) / convert(decimal(15,5),(@TotalRecords)),2)) set @highyear = (@highyear - 1) end
VIEWS ON THIS POST

105

Posted on:

Friday 28th December 2012
View Replies!

Sequence Number issue in 11g RAC

Hi , In our production, we have two nodes in the cluster. We use the sequence for one of the main table for primary key. Our application is expecting sequence number increments along with created date time stamp. Right now sequences are cached for each node and it creates problem for the application. We would not like to use NOCACHE option because it causes performance issue. This is the current scenario - Transaction #1 on Node 1 - Seq ID 1 - Time Stamp 12:01 Transaction #2 on Node 2 - Seq ID 51 - Time Stamp 12:02 Transaction #3 on Node 1 - Seq ID 2 - Time Stamp 12:03 When I try to query based on the time stamp, primary should also go up. How do I achieve that To be very clear on what I would like to have, please consider the following example. Without using NOCACHE option, I need to have the data in the following order. Transaction #1 on Node 1 - Seq ID 1 - Time Stamp 12:01 Transaction #2 on Node 2 - Seq ID 2 - Time Stamp 12:02 Transaction #3 on Node 1 - Seq ID 3 - Time Stamp 12:03 In other words, sequence number should always increment along with the time.
VIEWS ON THIS POST

172

Posted on:

Friday 28th December 2012
View Replies!

When using Form Builder to create a new form from TEMPLATE.fmb, there is an error

FRM-30064: Unable to parse statement select timezone_code,name ,to_char(trunc(gmt_offset),'S09') || ':' || to_char(abs(gmt_offset - trunc(gmt_offset))*60,'FM900') offset ,decode(daylight_savings_flag,'Y','*',null) dst_flag from fnd_timezones_vl where enabled_flag = 'Y' order by gmt_offset. ORA-00942: table or view does not exist Record Group TIMEZONES Form: TEMPLATE FRM-30085: Unable to adjust form for output. How can I fix this error
VIEWS ON THIS POST

244

Posted on:

Friday 28th December 2012
View Replies!

Collapse a matrix to a single line item

I think it would be best to use a simple example to explain my problem. The table below needs to become compressed into to distinct line items Customer_No Hair SP_Code Gender Age Colour Weight 1 blonde 123 M null null null 1 blonde 123 null 23 null null 1 blonde 123 null null Green null 1 blonde 123 null null null 98 1 blonde 123 null null null null 1 blonde 123 null null null null 1 blonde 123 null null null null 1 blonde 123 null null null null 2 Blue 444 F null null null 2 Blue 444 null 28 null null 2 Blue 444 null null Pink null 2 Blue 444 null null null 64 2 Blue 444 null null null null 2 Blue 444 null null null null 2 Blue 444 null null null null 2 Blue 444 null null null null Needs to become Customer_No Hair SP_Code Gender Age Colour Weight 1 blonde 123 M 23 Green 98 2 blue 444 F 28 Pink 64 Thank-you very much for your time. Banner: Oracle Database 11g Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production "CORE 11.2.0.2.0 Production" TNS for Linux: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production I have over 163 000 'customers' and customer_no is not necessarily consecutive. There may not exist a customer 3 for example. ie Customer 1, Customer 2, Customer 4...etc. Is there any way to loop through the procedure and pick up the customer_no that exists in the Customer column
VIEWS ON THIS POST

193

Posted on:

Friday 28th December 2012
View Replies!

Page 2 - User Defined Data Type

Select the base data type from the list box. The list box displays all data types except for the geography, geometry, sys name, time stamp , and xml data types. The data type of an existing user-defined data type is not editable. dapfor.com/en/net-suite/net-grid/tutorial/data-types
VIEWS ON THIS POST

141

Posted on:

Saturday 29th December 2012
View Replies!

Inefficient VIEW

I have a view that gets used 3 times by another query and am being told that the view is inefficient. Any reccomendations on how I could change this I noticed this view is getting the value of acad_prog three times. Once from ps_acad_prog when finding the MAX of effdt, again from ps_acad_prog finding the MAX of effseq, and from ps_acad_prog when find the MAX of effdt from the ps_acad_plan_tbl. PHP Code: SELECTa.emplid ,a.acad_career ,r.effdt ,c.acad_plan ,d.descr ,a.institution ,a.acad_prog ,b.descr ,d.degree ,b.acad_group \tFROMps_acad_proga \t,ps_acad_prog_tblb \t,ps_acad_planc \t,ps_acad_plan_tbld \t,ps_ntsr_ref2_tblr WHEREa.emplid=c.emplid \tANDa.institution=b.institution \tANDa.institution=d.institution \tANDa.acad_career=b.acad_career \tANDa.acad_career=c.acad_career \tANDa.acad_prog=b.acad_prog \tANDa.acad_prog=d.acad_prog \tANDc.acad_plan=d.acad_plan \tANDa.effdt=c.effdt \tANDa.effseq=c.effseq \tANDr.emplid=a.emplid \tANDr.acad_career=a.acad_career \tANDr.institution=a.institution \tANDa.effdt=( SELECTMAX(prg.effdt) \tFROMps_acad_progprg WHEREprg.emplid=a.emplid \tANDprg.acad_career=a.acad_career \tANDprg.stdnt_car_nbr=a.stdnt_car_nbr \tAND(prg.acad_progBETWEEN'200'AND'309' \t\tORprg.acad_prog='350' \t\tORprg.acad_progBETWEEN'401'AND'457') \tANDprg.effdt
VIEWS ON THIS POST

78

Posted on:

Saturday 29th December 2012
View Replies!

Handling NULL value in XML using getStringVal

I have below query which works fine if column 'XML_COL' has values. This select statement fails if the value is NULL for select xmltype(t.xml_col).extract('//fax/text()').getStringVal() from mytab t How to handle rows with NULL values in the column 'XML_COL'.
VIEWS ON THIS POST

475

Posted on:

Saturday 29th December 2012
View Replies!

Select fields longer than length x

I'm trying to select only codes from a column that are above a certain length. how would this be achieved I've tried char_length(fieldname) > x in the where clause but i'm getting the error ORA-00904: "char_length" invalid identifier.
VIEWS ON THIS POST

319

Posted on:

Saturday 29th December 2012
View Replies!

Having a problem with HAVING

The "HAVING" statement from the query below removes all rows where the "name" field starts with "Port" and it removes all rows where count is less than 11. What I want is to removes all rows where the "name" field starts with "Port" and the count for that row is less than 11. Any help or suggestions will be much appreciated.
VIEWS ON THIS POST

136

Posted on:

Sunday 30th December 2012
View Replies!

Sysdate if statement procedure help

Hi , I'm new here. I'm working on a project and I can't figure out the procedure I will need to use. I've got a sysdate field in my "Calls" table which generates a call date + time, however I need to insert a severity level of the call after a certain amount of time, eg after 1 hour level turns from level 4 to level 3 and so forth until reaching level 1 after x amount of time. I know I'd need to put in an else if statement within this procedure I just can't work out how to do it with the sysdate field that has also been created. Any help would be greatly appreciated. A bit of info about the table, table name = calls, column 1 = date_time and column 2 is called severity_level Cheers
VIEWS ON THIS POST

207

Posted on:

Sunday 30th December 2012
View Replies!

Issue to get record from database as text file in oracle form

Hi all, im really new in oracle and want learn something new lesson to me...can i now if i got 10k data,i want to show each data in row as flat file in oracle form..which means i want one row with one display item.please help me...
VIEWS ON THIS POST

104

Posted on:

Sunday 30th December 2012
View Replies!

How to make

how can I reduce the size of ------------- when table is null. I m in sqlplus I type Select A,B,C,D,F,G,H from SOMEHERE where B='GOAT1'; if A is 10 char long B is 50 c is 10 d is 30 e is 10 f is 50 if any of those don't have data it still outputs ------------------------------ (50) for B and tht covers the whole screen how can I make is to show less if it null
VIEWS ON THIS POST

85

Posted on:

Sunday 30th December 2012
View Replies!

Help Creating a 2D Table

Part of my homework requires me to make a couple of 2D tables. I completed one of the tables with no problem, but I am having trouble with the second one. The question I have finished: List hotel name and average room price for each room type for all hotels. Your output should look like a 2-D table. The finished table: Code: CREATE OR REPLACE VIEW SubProp AS SELECT hName, price, type FROM RoomDM JOIN HotelDM USING (hNo) WHERE type IN (Single) UNION SELECT hName, price, type FROM RoomDM JOIN HotelDM USING (hNo) WHERE type IN (Double) UNION SELECT hName, price, type FROM RoomDM JOIN HotelDM USING (hNo) WHERE type IN (Family) / CREATE OR REPLACE VIEW TabDetail AS SELECT * FROM ( SELECT hName, AVG(DECODE(type, Single, price))Single, AVG(DECODE(type, Double, price))Double, AVG(DECODE(type, Family, price))Family FROM SubProp GROUP BY hName ) / SELECT hName, TO_CHAR(Single, $999,990.99) Single, TO_CHAR(Double, $999,990.99) Double, TO_CHAR(Family, $999,990.99) Family FROM TabDetail; The Result to the finished table: Code: HNAME SINGLE DOUBLE FAMILY ------------------------- ------------ ------------ ------------ Holiday Inn $47.49 $84.99 $129.99 The Redmont Hotel $44.99 $84.99 $114.99 University Inn and Suites $64.99 $99.99 $119.99 3 rows selected. The Question I am having trouble with: Develop SQL code in Oracle that generates a statistical report of hotels verse room types in 2-D table format. The table I am having trouble with: Code: CREATE OR REPLACE VIEW SubProp AS SELECT hName, type, COUNT(type) AS tCount FROM RoomDM JOIN HotelDM USING (hNo) WHERE type IN (Single) GROUP BY hName, type UNION SELECT hName, type, COUNT(type) AS tCount FROM RoomDM JOIN HotelDM USING (hNo) WHERE type IN (Double) GROUP BY hName, type UNION SELECT hName, type, COUNT(type) AS tCount FROM RoomDM JOIN HotelDM USING (hNo) WHERE type IN (Family) GROUP BY hName, type / CREATE OR REPLACE VIEW TabDetail AS SELECT * FROM ( SELECT hName, COUNT(DECODE(type, Single, tCount))Single, COUNT(DECODE(type, Double, tCount))Double, COUNT(DECODE(type, Family, tCount))Family FROM SubProp GROUP BY hName ) NATURAL JOIN ( SELECT COUNT(type)Total FROM SubProp ) / BREAK ON REPORT COMPUTE SUM LABEL TOTAL OF Single ON REPORT COMPUTE SUM LABEL TOTAL OF Double ON REPORT COMPUTE SUM LABEL TOTAL OF Family ON REPORT COMPUTE SUM LABEL TOTAL OF Total ON REPORT SELECT * FROM TabDetail; The result to the table I am having trouble with: Code: HNAME SINGLE DOUBLE FAMILY TOTAL ------------------------- ---------- ---------- ---------- ---------- Holiday Inn 1 1 1 9 The Redmont Hotel 1 1 1 9 University Inn and Suites 1 1 1 9 ---------- ---------- ---------- ----------- TOTAL 3 3 3 27 3 rows selected. Looking at the result of this table it is obvious the numbers are wrong, but as I compare my code from the two tables I cannot see what I am doing wrong. I went ahead and determined what the result to the table would look like if done correctly, and it should look something like this table below. This is what the result should look like: Code: HNAME SINGLE DOUBLE FAMILY TOTAL ------------------------- ---------- ---------- ---------- ---------- Holiday Inn 15 10 5 30 The Redmont Hotel 9 7 4 20 University Inn and Suites 5 5 2 12 ---------- ---------- ---------- ----------- TOTAL 29 22 11 62 3 rows selected. HotelDM (hNo, hName, street, city, state, zipcode) RoomDM (rNo, hNo, type, price) In HotelDM hNo is the primary key In RoomDM rNo is the primary key and hNo is a primary and foreign key I will appreciate all the help I can get. Thank you.
VIEWS ON THIS POST

71

Posted on:

Sunday 30th December 2012
View Replies!

Inline pragma before cursor

I have a stored proc that calls a user-defined function "fun" like this (greatly simplified): Code: procedure foo(p_cursor out ref cursor) begin open p_cursor for select a.* from a where fun(a.b) = 1 end foo; I want to call fun with "pragma inline(fun, 'YES')". According to the Oracle documentation, the inline pragma will affect only the following statements if placed right before them: Code: Assignment CASE EXIT-WHEN Call CONTINUE-WHEN LOOP Conditional EXECUTE IMMEDIATE RETURN So, in my case, where should I put the pragma I do have a call, but it's wrapped in the SQL within a cursor definition. What is the right way (if this is feasible at all)
VIEWS ON THIS POST

261

Posted on:

Monday 31st December 2012
View Replies!

Collapse a matrix to a single line item

I think it would be best to use a simple example to explain my problem. The table below needs to become compressed into to distinct line items Customer_No Hair SP_Code Gender Age Colour Weight 1 blonde 123 M null null null 1 blonde 123 null 23 null null 1 blonde 123 null null Green null 1 blonde 123 null null null 98 1 blonde 123 null null null null 1 blonde 123 null null null null 1 blonde 123 null null null null 1 blonde 123 null null null null 2 Blue 444 F null null null 2 Blue 444 null 28 null null 2 Blue 444 null null Pink null 2 Blue 444 null null null 64 2 Blue 444 null null null null 2 Blue 444 null null null null 2 Blue 444 null null null null 2 Blue 444 null null null null Needs to become Customer_No Hair SP_Code Gender Age Colour Weight 1 blonde 123 M 23 Green 98 2 blue 444 F 28 Pink 64 I have over 163 000 'customers' and customer_no is not necessarily consecutive. There may not exist a customer 3 for example. ie Customer 1, Customer 2, Customer 4...etc. Is there any way to loop through the procedure and pick up the customer_no that exists in the Customer_no column Thank-you very much for your time. Banner: Oracle Database 11g Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production "CORE 11.2.0.2.0 Production" TNS for Linux: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production
VIEWS ON THIS POST

290

Posted on:

Monday 31st December 2012
View Replies!