Oracle/Questions Answered by Expert Elliot Mak

SubjectDate Asked
Extra error messages while execution of trigger2/3/2012
  Q: I want to reraise my question. Last time I asked you,I got the response as below: "Following is the ...
  A: Good day Prashant To my best knowledge, if you are using it from SQLPLUS (or development tools), ...
Audit Trail using Triggers1/20/2012
  Q: I'm using ORACLE 11gR2. I am working on audit trail for our project. There are some 300 tables in ...
  A: Good day Ravindra, Sorry for the late reply. I was on vacation after my initial reply. With ...
Audit Trail using Triggers1/19/2012
  Q: I'm using ORACLE 11gR2. I am working on audit trail for our project. There are some 300 tables in ...
  A: Good day Ravindra There are other ways to perform audit trail on Oracle RDBMS. I am not too sure ...
confusion in views12/2/2011
  Q: i have a little problem with accessing views, please help I am user 'system', i create a user snow, ...
  A: The reason why you got this error is because Oracle requires direct grant to the user. that means, ...
confusion in views12/1/2011
  Q: i have a little problem with accessing views, please help I am user 'system', i create a user snow, ...
  A: Good day Ritesh I recreated your scenario on my laptop. Here is what I have done. -- Script Start ...
Oracle 11g procedure11/2/2011
  Q: How do you populate a table or data in Oracle 11g step by step?
  A: Good day, Oracle uses SQL DML to populate a table with data. The four most common DMLs are select, ...
Importing a .dmp file to my oracle database10/28/2011
  Q: I have .dmp file that I want to import to my oracle 10g database through Toad for Oracle client.I am ...
  A: Good day Ankit, I never used Toad to manage my databases. Therefore, I cannot tell you how to do ...
function and procedure9/27/2011
  Q: create or replace function calc_avg_mark (roll_num in number) return number is avgm number; m1 ...
  A: Good day, As I said in the previous reply, you will need to have a cursor declare in the declare ...
function and procedure9/27/2011
  Q: create or replace function calc_avg_mark (roll_num in number) return number is avgm number; m1 ...
  A: Good day, In your anonymous pl/sql block, you have a typo in the function call. You have ...
set timestamp format in table9/26/2011
  Q: Can we set timestamp format like dd-mon-yyyy, hh:mi am/pm in table. Please explain me with example ...
  A: Hafsa, You don't need to set the timestamp format in table. There is only one format when store in ...
set date format and limit to enter data in table9/25/2011
  Q: hell sir, Currently, I am working on a project which i have to submit at my collage in time. I have ...
  A: Good day Let me rephrase your question. You have a table where there is a date column. Your ...
Oracle_DBA_Interview9/13/2011
  Q: . I will ask u more question as i will have some doubt... Please correct me if I am wrong. ...
  A: I see there are two questions here. (1) What is a package? (2) how does it compile? What is a ...
ORACLE_DBA_INTERVIEW9/13/2011
  Q: I have attended an interview they have asked the below questions. 1. How to give SELCT and INSERT ...
  A: There is no one easy way to tell you how to estimate the size of the SGA/PGA. There are some ...
ORACLE_DBA_INTERVIEW9/11/2011
  Q: I have attended an interview they have asked the below questions. 1. How to give SELCT and INSERT ...
  A: You are very welcome. The parameter you need to set is DISPLAY. For example Bourne, Bash, or Korn ...
ORACLE_DBA_INTERVIEW9/10/2011
  Q: I have attended an interview they have asked the below questions. 1. How to give SELCT and INSERT ...
  A: 1. How to give SELCT and INSERT grants to the 1000 users? That is a double edge question. The ...
Oracle magazine archives7/20/2011
  Q: I need to refer archives of oracle magazines prior to 2005 digital version. Can you mention any web ...
  A: Unfortunately, Oracle started the digital version as of 2005. There is no digital version available ...
Open Cursors exceeded3/21/2011
  Q: i'm working in oracle developer 2000 forms 4.5; Sometime message ORA-01000 maximum open cursors ...
  A: Good day, I believe the issue is the number of cursors opened in the Oracle database exceeded the ...
certification2/28/2011
  Q: i am currently preparing for OCA certification making my way to being an OCP, i just wanted to ask ...
  A: I always believe that the best way to prepare for the OCA and OCP is to study and review the ...
