You are here:
Oracle/Questions Answered by Expert Edson
| Subject | Date Asked |
| table | 2/17/2011 |
| Q: I wanted to know that is it possible to see tables of some other user when logged in as some other ... A: Actually, lock or unlock doesnīt make difference. What has to be done is that hr has to grant select ... | |
| ORA-01722: invalid number | 2/9/2011 |
| Q: When I try to execute the below query I am getting ORA-01722 error. If I remove group by clause its ... A: .. ORA-01722 01722, 00000, "invalid number" Probably part of the character is remaining after the ... | |
| Sql Query | 1/5/2011 |
| Q: I have a table structure like given below. ID Parent Left Right 1 0 2 3 2 1 4 5 3 1 6 7 4 2 8 9 ... A: I really did not understand what you really mean. But it appears like if you want to move in the ... | |
| Oracle 10g: SQL Constraints! | 10/31/2010 |
| Q: If one purpose of a constraint is to ensure data integrity, then why would a user ever disable a ... A: I donīt know what you mean by "user". A final user should never "touch" the database. But, ... | |
| query performance | 7/6/2010 |
| Q: When i add last line of the code it is taking longer time. is there any way we can tune this query ? ... A: Is it a function? act_prcs.get_appvl_date(af.act_i) If so, then you need to see how many time itīs ... | |
| connection client to server | 2/15/2010 |
| Q: I am using Oracle10gexpress edition Which is free for download I have installed OracleXE and ... A: Server is specified in the item HOST, so you must have a connection string with the "HOST" pointing ... | |
| connection client to server | 2/8/2010 |
| Q: I am using Oracle10gexpress edition Which is free for download I have installed OracleXE and ... A: Client to the server? Tell me, is your Tnsnames configured? The path of it is: ... | |
| Decimal position check | 12/4/2009 |
| Q: Example: 1.000345 I want to see 1.001 I do not need to see Any help would be appreciated! Thanks, ... A: I didnīt got the real reason of the question, but I guess that you can solve it using substr, instr ... | |
| Csv data into oracle | 10/22/2009 |
| Q: I want to load data from csv file into oracle database can u tell me step by step process for this A: SQL Loader SQL*Loader (sqlldr ) is the utility to use for high performance data loads. The data can ... | |
| sql query | 9/26/2009 |
| Q: I want to know that how to use flash back query if anybook please tell me I want to rollback The ... A: ---Examples of FlashBack create table test_flashback (ab number(2)); insert into test_flashback ... | |
| Execuitng queries stored in a table in a procedure | 8/31/2009 |
| Q: I have to execute 20+ static insert queries in procedure (which will be executed by a java ... A: A good and common approach is to create a table with a blob column. When you perform the loop, call ... | |
| Call a stored procedure from a function | 8/28/2009 |
| Q: I have written a function which calls a stored procedure. A single parameter is passed to ... A: I could not understand your question well. What you want is to know the name of the procedure? If ... | |
| Oracle | 8/16/2009 |
| Q: Sir I want to retrieve all records between two dates . that is all records between this two dates ... A: Some examples: select * from dual a where sysdate between to_date('16/07/2009','dd/mm/yyyy') and ... | |
| sql query | 7/31/2009 |
| Q: GM sir SELECT count(*), deptno FROM employees GROUP BY deptno HAVING count(*) = (SELECT ... A: The question is not so clear for me, but see if this query helps you: select a.deptno, co from ... | |
| sql query | 7/27/2009 |
| Q: emp001 emp002 14 emp002 emp003 8 emp003 emp001 12 emp003 ... A: I did the following: create table abc (a varchar2(100), b varchar2(100), c number); insert ... | |
| oracle | 7/22/2009 |
| Q: Respected sir, I am student of MCA and I have one query in oracle. I have to insert record in two ... A: I didnīt really understand the problem, but see if this command helps you: Insert all Into ... | |
| oracle - vb | 6/29/2009 |
| Q: How to insert a jpeg file into an oracle table? How to print the same column into data report in ... A: You must create a column with the type LOB (BLOB or CLOB). After you must create a procedure to ... | |
| DECODE Function in pl/sql | 6/21/2009 |
| Q: There is a table with three columns name,location& sex.in name column there are different names and ... A: select count(*), sex, substr(location,..,..) from table group by sex, substr(location,..,..) If ... | |
| Default Constraint | 6/20/2009 |
| Q: Will you please help me to implement Default constraint for multiple columns using alter statement. ... A: When you create a database table, you have the option to specify a DEFAULT value. Using default ... | |
| data transfer from CSV file to database. | 6/6/2009 |
| Q: I want to transfer the data stored in CSV(comma seperated value) file to Oracle Database table. Can ... A: So you must have to use the final choice, the utl_file. Utl_file is a oracle�ackage that ... | |
| data transfer from CSV file to database. | 6/5/2009 |
| Q: I want to transfer the data stored in CSV(comma seperated value) file to Oracle Database table. Can ... A: There are some good ways to do it. The simplest way is to use a interface, just like PL/SQL ... | |
| need help to create table different db in oracle | 2/13/2009 |
| Q: How can i create a table in remote database 'db2' for which i have the prveliges, from the database ... A: An easy way to do it would be: 1 - Create a procedure in db1 2 - Create a procedure in db2 ... | |
| why we need functions? | 1/6/2009 |
| Q: Procedures and functions both have same syntax except return. Both are functionally same. Then why ... A: You use functions when you need a result to be showed as an output. For example. Suppose you want ... | |
| query | 12/30/2008 |
| Q: it is a etable Name Distance a 51 b 102 c 122 d 32 this table i have ... A: No, it is not. Try this sql: select dsitance, count(noofpeople) noofpeople from ( select 'From 0 ... | |
| query | 12/9/2008 |
| Q: Please help me in this query: I have three tables tot_work, work_started and work_done. Each table ... A: select tab.*, case when work_done is not null and work_started is not null and tot_work ... | |
| oracle | 12/2/2008 |
| Q: I have Hierarchical data in my table. I need the output as <parent1> <child1> ... A: I really didnīt understand what you want. However, when we want to make a hierarchical query, we ... | |
| Oracle | 10/31/2008 |
| Q: I wanted to update some 400 records. 'RKTBG-EA-120001' To 'RKTBG-EA-120400' has to be changed as ... A: If all prefixes are RKTBGEA-EA- then update tab set col = 'R-KT-BG-EA-' || substr(col,12) if ... | |
| Total vacation days per month | 10/29/2008 |
| Q: Edson, I want to calculate the number of vacation days per month for each employee based on the ... A: It is not "end Dec", what is listed bellow is a case case when () then end alias The jan, feb ... | |
| Total vacation days per month | 10/26/2008 |
| Q: Edson, I want to calculate the number of vacation days per month for each employee based on the ... A: I made a query that I think can help you. But this query has to be a lot tested, so I suggest you ... | |
| Case statements and subqueries | 10/21/2008 |
| Q: I have a column with either percentages, grams or an error message stored in it. I.e. EnteredValue ... A: I donīt know if I understood your question, but I did a little sql that I tnink can help you: ... | |
| Clear Screen through PL SQL | 10/11/2008 |
| Q: ! Please help me to find out the solution for PL SQL questions : 1. How to Print the boolean ... A: One possible solution is the folowing: declare A boolean; B Varchar2(10); begin a := ... | |
| snap shot dropping(internal error) | 9/4/2008 |
| Q: i canot drop snapshot log SQL> drop snapshot log on sad.sadik it is working 1 Day but not completed ... A: This can be many things, but the last time I saw this I had to put oracle down and then star it ... | |
| Rearrange Columns in Table | 7/17/2008 |
| Q: I've been using MS Access for about a year and I am switching to Oracle lately. Is there a way to ... A: There is not a thing such this. What we usually do is to create another table with the same ... | |
| describe all tables in database | 7/1/2008 |
| Q: i am thivya. i want to know whether is there any privilege to describe all the tables in my datbase ... A: There is not a thing such that. What you can do is to make a script: SET HEAD OFF SPOOL ... | |
| Profile | 6/30/2008 |
| Q: We can see database entry by using oratab command and profile command..what is difference between ... A: "oratab" is a file created by Oracle in the /etc or /var/opt/oracle directory when installing ... | |
| Oracle8i- TNS could not resolve service name | 6/26/2008 |
| Q: i am thivya working as a software developer. we got a project with VB as front end and oracle8i ... A: It can be many things, but there are some more possible: 1) The file TNSNAMES contains the ... | |
| Can we have triggers on table present in different database. | 5/5/2008 |
| Q: Suppose we have two table present in database1 and database2 and we want to have a trigger to take ... A: Use the CREATE DATABASE LINK statement to create a database link. A database link is a schema object ... | |
| cursors | 5/3/2008 |
| Q: What is the difference between using explicit cursor(without for update) to update data of emp table ... A: Many users perceive Explicit Cursors as a difficult means of retrieving data from the database. In ... | |
| question abot matrix report | 4/15/2008 |
| Q: I am making a matrix report containing 2 rows and 1 column,I got it but my ist row feild is ... A: I would have to see your query for a deeper validation. But in a first see, I guess your are ... | |
| pl/sql | 4/8/2008 |
| Q: Hai, How to use unwrap in pl/sql? Bcoz i wrapped one file now i want to unwrap how is it ... A: Yes you can, When you refresh a materialized view, you must choose a method to do it. The ... | |
| Tuning | 3/28/2008 |
| Q: 1) I wrote two pages sql query but it will take time,now what are the steps i want to follow? ... A: You have some good bokks: http://www.amazon.com/SQL-Tuning-Dan-Tow/dp/0596005733 Almost All sql ... | |
| Log on Session time out | 3/26/2008 |
| Q: On the following Answer where do I look for idle period in application server. thanks, Hi ... A: Oracle provides the ability to limit resource consumption via the user PROFILE, a collection of ... | |
| pl/sql | 3/25/2008 |
| Q: 1) what is tkprof? and use of that? 2) I modified one table,some procedures,functions and packages ... A: 1) Understanding TKPROF You can run the TKPROF program to format the contents of the trace file ... | |
| importing | 3/25/2008 |
| Q: how can i load the data from excel sheet to oracle 9i tables A: There are two ways. A easy and a difficult. The difficult one is to create a utl directory, put the ... | |
| pl/sql | 3/19/2008 |
| Q: Hai, How to find out three maximum salary in Each department? How to find out ... A: ---1š Question select department, sal from ( select department, max(fpgg_vl_m01) sal, RANK() ... | |
| Substitution in PLsql | 2/25/2008 |
| Q: I want to know : Through Subsitution variable using '&'I am taking value within a 'for loop'. ... A: Doesīt it works? declare a number; begin for i in 1 .. 3 loop if i = 1 then a:= ... | |
| pl/sql | 2/22/2008 |
| Q: What is the use of raise_application ,exception_init? and when will use those give with example? A: Associating a PL/SQL Exception with a Number: Pragma EXCEPTION_INIT To handle error conditions ... | |
| Substitution in PLsql | 2/20/2008 |
| Q: I want to know : Through Subsitution variable using '&'I am taking value within a 'for ... A: .. When you use '&' in oracle your are telling it to get a variable. It does it once and ... | |
| Groups in oracle | 12/26/2007 |
| Q: .. Is it possible to manage groups in oracle. i.e. my requirement is i want to create a group and ... A: Managing Oracle Roles Administration of large numbers of objects can be difficult. Roles allow you ... | |
| Use of Substitution | 12/12/2007 |
| Q: ! Please consider the code and output : Code: declare a number; begin for i in 1 .. 3 ... A: .. Unfortunately '&' is made to use once in a code, So the first value you pass is the one used for ... | |
| Create Table | 12/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: Forgive me! There were errors in the code. Here goes the right version: create or replace trigger ... | |
| query | 12/4/2007 |
| Q: what is the default package in Oracle? ANSWER: Hi there. Could you be a little more specific? ... A: This will show you all packages: select * from ALL_OBJECTS A WHERE A.OBJECT_TYPE = 'PACKAGE' So ... | |
| Disadvantage of Index | 9/17/2007 |
| Q: Can you tell me what are the disadvantages, if we user Index A: Your question is to wide, but I can answer this way: Bitmap Index vs. B-tree Index: Which and When? ... | |
| Logical Directories/External Tables. | 9/12/2007 |
| Q: Could you please explain logical directories, including what they are and how you make them? What is ... A: Oracle introduced the concepts of DIRECTORY objects in Oracle 8.0. DIRECTORY object is a logical ... | |
| oracle Question | 8/25/2007 |
| Q: sir,I will be conduct the exam for oracle 9i OCA certification,so what is format of question paper ... A: Run this script below: Change the names of the tables for that you want: create table 'NEW_TABLE' ... | |
| oracle Question | 8/23/2007 |
| Q: sir,I will be conduct the exam for oracle 9i OCA certification,so what is format of question paper ... A: What is a unique constraint? A unique constraint is a single field or combination of fields that ... | |
| oracle Question | 8/23/2007 |
| Q: sir,I will be conduct the exam for oracle 9i OCA certification,so what is format of question paper ... A: 1)suppose i am creating 2 user in one user i am creating one table takeing a primery and not null ... | |
| oracle Question | 8/22/2007 |
| Q: sir,I will be conduct the exam for oracle 9i OCA certification,so what is format of question paper ... A: Specifying table-level or column-level constraints You can declare integrity constraints at the ... | |
| DB Connectivity Problem | 8/16/2007 |
| Q: Sir, Whenever I login to oracle no login is accessible & following message displayed: "ORA-01033: ... A: ORA-01033: ORACLE initialization or shutdown in progress Cause: An attempt was made to log on while ... | |
| ORA-00979: not a GROUP BY expression | 8/16/2007 |
| Q: I need advice on the above subject..below is my sql statement.. SELECT DISTINCT max(hdr_ver) as ... A: I really didnīt understand what you want...but here goes an advice: Which columns must be in the ... | |
| PL/SQL | 8/12/2007 |
| Q: I'm trying to do this : The More Movies company needs to be sure that the number on hand of each ... A: Here goes some material http://infolab.stanford.edu/~ullman/fcdb/oracle/or-triggers.html ... | |
| Oracle query | 8/10/2007 |
| Q: create table master_temp (Col1 number(2), Col2 varchar2(10)) insert into master_temp values ... A: select a.col2, mastval, c.keyval from master_temp a, child_temp b, transact c where ... | |
| Help | 8/1/2007 |
| Q: i am steve working on a project using visual basic 2005 and oracle 10g as back end application for ... A: Try to use this type of connection. Dim OraSession As OraSessionClass Dim OraDatabase As ... | |
| Help | 8/1/2007 |
| Q: i am steve working on a project using visual basic 2005 and oracle 10g as back end application for ... A: As your code is big I can be making a mistake. But arenīt you connecting to the database : ... ... | |
| Host bind array to small | 7/9/2007 |
| Q: The following happens on an Oracle 10.2.0.3 on a Sun Solaris 10 server. I have used JPub to ... A: ... I havenīt understand your function, I guess you didnīt send me all of it. Please send me all ... | |
| Temporary Table | 7/4/2007 |
| Q: I want to know what is the difference between creating a temporary tale and a static table. I want ... A: ORACLE 8I GLOBAL TEMPORARY TABLES ORACLE TEMPORARY TABLES In Oracle 8i, the CREATE GLOBAL ... | |
| Oracle locking on Unix V/s Windows | 7/3/2007 |
| Q: I have PeopleSoft applications as my front end and Oracle as backend. Presently I have both these ... A: I agree with you, but here goes an article for better explain: In support of Oracle Linux Roby ... | |
| adding a extra column onto a query | 7/2/2007 |
| Q: I am trying to complete the following query within oracle - Produce a report listing those students ... A: The problem is that you will have a a column with the same value along all the result: select ... | |
| calling a function inside a select statement | 6/12/2007 |
| Q: why "calling a procedure with a commit inside a select statement" is possible and why "calling a ... A: U can't commit within a function. the function will compile but u get error at run time like this ... | |
| Oracle Certification | 6/3/2007 |
| Q: I am planning to pursue DBA certification. Currently I am working as a Data Analyst. My question ... A: Oracle 10G has started working with grid applications, itīs the big difference, and of course those ... | |
| oracle v/s foxpro | 6/2/2007 |
| Q: i know foxpro, and would request you to guide me and many other foxpro users regarding :- 1) does ... A: I would say yes to almost all questions, but as Iīm not a FoxPro expert I canīt answer the questions ... | |
| oracle | 5/28/2007 |
| Q: i wanted to know about oracle. A: Oracle is a relational database worldwide used. Oracle is one of the best (if itīs not the better) ... | |
| Join with possibly empty results | 5/21/2007 |
| Q: My question is about how to build a query for an Oracle server, so I can get in the same row results ... A: .. What you want is to get a parent-child model and transform it in a generation model. Itīs used ... | |
| Join with possibly empty results | 5/18/2007 |
| Q: My question is about how to build a query for an Oracle server, so I can get in the same row results ... A: Sorry, but I really didnīt understand what you want. If you could please tell me the problem with ... | |
| Oracle Trigger Error | 5/16/2007 |
| Q: I am trying to create a trigger that does not allow any student to enroll in more than five courses ... A: Try this: CREATE OR REPLACE TRIGGER studentfive AFTER INSERT OR UPDATE OF studentno ON enrolment ... | |
| Oracle Trigger Error | 5/16/2007 |
| Q: I am trying to create a trigger that does not allow any student to enroll in more than five courses ... A: select count(studentno) into violation from enrolment group by studentno, sem, year; When you group ... | |
| question | 5/11/2007 |
| Q: Can u tell me how to check a range of dates within a another range in oracle sql? for example i want ... A: Sorry, but I really didnīt understand what you want. an you give a example, or specifically your ... | |
| subqueries | 4/16/2007 |
| Q: in oracle isqlplus how do you structure a multiple column subquery that allows you to return two ... A: This query represents the idea of what you want. See if it helps you, if not, ask me back Regards.. ... | |
| SQL Query | 3/27/2007 |
| Q: I want to populate one row of a table into multiple rows on the base of start and end date column ... A: I created a table with the same structure of yours called Teste. Please change it by the name of ... | |
| how to pivot a string | 3/23/2007 |
| Q: i want to break a string for Ex. 'William Jone Mathew' into three field and display it into three ... A: Try this block: select substr('William Jone Mathew', 1, instr('William Jone Mathew',' ', 1)), ... | |
| Help for Oracle 9I SQL | 3/10/2007 |
| Q: I have Debit and credit transactions for each of month days. The fields are :- account,DrCr_Code(D ... A: These two examples illustrate a option to solve your problem: Using group by cube Example 1: ... | |
| what are the "tab" and "dual"? | 3/7/2007 |
| Q: I always use "select * from tab" and "select SYSDATE from dual", what are the "tab" and "dual"? are ... A: Tab is a view, as seen: create or replace view sys.tab as select o.name, decode(o.type#, 2, ... | |
| what are the "tab" and "dual"? | 3/7/2007 |
| Q: I always use "select * from tab" and "select SYSDATE from dual", what are the "tab" and "dual"? are ... A: Tab is a view, as seen: create or replace view sys.tab as select o.name, decode(o.type#, 2, ... | |
| Replication | 1/9/2007 |
| Q: I am really new to this. I need to discuss with my manager and after that only I can decide. So this ... A: I guess there must be other solutions, but Iīve experience to work with this one and, it proved to ... | |
| Replication | 1/9/2007 |
| Q: I am really new to it. I need to discuss with my manager about this. Then only I will know whether ... A: I guess there must be other solutions, but Iīve experience to work with this one and, it proved to ... | |
| Replication | 1/9/2007 |
| Q: Host Machine:- VMS platform, Oracle database ( 7 database) Server Machine:- Windows 2003 Server, ... A: A way is: 1) create that replication triggers in the oracle database. 2) Create a process that ... | |
| Replication | 1/9/2007 |
| Q: I was thinking about the master/slave replication but it works within mysql. Is there any other ... A: Letīs go. Please, tell me all the process you want... replicate from where to where? when ... | |
| Replication | 1/9/2007 |
| Q: The trigger you have metioned, should I create them in the oracle(host) or mysql(server). Because I ... A: You would have to create them in oracle database. Unfortunately, if you want to create a ... | |
| Replication | 1/8/2007 |
| Q: 1st thing i am going to do this migration of database from oracle to mysql using mysql migration ... A: Well...about the replication, you can do the following.... create a replication table for each ... | |
| Replication | 1/7/2007 |
| Q: How to do replication from oracle to mysql. Can you explaind in detail pls.. Thanks A: This is a very wide question...full of possibilities. But thereīs two points we should have in ... | |
| Views in SQL | 1/5/2007 |
| Q: Unfortunately, the query which I have placed below doesn't run and highlights the b and gives the ... A: All right, But whenever you want to do a query like that, do the following.... select <columns> ... | |
| Views in SQL | 1/5/2007 |
| Q: I have written the query below but now I want to outer join my results which are in the select ... A: Try this select <columns> from tab1 A, <other table you want> (SELECT st.STORE_NO, ... | |
| Looping through a Ref cursor | 12/28/2006 |
| Q: The below code gives the following error at line (for i in V1 loop) PLS-00221: 'V1' is not a ... A: .. The problem seems to be in this line: TYPE MY_CUR IS REF CURSOR; If you change for MY_CUR ... | |
| how i read a string of format NN/NN....... | 12/13/2006 |
| Q: query to read a string, which in the format '--/--', the actual question is to check whether the ... A: Try: select decode(format,1,'Valid','Invalid') from ( select case when length('22/22') ... | |
| How to move database | 12/12/2006 |
| Q: If u don't mind can u give me a detail description or way of coding for import/export. ByeHi, I ... A: How does one use the import/export utilities? Look for the "imp" and "exp" executables in your ... | |
| Oracle SQL Query. | 12/8/2006 |
| Q: I have partly understood your answer and the have listed below the unanswered part that still ... A: Letīs go, You said: " For ex.: select <<expr>>,* from dual. But, this fails too. " It fails, ... | |
| Oracle SQL Query. | 12/8/2006 |
| Q: When we fire the query "Select * from emp" oracle resolves the * to all the columns present in the ... A: The problem is: There is a concept in Relational Methodology that says: A line can not be ... | |
| handing db_link error in exception | 12/7/2006 |
| Q: i want to trap the error regarding db_link in Exception. what should i write for raising an ... A: 1) Oracle execptions are: Oracle Exception Name Oracle Error Explanation ... | |
| oracle | 12/5/2006 |
| Q: what is the difference between a view and a snapshot? how can i encode my password? A: This is not a small but a big confusion.....anyway... A view, is physically not a segment, but the ... | |
| Oracle | 11/30/2006 |
| Q: I want to make a career in Oracle.So which certifications should i go for?And i would also like know ... A: I think a good way to start is to learn about relational databases. Itīs essencial that a good ... | |
| Tuning | 11/27/2006 |
| Q: I would like to know that how a programmer can know that which specific query out of 200 Queries in ... A: Sory about the long time you waited, but I had problems in my account. If I understood your ... | |
| round | 11/13/2006 |
| Q: how to get ROUND(0.51)=0.55 AND ROUND(0.50)=0.50 A: Forgive me aboute the long time...but I was in trouble with my account in allexperts. Oracle treats ... | |
Oracle
Edson
Top Expert on this page
Expertise
I can answer questions about Sql (including Sql tuning), pl/Sql and a bit of oracle administration
Experience
I work as a consultant in Brazil and I face problems of all kind in
oracle every day development.
Education/Credentials
INFNET,
Oracle Database SQL expert
©2012 About.com, a part of The New York Times Company. All rights reserved.