You are here:
Oracle/Questions Answered by Expert Suchitra Joshi
| Subject | Date Asked |
| pl/sql query | 2/7/2012 |
| Q: I'm running the following pl/sql program which should return three columns and insert the codes if ... A: There should be a semicolon after each SELECT statement. Your code is missing the semicolons for the ... | |
| how to connect 1st and call sql stored procedure from oracle database | 2/7/2012 |
| Q: How to connect 1st and call sql stored procedure from oracle database.Can you please guide me ... A: Here is a link which gives information on how to connect to database, depending on your OS and tools ... | |
| oracle doubt | 2/3/2012 |
| Q: this is ramana reddy from india.now i am learning oracle.i heared that procedures support differed ... A: Stored Procedure supports deferred name resolution. Example while writing a stored procedure that ... | |
| tuning | 12/2/2011 |
| Q: CREATE TABLE CURRENT_PLANO_NBR NOLOGGING PARALLEL 10 AS SELECT DISTINCT /* + PARALLEL (A,30,1) ... A: I am sorry I cannot help you with this. This task requires testing on similar data size, which is ... | |
| Pl/sql proc to generate excel file | 12/1/2011 |
| Q: I have seen the topic regarding Creating Reports in Excel file directly from Procedures using ... A: Sorry for the delay in response. I have not tried any formatting in excel, but you can check this ... | |
| DBA Question | 11/23/2011 |
| Q: Mam, I want to be a DBA, now I am preparing for OCA(1Z0-047). Is it better for me? please suggest ... A: DBA is always a good area to be in. But you will need more practice and experience in this area in ... | |
| Procedure to change the datatyoe of a column | 11/18/2011 |
| Q: I am looking for the procedure wherin i can change the datatype of a column from varchar to ... A: Here is the procedure that might help your logic - create or replace procedure (in_from_date date, ... | |
| SQL Max Count | 11/4/2011 |
| Q: I have this table CUST_RECIPE, which contains these fields CUST_RECIPE_ID and CUSTOMER_ID. I need to ... A: You can try either of these queries - 1) select customer_id, count(cust_recipe_id) from ... | |
| query | 11/3/2011 |
| Q: Can you give me an example and explain a select query showing columns of data from an oracle data ... A: I don't know the exact answer. But you can try to use ODBC and create a database link for SQL Server ... | |
| PL/SQL | 10/23/2011 |
| Q: Peace be upon You Mam, Actually I am working as ERP Consultant in Orion ERP, I know the basic ... A: Good to know that you found the site useful. For examples you search the net according to your ... | |
| PL/SQL | 10/17/2011 |
| Q: Peace be upon You Mam, Actually I am working as ERP Consultant in Orion ERP, I know the basic ... A: You can get all the documentation on this site - ... | |
| Pl/SQl | 9/27/2011 |
| Q: Suchitra I am Abhishek a 2011 b.tech(ece) batch passout and i am currently working with a software ... A: Its good to hear that you are enjoying software field. For any field, I would suggest you start ... | |
| oracle apps or sap | 8/16/2011 |
| Q: I have completed oracle D2k forms and reports 6i but i hardly find any jobs in the market. I have ... A: If you have done Forms and Reports, then Oracle Apps is good. It contains both - the Oracle GUI and ... | |
| Oracle questions | 8/5/2011 |
| Q: Please answer all the questions. 1.Difference between equi index and non equi index. 2.How to delete ... A: I did not understand your 1st question. Did you mean equi and non equi joins ? Regarding 2nd ... | |
| Trigger | 6/23/2011 |
| Q: Generally we cant use commit in trigger but we can use commit using by autonomous transaction.why we ... A: Actually you can, if you declare an autonomous transaction in your trigger, but you should only do ... | |
| Oracle Versions | 6/15/2011 |
| Q: Differences between oracle 9i and 10i? A: The database version is 10g, not 10i. You can check the internet and search for 'differences ... | |
| ORACLE FORMS- DATA BASE BLOCK | 6/10/2011 |
| Q: Hai, I have one doubt that how do we find that whether the created block is database block or non ... A: Check the following block property - Database Block property - Specifies that the block is based on ... | |
| Check Constraints | 6/7/2011 |
| Q: Greetings... I'm not exactly sure if this is even possible or not, but I figure I'd ask someone ... A: Try creating the table with the check constraint as follows - drop table r; create table r ( a int ... | |
| Getting error while using UTLFILE. | 5/31/2011 |
| Q: I want to read from a OS file kept in a directory and I want to print the content of that file. The ... A: Try giving the following parameter in your Init.ora file for the instance where you are running ... | |
| Getting error while using UTLFILE. | 5/27/2011 |
| Q: I want to read from a OS file kept in a directory and I want to print the content of that file. The ... A: Your first task would be to write an exception section and handle all the UTL_FILE errors as ... | |
| Sort order in 11g | 5/25/2011 |
| Q: In 9i when Oracle retrived data from an index it kept it sorted. Since upgradeing to 11g I noticed ... A: The only way to get sorted results is using ORDER BY in your queries. In 9i or any other versions, ... | |
| send me ans and qn | 5/23/2011 |
| Q: what are the save commannds in sql. ANSWER: Hi Seshu, I did not understand your question properly. ... A: A Stored Procedure is a procedure (pl/sql block) which does some database tasks ... | |
| SQL | 5/21/2011 |
| Q: MADAM, I HAVE A DOUGHT HOW TO USE THIS COMMANDS IN PROJECT WORK. A: I can explain how to use SQL commands, if you give the following details - 1) Your overall project ... | |
| send me ans and qn | 5/21/2011 |
| Q: what are the save commannds in sql. A: I did not understand your question properly. If you mean how to save data using sql command, then ... | |
| Text search in views | 5/10/2011 |
| Q: I would like to know , how we can search any text from column 'TEXT' of DBA_VIEWS. we can do similar ... A: If you have privileges on dba_views, then you can modify the function and query to select from ... | |
| downloading oracle products | 5/9/2011 |
| Q: i wanna ask how can i download oracle forms and reports setup and are these products free or they ... A: You can check this site for all the downloads (They mention that it is free) - ... | |
| Query related to SYS REF cursor | 5/6/2011 |
| Q: I want to know the difference between ref cursor and sys ref cursor in oracle. Can you explain me ... A: SYS_REFCURSOR is a built-in data type and a weakly-typed cursor (No return type declared), whereas ... | |
| Text search in views | 5/6/2011 |
| Q: I would like to know , how we can search any text from column 'TEXT' of DBA_VIEWS. we can do similar ... A: We have to first create a function for this as follows (Note that it will work only for text upto ... | |
| Bash Scripting | 4/28/2011 |
| Q: I have done a script for extracting a jpeg, im not sure how to do this for more than one is a loop ... A: If you have windows, you can ftp the script from unix to a windows folder. Steps ------- 1) Windows ... | |
| Bash Scripting | 4/27/2011 |
| Q: I have done a script for extracting a jpeg, im not sure how to do this for more than one is a loop ... A: You can find examples of loops at the following link - http://www.cyberciti.biz/faq/bash-for-loop/ ... | |
| Oracle 11i vs 11g | 4/22/2011 |
| Q: Can you explain me what is the difference between 11i and 11g in oracle? I did some googling but ... A: Oracle 11g is the Oracle database version 11g. Oracle 11i is the Oracle ERP Applications Suite ... | |
| Number vs float | 4/11/2011 |
| Q: Suchitra, My company has an oracle database that I want to send data to. I am going to send this ... A: Oracle can have float values like 12.356 stored in number datatype with scale as 3. eg. fld1 ... | |
| Web Forms | 4/6/2011 |
| Q: Bear with me as I explain this. In my company we have a web application built with oracle. As you ... A: This seems to me as a front end problem. So I cannot help you with this. If there is a change in ... | |
| Peculiar issue in Aggregation to represent rows as cartesian groups | 3/23/2011 |
| Q: I have query which returns (after several number of joins and formatting) rows like this: ... A: I did not understand your question. Can you please give the following details so that I can help ... | |
| privileges | 3/22/2011 |
| Q: here's my problem 1)i log in as sys, grant update,insert etc object privileges to user scott, 2)now ... A: There must be some privileges/roles that were assigned to scott or public. You can check the ... | |
| Open Cursors exceeded | 3/21/2011 |
| Q: 1. Why sometimes message like ORA-01000 maximum open cursor exceeded is coming? I am working in ... A: Here are some links which might help you - ... | |
| privileges | 3/21/2011 |
| Q: here's my problem 1)i log in as sys, grant update,insert etc object privileges to user scott, 2)now ... A: System Privileges are normally granted by a DBA to users. Examples of system privileges are CREATE ... | |
| Career Advice | 3/16/2011 |
| Q: I am looking for advice from you. I have 6 years of experience in Oracle (SQL and PL/SQL ... A: If you stick to SQL and PL/SQL development, there is no great future ahead. Nowadays PL/SQL is like ... | |
| Table is getting Lock on retrieving data | 3/11/2011 |
| Q: I am facing a problem that sometimes my table is getting lock and i am unable to retrieve data from ... A: Please send the query and data in the table. Also send the no of users using this table, the timing ... | |
| how to check dependencies in a column | 3/10/2011 |
| Q: I have a table with a column that holds a formula. The formula is again a combination of few columns ... A: I did not quite understand your question. Can you let me know the expected output in terms of data ? ... | |
| CREATE VIEW USING PROCEDURE OR VIEW | 3/5/2011 |
| Q: Sir, I would like to create a view using procedure. Say, I one to create a view out of SALGRADE ... A: You can create a view through a procedure using the EXECUTE IMMEDIATE command to create the view. ... | |
| Best book to learn PL/SQL | 3/4/2011 |
| Q: I am into Java/J2ee. I would like to learn Oracle PL/SQL for project purpose. I have basic knowledge ... A: You can read any Oracle Press PL/SQL book. You can also read the PL/SQL Users guide and reference on ... | |
| pl/sql wrap | 2/20/2011 |
| Q: I tried to perform pl/sql wrap using the following syntax: WRAP INAME=test and then I press enter. ... A: Check whether your input file is an SQL file. wrap is used to wrap a single source file, such as a ... | |
| reg:concepts in procedure | 2/19/2011 |
| Q: Could u give clear description about IN,OUT and INOUT parameters A: Following is the information of each parameter - 1) IN - It is the default parameter. i.e. if no ... | |
| find out duplicate and delete | 2/17/2011 |
| Q: I have a table it has lot of records,and there is no primary key for that table.I have lot of ... A: You cannot do this without using rowid. Since all the column values are duplicates, there is no ... | |
| reg:I need explination for the fallowing procedure | 2/16/2011 |
| Q: PROCEDURE etl_request_period_load ( p_book_cd VARCHAR2, p_period_from VARCHAR2, ... A: It is very difficult to explain any procedures which is written by others. For this, you will have ... | |
| convert string to number | 2/11/2011 |
| Q: I am very new to SQL (beginner of a beginner) I have a .csv file that contains numbers with comma ... A: Please send the following details, so that I can help you further - 1) Oracle version 2) Oracle ... | |
| convert string to number | 2/11/2011 |
| Q: I am very new to SQL (beginner of a beginner) I have a .csv file that contains numbers with comma ... A: Here is the query which can give you the required results - (Assume table t1 and column c1 defined ... | |
| SQL -Joining table | 2/8/2011 |
| Q: I have a TEMP table here with the columns as below: TEMP - cust_id(PK), cust_name, card_no, ... A: The insert in failing because there must be duplicate cust_id in your data which you are trying to ... | |
| instance or database | 2/8/2011 |
| Q: what is difference between instance and database? how many instances or databases can load at a time ... A: You can find all the information at the following locations - ... | |
| pl sql | 2/7/2011 |
| Q: i am joining a company as a d2k trainee. i want to know about practical question in pl sql or oracle ... A: The form or pl/sql code really depends on how the database is structured and what is the company ... | |
| oracle | 2/1/2011 |
| Q: how we know what he procedure doing it? and what is happen inside the procedure ? ANSWER: Hi Mayur, ... A: 1) You can have n number of indexes on a table. But too many indexes will slow down performance. ... | |
| Creating reports from Different Users | 1/12/2011 |
| Q: I would like to use a Table exist under different users for reporting persons. How to do the same? ... A: Here is the query you can use - INSERT INTO Main.Consolidate ( SELECT rownum, b_code, dt_start, ... | |
| LOGIN NOT ALLOWED | 1/7/2011 |
| Q: MADAM, WHENEVER I TRY TO RUN MY APPLICATION THE FOLLOWING MESSAGE IS SHOWING OFF. "LOGIN NOT ... A: This is a question that you can ask your DBA if this is related to your official database. There is ... | |
| 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: Try this query for id 3 (simlarly for any id) - SELECT a.id, a.parent, a.left, a.right FROM tab1 ... | |
| oracle | 1/5/2011 |
| Q: how we know what he procedure doing it? and what is happen inside the procedure ? A: I did not quite understand your question. Do you want to debug the procedure and see outputs at ... | |
| ORA :06563 | 12/27/2010 |
| Q: This is the followup to my previous question related to ORA:06563 You asked me to give the details. ... A: What is your schema where you created the procedure ? Are you getting error while running the ... | |
| oracle dba material | 12/21/2010 |
| Q: sir, am mallikarjun...! from India i had done recently DBA(oracle 11g) Course in Willshire ... A: You can check the online Oracle DBA documentation at the following site - ... | |
| ORA 06563 | 12/18/2010 |
| Q: I am getting ORA-06563: top level procedure/function specified, cannot have subparts ERROR while ... A: Based on the given details, I cannot guess what might be the problem. Please give me the following ... | |
| ORA 06563 | 12/16/2010 |
| Q: I am getting ORA-06563: top level procedure/function specified, cannot have subparts ERROR while ... A: Please send the details of the package/procedure/function (names, structure, how you are ... | |
| Validating SQL Query | 12/10/2010 |
| Q: Can we validate an sql query without execution. Is there any in built fun/proc to validate? Please ... A: I have never used anything for sql validation only. But found some links on net which might help you ... | |
| Difference Between Function & Procedure | 12/6/2010 |
| Q: its a very common question. But till date i didnt get a satisfying answer. Please help A: The main difference is that a function is generally used for computations, after which the result ... | |
| Oracle-RDBMS | 11/13/2010 |
| Q: What are the characteristics of RDBS-Oracle ?(Points on 1.High Security,2.Multiuser environment, ... A: Here are the answers - 1) Security ... | |
| DBA Course contents and future | 11/11/2010 |
| Q: I've done my graduations in computers quite few years back and now i'm again want to start learning ... A: Database is a good area to start. You can check on net for the latest database concepts. You have ... | |
| Reg: Error Traping | 11/6/2010 |
| Q: Could u please tell the process how to trap the errors if i am suppose to insert 1000 records in to ... A: You can declare a seperate block for each insert in which you can trap the errors using exceptions ... | |
| that toad , window | 11/3/2010 |
| Q: please tell step by step export and import ,(table ,user , database ,tablespaces) A: Following are the details of each mode - Full: Exports and imports a full database. Only users with ... | |
| Oracle 10g: SQL Constraints! | 10/31/2010 |
| Q: Why is a NOT NULL constraint type identified in the USER_CONSTRAINTS view with the letter C? How is ... A: Please check this link for the all_constraints table definition. user_constraints is similar except ... | |
| import dmp file | 10/27/2010 |
| Q: when i am importing .dmp file it is importing in system user.but this is not importing in other ... A: To import to a different user try the following - imp myuser/mypasswd FILE=x.dmp FROMUSER=expuser ... | |
| database trigger vs integrity constraints | 10/19/2010 |
| Q: What is main difference between database triggers and integrity constraints? Rajesh A: Following are the differences - 1) A declarative integrity constraint is a statement about the ... | |
| Title of a hindi movie | 10/11/2010 |
| Q: I think it was you who handled my question a while ago when I asked about the english translation to ... A: The actual spelling for the film is Humraaz. Since this is a very old movie, I am not sure whether ... | |
| Oracle Index | 10/6/2010 |
| Q: I need to compare 2 tables, Say A and B(10 million records each). I want to select all rows from A ... A: Create index on the columns which are included in the WHERE clause of the query. So if query is as ... | |
| Table inside procedure | 10/4/2010 |
| Q: Can we create a table inside a procedure? If yes, could you please explain how? A: You can create a table inside a procedure using the EXECUTE IMMEDIATE command as follows - create ... | |
| exists & in | 9/29/2010 |
| Q: How to use functions in DML? ANSWER: Hi Santhosh, I am assuming that the functions you are ... A: IN and EXISTS are both WHERE clauses but whereas IN is a function, EXISTS is a condition. Also, both ... | |
| Looping, Exception Handling and Output Formatting in SQL Plus | 9/28/2010 |
| Q: Overview: I am working on Unix Environment and using SQL Plus for running queries. Requirement: I ... A: This is due to the bash script which connects to the database for every line in the text file, since ... | |
| procedure | 9/26/2010 |
| Q: How can we call a procedure from a function? how can we call a function from a procedure? how can we ... A: Here is the syntax of how to call a procedure or function - 1) Calling a procedure from function ... | |
| Generate a View showing Opening Balance, the transactions and Closing Balance in a given period | 9/24/2010 |
| Q: I have a table (say table1) like given below. From the said table, I want to generate(View) a ... A: You can create the view as follows - CREATE OR REPLACE VIEW v1 AS SELECT ob.sub_cd, ob.ac_no, ... | |
| Transfer data in a pre-formatted text file | 9/16/2010 |
| Q: I need an urgent help of transfer data from a oracle table to a pre-formatted text file which is as ... A: Here is a query which can give you the text output. Table name is table1. SELECT ... | |
| oracle errors | 9/9/2010 |
| Q: Madam,Thanks first for your last response it worked successfully.I have a new problem from my ... A: If this problem is for SQL plus help, then you may find more information on this site (Check page 2 ... | |
| Prevent PL/SQL from running if another session is still not complete | 9/6/2010 |
| Q: I have written a package with a function that is meant to be called from dmbs_jobs or scheduler. But ... A: Sorry for the delay in reply. You can try the following - 1) Create your procedure in a package. ... | |
| oracle sql queries | 9/2/2010 |
| Q: Madam,I am a student and new to oracle i have question related to oracle hope you would like to ... A: The output depends on the column width of each column and how many columns are selected. If you ... | |
| to get the knowledge of pl/sql on unix/linux plateform | 8/26/2010 |
| Q: i have working experience in pl/sql on windows. but i wish to get the knowledge of pl/sql on ... A: You can learn the following commands which will be helpful for you in Unix - Basic commands like ... | |
| XML to PL SQL career | 8/25/2010 |
| Q: I am BE. I have total 3 years of IT experience of which 1.6 years as XML web developer for a BPO ... A: I can understand your situation. Please go through the Oracle 10g documentation (SQL and PL/SQL with ... | |
| Oracle as a Career | 8/18/2010 |
| Q: I want to ask you that whether to be a DBA, an experience in system adminstration is required or ... A: You better target to be a DBA directly. No system administration experience is required, although it ... | |
| Career Path | 8/14/2010 |
| Q: I've just done Graduation in IT and want to pursue career as DBA. But I've been told that there is ... A: I will definitely suggest you to go for a career as DBA. You will find it little difficult at entry ... | |
| Date format | 8/12/2010 |
| Q: What are the conditions we have to keep in mind while using date format? or datetime format? in ... A: Date formats can be used to convert Date columns to strings or vice versa. So you have to be careful ... | |
| problem | 8/3/2010 |
| Q: ORA-12541: TNS:no listener solusion A: The main problem is listener is not started. So if you have Oracle installed on your PC, try to ... | |
| plsql function | 7/29/2010 |
| Q: i am trying to develop a plsql function that performs data transfer from 1 table to another. This ... A: This is definitely possible in PL/SQL. Instead of function, you can create a procedure to do this as ... | |
| Oracle Mutating Error | 7/20/2010 |
| Q: Pl. answer How To resolve mutating errors with example A: You can find the answers and examples on these sites - ... | |
| DROP | 7/19/2010 |
| Q: I use the code - select PURCHASE_MECHANISM, ... A: If you do not want the 2 columns in output, you will have to remove them from the group by clause ... | |
| DROP | 7/19/2010 |
| Q: I use the code - select PURCHASE_MECHANISM, ... A: I am assuming that you want to drop columns from the table itself. Here is the command which can do ... | |
| oracle forms 10g | 7/14/2010 |
| Q: how can i selectively enable/disable a set of rows of a block in a form based on a flag set to ... A: It has been a while since I last used Forms. 1) You can try to enable/disable all the fields of ... | |
| oracle | 7/12/2010 |
| Q: mam,my question is how can we upload a csv file in oracle database table in regular interval of ... A: You will have to use SQL*Loader utility for this. You can find more info on SQL Loader at the ... | |
| procedures and function in oracle | 7/9/2010 |
| Q: are procedured pre compiled and stored in diffenr format iin oracle,and functions are parsed at ... A: Sorry for the late reply. Procedures are considered better because they can do multiple tasks in a ... | |
| procedures and function in oracle | 7/8/2010 |
| Q: are procedured pre compiled and stored in diffenr format iin oracle,and functions are parsed at ... A: Both procedures and functions are compiled and stored in database. The only differences are as ... | |
| 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: The trunc function on LHS is causing the problem. I assume that act_prcs is a package and ... | |
| Prc Re | 7/6/2010 |
| Q: I have one odube that when I take the differenec between the dates and 01/03/2008 and 07/12/2007 i ... A: Please send your question again. I did not understand it as format given is 1yyddmm and it looks ... | |
| oracle | 7/4/2010 |
| Q: Akshya here.i have two questions 1)write a query for find which rows are apdated on last 2 day ... A: 1) If you have a column in table which stores datetime, then use the following query - select * ... | |
| SQL | 6/28/2010 |
| Q: Kindly have a look at the code : SELECT ACCOUNT_YEAR_WEEK,FINANCIAL_YEAR_WEEK, ... A: Please check your data in the table. If the column is of varchar2 type, there may be a difference of ... | |
| about tables | 6/27/2010 |
| Q: I am facing much difficulty and some times i cant understand one thing. That is How the GROUP BY ... A: If you want tables other than those provided by Oracle, then you can use some of the below ... | |
| date | 6/22/2010 |
| Q: CUSTOMER_ACCOUNT_ID DATE_OF_PAYMENT Sum(PAYMENT_AMT) 00002135 06/11/2007 100.02 00000353 ... A: Add WHERE clause before GROUP BY. If DATE_OF_PAYMENT column is of DATE datatype, use the below ... | |
| TO_DATE | 6/22/2010 |
| Q: Sorry, i use the code in the last querry as : SELECT CUSTOMER_ACCOUNT_ID, ... A: Write your query as below - SELECT CUSTOMER_ACCOUNT_ID, DATE_OF_PAYMENT, PAYMENT_TYPE, ... | |
| TO_DATE | 6/21/2010 |
| Q: I use : SELECT CUSTOMER_ACCOUNT_ID, DATE_OF_PAYMENT,SUM(PAYMENT_AMT) PAYMENT_TYPE FROM ... A: I think this may be a data issue. Please send me the table structure and sample data (maybe for 1 ... | |
| SELECT DISTINCT | 6/21/2010 |
| Q: I use : SELECT distinct PURCHASE_MECHANISM FROM CIS.PURCHASE_REQUEST_ITEM; and get error that ... A: If you are using Teradata, then the following link will give you all the information and remedies - ... | |
| login problem | 6/21/2010 |
| Q: this is shikhar I have a problem in login sql plus.I don't know the username and password. When i ... A: You will have to contact the appropriate person who has all the database connection details ... | |
| Create Table | 6/21/2010 |
| Q: Is it possible to create a duplicate table from an existing table with all its existing constraints? ... A: I assume that by duplicate you mean table with same structure and not the same name. With Oracle 9i ... | |
| login problem | 6/20/2010 |
| Q: this is shikhar I have a problem in login sql plus.I don't know the username and password. When i ... A: Please let me know which OS you are using to access sql*plus, also if there are any login scripts in ... | |
| About group by clause | 6/20/2010 |
| Q: I am facing much difficulty and some times i cant understand one thing. That is How the GROUP BY ... A: Group by clause can only be used if we are using any aggregate functions like min, max, sum, count ... | |
| Query | 6/18/2010 |
| Q: I want date And time functions information or some examples....which give me a knowledge about that ... A: You can find documentation (with examples) of all date functions at the following location - ... | |
| GROUP BY multiple variables | 6/17/2010 |
| Q: I use the code := SELECT sum(OUTSTANDING_BNPL_BALANCE_AMT), sum(OUTSTANDING_WWST_BALANCE_AMT), ... A: You will hust have to add a comma and customer account field name after the date field in group by ... | |
| pl/sql functions | 6/15/2010 |
| Q: Madam, I have a question related to pl/sql and I cannot wait to ask you another question ... A: There are many ways to write this function. Here is one of them - CREATE OR REPLACE FUNCTION fact ... | |
| DataBase Triggers | 6/15/2010 |
| Q: Madam, I am a student and this is my first interaction with you and allexperts.com, I have a ... A: Here is a trigger for your scenario - CREATE OR REPLACE TRIGGER emp_bi BEFORE INSERT ON emp FOR ... | |
| TO_DATE | 6/14/2010 |
| Q: Kindly guide in the code . I use the code as : SELECT CUSTOMER_ACCOUNT_ID, ... A: Please check your quotes in the to_date functions. The condition should be as follows - WHERE ... | |
| corelated subquery | 6/11/2010 |
| Q: what is the difference between query and acorrelated query A: Here are the definitions and examples - 1) Query - Normal SQL statement (insert/update/delete) for ... | |
| QUERY | 6/11/2010 |
| Q: I use the code : Select C.CUSTOMER_ACCOUNT_ID,C.APR,C.OPENING_BAL_AMT,C.CLOSING_BAL_AMT, ... A: You can use OUTER JOIN for this. The query depends on what data is in your table and what your exact ... | |
| SQL Date Format | 6/4/2010 |
| Q: I'm Stuck in a SQL querry getting error that a character string failed conversion to a numeric ... A: Please check what datatype is 'date' column and convert accordingly. If DATE datatype then try the ... | |
| Oracle in General...! | 5/23/2010 |
| Q: I need your help...! I am new to comuter field. though I hav some basic knowledge. I was planning ... A: Here are some answers to your questions - 1) OCI (Oracle Call Interface) provides various language ... | |
| Certification | 5/19/2010 |
| Q: I am a fresher engineer and really into database. I was wondering if you could tell me about ... A: If you are fresher, I would suggest you first get hands on experience in SQL, PL/SQL and try those ... | |
| Oracle Query Help | 5/5/2010 |
| Q: I have a table stored with dates .. i need to display the quarter comparing the date(mm/yy/dd). for ... A: You can use the following query to get the quarter (assuming table name tab1, column name ... | |
| Group by and Order by | 5/5/2010 |
| Q: please send me some group by and order by(complex) queries. Thanks in advance, Venkat Reddy A: You can find some complex queries with group by and order by, while using analytic functions or ... | |
| using global value oustide group | 5/4/2010 |
| Q: ..hru...In report builder...in one of my reports I have a query and a group....I have a summary ... A: I have not used Reports for a long time and since I do not have any setup, so cannot code and test ... | |
| sql | 4/30/2010 |
| Q: display the dname and loc having maximum numbers of employee working in the department from oracle ... A: Here are some ways that you may write the queries - 1) SELECT dname, loc FROM dept WHERE deptno ... | |
| HELP | 4/21/2010 |
| Q: I have this query to insert into a table a ref to a certain type. I am creating a facebook like ... A: But can you give me the exact steps you did while creating these objects and tables ? I want to know ... | |
| Creating Nested Tables | 4/21/2010 |
| Q: Compilation unit analysis terminated This is the error im getting when i declared a nested table ... A: The type you are creating person_objtyp has no columns, so while creating a table of that type ... | |
| HELP | 4/20/2010 |
| Q: I have this query to insert into a table a ref to a certain type. I am creating a facebook like ... A: Insert into clause should contain the table and column names only. No Select is allowed. So try the ... | |
| HELP | 4/19/2010 |
| Q: I have created an application inside apex with a post field and a post ID I use this query to insert ... A: Try using ; at the end of insert statement. If this does not work, please send be the table ... | |
| metadata in oracale | 4/18/2010 |
| Q: how to see the body of the procedure with using ORACALE METADATA. A: You can see the source of procedure, functions or packages in the table user_source (or all_source). ... | |
| About oracle certification | 4/17/2010 |
| Q: Respected suchitra madam, i have three years of acacdemic exp. but i want to do dba certification , ... A: You can do DBA certification. But along with that you will need actual DBA experience to get a job. ... | |
| Images | 4/16/2010 |
| Q: I have several images one for each user for my application. when they load i want it to display the ... A: I require more information on this - 1) Is you application in PL/SQL ? 2) Are you storing these ... | |
| Help | 4/15/2010 |
| Q: I have an object with which to hold two nested tables within an object of person. create or replace ... A: You can create a new type which includes all these types as follows - CREATE OR REPLACE TYPE ... | |
| Help | 4/14/2010 |
| Q: I have an object with which to hold two nested tables within an object of person. create or replace ... A: You can create the Person object type first (without the Friends and PendingFriends columns. Then ... | |
| Help | 4/14/2010 |
| Q: I have an object with which to hold two nested tables within an object of person. create or replace ... A: The types Friends_NTABTYP and PendingFriends_NTABTYP may be invalid. Both these types are created as ... | |
| Related to Career | 4/12/2010 |
| Q: . This is Jalandhar.Ch from India. I finished my MCA, Presently I'm in teaching field on DBMS with ... A: If you think of financial satisfaction, then better go for DBA or Oracle Applications. They are much ... | |
| purity level | 4/9/2010 |
| Q: I have a function in package. I am able to query using the fucntion in SQL withouot defining purity ... A: Prior to Oracle8i, it was necessary to assert the purity level of a packaged procedure or function ... | |
| Collections or dynamic sql ? | 4/5/2010 |
| Q: I will try to explain the requirement by taking the example of scott.emp table. Lets take the ... A: You will have to use Dynamic SQL for this. Modify your package as follows - CREATE OR REPLACE ... | |
| Collections or dynamic sql ? | 4/2/2010 |
| Q: I will try to explain the requirement by taking the example of scott.emp table. Lets take the ... A: I did not quite understand what you are trying to do here. Please clarify so that I can help you ... | |
| Cursors | 4/1/2010 |
| Q: I have a doubt regarding key constraints, 1.We can assign forignkey to column having primary key ... A: We cannot use cursors in queries. Generally a cursor itself is a query in pl/sql. We cannot use this ... | |
| Constraint | 3/29/2010 |
| Q: I have a doubt regarding key constraints, 1.We can assign forignkey to column having primary key ... A: According to my knowledge, foreign key (FK) constraints without supporting indexes can cause severe ... | |
| writing to a file | 3/28/2010 |
| Q: I need to write the my dynamically generated query to a .sql file in windows. Is it possible using ... A: Sorry for the late reply. You cannot spool any pl/sql output. You can set serveroutput on and issue ... | |
| Changing "SQL>" prompt | 3/26/2010 |
| Q: hw can we toggle SQL> to C:\> like in unix we can do by SQL>! is there any way we can do the same in ... A: If you are talking about the SQL*Plus prompt, then you can change it by following command - set ... | |
| oracle reports | 3/25/2010 |
| Q: how to force new page on every group. (in oracle reports) A: aari, I have not tried this. But you can check the following link and see whether your problem will ... | |
| Function Purity level | 3/23/2010 |
| Q: In one of document, I read a sentence as WNDS is mandatory for every function. Is this correct ? ... A: Prior to Oracle8i, it was necessary to assert the purity level of a packaged procedure or function ... | |
| SQL query | 3/22/2010 |
| Q: Please help answer the belowConsider following tables used in rental-car application Customer(cid, ... A: Answer is D. count (distinct make) - gives number of cars rented of a particular make (one per ... | |
| ora 12170 | 3/10/2010 |
| Q: Please help me with describe command for oracle 10g. I am getting error ora00900 for 'desc ... A: Check the following links and you might find your solution - ... | |
| how to find a field is primary key in oracle | 3/10/2010 |
| Q: In oracle, How to find a field is primary key in a table. Thanks A: Here is a query to find the primary key of a given table (EMP) - select ucc.table_name, ... | |
| convert | 3/9/2010 |
| Q: I wont to convert hex format to decimal by sql A: If you are using Oracle 8 and above, you can use to_number function as follows - select ... | |
| problem while comparing student _id in student details table with attendence details table using cursor. | 3/4/2010 |
| Q: firstly i want to say thanks for the answer given for my previous query. sir, i created 2 tables ... A: Try to write following procedure - PROCEDURE SP1 (p_fromdate date, p_todate date) IS -- ... | |
| SFTP From PL/SQL | 3/1/2010 |
| Q: How can we transfer file from one server to another using SFTP from PL/SQL. As per my understanding ... A: Sorry. I cannot help you with this one as I have not used it until now. I checked on google too, but ... | |
| Oracle career | 2/28/2010 |
| Q: Madam, I was working in web designing field and want to switch to oracle. I am totally new to this ... A: Yes. If you are new to Oracle, it will be better if you join an institute. Just get feedback from ... | |
| oracle dump | 2/26/2010 |
| Q: I need to import x dump into y user of same database,it is creating but it is using x table space ... A: Try the instructions given on the following link - http://www.orafaq.com/wiki/Import_Export_FAQ - ... | |
| Oracle career | 2/25/2010 |
| Q: Madam, I was working in web designing field and want to switch to oracle. I am totally new to this ... A: If programming is not suitable for you, you may chase the DBA path. It is in high demand and ... | |
| Tables required for attendence mgmt system. | 2/24/2010 |
| Q: I am a student in final sem. as a part of my accadamic project i had chosen student attendence ... A: The tables required totally depends on all your requirements for this project. The general structure ... | |
| DBA Materials | 2/22/2010 |
| Q: sir, am moin...! from India i had done recently DBA(oracle 11g) Course in Willshire Institute, ... A: Sorry. I do not know of any material other than the online documentation - ... | |
| SQL query | 2/15/2010 |
| Q: Please help in below query: Given a table emp(emp_id, emp_name, emp_salary, emp_location). What ... A: This is quite tricky. Answer might be B or D, because both mean the same thing. First all Delhi ... | |
| Database | 2/12/2010 |
| Q: How can i activate oracle9i database table... after installation table not appear.. when i write ... A: You can check if the script utlsampl.sql exists. If yes, run it. That will create all the emp, dept ... | |
| oracle | 2/3/2010 |
| Q: difference between functions and procedures A: Following are the major differences - Functions ---------- 1) Generally used for computation. 2) ... | |
| Need help once again | 1/31/2010 |
| Q: Im muzammil i want to ask a question that i want to play a happy birth day song in oracle when ... A: Since I am staying in US, I cannot guide you much regarding professional oracle training contacts ... | |
| Need help once again | 1/28/2010 |
| Q: Im muzammil i want to ask a question that i want to play a happy birth day song in oracle when ... A: Sorry. I cannot answer this question since I have no knowledge/experience in this area. I tried to ... | |
| selecting column2 value if column1 value is null | 1/27/2010 |
| Q: I have 2 columns in a table. column1 and column2. (column2 is not null). I need to select column1 ... A: You can use the NVL function to write the query as follows - select NVL(column1, column2) from ... | |
| schema creating and trigger | 1/27/2010 |
| Q: sir, I am computer engineering student ,completed oracle course from my university .I am trying to ... A: You can google for social network schema and find many sites which can help you. Following is one ... | |
| Tuning | 1/18/2010 |
| Q: Here, i am facing performance issue in below query. FM_LOG is a table name, which contains more ... A: Here are some tips - 1) Check the position of columns in index on FM_LOG table. If Timestamp is in ... | |
| Oracle PL/SQL Developer Jobs | 1/15/2010 |
| Q: I have a background in electrical engineering and have experience in the same field. Now I want to ... A: Congratulations for completing the major Oracle exams. If you want to look for a job, try searching ... | |
| Connect to Crystal Reports | 1/13/2010 |
| Q: We have created an Oracle application using Application Express. I am not familiar with pl/sql ... A: The best way to is use Oracle Reports. Oracle does support this and you can try the Oracle 10g ... | |
| Connect to Crystal Reports | 1/13/2010 |
| Q: We have created an Oracle application using Application Express. I am not familiar with pl/sql ... A: I am not familiar with Crystal Reports, But you can check the following link for your problem - ... | |
| About SQL | 1/13/2010 |
| Q: 1] Which of the following functions are available in SQL?(Choose Four) a. INSTR b. NVL2 c. TRUNCATE ... A: 1. a. TRUE b. TRUE c. FALSE - because Truncate is a command, Trunc is a function d. FALSE - because ... | |
| Exception handling in SQL Loader in Oracle. | 1/12/2010 |
| Q: Please let me know how do I handle exceptions in SQL loader in Oracle ? If there are any system ... A: We cannot handle exceptions in SQL Loader, as opposed to PL/SQL. If there are any errors in SQL ... | |
| query | 1/12/2010 |
| Q: which two statements would cause an implicit commit occur? A. Grant B. Update C. Commit D. Select ... A: Yes. A & F is right. Here is the reasoning (DDL and DCL commands do implicit commit) - A) True ... | |
| Comparing 2 tables col valuus | 1/10/2010 |
| Q: . I have 2 tables in oracle 10g . table A contains 2 Mobileno (number columns, name varchar2() and ... A: You can use the IN operator which selects data from one table only if it is present in the other ... | |
| questions regarding :PL/SQL, Procedures, Functions | 1/8/2010 |
| Q: a.)A procedure cannot return a value,why? b.)what exactly a procedure can return? b.)How internally ... A: Please see answers below - a) A procedure cannot return a value using Return Statement. If we give ... | |
| oracle dba | 1/4/2010 |
| Q: i'm sandeep completed my mca in 2008. now at present i'm learning oracle 11g. After completion i'll ... A: You have made a good decision. Oracle DBA is the best area where you can achieve success, get ... | |
| oracle | 1/1/2010 |
| Q: I have a form that contains records from a table. The records are displayed: field x ... A: You can do the delete as follows - 1) Add checkbox as a non base table item (eg. chk_delete). ... | |
| Non Query | 12/28/2009 |
| Q: What is a Non Query Record Group? pLEASE DESCRIBE ME IN dEATAIL A: A non-query record group is a group that does not have an associated query, but whose structure and ... | |
| export database | 12/28/2009 |
| Q: Please tell me the difference between Exporting the database and cold backup A: Here are the major differences - 1) Exporting database is a "logical" database backup. It is used ... | |
| Sorting | 12/24/2009 |
| Q: I have a table which contains scores for students. And what do i need is to get the rank for each ... A: You can write the query as follows - SELECT * FROM (SELECT name, score, rank() OVER ... | |
| sql loader control file | 12/24/2009 |
| Q: I have data feed as follows(have included line no.s for understanding ) 1 xxx 2 adf|123|y 3 ... A: You can write a PL/SQL procedure to do this. Following is the logic using UTL_FILE package 1) Open ... | |
| Tried but failed. | 12/24/2009 |
| Q: as you said I did it ,means I have asked for a code that will print the output directly from a ... A: Sorry for the delay. Create database directory as follows - create or replace directory dr as ... | |
| Tried but failed. | 12/23/2009 |
| Q: as you said I did it ,means I have asked for a code that will print the output directly from a ... A: The possible causes for this are - 1) The specified OS directory does not exist (the pathname ... | |
| Question | 12/23/2009 |
| Q: 1. how to pass the string as a paramter in stored procedure? A: Create a stored procedure with string parameter s_text as follows - create or replace procedure ... | |
| oracle | 12/22/2009 |
| Q: how to do auditing in triggers A: If you want to audit a table A, create a backup table with same structure and 2 additional columns - ... | |
| Creating Reports in Excel or in PDF file directly from Procedures. | 12/21/2009 |
| Q: I am giving a total picture of what I want. I am working on oracle database & I am responsible for ... A: Check if any database directories are present by using the following command - select * from ... | |
| clarification | 12/20/2009 |
| Q: now i am here with next question. My problem is that i am getting memory out of exception error. my ... A: Since this application is not written in PL/SQL, I don't think I can help you. But just checking on ... | |
| Creating Reports in Excel or in PDF file directly from Procedures. | 12/18/2009 |
| Q: I am giving a total picture of what I want. I am working on oracle database & I am responsible for ... A: You can do this in multiple ways - 1) Using UTL_FILE package (Assume that REPORTS is a ... | |
| Image Item | 12/17/2009 |
| Q: I have a Image Item on my forms 6i ....In the Image format property of the image file I have ... A: If you want to show the image in your image item, use the following built-in in an appropriate ... | |
| clarification | 12/17/2009 |
| Q: now i am here with next question. My problem is that i am getting memory out of exception error. my ... A: , I will need more information on this. 1) What is the application written in (PL/SQL / Forms / ... | |
| Excel to Oracle Upload | 12/16/2009 |
| Q: I have a Browse Button and Upload Button in a Forms.... Please help me with the coding of both the ... A: Try this - Browse button - Google the following text and go to the link that matches the one that I ... | |
| Excel to Oracle Upload | 12/16/2009 |
| Q: I have a Browse Button and Upload Button in a Forms.... Please help me with the coding of both the ... A: This is a question which I have no experience with. But I can give you some links which may guide ... | |
| barcode in report builder | 12/14/2009 |
| Q: In one of my report build in report builder.. I want a field retrieved from data base to be changed ... A: Sorry. I am not familiar with this. But you can check these sites - ... | |
| Canvas | 12/13/2009 |
| Q: First of all I would like to thank you for your continous support. I use to Google before for ... A: I think you will have to write a trigger as follows - Triggering event - WHEN-NEW-FORM-INSTANCE ... | |
| Objective question | 12/12/2009 |
| Q: If you have a enough time then please solve these question and return me because i am not able to ... A: Sorry for the late reply. While going through all the questions I found that these questions are ... | |
| TORCH button/Find button in oracle application custom form | 12/12/2009 |
| Q: how to show torch icon (find button) in oracle application custom form.Thanks in advance. Regards ... A: For creating icons, create a button, set iconic property to Yes and specify the icon filename for ... | |
| unix shell scripting | 12/8/2009 |
| Q: i have working experience in pl/sql on windows. but i wish to get the knowledge of pl/sql on ... A: You will need to know all the basic Unix commands. See the following sites - ... | |
| User Defined error message on database constraint | 12/8/2009 |
| Q: I hav a unique contraint on two column of a database table, column name are 1. Date 2. Shift Unique ... A: You can try this - Trigger - On-Error DECLARE errcode NUMBER := ERROR_CODE; dbmserrcode ... | |
| Describe command in oracle 10g | 12/8/2009 |
| Q: Please help me with describe command for oracle 10g. I am getting error ora00900 for 'desc ... A: One option is as follows: (1) Turn the auditing on: AUDIT_TRAIL = true in init.ora (2) Restart ... | |
| User Defined error message on database constraint | 12/7/2009 |
| Q: I hav a unique contraint on two column of a database table, column name are 1. Date 2. Shift Unique ... A: You can write an ON-CHECK-UNIQUE trigger on the block (block b1 with base table tb1) as follows - ... | |
| Please Help | 12/5/2009 |
| Q: Good evening, i was wondering if you could help me with this please.. I am using Oracle 10g express ... A: I have not used Oracle Express edition. You have to do the following steps, but I am not sure about ... | |
| can I fire a trigger only for once. | 12/3/2009 |
| Q: suppose I want to truncate / delete all the rows that a table already has & then insert new records. ... A: You can write the mutating error trigger as follows - create or replace trig_name before insert on ... | |
| can I fire a trigger only for once. | 12/1/2009 |
| Q: suppose I want to truncate / delete all the rows that a table already has & then insert new records. ... A: If insert into A and C is only once, you can write the trigger as follows - create or replace ... | |
| can I fire a trigger only for once. | 11/30/2009 |
| Q: suppose I want to truncate / delete all the rows that a table already has & then insert new records. ... A: You can write statement level trigger which fires only once for the particular statement ... | |
| Query to find procedures list in package body | 11/26/2009 |
| Q: How to find procedure list that available in package body?user_procedures gives list of procedures, ... A: The procedures or functions declared in package specs are public, hence they can be viewed using ... | |
| about report | 11/24/2009 |
| Q: i have problem with my report i want to draw fields in main frame under repeating frame but when i ... A: I will need more information on this - 1) Query /Queries 2) Report type 3) Report format 4) Example ... | |
| 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 ... | |
Oracle
Suchitra Joshi
Top Expert on this page
Expertise
I can answer questions regarding SQL, PL/SQL, Procedures, Functions, Triggers, SQL Loader, Oracle Forms, Oracle Reports, and some basic dba and performance tuning activities.
Experience
15+ years of Oracle PL/SQL Development
Education/Credentials
B.Sc (Electronics), Diploma in Computer Applications (DCA)
Awards and Honors
Oracle PL/SQL Developer Certified Associate (OCA)
Oracle Database: SQL Certified Expert
Brainbench certifications in Oracle Administration, PL/SQL, Developer 2000
©2012 About.com, a part of The New York Times Company. All rights reserved.