-
What is select statement to spell out SALARY?(Query should work upto 5 million)
krishnaindia2007
- Jun 22nd, 2008
SELECT TO_CHAR(TO_DATE(11111,'J'),'JSP')||' DOLLARS' FROM DUALThis will work for the values in between 1 and 5373484 only.
-
Can a function take OUT parameters. If not why?
krishnaindia2007
- Jun 22nd, 2008
A cusor can tabke out parameter. But never use OUT and IN OUT parameters in functions. The main purpose of function is to take zero or more parameters and return a single value.
BASAVARAJ KOLUR
- Mar 9th, 2007
function can take out parameter and in out parameter in that case it returns values to calling program with return value also accroding to me its not good programing style to u...
-
What is the difference between %type and %rowtype?
krishnaindia2007
- Jun 22nd, 2008
%tye and %rowtype provide data independence and allows programs to adapt to database changes due to new business requirement.%type is used to declare a variable with same datatype as that of...
BASVARAJ KOLUR
- Mar 9th, 2007
the %type will return datatyp attached to refernced column or itemex: v_a emp.empid%type;it will return datatype attched to empid of emp table. the %rowtype will return all datatype attached ever...
-
How can i spellout the number. but the number like $24 and output will be twenty four doller
Kranthi Reddy
- Oct 6th, 2006
Please find the sample below.select 24||'$', decode (substr('24$',1,1),1,'One',2,'Two',3,'Three',4,'Four','Others')|| decode (su...
-
May i know about ref cursor?.
krishnaindia2007
- Jun 22nd, 2008
Ref cursor is basically a data type. A variable declared based on that data type is called cursor variable. The main advantage of using this variable is we can dynamically assosiate with different queries at runtime depending upon our requirement.
tariq_oracle
- Jun 8th, 2008
1. This is a dynamic cursor which can holds multiple value at run time2. Actually this is nothing but a data type of cursor variable
-
I have two difference database (Not Table) in SQL. and Now I want to connect/Transfer daily basis data from one database table to another database table. Please tell me How to connect and tranfer data using Visual Basic.
Arun James
- Nov 8th, 2006
You can use a single query to do it ...Use the ordinary INSERT INTO Table2 SELECT col1, col2 .. FROM Table1But only consideration should be done is the two different DataBases .. Suppose Say our appl...
-
How to write the CSV command in SQL?
krishnaindia2007
- Jun 22nd, 2008
Spool the out put to text file as follows.SET HEADING OFF;SET FEEDBACK OFF;SPOOL EXAMPLE.TXTSELECT EMPNO || ','||ENAME||','||SAL FROM EMP;SPOOL OFF;SET FEEDBACK ON;SET HEADING ON;
Prashant Amburle
- Nov 4th, 2006
Try below wiil resolve the problemspool c:a.txtselect tname||','||tabtype from tab;spool offopen a.txt
-
Hi1. State the order in which the trigger will get fired2.I have an doubt in trigger. I created two trigger. trigger name is only different but structure is same. I want to know the order in which these two trigger will get fired
krishnaindia2007
- Jun 22nd, 2008
1.Firing sequence of Database triggers on multiple rows Before statement trigger Before row trigger for first row After row trigger for first row Befo...
Prashanth
- Apr 24th, 2007
The one that is created last executes first
-
CBO & RBO
What is CBO and RBO? What is diff between these two?
krishnaindia2007
- Jun 21st, 2008
The job of optimizer is to decide the most efficient method to execute a query. There are two types of optimizers in oracle RBO or Rule Based Optimizer since v6 CBO or Cost Based Optimizer sinc...
-
View sysdate
How can we view sysdate for n number of times
krishnaindia2007
- Jun 21st, 2008
Select sysdate from user_tables where rownum <=50; Here you can use any table name that contains more than 50 rows.
-
Constraint and Index
What is difference between constraint and index? How do use them in SQL query?
krishnaindia2007
- Jun 21st, 2008
Constraint is used to restrict invalid and inconsistent data entry into the table.Index is used for faster retrieval of data.Both are entirely different concepts.
srinivasa.dinesh
- Jan 29th, 2008
Constraints are rules applied to Table/Columns of a table. Constraints are one type of restriction which are useful while data is manipulated.Index are ordered list of rows of tables. Index are useful while faster retrival of data.Hope this anwsers to your question.Cheers,Srini.
-
Copying table with constraints and data
How to copy a table with constraints and data to another table.
krishnaindia2007
- Jun 21st, 2008
Using the following query you may copy table structure and data but you can't copy constraints.Create table emp_copy as select * from emp;
ugly1
- Jan 25th, 2008
below shown query will copy table with constraints and data into new table. here table_copy is the new table and old_table is the table whose data and constraints are to be copied.create table table_copy as (select * from old_table)
-
Table and Column Level Locks
How to handle the table level locks and column level locks?
krishnaindia2007
- Jun 20th, 2008
When you are performing INSERT , DELETE , UPDATE operations on a table it implicity aquires row level locks. If a transaction obtains a row lock for a row, the transaction also aquires a table l...
-
Sub query and correlated sub query
What is the difference between sub query and correlated sub query
krishnaindia2007
- Jun 20th, 2008
In sub query the inner query is executed only once. Depeding upon the results of inner query , outer query is evaluated.In correlated subquery the inner query is evaluated once for each row processed by the parent statement or outer query.
tariq_oracle
- Apr 13th, 2008
subquery: required in those cases where requisted data are not known and always depend on inner qu...
-
Alias in Joins
Why do we use aliases during joining two tables?
krishnaindia2007
- Jun 20th, 2008
Alias name it temporary name given to a tablen or columns of a table.It has its existence until the satement is executed. For example in the following example instead of typing tablenames ...
-
Find all tables in a database
How to find all the tables existing in a database?
krishnaindia2007
- Jun 20th, 2008
To view tables in the current schemaSELECT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE'To view all the tables that you have access SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OBJECT...
Imran_Javed
- May 8th, 2008
SELECT TABLE_NAME FROM All_TABLESOrder by TABLE_NAME;The above query will display all the table names in the database.SELECT TABLE_NAME FROM USER_TABLESOrder by TABLE_NAME;The above query will display all the table names in a particular schema.
-
Lock Row of a Table using SQL
How to lock a particular row of a table using SQL?
krishnaindia2007
- Jun 20th, 2008
INSERT , UPDATE, DELETE operations implicity lock rows of a table that satisfies the condition.Explicitly you may lock a table using for update of as followsSELECT * FROM EMPWHERE EMPNO = 7369FOR UPDATE OF SAL NOWAITIt will lock single row of emp table having employee number 7369.
-
Can we use aggregate function in another aggregate functionsuppose we got count of a column by using the group by clause so we get different counts in the same statement how to get the maximum of count
gkbiswal
- Sep 29th, 2007
We can use aggregate function inside another aggregate function.Example:SQL> select max(Count(*)) from emp group by deptno;MAX(COUNT(*))------------- 6DB Version: Oracle9i Enterprise Edition Release 9.0.1.1.1
-
Security for SQL Queries?
Can you provide any security for SQL queries? If Yes, How?
-
How to insert of only system time(hh:mi:ss) into table columnnot in the format of 'dd/mm/yyy hh:mi:ss' only system time not date.
Waseem Mehmood
- Nov 20th, 2007
CREATE TABLE table_name(col1 VARCHAR2(20));INSERT INTO table_nameVALUES(TO_CHAR(SYSDATE, 'HH:MI:SS'))While creating table you must keep in mind the datatype must be Varchar2 for required column
Oracle SQL Interview Questions
Ans