Need Help....2/26/2011
  Q: I am student of oracle 8 enterprise edition and making some small softwares. I have a major problem ...
  A: I believe Windows XP is not a certified platform for Oracle 8i EE. Windows 2000 (release around ...
Can Sys run procedures?2/13/2011
  Q: I am just wondering can the sys user run procedures without being granted permissions on the ...
  A: I think this caught a lot of people off guard with related to table grant permission. You are ...
DataBase Trigger2/3/2011
  Q: If a database trigger with is a statement trigger has no rows and you insert a new row assuming the ...
  A: First I would like to apologize for the rejection with regarding to your original post. The reason ...
oracle databse1/19/2011
  Q: I have install oracle database 10g on window server 2003 , But after each times computer reboot , ...
  A: Good day Tran You will have to make sure the Oracle services are started automatically at boot. To ...
Oracle PL/SQL - Dynamic SQL1/14/2011
  Q: I am Learning PL/SQL and looking at Dynamic SQL. I have a table named employees which has 107 rows. ...
  A: Good day Donald, Is there a specific reason why you need to use the DBMS_SQL package for dynamic ...
ORACLE Installation12/10/2010
  Q: I need step by step instructions in installing Oracle Database 10g on my MAC OSX 10.6.5. I already ...
  A: Good day Sarah, I have never installed Oracle Database 10g on OS X platform. However, here is the ...
wrap a procedure12/8/2010
  Q: How to wrap an existing procedure in PL/SQL. I tried with wrap utility wrap iname='inputfile' ...
  A: Good day Radhika The wrap utility from Oracle is provided in %ORACLE_HOME%/bin/wrap.exe When you ...
OCP Developer12/7/2010
  Q: I want to write OCP PL/SQL Developer certification exam. Please share the book name and website ...
  A: Good day Radhika As per Oracle certification requirement, you are required to take three exams in ...
about arangment of column in oracle11/15/2010
  Q: sir, i have create a table in oracle 10g and there is 8 column , when i exec. it its print in 2 row ...
  A: Good day Sanyog I assume that you are using SQLPlus. The reason why it is printing in 2 rows ...
Oracle 8i Clone10/29/2010
  Q: Hey'a! I have a quick question. I have made a copy of an Oracle 8i database (8.1.7) and would like ...
  A: Good day Max Bosch, I have found the following steps for changing the SID from asktom.oracle.com. ...
oracle forms 6i configuration10/18/2010
  Q: one error is occur that while connecting oracle 8i d2k that destination is not connected.. i posted ...
  A: Good day Preeti, Just to confirm that you are running on 9i database, right? What is the name of ...
oracle forms 6i configuration10/14/2010
  Q: i m using oracle 9i and now i install oracle form 6i..it installed properly.but at startup it give ...
  A: Good day Preeti Unless the D2K host and your machine are on a different network (for example subnet ...
Oracle DBLINK10/14/2010
  Q: Good day Mr. Elliot, i am trying to connect 2 dbs on different subnets, both servers have ...
  A: The problem is that your source and target are on a different subnets, and that's precisely why they ...
oracle forms 6i configuration10/12/2010
  Q: i m using oracle 9i and now i install oracle form 6i..it installed properly.but at startup it give ...
  A: First, you will need to make sure the window services are up and running for your Oracle Database. ...
Rman backup error10/7/2010
  Q: at first I'm sorry cause the question maybe silly a little bit when i try to do a backup for my ...
  A: There's never a silly question. If you are using a local backup, your rman backup writes to your ...
trigger problem...9/22/2010
  Q: Sir, I have created one trigger as limit, now my trigger is working fine, but generates ...
  A: Good day Anirban, To my best knowledge, it cannot be done. Don't forget that SQLPLUS is a command ...
Doubts regarding Oracle7/15/2010
  Q: Currently i am pursuing Oracle 9i. After completion i will receive a oracle 9i cerification. Later ...
  A: Good day, Since you did not mention which Oracle 9i certification, I assume that you are pursuing ...
query6/10/2010
  Q: Kindly have a look at the code - select CUSTOMER_ACCOUNT_ID, OPENING_BALANCE_AMT AS ...
  A: Can you explain to me the functional requirement for this SQL? I think I know what you are trying ...
SQL like clause6/7/2010
  Q: I use the code - SELECT * FROM new where week like '2003%' ; and get error - partial string ...
  A: Sid, If the column "WEEK" is a date field, then you can use WHERE week BETWEEN TO_DATE('20030101 ...
SQL6/4/2010
  Q: I use the code - SELECT CUSTOMER_ACCOUNT_ID,DATE_ACCEPTED FROM cis.CA_ACTIVE_INS_PROD_HISTORY ...
  A: Is TO_DATE_ACCEPTED a custom function that you created? If not, then the correct syntax should be ...
SQL Date Format6/4/2010
  Q: I'm Stuck in a SQL querry getting error that a character string failed conversion to a numeric ...
  A: Good day Siddarth The default date format of the Oracle database is determined by the ...
help with oracle trigger to open an excel file after an update on a table row6/3/2010
  Q: Can you please help me understand why I am getting the error message :'PLS-00201: identifier ...
  A: Since Oracle database does not deliver DDE.APP_BEGIN procedure, therefore, I believe that ...
help with oracle trigger to open an excel file after an update on a table row6/3/2010
  Q: Can you please help me understand why I am getting the error message :'PLS-00201: identifier ...
  A: Good day Kathi Is DDE.App_Begin a custom procedure? If that is the case, you may want to check ...
ORA 009423/31/2010
  Q: some times , I am getting ora 00942 error . i am getting this error for any table of my schema or ...
  A: Now it's a little more clear after you mentioned the restart. Are you using 10gR2? If you have ...
ORA 009423/31/2010
  Q: some times , I am getting ora 00942 error . i am getting this error for any table of my schema or ...
  A: Good day Radhika ORA-00942: table or view does not exist The above is the official error message ...
Peoplesoft & SQL3/29/2010
  Q: I can do some programming in SQL, but inherited this code from a new PeopleSoft installation: ...
  A: Good day Michael, B_ED is the alias for P_S_PRD.PS_S_NAMES. It is the same as the ...
connectivity between Oracle client & Server3/20/2010
  Q: can pls guide how to connect my client oracle application system to Oracle Database Server which is ...
  A: In order for you to connect to an instance on Oracle Database Server, you will need to configure the ...
procedure3/10/2010
  Q: i wont to create procedure , the content in the procedure is a query , it is possible ? because i ...
  A: Good day Miran It is definitely possible to create procedures with a query within. The question ...
question procedure2/12/2010
  Q: how can i view stored procedures through my query
  A: Good day Sankar, There is a table called dba_source (or all_source or user_source). You will have ...
c0nnection client to server2/8/2010
  Q: I am using Oracle10gexpress edition Which is free for download I have installed OracleXE and ...
  A: Good day San, What is the actual error message you got when you try to connect? Did you get a TNS ...
import dmp file1/7/2010
  Q: i want to know how can i import dmp file into oracle
  A: Good day Bikram, First you will need to know how was the dump created. Is the dump file created by ...
compare data in two columns11/11/2009
  Q: I need to monitor the changes in two columns,example: Table 1 Line Id,Ship Date,Ordered Qty EAK ...
  A: Jing, Assuming that the line_id is the primary key of the table. You can add an additional ...
compare data in two columns11/11/2009
  Q: I need to monitor the changes in two columns,example: Table 1 Line Id,Ship Date,Ordered Qty EAK ...
  A: Jing, The solution that I had before is assumed the line_id is unique. Without an unique column, ...
compare data in two columns11/10/2009
  Q: I need to monitor the changes in two columns,example: Table 1 Line Id,Ship Date,Ordered Qty EAK ...
  A: Good day Jing, Here is what you can do TABLE1 - data from yesterday TABLE2 - data from today The ...
oracle database11/5/2009
  Q: Respected Sir/Mam I am using oracle10gXe but I am not able to see the my database name. Is ...
  A: Good day San I assume that you are using Oacle10gXE on WIndows Platform. There is no table to ...
Data Guard Physical database11/4/2009
  Q: Right now, I'm configuring DG Physical Standby on two separates servers, and I have to create ...
  A: Good day This is what I understand from your question. (1) Configuration of DG Physical Standby ...
Oracle data Guard Physical Standby Database11/4/2009
  Q: Right now, I'm configuring DG Physical Standby on two separates servers, and I have to create ...
  A: Good day This is what I understand from your question. (1) Configuration of DG Physical Standby ...
Oracle 11g install laptop spec10/21/2009
  Q: Elliot, I wish to port my ORACLE 11g application and for demonstration purposes onto a laptop. what ...
  A: Good day Debra, AS you know, Oracle database is mostly run on memory. Therefore, I will recommend ...
sql query or cursor9/29/2009
  Q: Fullname TIT NAME MIDDLENAME surname Mr rahul kumar shukla Mr rahul ...
  A: Here are the rules that I assume according to your question (1) If fullname contains [TITLE] [NAME] ...
need help9/10/2009
  Q: i install oracle 10g on my computer and connect it with another computer with LAN. on this computer ...
  A: Let me see if I understand that correctly. You have entered http://<hostname>:5560/isqlplus, you ...
need help9/8/2009
  Q: i install oracle 10g on my computer and connect it with another computer with LAN. on this computer ...
  A: Good day Muzammil For you to access your 10g database via IE or any web browser, you will need to ...
result set from anonymous block8/28/2009
  Q: I have written a pl/sql anonymous block which is giving the dynamic select statement. But my ...
  A: "EXECUTE IMMEDIATE" primary used for DCL, maybe occasionally for DDL or DML (highly unlikely). It ...
comparing data in columns from two tables8/27/2009
  Q: I have two tables say (Table A and table B). If we assume that table A has be generated on every ...
  A: Good day Prath, Which version of Oracle are you using? I tried your code using Oracle 10gR2, and ...
comparing data in columns from two tables8/25/2009
  Q: I have two tables say (Table A and table B). If we assume that table A has be generated on every ...
  A: There are couple other ways to get the data out. Here is one of them. CREATE TABLE table_a ...
comparing data in columns from two tables8/24/2009
  Q: I have two tables say (Table A and table B). If we assume that table A has be generated on every ...
  A: Good day Prath Based on what I understand your post, I created two tables. CREATE TABLE table_a ...
pl/sql8/21/2009
  Q: I have two query in first one using NVL() and another query using MOD() function in WHERE clause, so ...
  A: Sharad, In this case, you cannot create your function based index using a variable that changes ...
pl/sql8/21/2009
  Q: I have two query in first one using NVL() and another query using MOD() function in WHERE clause, so ...
  A: Good day Sharad To create a function based index, there is no difference than creating a normal ...
AWR Report - Understanding8/20/2009
  Q: I have a AWR report generated in weekly basis, how do I understand which component has the problem ...
  A: It is very difficult for me to show you a case study of AWR report of my site. And to explain how ...
Surrogate key8/9/2009
  Q: What is a surrogate key? how it is different from primary key? what is a natural key and can it be ...
  A: Good day Ruchi, To answer your first question, it is easier for you to review the attached site. ...
Database8/8/2009
  Q: Sir, currently my software is in SQL SERVER 2005. But now i need to convert it into Oracle 10g. Is ...
  A: Good day Gulraj, It is very difficult for me to say if it is worthy for you to convert from SQL ...
Oracle 10g date issue8/6/2009
  Q: I made date as primary key with an integer field i.e composite primary key. But it is accepting ...
  A: Good day Asif I am not familiar with form6. Therefore, I can only with respect to Oracle 10g. ...
sql query7/27/2009
  Q: date empcode1 empcode2 workdone 11/7/09 emp001 emp002 14 12/7/09 emp002 ...
  A: Good day Sandeep, See the following example: I have created a table with four columns. They are ...
sql query7/27/2009
  Q: emp001 emp002 14 emp002 emp003 8 emp003 emp001 12 emp003 ...
  A: I think you have sent me a question this morning. I have accidentally clicked the wrong button, can ...
oracle training7/17/2009
  Q: sir, i have completed BE(computer). i am interested in oracle ocp training, i have very basic idea ...
  A: Good day Nirav, With regarding to Oracle OCP training, I will recommend either 10g or 11g. 9i is ...
Exp-Imp between different versions of Oracle7/16/2009
  Q: Good day! I've already asked this question in this forum but sent it to the wrong expert, so here it ...
  A: Good day Vien, In order for you to export your 11g database and import it back to 9i, you will have ...
Creating dynamic table6/16/2009
  Q: I want a help in creating dynamic table as per the csv file. The csv file which is to be uploaded ...
  A: Good day Rocky If you are using v9.2, you may not have the ability to create an external table. I ...
Query on plsql program6/16/2009
  Q: Greetings Elliot Here is my requirement .. I am given the "column_ID"s of a table. Using that i ...
  A: I have a little bit difficult time to understand the situation. Here is what I did based on what ...
Creating dynamic table6/15/2009
  Q: I want a help in creating dynamic table as per the csv file. The csv file which is to be uploaded ...
  A: Good day Rocky, I will need to make some assumption based on your question. 1. A new table will be ...
How to get all the childs,subchilds of parent record6/15/2009
  Q: My requirement is to get all child, subchild of parent record. Consider this explanation: I want ...
  A: Good day Roshan To get the parent, child, and subchild is pretty straight forward with SQL. In the ...
ABOUT DECODE AND SIGN in a single query.6/10/2009
  Q: Please explain me what does this query do exactly: SUM( DECODE(SIGN(ic.f_i_soh_rglr_qty ...
  A: Good day Raghav SIGN function returns three values. They are -1, 0, and 1. If number < 0, then ...
sql5/29/2009
  Q: EMPLOYEE) of database1 to the same table EMPLOYEE of database2. How to do it pls help me
  A: Good day Bhuvaneswari There are multiple ways you can copy the data from one database to the other. ...
One software support Oracle 9i and Oracle 11g5/21/2009
  Q: The software 'AAA' use the Oracle 9i as the database. Now we would like to use the Oracle 11g ...
  A: Good morning Ning, I will assume that you are upgrading your Oracle 9i database to Oracle 11g ...
Making Data as Column Names for View5/15/2009
  Q: Good day Sir! How do I create a view where the columns are based on data in the table?Is that ...
  A: Good day Vee, I apologize for the late reply. It is currently a long weekend here in Canada and I ...
PL/SQL function5/13/2009
  Q: I have a requierement as below. - I have a formula (Ex: (120/(120+20))*100). I need to pass this ...
  A: Good day Uma When you are passing PL/SQL a formula, as you said in your email, PL/SQL interprets it ...
Oracle Licensing - HACMP3/26/2009
  Q: I have a customer with Oracle on IBM AIX using HACMP in an active/passive configuration. In HACMP ...
  A: In your situation, you are running Oracle on IBM AIX with HACMP. Two servers are clustered with one ...
Oracle data import3/13/2009
  Q: I was able to get my tablespaces created using an indexfile created from the imp command. I am very ...
  A: Let's try this before I install XE for the troubleshooting. The error you got, ORA39213 can be ...
Oracle data import3/10/2009
  Q: I was able to get my tablespaces created using an indexfile created from the imp command. I am very ...
  A: When yo are using data pump, you don't need to create the user before doing the import. As you ...
oracle3/7/2009
  Q: i create on stored procedure to fetch some values from on table and update it into second table. and ...
  A: Good morning Vivek Let me try to rephrase your question. You have created a procedure that fetch ...
Oracle data import3/6/2009
  Q: I was able to get my tablespaces created using an indexfile created from the imp command. I am very ...
  A: I guess you got the following message when you did the import IMP-00015: following statement failed ...
*-* Spooling and Scripting *-*3/6/2009
  Q: I need to spool files and also write script files for a college project. I need to do this in an ...
  A: Apologize for the late reply. I was away for the weekend. If I can assume, I believe you are ...
Creating csv files from oracle .dmp files3/4/2009
  Q: I'm working on a project where I need to be able to create csv files from oracle dmp files without ...
  A: Here is an example that I did this morning: In a 10G database, create a table with some data. ...
Oracle upgrade3/2/2009
  Q: I am thinking about upgrading from Oracle 9.2.0.6 to Oracle 10.2. I have been told that that this ...
  A: Good morning Sergio, There are quite a bit of changes with the Oracle parameters between 9.2.0.6 to ...
Creating csv files from oracle .dmp files2/24/2009
  Q: I'm working on a project where I need to be able to create csv files from oracle dmp files without ...
  A: Some of the parameters are deprecated since version 8/9 that are no longer valid in version 11. It ...
Creating csv files from oracle .dmp files2/23/2009
  Q: I'm working on a project where I need to be able to create csv files from oracle dmp files without ...
  A: Good day Robert, Oracle export dmp file is Oracle proprietary binary file. This file can be ...
Link for sql commands..2/17/2009
  Q: Can u give me a link where i can find all commands used in sql* plus(oracle).All commands as in all ...
  A: Good day Suhel Enclosed you will find the reference guide for SQL*PLUS Oracle 10g Release 2 ...
*-* Setting up Database *-*2/6/2009
  Q: I'm a student doing business computing and I am in my final year. We just started using Oracle ...
  A: I will assume you will be installing either Oracle 10g or 11g on a Windows platform. In most ...
About calculating days.1/26/2009
  Q: I want to calculate difference between 2 dates in days. e.g. difference between 2-Jan-2009 & ...
  A: You can do a straight substraction between the two dates and it will return the differences between ...
Inserting 2 items in PL/SQL procedure1/24/2009
  Q: I am writing a database for an ordering system which is as follows: Drop table Orders cascade ...
  A: First of all, it seems to me that the table that you are trying to insert is not normalized. The ...
Oracle Installation steps and procedure1/14/2009
  Q: Kindly let me know the complete installation steps to install the Oracle 9i from beginning to end so ...
  A: Good day Dileep Jian, The Oracle database installation steps are very well documented in the ...
Inactive sessions12/17/2008
  Q: Session are shown as inactive 1. When no SQL are fired from that session and also 2. after logoff ...
  A: Good day Pradeep I am not too familiar with the application that you used to connect to the ...
oracle12/7/2008
  Q: i want to know that, in a table there are two fields (name & age) with lots of data. i want to know ...
  A: You can use RANK function to retrieve it. See the below example ...
create a new table from another conditionally, using SQL and PL/SQL12/4/2008
  Q: I have a table A with a primary key and a secondary key and lot many othere details. I want to ...
  A: Which version of Oracle are you using? If you are using 10gR2/11g, you can use "insert when" ...
Count(*) inside a union all11/26/2008
  Q: First of all thank you for your attention and your time. My question is the following: I am making a ...
  A: It looks like you have a cartesian product. I have converted your ANSI SQL so that it is easier to ...
Access seed database11/26/2008
  Q: I have installed Oracle 9i database. I wanna access seed database. I have tried using the following ...
  A: Good day Shilpa Before I begin, I would like to state the following assumption. 1. You are on ...
Why do LOB segments shrink when MOVEd ?11/13/2008
  Q: I am wondering why when I execute SQL like : ALTER TABLE MYTABLE MOVE LOB(LOBCOLUMN) STORE AS ...
  A: I am not too clear of your question. So I am going to answer your question differently and hope it ...
Why do LOB segments shrink when MOVEd ?11/12/2008
  Q: I am wondering why when I execute SQL like : ALTER TABLE MYTABLE MOVE LOB(LOBCOLUMN) STORE AS ...
  A: Good day Serge, That's a very interesting question. First of all, there are two types of LOB ...
Oracle index internals11/7/2008
  Q: When index is created on a column of a table, what exactly is stored in the index. If every value of ...
  A: Let's talk about b-tree index. In a b-tree index, it contains two types of blocks. They are branch ...
Oracle Select *11/6/2008
  Q: Is there any advantage on performance of putting table column headings in select statement instead ...
  A: There are many ways to look into your question. Let's start from the database, then the network, ...
Case statements and subqueries10/30/2008
  Q: I have a column with either percentages, grams or an error message stored in it. I.e. EnteredValue ...
  A: This is how I understand your question. On the table, you have two columns. using the same data ...
Oracle database10/29/2008
  Q: Sir, I created a database manually in Oracle 9i with CREATE DATABASE command. it has been ...
  A: Good day Jasvinder Before I provide you the respective command, I just want to make sure that all ...
Case statements and subqueries10/21/2008
  Q: I have a column with either percentages, grams or an error message stored in it. I.e. EnteredValue ...
  A: Personally, I try to avoid using subquery in a column. The reason is that for each row returns from ...
oracle license10/6/2008
  Q: do license really very important , I want buy CD and install it in one computer which will be the ...
  A: License is important. Oracle provides two options for licensing. By processors or by users. ...
Use LIKE in subquery10/1/2008
  Q: I would like to be able to use the results of a subquery in the where clause of the outer query ...
  A: You cannot embed subquery within INSTR. INSTR expects the string or the column name to be passed ...
Use LIKE in subquery9/30/2008
  Q: I would like to be able to use the results of a subquery in the where clause of the outer query ...
  A: You cannot use LIKE in such a way. However, you can use instr to give you the same result. Here is ...
protocol adapter error in sql*plus9/26/2008
  Q: I installed in two different folders I logged itto database using sql*plus using oracle9i database.i ...
  A: The tnsnames.ora for your 9i developer suite is misconfigured. HOST names is the name of your ...
protocol adapter error in sql*plus9/26/2008
  Q: I installed in two different folders I logged itto database using sql*plus using oracle9i database.i ...
  A: It looks like you are trying to use IPC to connect to the database from the developer suite. You ...
retrieve table constraints9/23/2008
  Q: I am working in Oracle8i. How can I retrieve the constraints associated with tables in Oracle.From ...
  A: I don't think there is a change between 8i and 10g, and since I don't have 8i installed, I use 10g ...
STRTDB80.EXE problem9/17/2008
  Q: Sir, I have installed oracle8i in my system on windows XP home edition.I am not an expert. I am ...
  A: It seems to me that there is some issue with the installation. Since you are unable to remove ...
Data guard9/16/2008
  Q: Can we dataguard a 9.2.0.8 database to a 10.2 database? In other words, I have a 9.2.0.8 primary ...
  A: Good day Kaz, I have not virtualized an oracle physical database, and I have not heard any of my ...
error while loggin9/14/2008
  Q: i have installer oracle9i on my machine(windows Xp )after typin user name and password it shows ...
  A: Good day Ram, It seems that there are some services and/or configuration have not been done. Here ...
oracle differences7/14/2008
  Q: I have done oracle 8i a couple of years back.now i want to get a job on oracle. so I have to learn ...
  A: Good day John, There is quite a bit difference between 8i, 9i, and 10g. I will provide you their ...
Oracle 10g login7/12/2008
  Q: I have the problem while logging to the SQL*PLUS. after i entered the username and password as ...
  A: Good day Anil, Here is what you need to do: 1. Logon to the database using SYSTEM or SYS account 2. ...
import data from .dmp file7/5/2008
  Q: I have a vdata.dmp file created in oracle 9.0.1.1.1. I do not know any user,password and any ...
  A: Good day Munna Here is what I will do in this situation 1. Write the DDL from DMP file 2. Examine ...
oracle download7/4/2008
  Q: could you please guide me on how to download oracle 10g and oracle 11g from their download ...
  A: Good day ddy, There are only two ways to obtain Oracle binaries, they are 1. Through ...
Recovery with RMAN7/4/2008
  Q: I'm doing a recovery of a database with RMAN but I've a problem: When I recovery until time1, I've ...
  A: I have never done this before, but from what I understand about RMAN configuration, you can ...
Oracle IMP utility7/2/2008
  Q: I have created two databases in oracle on the same PC. Now If I run IMP command how can I select a ...
  A: Good day Pankaj In order for you to perform the import, you will have to set the ORACLE_SID ...
important7/2/2008
  Q: Error coming as Ora600 while import the dmp file. help me in resolving the issue.
  A: Sorry for the previous message. It was responding to another issue. By have put this in yours by ...
ORA-12545 connect failed target host or object does not exist6/27/2008
  Q: we are doing a project in VB with oracle8i as back end. when i try to run the command "tnsping ...
  A: Sorry for the late reply. I just got access to internet just now. To answer your questions. It ...
output statement6/25/2008
  Q: can i print my output in pl/sql in a single line please write statement for this ANSWER: Good day, ...
  A: In order for you to write a program like this, the program will need to: - know how many rows it ...
output statement6/21/2008
  Q: can i print my output in pl/sql in a single line please write statement for this
  A: Good day, You can print your PL/SQL output in a single line using concatenation. Here is an ...
Oracle 9i to 10g upgrade6/12/2008
  Q: .We are planning to upgrade Oracle from 9i to 10g.We are currently using Weblogic Application ...
  A: Good day Naresh In order to upgrade Oracle from 9i to 10g Release 2, there are a lot of planning ...
creating a CSV file from oracle 10g database6/9/2008
  Q: I am using oracle 10g with unix server,what i need is to export the database into a CSV file and ...
  A: It looks like the Oracle cannot access the directory specified due to the following: 1. Oracle does ...
some dba questions6/3/2008
  Q: How does one use the import/export utilities? Can one resize tablespaces and data files? Can one ...
  A: Good day Shanthi I assume that you are using Oracle 10g R2 or above. Question #1: How does one ...
creating a CSV file from oracle 10g database6/3/2008
  Q: I am using oracle 10g with unix server,what i need is to export the database into a CSV file and ...
  A: Good day, Tom Kyte has provided a very good example in how to perform the extract using PL/SQL that ...
creating a CSV file from oracle 10g database5/30/2008
  Q: I am using oracle 10g with unix server,what i need is to export the database into a CSV file and ...
  A: Good day Amit, I have been using this code created by Mark Powell to generate CSV file from Oracle ...
installing oracle 9i for personal use5/29/2008
  Q: i would like to configure oracle 9i for my personal use .should i need to install oracle server in ...
  A: Good day Palani Kumar You can find the following document be particular helpful to you. ...
transfer of .csv file5/26/2008
  Q: my question is: "how can we tranfer .csv file to oracle server" thank you seema
  A: Good day Seema, Thank you for your question. To answer your question, transferring CSV file to ...
oracle error 271015/26/2008
  Q: As per you previous suggestions iam not able to find the services where the problem for which iam ...
  A: Good day Deepak, Based on what I see from the output you provided, it does not seem like another ...
oracle error 271015/23/2008
  Q: As per your previous suggestion given, can explain the three questions which you had asked me to ...
  A: Good day Deepak 1. Is there another database instance runninn on the server using simutaneously? ...
oracle error 271015/22/2008
  Q: as per your previous solution for the location of the alert log i found a file named alert_orcl in ...
  A: I reviewed your log, and I have noticed that the database instance failed at around 1:10 to 1:20 ...
oracle error 271015/21/2008
  Q: where can i find the alert.log file because as per your previous solution you had told to see what ...
  A: Good day Deepak To find where the alert.log is, there are multiple ways 1. Go to your ...
oracle error 1034: 2701 error5/20/2008
  Q: I have installed fresh windows 2000 server & oracle 9i on a server but every day when we try to run ...
  A: Good morning Deepak ORA-27101: shared memory realm does not exist message is generally caused by ...
Move LOBSEGMENTS5/15/2008
  Q: , I try to rebuild a LOB INDEX (Index type LOB) and I can't do it: ERROR at line 1: ORA-02327: ...
  A: Given that I don't have enough information to troubleshoot it for you. However, here is what I have ...
BCNF Normalization3/20/2008
  Q: I have a question regarding BCNF normalization. What I understood so far is that with BCNF ...
  A: BCNF is a more stringent form of 3NF, in most cases, 3NF meets the requirements of BCNF. The only ...
hardware requirements for installing oracle10g3/6/2008
  Q: what are the hardware requirements for installing oracle 10g XE. will my system get slow after ...
  A: Good day Rishika My assumption is that you are installing on Windows Platform. The hardware ...
which version of oracle???3/6/2008
  Q: i m a student developing a live project which has to work on minimum 5 computers.i am using java as ...
  A: It is really depending on what you need it for and what of hardware you want to put this on. The ...
data guard3/6/2008
  Q: what do u mean by data guard,how does it help us, ANSWER: Good day Ibbu, Data Guard (DG) is an ...
  A: There are quite a bit of steps need to do to setup DG. I have enclosed the instruction from Oracle ...
Oracle3/4/2008
  Q: Can you give the major differences between oracle 10g and oracle 11g?
  A: Here are some highlights of 11g improvement 1. Automatic Memory Tuning 2. SQL Performance Analyzer ...
data guard3/3/2008
  Q: what do u mean by data guard,how does it help us,
  A: Good day Ibbu, Data Guard (DG) is an effective and comprehensive data availiability, data ...
QueryWhere2/25/2008
  Q: I have successfully used &A > ’14-FEB-08’, but if I wanted to say NOW DATE Plus 70 Days. Can this be ...
  A: It is very easy to do that in Oracle. Here is an example for your reference. If the datatype of ...
QueryWhere2/24/2008
  Q: Appreciate your help as a user-end rather then programmer I would like help with retrieving data on ...
  A: It is very easy to do that in Oracle. Here is an example for your reference. If the datatype of ...
QueryWhere2/23/2008
  Q: Appreciate your help as a user-end rather then programmer I would like help with retrieving data on ...
  A: Good day Kiran, Oracle 10g Release 2 by default, the date is in DD-MON-YY format. Therefore, if ...
re2/22/2008
  Q: ORA-06553: PLS-213: Package STANDARS not accesible problem shown at the time of user/table creation
  A: Good day, It seems like you have some problem with your shared_pool_size. Can you do the ...
Oracle 10g -> 9i backout2/20/2008
  Q: Can you let me know how to back out if something goes wrong with Oracle 9i-10g upgrade using DBUA
  A: Good day Anoop To perform an upgrade, here is what I recommended. 1. Install your new Oracle ...
help me2/20/2008
  Q: i have installed oracle 8i i cant log in into oracle i tried scott n tiger while installing oracle ...
  A: Since there isn't additional information (like error messages) you have provided, it is very ...
syntax2/18/2008
  Q: i want to import a data into a table which i have manually created through a dat file. there are ...
  A: Good day Rahman The fastest way to import the data is to use SQL*Loader that comes with Oracle ...
Oracle global variable2/18/2008
  Q: I having an oracle 3 tier design, i have a hp-ux based oracle database, and windows 2003 base oracle ...
  A: Good day, I am not expert in Oracle Application server and application. But from what I can see ...
Pl/sql2/15/2008
  Q: I am having view,procedure,synonym,index. The all schema objects depends on Table, if i drop the ...
  A: Good day, Index is coupled with the tables. Therefore, if you drop a table, the index will be ...
Oracle 9i, 10G or 11i?2/15/2008
  Q: I am 3 Years experienced in IT. I am into ERP(JD Edwards). I want to learn Oracle Database. So among ...
  A: It is great to hear that you have installed Oracle 10g on your PC, and I hope that you didn't ...
Oracle 9i, 10G or 11i?2/14/2008
  Q: I am 3 Years experienced in IT. I am into ERP(JD Edwards). I want to learn Oracle Database. So among ...
  A: Good day Swathi, Welcome to the world of Oracle database. I work in PeopleSoft myself. Oracle ...
Question abt simple sql query2/11/2008
  Q: I am new to sql programming....What is the use of || symbol in sql programming? plz can u explain ...
  A: Good day Vijju The '||' is for concatenation. What it does is to string multiple variables into a ...
oracle2/7/2008
  Q: Why after 9i to 10g why not 10i?
  A: Good day Manish That's a very good question. "i" in 8i and 9i stands for "Internet". "g" in 10g ...
difference between oracle 9i and oracle 10g for simple usage2/1/2008
  Q: I have windows vista on my laptop so I can only use oracle 10g with vista. I have used oracle 9i in ...
  A: Follow up: Good day, I have noticed that i have not answered the last question about Oracle 9i with ...
difference between oracle 9i and oracle 10g1/26/2008
  Q: I can get the answer from reading the oracle documents. but please i want the real differences ...
  A: Good day, As Oracle has enchanced many functionality between each releases, Oracle document is ...
oracle 11i1/24/2008
  Q: I want to install oracle 11i in my stand alone system..for that tcp/ip config and domain name config ...
  A: Good day I have not installed Oracle 11i on a stand alone system, but I have spoken with some of my ...
imp oracle8i server procedure12/21/2007
  Q: How can i import the oracle8i server dump? I want to upload the dump from windows directory (ie. ...
  A: Good day In order for you to perform the importdo it using command line, in this case, MS-DOS ...
After the oracle upgrade from 9i to 10g Db performance is very very slow.12/18/2007
  Q: Recently we had an upgrdae from 9i to 10g. After that the mview normally used to get built in 10 ...
  A: Good day, Without knowing your database and its structure and setup, it is very difficult for me to ...
Oracle 10g12/13/2007
  Q: AM working in a small corporate,my task is to dump all my employee databases on to my system,so that ...
  A: Good day, I will try to answer your question as best as I can, giving that the question is a little ...
Number data type query12/12/2007
  Q: My question goes as follows. I have a column with data type Number(22,4). When i try to store some ...
  A: Good day, In Oracle perspective, 20.00 or 20, both of them represent the same number, i.e. 20. It ...
password renew12/11/2007
  Q: how can change system password?
  A: Good day Suresh, To change SYS and/or SYSTEM have no difference than any other users. The syntax ...
Oracle9i installation12/10/2007
  Q: I have got oracle9i client version installed in my home pc. but i am not able to access the HR ...
  A: Good day Ramesh In order for you to access an Oracle database, you will need to setup your ...
Table in SQL12/6/2007
  Q: i am new to sql, i have currently created database in oracle, i have put the fk and pk on all table ...
  A: Good day Ash, There are three ways to perform that (to my best knowledge). You will need to add a ...
Creating user identified externally12/6/2007
  Q: CREATE USER XYZ IDENTIFIED EXTERNALLY In this command after creating user how it can be used? Please ...
  A: Good day, There are several things you will need to setup the database and the operating systems so ...
Basic difference between Oracle 9i and 10g11/28/2007
  Q: What are the main difference between Oracle 9i and 10g application.
  A: Good day Ranvijay There are a lot of differences and enhancement between Oracle 9i and 10g, for ...
Slow execution11/22/2007
  Q: I was using ORACLE 8.1.6. on OS Windows 2000. Somehow, my PC got corrupted & stopped working. I ...
  A: Good day, There are couple things I will suggest you to take a look at, and they are as follows: ...

All Questions in This Category

Oracle

All Answers


Answers by Expert:


Ask Experts

Volunteer


Elliot Mak

Top Expert on this page

Expertise

I can answer questions with regarding to Oracle DB (8i, 9i, 10g, 11g) installation, configuration, administration, Data Guard, SQL, and PL/SQL (Procedures, triggers, functions).

Experience

I am an Oracle DBA, Senior PA, Project Manager, and Data Architect with 10 years of experience.

Education/Credentials
Bachelor of Science in Management System

©2012 About.com, a part of The New York Times Company. All rights reserved.