| Subject | Date Asked |
|
| Describe command in oracle 10g | 11/19/2009 |
Q: Please help me with describe command for oracle 10g. I am getting error ora00900 for 'desc ...
A: You are getting this error because you must be using it in a SQL statement. 'desc' is not a SQL ...
|
| regarding CSV upload ! | 11/15/2009 |
Q: i would like to upload a CSV format file into my oracle database. As n when the file is loaded, i ...
A: To load the CSV file you can use the SQL*Loader utility. All the errors related to table constraints ...
|
| awr report | 11/7/2009 |
Q: Respected Sir/Mam Question Number (1) drop table table1; FLASHBACK TABLE table1 TO BEFORE DROP ...
A: Check whether your table is in SYSTEM tablespace. If yes, then flashback drop won't work. Please see ...
|
| save image using oracle10gXE | 11/4/2009 |
Q: Respected mam i am using oracle10gXE I want to store image on oracle 1og using sqlplus i ...
A: 1) Check that you have read permission on the directory mydir. If not grant it as follows - GRANT ...
|
| Regarding PL-SQL Developer Requirement | 11/3/2009 |
Q: I am having 2.10Yrs of experience in Application Support including PL-SQL.I have done OCP(DBA) and ...
A: Even if your project was only data related, you can add some points to indicate that you have used ...
|
| forms 6i | 11/3/2009 |
Q: I hav developed a form in forms 6i. This form is having one tabular layout data block say D1. No of ...
A: You can try the following code in your 'when-button-pressed' trigger on blk D2. go_block('D1'); ...
|
| oracle 8i installation | 10/28/2009 |
Q: After installation, the SQL plus is started with scott/tiger.But once system restarts again the ...
A: I will require more information on this like what is the error shown for connection failed, which OS ...
|
| SQL Tuning | 10/27/2009 |
Q: I need you favor to tune the SQL, See below. The same kind of SQL is running parallely (9 SQL's) ...
A: You can check the following to fine tune this query - 1) Index created on table MCCDSS_QTR_HPFU_STG ...
|
| Oracle (No of Rows Into Single row) | 10/24/2009 |
Q: In Oracle I want to select No of rows into Single Row. For example (In the huge records) Name ...
A: There are multiple ways to get this result. The possible ways are listed at this site - ...
|
| oracle tuning | 10/24/2009 |
Q: Respected Sir I want to become a tuning expert of oracle but i dont know that which ...
A: Here are some links for performance tuning - ...
|
| Csv data into oracle | 10/22/2009 |
Q: at present i am using mysql database but i want to convert my whole "mysql data" into oracle data ...
A: I am not sure of MySQL. But you could try a mysqldump to take backup. Basically create a customised ...
|
| 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: You will have to use SQL*Loader to load csv file to Oracle tables. For this you will have to create ...
|
| Creating multiple tables with Shell Script | 10/12/2009 |
Q: I'm looking for a pure sql Cum Shell solution to the problem described below. I'm a beginner in ...
A: Since I do not have access to any Unix m/c, I cannot try and test this code. But I suggest you do ...
|
| Creating multiple tables with Shell Script | 10/9/2009 |
Q: I'm looking for a pure sql Cum Shell solution to the problem described below. I'm a beginner in ...
A: Try the following - [code] #! /bin/bash tname="table" for i in * do sqlstr="create table $tname$i ...
|
| date in forms | 10/9/2009 |
Q: In my form there is 3 field 1)header_id 2)so_date 3) description here header_id is a disabled feild ...
A: I will require more details for this - 1) Is date field required field ? If yes, are you entering ...
|
| openings | 10/8/2009 |
Q: how are you? I required some information from you about opportunities on oracle. i am planning ...
A: You have many opportunities in Oracle - PL/SQL Developer, Oracle Forms/Reports developer, Oracle DBA ...
|
| oracle | 10/7/2009 |
Q: what are the differences between oracle 10g and 11g version? how can i check version of oracle from ...
A: 1) You can check the Oracle 11g features so that you can identify the differences with 10g for each ...
|
| About Advanced Replication (Updatable MView) and recovery of System datafile | 10/7/2009 |
Q: This is Mahesh, DBA - Reflexis Systems India Pvt. Ltd. from Pune(M.H), India 1. I want to Set an ...
A: Both of these are DBA questions and I think these can be answered properly by some expert DBAs. I ...
|
| opportunties | 10/7/2009 |
Q: how are you? I required some information from you about opportunities on oracle. i am planning to ...
A: You have many opportunities in Oracle - PL/SQL Developer, Oracle Forms/Reports developer, Oracle DBA ...
|
| orecal | 10/1/2009 |
Q: v can see the procedure that v created by select * from user_object where user_object ='procedure'.. ...
A: To check your procedure code, you can check the table user_source or all_source. select line, text ...
|
| sql query or cursor | 9/30/2009 |
Q: Fullname TIT NAME MIDDLENAME surname Mr rahul kumar shukla Mr rahul ...
A: Given the data with possible spaces in Title, middlename and lastname positions, this query is not ...
|
| sql query or cursor | 9/29/2009 |
Q: Fullname TIT NAME MIDDLENAME surname Mr rahul kumar shukla Mr rahul ...
A: I did not understand your question properly. If anything is missing in fullname, do you want the ...
|
| Pass a Unix array variable to PL/SQL | 9/25/2009 |
Q: Is there any way by which we can store a list of file names in an array variable in unix and pass it ...
A: According to my knowledge we cannot pass unix array to PL/SQL procedure. But here is an ...
|
| image in oracle | 9/25/2009 |
Q: Respected Madam how to store image in oracle 9i can you tell me the step by step process to save ...
A: To store and retrieve image file, follow these steps - 1) Create an oracle directory as follows - ...
|
| image in oracle | 9/23/2009 |
Q: Respected Madam how to store image in oracle 9i can you tell me the step by step process to save ...
A: I will need the following information - 1) OS that you are using 2) How you are planning to save ...
|
| Oracle error | 9/21/2009 |
Q: i written the code like the following.Butyour code is very optimized ma'm. Thanks a lot ma'm. See ...
A: Your code looks ok, except for the part where you fetch cursor bulk collect into pl/sql table. The ...
|
| max search record | 9/21/2009 |
Q: I want to get search records which find the maximum number matches. basically i havce a table which ...
A: Try the following query (assuming input is p_in_compid) - SELECT a.tag_id, a.company_id, COUNT(*) ...
|
| Oracle error | 9/19/2009 |
Q: Please help in this and if you don't mind can you send the solution to my compnay id Actually what ...
A: I think the problem is that you are updating the same table (t2) which you have based the cursor on. ...
|
| uninstall orcle | 9/19/2009 |
Q: when i am reinstalling it i am getting an error as itis alredy existing although i have ...
A: You can go to the following link and check how to uninstall oracle 10g - ...
|
| SQL query | 9/16/2009 |
Q: Kindly help in answering below questions; Q1. Consider the following table PRODUCT (prod_id, ...
A: Here are the answers - Q1. Answer is B (A- cannot use group function in where clause C- ...
|
| set constraint character & numeric & character | 9/15/2009 |
Q: my name is vandan & i want to know that how to put constraint that first letter is 'C' & second ...
A: If you are using Oracle 10g, you can use regexp_like function to create the constraints. You will ...
|
| SQL Query | 9/9/2009 |
Q: Please help answer the following questions: Q1. Consider a query: SELECT sign(floor(ceiling(X))) ...
A: Here are the answers - Q1. C (sign returns 0 if value is 0, 1 if value is positive and -1 if value ...
|
| SQL Query | 9/2/2009 |
Q: Q3. For tables A (empid,empname,deptid) , B (deptid,deptname,location), C (managerid, deptname, ...
A: Answer is (B). (A) line 1 is correct since it selects all existing columns from tables A,B,C (C) ...
|
| SQL Query | 9/2/2009 |
Q: Which of the following query will produce result similar to SELECT dept.name FROM dept WHERE ...
A: The answer would be (C). The main query will return no rows, because emp will either contain ...
|
| Running 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: Consider this example - Table - query_table (id number, query varchar2(2000)) Data - (1, 'Insert ...
|
| pl/sql progrram | 8/31/2009 |
Q: i have a table Plot_table plot_iD SECTION_ID GRAVE_ID ------ --------- ---------- ...
A: Try the following - declare cursor cur1 is select p.plot_id, p.section_id, p.grave_id, ...
|
| pl/sql progrram | 8/29/2009 |
Q: i have a table Plot_table plot_iD SECTION_ID GRAVE_ID ------ --------- ...
A: Try the following - declare cursor cur1 is select p.plot_id, p.section_id from ...
|
| result set from anonymous block | 8/28/2009 |
Q: I have written a pl/sql anonymous block which is giving the dynamic select statement. But my ...
A: Use the following pl/sql - DECLARE sql_str long(32000); where_str long(32000); counter ...
|
| deleting rows for mysql | 8/27/2009 |
Q: delete emp where rowid not in (select min(rowid) from emp froup by empno); this query is ...
A: I have not used MySQL, but I read somewhere that MySQL uses rowid as _rowid. Try your query using ...
|
| comparing data in columns from two tables | 8/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: Assuming there are columns from col1 to col104 in your tables (and no other columns), here is a ...
|
| pl sql prograaming | 8/24/2009 |
Q: i have a table Plot_table plot_iD SECTION_ID GRAVE_ID ------ --------- ---------- ...
A: As mentioned earlier, use the following query - update interments i set i.grave_id = (select g.id ...
|
| pl/sql progrram | 8/24/2009 |
Q: i have a table Plot_table plot_iD ID SECTION_ID GRAVE_ID ------ --------- ...
A: You can use the following query - update interments i set i.grave_id = (select g.id from ...
|
| update tbale using subquery | 8/22/2009 |
Q: I have a problem to update a table using subquery. what i want is to update the credit field from ...
A: Please disregard my previous answer. I tried this query instead and it worked - update ...
|
| oracle sql | 8/18/2009 |
Q: Mr. Suchitra first I'd like to generate a random subject code in the following form: 'cccc999', ...
A: 1) You can use the dbms_random package in oracle to generate random numbers and strings. For ...
|
| schema | 8/17/2009 |
Q: kindly let me know how to list down the objects of a particular schema, I mean if i have a schema a ...
A: To list all the schema objects use the following sql in the schema - select * from user_objects; ...
|
| Help needed | 8/15/2009 |
Q: I am student of oracle and have a problem to retrieve values from data base to detail table. ...
A: If you can send me the detail table structures and sample data, I will be able to help you further. ...
|
| Post method in Oracle Forms | 8/13/2009 |
Q: when i m using Post method in when-new-item-instance trigger of one of my data block, its not ...
A: Following is the description of the Post method in forms - Writes data in the form to the database, ...
|
| oracle | 8/8/2009 |
Q: difference between different versions of oracle. how to add graphics in table. descripton of latest ...
A: You can find all the Oracle Versions and their features list at the following link - ...
|
| How to store picture in database | 7/11/2009 |
Q: Respected sir, Hello i am a student of Oracle database (self learning)i have already make some of ...
A: You can create a table with a bfile column type. Then you can insert/retreive the image as per the ...
|
| Sorting a set of Values in PlSQL | 6/8/2009 |
Q: i have question regarding sorting values using Plsql.. My requirement is to get N values from user ...
A: Sorry for the delay. Reading value one by one in PL/SQL is not possible. For that you can use Unix ...
|
| Invalid Number while using to_char date and group by of same column | 6/6/2009 |
Q: I have been reading your answers. All you guys are doing a great service. Thanks a lot. I am ...
A: If the SOME_DATE column is a date column, then it should not be given in single quotes (since you ...
|
| 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: I am not understanding exactly what you want. If you want to create an sql file with insert ...
|
| Search query | 5/13/2009 |
Q: i have to write a search statement on a table which is index based where the user wants to search ...
A: If you have to search all remaining columns for my_search, then give the following conditions - ...
|
| Need some help un PL/SQL procedures | 5/13/2009 |
Q: I have a requierement as below. - I have a formula (Ex: (120/(120+20))*100). I need to pass this ...
A: I am not sure if the following will work, but you can try it. 1) Pass formula p_formula as IN ...
|
| How to avoid mutating error | 4/29/2009 |
Q: I have a table in which i need to insert a row. before inserting into that table i need to check a ...
A: Here is logic you can apply - 1) Create a package and declare a variable for total issue qty in the ...
|
| file transfer | 4/14/2009 |
Q: How to convert oracle output text document into excel file. If its possible please suggest me. ...
A: These are some ways to convert oracle output to excel file - 1) Using TOAD - select the rows from ...
|
| oracle job | 4/11/2009 |
Q: I finished my oca exam, now preparing for the fundamental II-oracle-9i,I want to know about the ...
A: Sorry for the delay. Oracle DBA jobs have a great demand and they are high salaried jobs. So you ...
|
| mysql | 4/9/2009 |
Q: what is cursor and use of cursor
A: Sorry for the delay. A cursor is a mechanism by which you can assign a name to a "select statement" ...
|
| pl/sql sever procedure | 4/8/2009 |
Q: 1- x="abc" y="abd" z="abcd" how can i write this with procedure? 2- x="1 2 3 4" y="1 3 5 7" z="1 ...
A: Sorry for the delay. Before answering the question, I wanted to know what will be the input ...
|
| Diff between TAB and TABS in Oracle | 4/1/2009 |
Q: Can you explain me what is the difference between TAB and TABS table in Oracle Database. ? ANSWER: ...
A: Both TAB and TABS must be views (based on table USER_TABLES). You can check the view definition ...
|
| oracle | 3/27/2009 |
Q: difference between function & procedure in oracle
A: Following are the differences - 1) Function has to return at least one value to calling program, ...
|
| sql engine related | 3/8/2009 |
Q: I came accross a statement susc as this: SELECT /*+ LEADING(C)*/COUNT(*) FROM A, WHERE EXISTS ...
A: These directives are nothing but Optimizer Hints used for Performance Tuning. You can find more ...
|
| Can someone please answer this question that what will be the code in Oracle for this? | 3/2/2009 |
Q: Extract and display all the details of students those over 21(born before 1st April 1982).Order the ...
A: Here is the Oracle (SQL) query for your problem (assuming table name is students and columns are ...
|
| Query to fetch products in a price range | 2/26/2009 |
Q: I have a product table as shown below: Model Item Cost 1001 TV 112 1002 fridge ...
A: Try the following - SELECT distinct item, sum(case when (cost between 0 and 100) then 1 ...
|
| Query to fetch products in a price range | 2/26/2009 |
Q: I have a product table as shown below: Model Item Cost 1001 TV 112 1002 fridge ...
A: You can try the following query - SELECT distinct item, sum(case cost between 0 and 100 then ...
|
| Attendance Record.... | 2/25/2009 |
Q: Sir I am making a software in Java connecting to oracle for Student Management System. All Modules ...
A: The structure will depend on what is required by the system. If only present/absent is reqd the ...
|
| Oracle sequence failure | 2/24/2009 |
Q: This is a production Scenario:(names are changed) I have created a sequence in a schema DEX. I am ...
A: I don't think there is any problem in the sequence generation. Did you try generating this number ...
|
| Index | 2/18/2009 |
Q: Madam, I have a table which has 2 million rows (AM_TEMP1).I have to update some columns using other ...
A: You can create the plan_table using the script utlxplan.sql. In unix, it is present in the following ...
|
| Index | 2/17/2009 |
Q: Madam, I have a table which has 2 million rows (AM_TEMP1).I have to update some columns using other ...
A: Try creating an index for column location_group_size_cd on table am_temp1. You can check whether ...
|
| oracle | 2/16/2009 |
Q: is the oracle language same with c language
A: Oracle is a database and not a language. PL/SQL is a language which can be used to work on the data ...
|
| How to Disable Logon Password | 2/16/2009 |
Q: My name is Shakeel Ahmad iam from Pakistan first of all i would like to appreciate u people for ...
A: If you have a .fmb (source) file, check if there is any trigger named on-logon. You can change the ...
|
| Exporting Oracle Data to flat file | 2/6/2009 |
Q: I would want to write nearly 20 tables from Oracle database to flat files using a single SQL query. ...
A: Sorry for the delay. If your table structure for the 20 tables is same, you can convert them to a ...
|
| Oracle procedure | 2/2/2009 |
Q: I have a procedure like this, when i try to compile it it giveme error "Script was executed with 0 ...
A: Try the following - create or replace procedure insert_New_Applicant ( str_FirstName in varchar2, ...
|
| Oracle procedure | 2/2/2009 |
Q: I have a procedure like this, when i try to compile it it giveme error "Script was executed with 0 ...
A: The first error I saw was that there is no variable declaration for num_count. So declare num_count ...
|
| Exception Handling | 2/1/2009 |
Q: I will explain my doubt with an example. Let us take one anonymous block... In that Block, am ...
A: What I am trying to tell you is that you write a begin exception end block inside the loop. So if ...
|
| Exception Handling | 1/30/2009 |
Q: I will explain my doubt with an example. Let us take one anonymous block... In that Block, am ...
A: Try the following structure - BEGIN FOR i in 1 .. 10 LOOP BEGIN <INSERT into table ...
|
| Calling an Outside Function and Procedure in Procedure | 1/29/2009 |
Q: I'm Telling one example. That i have one function that has return one numeric return value. And Two ...
A: You can call a function or procedure from within another procedure. Suppose f1 is the function ...
|
| mutation trigger | 1/28/2009 |
Q: how can mutate triiger please explain with example
A: I did not understand your question properly. But I hope you want to know about mutating triggers. ...
|
| Need urgent Advise on oracle 10g | 1/21/2009 |
Q: I have worked on databases as DB developer(PLsql),mainly Oracle 10g Recently,i have joined in a ...
A: Good to hear that you are given an opportunity to take initiative. 1) you can try all the DBA tasks ...
|
| retrieving the data not in a table | 1/15/2009 |
Q: Mam,Iam here again with a newFor the same tables I was trying to query the data for the students who ...
A: I understood your problem. Try the following query - select a.id, a.name, '18/11/2008' from ...
|
| retrieving the data not in a table | 1/13/2009 |
Q: Mam,Iam here again with a newFor the same tables I was trying to query the data for the students who ...
A: Have you replaced the start_dt and end_dt with your specific dates? eg. ...
|
| retrieving the data not in a table | 1/12/2009 |
Q: Mam,Iam here again with a newFor the same tables I was trying to query the data for the students who ...
A: Try the following query - select a.id, a.name from enrollments_details a where a.id not in (select ...
|
| ref-cursor | 1/11/2009 |
Q: what is ref-cursor? why we need that? what is the difference between Explicit cursor and ref-cursor?
A: A ref cursor is a variable, defined as a cursor type, which will point to, or reference a cursor ...
|
| why function is not a complete program? | 1/11/2009 |
Q: I have refered in couple of books...in that they are considering Procedure as a complete program ...
A: Following are the differences - 1) Function returns only one value whereas procedure can return 0, ...
|
| retrieving wrong records from a table | 1/9/2009 |
Q: Iam retrieving wrong records from a table...and not able to get the solution... If I fire the query ...
A: It seems there is some invalid data in the adate field. You will have to find it out manually using ...
|
| retrieving wrong records from a table | 1/8/2009 |
Q: Iam retrieving wrong records from a table...and not able to get the solution... If I fire the query ...
A: Try the following query - select ATTENDANCE.ADATE, ENROLLMENTS_DETAILS.ID, ...
|
| retrieving wrong records from a table | 1/7/2009 |
Q: Iam retrieving wrong records from a table...and not able to get the solution... If I fire the query ...
A: Sometimes queries having date datatype and checking with strings are very confusing. So you better ...
|
| why function is not a complete program? | 1/6/2009 |
Q: I have refered in couple of books...in that they are considering Procedure as a complete program ...
A: According to my knowledge, functions is also a complete program. It serves the purpose of return a ...
|
| reg usage of group by clause | 1/5/2009 |
Q: I have a query like select distinct ...
A: To get the required output, you will need to use sql*plus. There is a BREAK command which suppresses ...
|
| Missing or invalid option while using reference.. | 1/3/2009 |
Q: i tried to create a table using a ref of <object_name>, but it shows that missing or invalid ...
A: For creating object, I found the following syntax on site ...
|
| r here any difference between 11i and 11g. | 12/31/2008 |
Q: I am 2 Years experienced in IT. I am into ERP(JD Edwards). I want to learn Oracle Database ...
A: Oracle 11i refers to the Oracle ERP Application Suite (often called Oracle E-Business Suite) and ...
|
| database | 12/24/2008 |
Q: IAM DOING A PROJECT IN THAT I HAVE TO CREATE EMPLOYEE DATABASE PLS TELL ME WAHT ARE THE MAJOR ...
A: You have specified all the basic fields in employee table. In addition (or as per requirement) you ...
|
| Sql Loader | 12/23/2008 |
Q: While loading from a Flat file, i want sql loader to store a column as 'date' data type. i.e ...
A: You can use the following syntax in control file - field_name Date "DD-MON-YYYY" The date in the ...
|
| query | 12/6/2008 |
Q: I have a table pc having one column hd. I am looking for a query which can find out the hd capacity ...
A: Here is the query you can use - select hd from pc group by hd having count(*) > 1 order by hd; ...
|
| CALL and EXECUTE procedure | 12/4/2008 |
Q: I'm trying to execute SQL procedure from C++ program. But i had error ORA-00900: invalid SQL ...
A: The main difference between EXECUTE and CALL command is that EXECUTE is a SQL*Plus command whereas ...
|
| uses count inside union all | 11/25/2008 |
Q: First of all thank you for your time and your help that always is so useful. The question that I ...
A: You will have to include the relation (where conditions) between the fdf and agents table and tgt ...
|
| Sql loader nullif duplicate column data | 11/18/2008 |
Q: I want to null a column (DOC_XREF2) if the data is equal to another column (DOC_XREF1). I can't get ...
A: I have never tried this. But you can try removing the brackets around the condition. i.e use the ...
|
| whats my next step | 11/18/2008 |
Q: I'm a b.tech in computer Science and Eng, and took my OCP certification in Oracle 9i in 2004. ...
A: I think Oracle experience is the most important factor in your career. The certifications will not ...
|
| related to reports | 10/31/2008 |
Q: Can I run the oracle report from local??? Means I want to call a report from my oracle forms. If ...
A: I have not used Oracle forms and reports for last 5 yrs. But I am sure there is some run_report ...
|
| Basic Parent child Relationship | 10/17/2008 |
Q: Table 'Relation' has 2 fields, Parent and Child. Records Parent Child p1 c1 p2 ...
A: Following is the query to get all child/subchild - select child from relation start with ...
|
| Future Advice | 10/13/2008 |
Q: I am MSC(IT),OCP and preparing SCSA.I have around 1.6 years of experience as developer DBA. ...
A: If you have Oracle DBA experience, it is a great plus in your career. You try to get jobs in the ...
|
| sql query | 10/8/2008 |
Q: i have been trying some queries on a tutor site and i came across the following query in which i am ...
A: Here is the query - select distinct maker ,case (select nvl(count(distinct pc.model),0) ...
|
| Table creation in oracle | 10/7/2008 |
Q: Good Evening Mam, Please Tell me How create a sql query for such this table..... ...
A: Following is the create statement - create table marks_detail ( sem_code number(1), subject_code ...
|
| Creating new databases | 10/5/2008 |
Q: I have an Orcale 8i in my system. After installing it, I will try to entre into it with username ...
A: Nimitha, I am not sure what the exact problem is. Maybe the oracle instance is not started when you ...
|
| Help in multiple in paramters | 10/2/2008 |
Q: it is a big one but i have to write. in Orcale 10g, i am trying to write a dynamic query that ...
A: Whenever a single quote is needed in a char string, you have to precede it by another single quote. ...
|
| procedure and function | 9/30/2008 |
Q: Myself is vivek kumar gupta.I am pursuing MCA. Please tell me about procedure and ...
A: In Oracle following are the definitions - Procedure - Procedure is a subprogram used to perform a ...
|
| solution to ora-12560 tns adapter error | 9/26/2008 |
Q: I installed in two different folders I logged itto database using sql*plus using oracle9i database.i ...
A: I am not sure if this will work. But why don't you try copying the listener file from database ...
|
| consolidate data with group by and sub query | 9/25/2008 |
Q: In my database, I have a table named as Delta_Src with columns and data as following: ...
A: Try this query select DISTINCT D.SBS_NO, D.STORE_NO, D.ITEM_SID, ...
|
| Use condition on a field from CSV file | 9/24/2008 |
Q: I am using SQL loader to load CSV data into a table and want to skip records based on a condition ...
A: You can use the WHEN clause for checking a particular value, but the field must be present in the ...
|
| DBMS info | 9/24/2008 |
Q: Can u give me a material or link that can give me A-Z information about DBMS Eg..from "what is DBMS ...
A: Basically 1) Oracle is a relational database (RDBMS) 2) SQL is a structured query language used to ...
|
| Log Table | 9/18/2008 |
Q: I am having one table with some data,i want to transfer the data to second table after some ...
A: Sorry for the delay. If you are loading the records from emp_temp to emp, and if some records are ...
|
| SQL query Tuning | 9/17/2008 |
Q: I use to optimize SQL query but only have little knowledge on it. It is limited on how to arrange ...
A: Explain plan helps you understand which path the query is taking eg. FULL TABLE SCAN, INDEX, etc. ...
|
| sql | 9/9/2008 |
Q: ex create table "emp"("eno" number(5),"ename" varchar2(20)); create table emp(eno number(4),ename ...
A: Sorry for the late reply. Double quotes are used to specify case sensitive names. So, 1) create ...
|
| select statement | 8/25/2008 |
Q: I have problem with case statement in select query. I have a table with structure ...
A: You will have to use dynamic queries in this case. You first get the query depending on category - ...
|
| select statement | 8/23/2008 |
Q: I have problem with case statement in select query. I have a table with structure ...
A: Here is the query - select pid, category, address, case lower(category) when ...
|
| Oracle 10g sql/pl-sql | 8/11/2008 |
Q: i have taken exam for Oracle 10g sql/pl-sql,can u give some documents regarding this and i want to ...
A: For any oracle exams you need more practice and experience. Same is the case for becoming Oracle ...
|
| Procedure | 8/9/2008 |
Q: How to write a procedure that calculate fine for books which were not returned to the library in ...
A: Here is a simple procedure (Assuming you input the transaction id for which you want to find the ...
|
| oracle | 8/6/2008 |
Q: 1 - How many foreign key can i have in a table? 2 - How many columns can i have in a table?
A: I have never tried to create more than 50 columns/forign keys in a table. But the documentation ...
|
| plsql | 8/4/2008 |
Q: I hope you can clear my confusion. I have a plsql code.The logic inside required as 1.cursor ...
A: I did not quite get your question. What do you mean by - cursor variable matches table column ? ...
|
| Indexing table | 8/4/2008 |
Q: When we ReIndex a table what precaution should we take to make sure things are smooth after ...
A: You can check your queries on the columns which are indexed - before and after re-index to make sure ...
|
| Help | 7/30/2008 |
Q: I am using a software package, having visual basic as front end & oracle 8.1.6.0.0 back end. I am ...
A: I hope you are getting this error after your form entry. If this is not the case then send me the ...
|
| SQL query | 7/29/2008 |
Q: i have a table name Vinod(fname,lname, salary) now i want to add a field mname between fname nad ...
A: You cannot add column directly in between 2 existing columns. The new column is always added at the ...
|
| Reg Oracle | 7/28/2008 |
Q: 1)there is schema A and schema B.Schema A has a table .schema B is given all the permission to the ...
A: 1) In this case, since truncate is a DDL command, schema B can truncate the table only if it has the ...
|
| sqlldr | 7/24/2008 |
Q: while using sqlldr , if my .csv file contains date as '01-01-08' and date in oracle is in the format ...
A: You can use the date format in the control file as follows - edate date "MM-DD-YY" where edate ...
|
| Exception Handling PL/SQL | 7/24/2008 |
Q: Very good evening to you, I have written a PL/SQL script in oracle, i am using cursors in this ...
A: This is the sample pl/sql block that you have to write to get all table names, column names etc - ...
|
| Hierarchical Query | 7/18/2008 |
Q: hope that u r very fine. I have a table in oracle 9i, named 'BOM'. It has fields main_product_code, ...
A: Sorry for the long delay. Here is the query which can find the total qty for a given part code - ...
|
| Procedure | 7/7/2008 |
Q: I just faced a question from oracle corp that Whenever a procedure is called inside a package ...
A: The main reason is that the stored procedures/packages are loaded once into the SGA and remain there ...
|
| Performance of a Stored Procedure | 7/3/2008 |
Q: How to check the performance of a stored procedure. and how to check the performance of a query? ...
A: 1) You can check performance of the stored procedure by either of the 2 ways - - SQL_TRACE and ...
|
| Index | 7/3/2008 |
Q: 1>How to check a query is utilizing a particular index. 2>If a table consists of 2 or 3 indexes ...
A: 1) You can check the query execution using EXPLAIN PLAN. More info at following site - ...
|
| SQL Loader | 7/2/2008 |
Q: I need to create a .CTL file for a given table and in the data file the columns are scattered here ...
A: The Filler option is used only when there is a column in the table, but no corresponding column in ...
|
| profile in unix | 6/30/2008 |
Q: We can see database entry by using oratab and profile command..what is difference between oratab and ...
A: Oratab ------- "oratab" is a file created by Oracle in the /etc or /var/opt/oracle directory when ...
|
| Cluster Index and Non Cluster Index | 6/19/2008 |
Q: Mam what is the difference between clustered Index and Non Clustered Index.Do they come under ...
A: I am not sure I can explain this in detail, but you will get more information on indexes at the ...
|
| Call a Function inside sql | 6/19/2008 |
Q: How to call a function inside a sql statement whereas we cannot call a procedure. please give me a ...
A: Here is the sample code to call a function from SQL (Assuming you have table project having columns ...
|
| DML statement with Dynamic Query or Execute Immediate | 6/17/2008 |
Q: Can I use Select or DML statements with Dynamic Query or Execute Immediate Statement. Scenario I ...
A: Dynamic query is mainly used when we do not know the tablenames or where conditions in advance. So ...
|
| Oracle - list databases | 6/16/2008 |
Q: What command line do you use to list the databases and tables in an oracle instance and also ...
A: To view database, instances, tables and columns, you have to use different queries as follows ...
|
| Calling SQL Stored procedure from PLSQL function | 6/11/2008 |
Q: As a follow-up, I could not reply back to the previous thread. The only option that successfully ...
A: In that case, I would suggest you write the procedure/function tempsqlsp in oracle instead of sql ...
|
| Calling SQL Stored procedure from PLSQL function | 6/11/2008 |
Q: I have created a simple PLSQL function to return a value that is pulled back from SQL server. ...
A: Remove the RNPS level from the pragma and check whether it compiles. Following is the explanation ...
|
| Calling SQL Stored procedure from PLSQL function | 6/11/2008 |
Q: I have created a simple PLSQL function to return a value that is pulled back from SQL server. ...
A: I am not quite sure of this. But you can try writing a packaged function as follows - CREATE OR ...
|
| union syntax | 6/11/2008 |
Q: how to fetch fields from three tables in a single statement The Query(select ...
A: The error indicates that there are some duplicate column names in some of the 3 tables. For eg. ...
|
| pl/sql | 6/4/2008 |
Q: what are the types of procedures? what is the difference between stored procedures& stand-alone ...
A: As you pointed out, there are only two types of procedures - stored and stand alone. Stored ...
|
| insert with select command | 6/2/2008 |
Q: I have a problem with insert statement with select. I have two tables named tbl1 and tbl2 with 5 ...
A: The query will be as follows - insert into tbl2 select a.f1,a.f2,a.f3,a.f4,'xxx' constant_v2, ...
|
| Oracle 9i Z0-007 exam | 5/30/2008 |
Q: i have to appear in exam Z0-007 for which i have downloaded some of the questions from ...
A: Sorry for the delay. Here are the answers to your questions - 1) Q.37 A. False because aggregate ...
|
| oracle | 5/23/2008 |
Q: i want to display at a time along with ename,max(sal),deptno from emp group by deptno
A: I think this query will solve your problem - select a.ename, b.maxsal, b.deptno from emp a, ...
|
| locking | 5/22/2008 |
Q: I want to know how to LOCK and UNLOCK the emp table. please send me the query. Thanks for ...
A: You can have detail information on how to Lock tables at this site (along with the options) - ...
|
| tables where characteristics of | 5/19/2008 |
Q: Name the tables where characteristics of package,procedure and function are stored?
A: Following are the tables which have characteristics of packages, procedures or functions - 1) ...
|
| Virtual tables during | 5/19/2008 |
Q: What are the virtual tables available during database trigger execution? Please give me some example
A: The two virtual tables available during trigger execution are OLD and NEW. These have the same ...
|
| What are the | 5/19/2008 |
Q: What are the demerits of trigger? Can I declare a trigger inside a package?
A: According to my knowledge following may be treated as disadvantages of triggers - 1) System ...
|
| pl/sql | 5/16/2008 |
Q: How to display the number in the format of 1 1 1 1 1 1 and 1 2 2 3 3 3 please send ...
A: Here is the plsql program to find the 1st pattern. Following is the description of the parameters - ...
|
| DDL statement inside a trigger | 5/13/2008 |
Q: How to write DDL statement (Create,Alter) inside the begin block of a trigger or any plsql block? ...
A: Here is how you can write a DDL in a trigger - create or replace trigger <triggername> after insert ...
|
| odd rows | 5/13/2008 |
Q: i have a table ,i want to display the odd rows in a table.
A: Considering emp table, here are the queries - For Odd rows select empno,ename from emp group by ...
|
| using sql in unix shell script | 3/31/2008 |
Q: running SQL within shell script
A: You can call sql from unix using the sqlplus command as follows # # Call SQL*Plus # ...
|
| exception handling | 3/29/2008 |
Q: whats the difference between exception_init and raise_application_error ? i want difference and not ...
A: Exception_Init assigns any exception names to any oracle errors so that the assigned exceptions can ...
|
| pl/sql | 3/29/2008 |
Q: 1) Is it possible use exception within exception?(ie: Nested Exception) 2) I wrapped one source ...
A: 1) It is possible to use exception within exception. Eg below begin --- exception when others then ...
|
| oracle | 3/14/2008 |
Q: what is a function and a procedure with examples
A: Procedure - plsql block to do a certain task Function - plsql block to do certain task and return a ...
|
| Pl/sql | 3/6/2008 |
Q: 1) Which situation going to be use View? Bcoz if i use view that will affect performance issue? 2) ...
A: 1) Did not get your question. 2) The correlated subquery evaluates the inner query for every row of ...
|
| Pl/sql | 3/3/2008 |
Q: Hai, 1) I install database in client side. That database contains packages,procedure,table.. ...
A: 1) I did not get this question. 2) I will have to check this further. 3) You may create indexes ...
|
| pl/sql | 2/26/2008 |
Q: Is it possible procedure with in procedure give with example? what is the difference between ...
A: For procedure within procedure, it is possible. I will let you know an example soon. Regarding diff ...
|
| sql | 2/26/2008 |
Q: 1) I am having a Table given below Name Month Amt xxx Jan 1000 xxx Feb 2000 yyy ...
A: The (+) sign means outer join in Oracle sql queries. a.name = b.name(+) means that even if there ...
|
| pl/sql | 2/26/2008 |
Q: What is the initialize section in package and what is the use of that? When will use initialize ...
A: The initialization section consists of all statements following the BEGIN statement through the END ...
|
| sql | 2/25/2008 |
Q: 1) I am having a Table given below Name Month Amt xxx Jan 1000 xxx Feb 2000 yyy ...
A: 1) You will have to use SQL*Plus report for the required output as follows - set pagesize 66 set ...
|
| Diff betwn function and statement. | 2/22/2008 |
Q: What is the difference between FUNCTION and STATEMENT? and difference between PROCEDURE and ...
A: I did not quite get your question. A function is a pl/sql block written to calculate/do some work ...
|
| pl/sql | 2/22/2008 |
Q: What is the use of following package ? Please give with example Dbms_scheduler, ...
A: DBMS_JOB is the package which allows users to schedule their jobs at a specific time. This package ...
|
| pl/sql | 2/21/2008 |
Q: 1)What are the difference between procedure and function? 2)when will use procedure or function? ...
A: 1) Following are the differences between procedure and function - Function - 1) Have to return a ...
|
| Form related question | 2/20/2008 |
Q: what is the difference b/w commit and commit_form
A: According to my knowledge, there is no difference bewtween commit and commit_form. Commit ...
|
| importing dat a | 2/20/2008 |
Q: can a store procedure be used to impot data from oracle global server and text file to local server ...
A: According to what I understood from your question, you will not be able to import data from the ...
|
| pl/sql | 2/19/2008 |
Q: I am having procedure create or replace procedure aaa as ( do add . ...
A: You can write seperate begin-end blocks for your add, subtract and divide routines. In this way, you ...
|
| pl/sql | 2/19/2008 |
Q: What is statment level rollback and use of that given with example?
A: If any error occurs during execution of a SQL statement, all effects of the statement are rolled ...
|
| Explain sql query | 2/11/2008 |
Q: Can you explain the following query..... SELECT cont.first_name || ' ' || DECODE( cont.middle_name, ...
A: I am not sure what the ? in Decode and Nvl means. Actually it should be a field or expression in the ...
|
| Pl/sql | 2/5/2008 |
Q: I have used multiple select statements in inside procedure and i used only one exception ...
A: There are two ways to capture which select statement gave the error. 1) Write exceptions for the ...
|
| Updation | 2/4/2008 |
Q: Sir, I have a sample table like this... COUNTRY YEAR AGE A1 A2 CANADA 2000 Total 15999.2 638.4 ...
A: I would like to know whether the country and year columns are null for the values having the age ...
|
| csv to excel conversion | 1/29/2008 |
Q: Sir, I have 130 csv files in a folder.I need to convert those csv files to excel.Is there any ...
A: I am not aware of any pl/sql script to convert csv to excel. But when I open my csv file, it is ...
|
| View Data Imported Data | 1/29/2008 |
Q: I have already imported dumpfile using imp utility. Now I want to see data and create indexes from ...
A: You will be able to see data in the tables that were imported. For indexes, you can create ...
|
| View Data Imported Data | 1/28/2008 |
Q: I have already imported dumpfile using imp utility. Now I want to see data and create indexes from ...
A: What were the options given while importing data ? If indexes was set to N, then you will have to ...
|
| Retrieve, Dropped tables | 1/21/2008 |
Q: Sir, Could you please explain me how to retrieve dropped tables in oracle? Thanks Kevin
A: If you are using Oracle version less than 10g, then the only way is to restore the previous backup. ...
|
| Usage of quote | 1/18/2008 |
Q: Sir, What is the difference between single quote (') and double quote(") in relates to using in ...
A: Double quotes are used in SQL to give an alias name to a column. See eg below select empno emp_no, ...
|
| Scramble data on a field | 1/16/2008 |
Q: I have a field with usernames. I need to scramble the data so that it will be unidentifiable. Is ...
A: You can use some string functions to scramble your data. eg. select ...
|
| Transposing Table | 1/15/2008 |
Q: Sir, Question is been asked in the interview., I have table Department with details like DeptId ...
A: You will have to create a function first (as below) - CREATE OR REPLACE FUNCTION rowtocol( p_slct ...
|
| Schema Name | 1/15/2008 |
Q: Sir, How can i find out 'schema name'? Thanks Kevin ANSWER: Hi Kevin, If you want to find ...
A: The schema in this string means the database connect string/instance name. You may find this in your ...
|
| Schema Name | 1/11/2008 |
Q: Sir, How can i find out 'schema name'? Thanks Kevin
A: If you want to find schema name for a partcular object (table/index/view/procedure etc), then use ...
|
| Exporting data from an Oracle 8i table to text | 1/8/2008 |
Q: I made a public records request for data from a state agency in Florida and was told they would ...
A: To create a text file from Oracle table takes very less time as compared to the estimated 10+8 hrs. ...
|
| regarding the doubt | 1/5/2008 |
Q: Actually i started my carrier with oracle (sql,plsql,forms),( one year of experience). but now iam ...
A: Actually I don't have any idea regarding Oracle ERP professionals. But whatever I have read online, ...
|
| oracle date filtering (where cause) | 12/31/2007 |
Q: what is wrong with this syntax ? WHERE (DPFOLIO.BUDGET_PROD.MONTH > '2005-12-31 12:00:00 AM') ...
A: You will have to check what is the default date format in your database - select sysdate from ...
|
| oracle date filtering (where cause) | 12/31/2007 |
Q: what is wrong with this syntax ? WHERE (DPFOLIO.BUDGET_PROD.MONTH > '2005-12-31 12:00:00 AM')
A: What datatype is the column that you are checking? In the example given, I see you are checking the ...
|
| Diff between count(1) and count(*) | 12/28/2007 |
Q: Diff between count(1) and count(*)
A: According to my knowledge, there is no difference between count(1) and count(*). Both give the same ...
|
| suggestion needed on oracle | 12/28/2007 |
Q: I'm Prashanth working as a oracle pl/sql d2k professional in india..i've got some doubts which are ...
A: 1) I did not get your question. Can you please elaborate? 2) For OCP you can use the OCP guides ...
|
| Managing Oracle Users | 12/17/2007 |
Q: If we create index on a column of a table using CREATE INDEX COMMAND, then after the index has been ...
A: The user created with the identified externally clause is mainly used for the operating system ...
|
| Oracle Indexes | 12/17/2007 |
Q: If we create index on a column of a table using CREATE INDEX COMMAND, then after the index has been ...
A: The user created with the identified externally clause is mainly used for the operating system ...
|
| Oracle Indexes | 12/14/2007 |
Q: If we create index on a column of a table using CREATE INDEX COMMAND, then after the index has been ...
A: When an index is created on any column, it will be used while executing the query even if we don't ...
|
| Insert rows in a table | 12/8/2007 |
Q: First of all let me thank you for the help you have given for updating my table. Now another ...
A: You will have to add the rows for all countries with year 2006 and salary as null. Use the following ...
|
| Updating one table using another | 12/7/2007 |
Q: Sir, I have two tables k1 and k2. desc k1; Name Null? Type COUNTRY VARCHAR2(12) YEAR ...
A: I had created the same data in my temp tables. And the query given, worked fine here. I don't know ...
|
| creating table on sql | 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: Limiting no of rows in a table is difficult. I am not so sure. But you can try writing a procedure ...
|
| Updating one table using another | 12/6/2007 |
Q: Sir, I have two tables k1 and k2. desc k1; Name Null? Type COUNTRY VARCHAR2(12) YEAR ...
A: Try the following query - update k1 a set a.salary= (select b.salary from k2 b where ...
|
| Regarding SQL | 11/30/2007 |
Q: "I have a table consiting of Three columns:-month, year and date. The data in the date column is ...
A: Try this query - select month,year,last_day(to_date('01-'||month||'-'||year,'DD-MON-YYYY')) from ...
|
| Inserting decimal numbers in a Number field | 11/22/2007 |
Q: I have a table with a column with datatype Number. I tried inserting 1.123 in this column. after ...
A: When you have a column with NUMBER datatype, it should take 1.123 as a valid value and should ...
|
| oracle problem | 11/6/2007 |
Q: PROBLEM: given a string of the format 'nn/nn', verify that the first and last 2 characters are ...
A: Try the following function - create or replace function checkstring(instr in varchar2) return ...
|
| need for a solution | 11/4/2007 |
Q: 1)what do u mean by trigger? 2) what do u mean by cursor? 3)what do u mean by package? 4)what do u ...
A: You will find everything related to pl/sql at the following sites - Oracle 8i - ...
|
| ORA-00979: not a GROUP BY expression | 11/2/2007 |
Q: My query, SELECT OPWD.DLVRY_RTE_MAST_ID,ODRM.RTE_CD,OW.WAYBL_NO,GET_BR_NM(OW.BKG_BR_MAST_ID) BR_NM, ...
A: Sorry for the delay. The problem in above sql is that we need the same columns in the group by ...
|
| Validation | 10/29/2007 |
Q: lets say suppose I have 2 tables saying Tabe1, Table2 in Table1 I have columns Name, IDNo. Table 2 ...
A: If you want the result as shown, first of all you should have one more column (city) like name and ...
|
| regarding copy data from one database table to another database table | 10/28/2007 |
Q: I have two databases. Each database having one table with same structure. First database table ...
A: You can try any of the following methods - 1) Export the table with 1000 rows to a file (using exp ...
|
| Validation | 10/26/2007 |
Q: lets say suppose I have 2 tables saying Tabe1, Table2 in Table1 I have columns Name, IDNo. Table 2 ...
A: I am giving the logic of the program below - declare cursor t1 is select a.idno, a.name ...
|
| oracle | 10/24/2007 |
Q: how to get oracle date field as the format we want in vb6.0 ANSWER: Hi Rama, Please let me know ...
A: If you want to convert date in this format from Oracle (using a query) use the following - select ...
|
| Orcale | 10/19/2007 |
Q: I know you are not a DBA expert, but do you know if Oracle could be set so as to take the left most ...
A: I am not sure whether this will work, but still you can give it a try. Write a database trigger as ...
|
| oralce | 10/16/2007 |
Q: In oralce1og my tablename is in "" (ex. "cctincidentinfo"). Can I remove "" by changing table name ...
A: First let me make it clear that I have not used Oracle 10g yet. I am not sure whether you are ...
|
| Database updation | 10/13/2007 |
Q: My question seems like vague , but I would like to know about that for my work.. In case when ...
A: For database job scheduling, you can use the dbms_job package. Following is the syntax to schedule ...
|
| question | 10/11/2007 |
Q: why we use cursor function and procedure.
A: Following are the definitions/reasons for using cursors, functions and procedures 1) Cursors ...
|
| sqlloader | 10/10/2007 |
Q: can we read line by line in sql loader.(each line will represent a column name) for example: Product ...
A: According to my knowledge there is no way to load data line by line (columns on seperate lines) ...
|
| Create PL/SQL PROCEDURE | 9/24/2007 |
Q: I am stuck in a query can you help. The question is that. Given that the salary earned by an ...
A: Try this query select empid, last_name, first_name, sal*12 annual_salary, ...
|
| About Design in PLSQL | 3/30/2007 |
Q: I read your article on Purity Levels for objects in Oracle and got a clear picture. I am designing ...
A: 1) You can use the truncate table or delete table command in PL/SQL. But this is to be executed ...
|
| PL/SQL | 3/24/2007 |
Q: 1. I want the differences,advantages and disadvantages of procedures,functions and packages. 2. ...
A: This question is too long to answer. Hence giving the reference sites. 1) ...
|
| run queries | 3/18/2007 |
Q: how can i run pl/sql queries? which software i required? Can i run it on scendlone system or i ...
A: You should download Oracle software to use SQL queries and PL/SQL programs. You can install it on ...
|
| Help for SQL | 3/13/2007 |
Q: I have Debit and credit transactions for each of month days. The fields are :- account,DrCr_Code(D ...
A: According to my knowledge, sql query will not work for this type of output. You will have to use ...
|
| 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: Both TAB and DUAL are tables. 1) TAB table contains a list of all of the tables in a database. 2) ...
|
| Delete DUP Rows | 3/6/2007 |
Q: I'm trying to load the DUPS into a 2nd table to process for deletion. Here ids the code I'm using ...
A: Check the SQL Query first through SQL*Plus. If it does not give any error, check the columns in the ...
|
| How to display a package specification ? | 3/5/2007 |
Q: We can display a package body from user_source. How to display a package specification ? Thanks, ...
A: You can display both the spec and body from user_source. For package spec use select line,text ...
|
| What is the DBMS_DESCRIBE package used for? | 2/20/2007 |
Q: What is the DBMS_DESCRIBE package used for? Thanks, lzzzz
A: You can use the DBMS_DESCRIBE package to get information about a PL/SQL object. When you specify an ...
|
| I wonder whether the EXECUTE command is a SQL*Plus command or PLSQL command or just SQL command? | 2/15/2007 |
Q: I wonder whether the EXECUTE command is a SQL*Plus command or PLSQL command or just SQL command? ...
A: Execute is a SQL*Plus command. Following are the different types of commands SQL* Plus commands ...
|
| Sequence and Tables | 2/5/2007 |
Q: 1) What is the code required to create a sequence and connect it to a field in a table? 2) How are ...
A: Following are the answers 1) Create sequence using following command CREATE SEQUENCE cust_seq ...
|
| SQL Plus Select Statement | 1/16/2007 |
Q: Using Oracle 8.1 SQL Plus. Below is my select statement to create a delimited file. I want to add a ...
A: You may try the query as given below SELECT RESPONSEID,DEPT, DECODE(RESP,NULL,'NO ...
|
| oracle - instance handling | 1/11/2007 |
Q: 1) i want to run more than one instances in a single oracle server.How can i . i want a deep ...
A: You can use different unix users to access the different instances. First start the two instances. ...
|
| import data from externam data files using SQL LOADER | 1/4/2007 |
Q: I cannot insert data into a table from external file using the function sqlldr . The error is ...
A: According to my knowledge, if this table is an external table, you will not be able to issue any DML ...
|
| Error: 00922 ORA | 1/1/2007 |
Q: This is the table that I am trying to create with constraints. I have already created a table named ...
A: The only thing I found wrong in this script was the column course (misspelt as coursr). But if this ...
|
| oracle | 12/28/2006 |
Q: i did not get properly you answer.so can u write a query TO VIEW THE BODY OF A GIVEN PROCEDURE ...
A: You can try this spool proc1.txt select text from user_source where name = ...
|
| error : ORA:06502 pl/sql:numeric or value error | 12/20/2006 |
Q: this is the procedure i am using PROCEDURE SP_test( i_Month IN VARCHAR2, ...
A: I will not be able to test this procedure as I don't have oracle installed on my m/c. Please can ...
|
| SQL query ... | 12/19/2006 |
Q: In a table I have a field "Ticket No.". I am using Ticket No in a format like: 00021-10-06-0201 ...
A: Please can you be more specific about the field ticketNo and ticketId. Are they two different fields ...
|
| oracle | 12/19/2006 |
Q: 1)I want to migrate sql server procedure to oracle procedure .but the restriction is that i can not ...
A: 1) I did not understand this question. Can u explain in details pls. 2) According to my knowledge ...
|
| Another way to make this SQL statement? | 12/18/2006 |
Q: I have been using the Toad Schema browser (by Quest Software)to run a statement on my set of tables ...
A: I am not sure of the results but you can try this SELECT ACCOUNT, START_DATE, hdr.PROMO_CODE, ...
|
| your opinion......... | 12/13/2006 |
Q: Hope u fine? I am a B.E(eletrical)passed in 2004,presently working as a support engg.in one of the ...
A: If you had Oracle in your academics then it is easy to pick up. Else you have to start from scratch ...
|
| tnsnames.ora - service_name | 12/11/2006 |
Q: If I unsderstand correctly: when configuring a remote connection in the tnsnames.ora file ...
A: Oracle SID is the unique name that uniquely identifies your instance/database where as Service name ...
|
| Unix Shell Scripting | 12/11/2006 |
Q: I have experience in Oracle and Oracle Development tools, but I am new to Unix and Unix Shell ...
A: You can start learning Unix referring to any books or online. Shell scripts are useful for Oracle ...
|
| oracle user privilege | 12/9/2006 |
Q: I just created a oracle user using SQL*PLUS, what is the best way to grant privilege to the user? ...
A: You can grant privileges to the user as required (system or object privileges). Please refer ...
|
| Session Duration | 12/6/2006 |
Q: I am passing query of Insert or update. I want to know the duration to execution taken by oracle ( ...
A: Oracle provides a TIMING command for measuring the running time of SQL commands. To activate this ...
|
| Oracle Character set | 12/5/2006 |
Q: We are using front-end application devaloped by J2EE. If we store information about customer ...
A: As far as Oracle database is concerned, you will get the string as it is stored in the table. So if ...
|
| guide | 12/2/2006 |
Q: .and very interested in oracle,again i want to become dba so what should be do me....i am not good ...
A: I would suggest you do some oracle basic courses. You also try to get a job in Oracle. B'coz ...
|
| View Procedures | 11/29/2006 |
Q: .. ur answer says how to view a procedure.. how can we edit a procedure fomr SQL prompt.. Regards, ...
A: You must have created a sql file to create the procedure (file with ext .sql). The starting command ...
|
| View Procedures | 11/28/2006 |
Q: Can u let me know how can we view/edit procedures from oracle SQL prompt. The same can be seen from ...
A: You can see the procedure through sql prompt as follows select * from user_source where name = ...
|
| Regarding triggers | 11/27/2006 |
Q: This is regarding my doubt about triggers. can u plz explain where & how triggers are stored in a ...
A: Triggers are stored in the database as objects. So you can view the stored triggers through the ...
|
| Thanks in advance for your feedback | 11/17/2006 |
Q: Please explain in detail:- What is whenever in sql? what is the purpose of using whenever ...
A: The WHENEVER statement specifies the action to be taken when a specified exception condition occurs. ...
|
| oracle | 11/14/2006 |
Q: WAT IS ORDER BY CLAUSE AND WAT IS GROUP BY CLAUSE?WAT IS THE DIFFERENCE BETWEEN THEM?
A: Order by clause is used to sort the rows in a particular order (asc or desc). eg. order by deptid ...
|
| oracle | 11/10/2006 |
Q: wat is indexeses? wat is a view? and tell me some examples?
A: 1) Indexes are used for faster access of rows in a table. wg. if column is indexed on empid. a row ...
|
| oracle | 11/7/2006 |
Q: WHAT R TYPES OF IMPLICIT AND EXPLICIT CURSORS? WHY IS SELECT PREFFERED OVER CURSOR? WHAT R DRAWBACKS ...
A: Sorry for the delay ! PL/SQL issues an implicit cursor whenever you execute a SQL statement ...
|
| Advise on Career | 11/6/2006 |
Q: I am currently an end user of a program written in Visual Basic but integrated into Oracle Server. I ...
A: Oracle database administration is a very high profile job. But to gain knowledge on this, you should ...
|
| operations on one table reflecting on other table automatically | 11/6/2006 |
Q: suppose i have two tables emp1 and emp2,both having same structure. emp1 is having data whereas emp2 ...
A: You can create trigger for this as follows before insert or update or delete on emp1 for each row ...
|
| QUERY | 11/4/2006 |
Q: I HAVE A TABLE CALLED "CALLDETAIL" IN THAT TABLE THERE IS A FIELD CALLED"STARTTIME" IS OF TYPE DATE. ...
A: You can try the below query select to_char(starttime,'MON') month, count(*) noofcalls from ...
|
| surrogate key ? | 11/1/2006 |
Q: What do you mean by 'not necessarily identify'? as we know, the primary key is unique enough to ...
A: Primary key identifies a unique row by one or more fields data in the database (eg. order no + ...
|
| SQL/DB2 | 10/31/2006 |
Q: When to use Store Procedures & when to use Triggers?And What are the differences between the above ...
A: Stored procedures are used when you have to carry on a task at a particular time. Their execution ...
|
| Oracle Query | 10/25/2006 |
Q: Can you help me - for 1) View All procedure List in Database , 2) View specific Procedure ...
A: You can use TOAD for checking all the objects in a database (You must have system privileges). 1) ...
|
| oracle | 10/18/2006 |
Q: what is the difference between parameter and global variable.exln both terms in detail. what is ...
A: 1) Paramater is similar to a variable but can be passed at runtime. Paramters are used in ...
|
| Oracle Unix shell script | 10/13/2006 |
Q: 1)How to execute a Oracle SQL query from unix prompt? 2)In a batch if there are 3 scripts has to ...
A: You can call sql queries from unix shell script as follows sqlplus -s usr/pwd << EOF > x1.log ...
|
| Oracle Unix shell script | 10/12/2006 |
Q: 1)How to execute a Oracle SQL query from unix prompt? 2)In a batch if there are 3 scripts has to ...
A: 1) You can execute SQL query using following on unix prompt sqlplus <username>/<password> Here ...
|
| Mutating table. | 10/11/2006 |
Q: i new to pl/sql programming. Could you please explain me the mutating table and constraint table ...
A: Table constraints are restrictions on the table/fields. Some egs are unique constraint (unique value ...
|
| sql | 10/9/2006 |
Q: Please Explain differance between package cursor and procedure cursor. Regards Ravindra
A: Actually I do not think there is any difference in the two. The cursor will remain the same. As ...
|
| ORA-00001 Unique constraint violated. (Invalid data has been rejected) | 10/8/2006 |
Q: Give a some causes of ORA-00001 Unique constraint violated. (Invalid data has been rejected
A: Cause This error means that an attempt has been made to insert a record with a duplicate (unique) ...
|
| Oracle SQL * Loader | 10/7/2006 |
Q: What is the different between conventional and Direct Method loading?
A: Following are some differences Conventional 1) When SQL*Loader performs a conventional path load, ...
|
| sql | 10/6/2006 |
Q: Please explain why faster turncate into delete.
A: Delete table is a logged operation, so the deletion of each row gets logged in the transaction log, ...
|
| Difference between Function and Procedure | 9/29/2006 |
Q: This is Shashank. I wanted to know what is the exact difeerence between oracle function and ...
A: Some of the differeneces are mentioned below Function - 1) Have to return a single value to the ...
|
| SQL Loader | 9/29/2006 |
Q: Madam My self Shashank,i m working as a Software Engineer.I wanted to know what is SQL Loader? In ...
A: SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle ...
|
| Find Children in heirarchy | 9/28/2006 |
Q: I have a table set up with a list of categories, their unique ID (number) and a third column that ...
A: Not sure. But you can try this select a.cat_id from cos_group_cat a start with a.cat_parent_id = ...
|
| how to avaoid mutating error? | 9/28/2006 |
Q: i have one table(emp), create one trigger for row level using that table(emp), again i am updating ...
A: Mutating occurs when u are trying to update/delete the same table on which the trigger is based. ...
|
| Procedure and Functions | 8/8/2006 |
Q: 1. What's the diff b/w PL/SQL and Procedure? 2. Define procedure,functions,triggers? 3. Need a spoon ...
A: 1) PL/SQL is a block of SQL statements doing one or more tasks. Syntax is as follows DECLARE var1 ...
|
| Export to text files | 8/1/2006 |
Q: Is this the only solution...:-)Hi, My question, Is there any way to export data from a table ...
A: You can also use select query in sqlplus to export data to text file. eg. set pagesize 0 set ...
|
| Why can | 7/18/2006 |
Q: Why can "set pagesize 999" make the output more readable? Thanks, lzzzz
A: set pagesize 999 makes the output readable because the header appears after 999 lines. The output ...
|
| running sqlplus under DOS command line and in a separate window? | 7/17/2006 |
Q: What is the difference between running sqlplus under DOS command line like ">sqlplus ...
A: The main reason is security. If you run through command line, the username and password are ...
|
| Extended/DEFAULT/TO_CHAR. | 6/14/2006 |
Q: I have a few questions. When you set a value to NULL, does the DEFAULT value get used? Can you use ...
A: 1) When you set a value explicitly to NULL, the default value is not used. But if you do not give ...
|
| Group by age range | 6/2/2006 |
Q: I have table like create table( user_id number primary key, age number); I want to count the toal ...
A: One possible solution is as below select '0-5' Range, count(user_id) Total_Users from tab1 where ...
|
| how to insert a date value in oracle insert statement | 6/1/2006 |
Q: when I write create table x(a int, b date) insert into x value(99, '1998/05/31:12:00:00AM') it ...
A: Please try following query INSERT INTO X VALUES (99,TO_DATE('1998/05/31 12:00:00', 'YYYY/MM/DD ...
|
| I want some reports on the basis of day of the week,month,year and hour of the day | 5/23/2006 |
Q: Will creating views in my case after whtever u have suggested ,help.. what do u mean by indexes..pls ...
A: In your case, views will not be needed. You can extract data depending on the new columns created ...
|
| I want some reports on the basis of day of the week,month,year and hour of the day | 5/22/2006 |
Q: I am working on a crash data anlysis system.I need to generate reports on the basis of day of the ...
A: If you are looking for reports where conditions are given in where clause, then you may have to add ...
|
| oracle | 4/21/2005 |
Q: what is the difference between procedure and fuction. what is the difference between dbms &rdbms.
A: Difference between procedure and function 1) Procedure may or may not return values. But function ...
|
| Translating views to comma, delimted files | 4/18/2005 |
Q: I have a project in which I am being asked to produce a comma, delimited file from a view. I am a ...
A: You can create a comma delimited file using a select statement. Create a file file1.sql as follows ...
|
| Oracle9i question | 4/3/2005 |
Q: I am trying to create a package name mm_rentals_pkg the contain two prodedurea and one function. ...
A: You can check the errors for package first using SHOW ERRORS. The main error I see is that you are ...
|
| Error while running procedure | 3/28/2005 |
Q: I have the following error while running the following procedure. Error: ORA- 22160:Element at ...
A: I have not used BULK COLLECT as of now. But while going through a document I found that the ...
|
| What is Diff.between Procedure... | 3/28/2005 |
Q: What is Diff.between Procedure & Function. I know function Return value but Procedure not. Other ...
A: Bot functions and procedures can return values. Apart from this following are the differences 1) ...
|
| Replace customer ID with customer Name in a form | 3/18/2005 |
Q: I have built a form based on a transactions table. This table has a field called "Customer_ID". I ...
A: Go to the block where you have created Customer_ID field. Create one more field Customer_Name with ...
|
| oracle forms forms | 3/14/2005 |
Q: what is 'synchronization' in Oracle forms, what is the purpose of using 'post' method in forms?
A: I have not used Forms recently. So according to my past knowledge I can answer your questions as ...
|
| What is correlated subquery? | 3/8/2005 |
Q: What is correlated subquery? Thanks, lzzzz
A: In a correlated subquery, the inner query uses information from the outer query and executes once ...
|
| sql statement | 2/11/2005 |
Q: what are the basic rules to follow when writing a sql statement and how to write good query.
A: First of all write the query on different lines. eg. select * from tab1 where col1 = 'value' order ...
|
| Purity level of functions | 2/9/2005 |
Q: .. Will you plz explain me the purity level of the function? what effect it will give if we use ...
A: The function purity level defines what structures the function reads or modifies. Following are the ...
|
| Doubt in query | 2/9/2005 |
Q: hai suchi(shall i call u like this) Once u gave me this query to use this query is to find the ...
A: Actually I do not remember your query I had replied. But according to what you have stated now, you ...
|
| plsql | 2/9/2005 |
Q: what is the diff between procedure&stored procedures. when we use packages? when we use ...
A: Procedure and Stored procedure is one and the same. As we store the procedures in database they are ...
|
| oracle | 2/8/2005 |
Q: 1)is there any new features comes in oracle 9i for using decode. 2)what is inline query pl give ex. ...
A: 1) You can use case statement instead of decode from Oracle 8i onwards. 2) You can write subquerirs ...
|
| sql,plsql | 2/3/2005 |
Q: what is the diff between decode and case? what is instead of triggers?why we use instead of ...
A: 1) DECODE is considered the most powerful function in Oracle. Oracle 8i release introduced the CASE ...
|
| sharing of data between applications | 2/2/2005 |
Q: Which has some 450 tables with thousands of records. Now for another application, the data is ...
A: You can follow the steps as below 1) Using existing database, create temporary tables using the ...
|
| pl/sql | 2/2/2005 |
Q: what is mutating table? what is mutating trigger? what is mutating error? thanks ...
A: A mutating table is a table that is currently being modified by an update, delete, or insert ...
|
| Oracle Help | 2/1/2005 |
Q: 1). I have created two tables using oracle in my computer at home with the user id Scott and ...
A: 1) You can coppy the two tables using export/import utility of oracle. First export the two tables ...
|
| query needed | 1/28/2005 |
Q: actually thi sis for my friend.Think that there is a sno as one of the fields.hai I need the wuery ...
A: You can try the following query to delete duplicate rows of given sno (keeping one sno as it is) ...
|
| Creating a Database | 1/27/2005 |
Q: Sir, 1). I have been using Oracle 8i in my computer. I commonly enter into Oracle using the ...
A: I did not get your first question. Regarding second, you can create another user in oracle using ...
|
| finding the current financial year in sqlplus | 1/24/2005 |
Q: Sorry for the confusion, in looking at the detail I failed to write a clear question. I have a ...
A: You can try the following CHECK constraint where fdate is from_date, eff_date is effective_date ...
|
| additional columns in a GROUP BY query | 1/20/2005 |
Q: Say I have a table called EMPLOYEE which contains emp_id, name, position, salary. I want to create ...
A: You can write a SQL*Plus report like this set pagesize 66 set linesize 80 col emp_id format 99999 ...
|
| SQL | 1/19/2005 |
Q: Thanx for ur instant response.i run this query, but the output does not match my requirement. It ...
A: Sorry for the delay. You can get the details by writing a subquery again. select * from emp c ...
|
| Query | 1/18/2005 |
Q: I have one table say tbl_cmp in this table i have 2 columns cmp_name and cmp_id cmp_id is primary ...
A: Try this query. First try the select and then if the result is as you expect, then delete the rows. ...
|
| Query on position of the column | 1/18/2005 |
Q: How Can I give Query Based on position of the column. say I want to give query on 3rd column in the ...
A: I have not tried such queries. But the table user_tab_cols gives you the column_name depending on ...
|
| SQL | 1/18/2005 |
Q: In Oracle 8i there are three master table i.e. EMP, DEPT and SALGRADE. my query is:- 1)Show the ...
A: I have tried this query in Oracle 9i. You will have to try it on Oracle 8i if it works select b.sal ...
|
| what is the difference between the following two statements, one has two zeros, another has two nines after decimal point? | 1/12/2005 |
Q: I am sorry, but I can not understand what you mean. Could you please give me an example? Thanks, ...
A: I mean to say that 00 or 99 format after the decimal point does not make any difference. But if it ...
|
| what is the difference between the following two statements, one has two zeros, another has two nines after decimal point? | 1/7/2005 |
Q: what is the difference between the following two statements, one has two zeros, another has two ...
A: In this case, there is no difference between the two statements. 9 - Return value with the ...
|
| Tablespace in Oracles 8.1 | 1/7/2005 |
Q: I am basically a programmer handeling a database of around 100 M.B. My database trasactions are ...
A: According to my knowledge you can alter the index tablespace using the following command for each ...
|
| forms 6i (d2k),pl/sql | 1/3/2005 |
Q: 1)how to pass parameter from one form to another form? 2)how to create matrix reports? 3)how to ...
A: I had worked on Forms 4.5 2 years back. So I cannot recollect the parameter passing. You can create ...
|
| Dual table | 1/3/2005 |
Q: Explain the concept of the DUAL table.
A: Following is the explanation dual is a table which is created by oracle along with the data ...
|
| Oracle query | 12/5/2004 |
Q: in an employee database which contains the join date of employee how will u retrieve the record of ...
A: Sorry. Gave you the wrong query. This is the right query (Please check the column names as per emp ...
|
| What is the difference between the following two clause, could you please help? | 11/25/2004 |
Q: I thought that nvl should only be used in RHS (that is, value side), because LHS is column name (nvl ...
A: We can use nvl ona any side (LHS or RHS), it can be used with column names too. There is no ...
|
| What is the difference between the following two clause, could you please help? | 11/24/2004 |
Q: What is the difference between the following two clause, could you please help? where ...
A: The difference in as follows 1) In the first clause the LHS i.e. if the batch_payment_id is null, ...
|
| about SQL*Plus command | 11/24/2004 |
Q: When spool SQL result to text file, there are too many space at the end of line (row) For example: ...
A: Regarding 1st query, you can decrease the linesize. But if you have selected the last column which ...
|
| Extracting Time part of date field | 11/23/2004 |
Q: Records are inserted all the day. In fact wer using forms application for data entry and format mask ...
A: If the records are inserted in the format you have said, then I think we will not be able to get the ...
|
| Extracting Time part of date field | 11/23/2004 |
Q: I tried with HH:MI:SS but its showing 12:00:00 only for all records. I want to get exact time of ...
A: If the records are inserted or updated using a single sql statement then the time will remain the ...
|
| sql | 11/22/2004 |
Q: Suchitra, I have two question . 1.why data fetching is so fast after creating a index and want to ...
A: 1) Do not know the exact mechanism. But after creating an index, the rows are arranged according to ...
|
| Oracle table creating | 11/22/2004 |
Q: I need help in to creat tables in Oracle for school project. If I email you the problem can you do ...
A: If you require any guidance for the creation of tables, I can give you anytime. But as this is your ...
|
| when a procedure or function... | 11/5/2004 |
Q: when a procedure or function required to recompile? Give answer with exp.
A: A procedure or finction needs compilation when the table structures which are referred in it are ...
|
| QUERY TO VIEW THE PROCEDURE | 11/3/2004 |
Q: HOW TO VIEW THE BODY OF A GIVEN PROCEDURE THROUGH SQL QUERY
A: You can use the table USER_SOURCE to view the procedure. It contains the following NAME - Name of ...
|
| I have schema and data as follows, | 10/19/2004 |
Q: This is a kind of inner or equal join, right? My understanding of inner/equal join is that it ...
A: It is an equi join query. But unless you give the condition of destination='toranto', you will not ...
|
| 0/0 PL/SQL: Compilation unit analysis terminated | 10/19/2004 |
Q: .. its fine without variable ..... just wanted to know is it necessary to give INTO clause in select ...
A: If you are using select statement in BEGIN and END block, you have to give into clause. But then you ...
|
| 0/0 PL/SQL: Compilation unit analysis terminated | 10/18/2004 |
Q: I have written a procedure in which i am just executing query. But i get error when i create ...
A: I think the procedure IN parameters are again declared in the pl/sql block. Try to compile the ...
|
| 0/0 PL/SQL: Compilation unit analysis terminated | 10/18/2004 |
Q: I have written a procedure in which i am just executing query. But i get error when i create ...
A: I think the procedure IN parameters are again declared in the pl/sql block. Try to compile the ...
|
| I have schema and data as follows, | 10/18/2004 |
Q: I have schema and data as follows, // table client CLIENTID CLIENTNAME INCOME 100 IBM 20000 // ...
A: Firstly you are joining the two tables with client_id. Then you select records where both ...
|
| ora-01536 space quota exceed (Urgent) | 9/23/2004 |
Q: I am facing problem of ora-01536 problem. The error says that ORA-01536: space quota exceed for ...
A: Though I am not an expert in this area, I would advise you to try this out ALTER USER username ...
|
| referenced columns in sql | 9/16/2004 |
Q: How can i find to which table and columns my table is related i want to check dependencies on my ...
A: Use this query to find out to which table is your table related to (replace &tablename with your ...
|
| future of oracle | 9/11/2004 |
Q: Respected Ma'm, I am a B.E.in Electronics & Telecom. Engg. I want to join oracle 9i DBA but don't ...
A: This course will definitely be good for you. But you have to gain practical knowledge while doing ...
|
| PSQL Question | 8/31/2004 |
Q: I have a question on the Rowid and Rownum pseudo columns. I have a table which I needed to start in ...
A: Actually rownum gives the order of records in the table depending on the select statement. So for ...
|
