-
Can we call user defined packages in SQL statements?
FALAK
- Apr 25th, 2006
A user defined package can be invoked in sql only if we r using it to call public functions or variables.but packaged procedures can not be called from sql
Mainak Aich
- Mar 17th, 2006
Hi,Everyone forgot to point out 1 thing,which is most essential to use a user defined package which is he/she must have the execute permission to use the package if he/she is not the creator of that package.If it is ok definitely an user defined package can be used.
-
What is the main difference between the IN and EXISTS clause in subqueries??
The main difference between the IN and EXISTS predicate in subquery is the way in which the query gets executed.IN -- The inner query is executed first and the list of values obtained as its result is used by the outer query.The inner query is executed for only once.EXISTS -- The first row from the outer query is selected ,then the inner query is executed and , the outer query output uses this result...
gemini burj
- Oct 23rd, 2007
In case of a big number of list from the select list IN operator should not be used but the exists shall be used with the proper condition this will definetely make the query optimal query.
Mahesh
- Jul 24th, 2007
In operaor scan all the values inside the IN block where as EXIST quit after 1st occurrence.
-
while this query dint work :
"select * from (select rownum,a.* from emp a) where rownum=70;"
2. as i've read that rownum can work only for < and <= but not for = or > or >=, then why the query worked for rnum=70">1. Why is it so that the given query worked :"select * from (select rownum rnum,a.* from emp a) where rnum=70; "while this query dint work :"select * from (select rownum,a.* from emp a) where rownum=70;"2. as i've read that rownum can work only for < and or >=, then why the query worked for rnum=70
krishnaindia2007
- Jun 21st, 2008
ROWNUM returns a number indicating the order in which the row was retrieved from the table, but this is not always the order in which a row is displayed. If you directly user rownum in the outer query it considers the rownum of outer query not that of inner query.
Lavanya Chowdary
- May 6th, 2007
It will work for = operator But it will work for the first row in the table pls check that
-
How to tune the following sql in order that the performance improve the query is SELECT DISTINCTA.BUSINESS_UNIT,A.CUST_ID,A.ASOF_DTFROM PS_ITEM_ACTIVITY A WHERE A.BUSINESS_UNIT = '1100G'AND A.ITEM LIKE 'OA%' AND A.PO_LINE = 0AND A.ENTRY_REASON 'CLEAR'AND A.ASOF_DT > '01-JAN-1900'AND A.USER1 = ' 'UNIONSELECT DISTINCTA.BUSINESS_UNIT,A.CUST_ID,A.ASOF_DTFROM PS_PENDING_ITEM A WHERE A.BUSINESS_UNIT = '1100G'AND A.ITEM LIKE 'OA%' AND A.PO_LINE = 0AND A.ENTRY_REASON 'CLEAR'AND A.ASOF_DT > '01-JAN-1900'AND A.USER1 = ' 'AND A.POSTED_FLAG = 'N'AND ERROR_FLAG = ' '
raji_4u
- Sep 19th, 2008
Remove union and place union allMake sure the where conditions is in order of1. =2. >3. 4. like.
Make sure that the condition that returns least records comes first.zskhan
- Oct 12th, 2006
Remove DISTINCT from both queries.Since UNION is being used, we are going to get distinct rows anyway and queries with distinct are quite expensive.
-
Employee Query question
Suppose im having employee table with fields, eno, ename, dept, address1, address2, address3. In address field employee can fill only address1 or address2 or address3... at a time he can fill three address fields. now i want all employee names who filled only one address field..
Difference between Store Procedure and Trigger?
jpundalik
- Dec 30th, 2009
Below are the few difference between Stored Procedure and TriggersBoth are stored objects and stored in data base dictionariesStored Procedure- Can pass parameters- Manual execution requiredTriggers:- No parameters can be passed - Automatical executed when the event occurs
chennoju
- Aug 3rd, 2006
Stored procedure and Trigger both will be in the database in precompiled formate which ready execute, the main difference between these two isto execute the stored procedure programe need to type...
There is a Eno & gender in a table. Eno has primary key and gender has a check constraints for the values 'M' and 'F'. While inserting the data into the table M was misspelled as F and F as M. What is the update statement to replace F with M and M with F?
CREATE TABLE temp(eno NUMBER CONSTRAINTS pk_eno PRIMARY KEY,gender CHAR(1) CHECK (gender IN( 'M','F')));INSERT INTO temp VALUES ('01','M');INSERT INTO temp VALUES ('02','M');INSERT INTO temp VALUES ('03','F');INSERT INTO temp VALUES ('04','M');INSERT INTO temp VALUES ('05','M');INSERT INTO temp VALUES ('06','F');INSERT INTO temp VALUES ('07','M');INSERT INTO temp VALUES ('08','F');COMMIT;UPDATE temp...
Elena
- Nov 8th, 2007
Update set gender=(case gender when 'F' then 'M' when 'M' then 'F' end)
Difference between a equijoin and a union?
srinu.oracle99
- Sep 1st, 2010
Equi Join is cartisan product of two tables and result is matching values of these tow tables but
both have common column.Union is just append two tables and restricts the duplicate rows and both tables have same number of columns.sunil_ronanki
- Nov 11th, 2008
Eqiui join is totally differene from unions Equijoin depends on a condition involving columns of both tables but where as in Union you can specify a condition based on a columns of the individual tables
How to retrieve record number 70 from a table of 100 records?
krishna
- Sep 26th, 2006
select * from emp where rownum<71minusselect * from emp where rownum<70;
How do you subtract two columns in oracle database
How do you subtract two columns in oracle database, two columns are in data format.Example : inserttime column senttime column?
pravesh28
- Nov 28th, 2010
select to_date(insertime,dd/mm/yy)-to_date(sentime,dd/mm/yy) as date_differnce from table_name;
rkh_301
- Aug 12th, 2010
If the fields are defined as TIMESTAMP type, you could do something like below. In this query, the startdttm and enddttm fields are defined as TIMESTAMP type:select phasename, stepname, enddttm-startdttm as Elapsed from psupgstatuswhere runname = 'dlm_v4d_b018';
Delete distinct name from table
How could i delete the distinct name from the table. the rest record would be available as it is in the table?
pravesh28
- Nov 28th, 2010
delete from table_name where column_name in (select distinct(column_name) from table_name);
preethi
- Jan 9th, 2007
To remove distinct rows from a table use thisdelete from table_name where column_name in (select distinct(column_name) from table_name);If u want unique values select distinct (column_name) from table_name ;
What is SQL injection? What are the uses of it?
Lakshaman
- Jan 24th, 2007
An exampleWe accept user and password and validate likeselect 1 from users where username = 'username' and password = 'pwd';What if username string is passed like [x' or 1=1; --]select 1 from users where username = 'x' or 1=1; --' and password = 'pwd';
sabithagopi
- Jan 23rd, 2007
SQL Injection happens when a developer accepts user input that is directly placed into a SQL Statementand doesn't properly filter out dangerous characters. This can allow an attacker to not only stealdata from your database, but also modify and delete it.
How to write a query to unsort while retrieving the data. it should be niether in ascending nor descending order
malapati
- Oct 10th, 2008
By default the "select" statement retreives the data in "unsort" form. If we want to sort the data we need to use "ORDER BY" clause. eg: select * from < table name > order by < column name >;?if we specify "desc" after column name it sort out in desending order otherwise default is asending order
Lavu
- May 6th, 2007
If you are selecting the data from the table it will not sort the data. If you are using group by clause then it will sort the result in the ascending order other wise not.
Why the Stored functions cannot called from DML statements?
Ayush.sh
- Mar 31st, 2009
To add in above mentioned constraints for using functions in DML statements:- Called function should not end a transaction
raji_4u
- Sep 19th, 2008
Who says we cannot?We can call stored functions from DML statements but follow some rules
The rules:----------1. Function should use only IN parameter mode. Don't use OUT or IN OUT mode.2. Functions used in Select statements should not update database tables, where as others can update database.Loading Huge data files to Oracle
What is the process that we use to load the data faster when there are millions of files?
kirandba
- Apr 1st, 2010
Use SSIS. But with in SSIS use SQL Server Destination adaptor instead of OLEDB Destination. But when we use SQL Server Destination adapter we can only run either on server or from SQL agent job.
Ayush.sh
- Mar 31st, 2009
For loading data files or flat files into DB, Unix utility SQL Loader is best suited. can load milions of records in just few secs. Easy to use and compatible with Unix, shell, PERL. Uses control file(.ctl) and parameter file(.par)to direct instructions.
How can we delete a check constraint in a table
Ans