-
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
-
Changing Rows into Colums
How to change rows to columns and columns into rows?
Santhoshkandula
- May 11th, 2011
create table #temptable(rowid int,colorname varchar(25),Hexa varchar(7),R tinyint,G tinyint,B tinyint) GO insert into #temptable values(1,'Violet','#8B00FF',139,0,255); insert into #te...
-
Find Base Table of the View
Views are created on the basis of base tablesWhat is the query to know the base table of the view? How to know the details of base table?
Prem_p
- Apr 28th, 2011
Guys,?you don't need a dba privilege for this.Run the query:select * from USER_DEPENDENCIES where ?type='VIEW' and name=''This will give you the dependent object information (name , type of the obje...
Balakrishna12207
- Feb 7th, 2011
SELECT * FROM dba_views WHERE View_name=;Text column can contain "The details of view and table"
-
SQL NULLIF Function
What is the use of NULLIF function in SQL?
sampra
- Mar 6th, 2012
In SQL, NullIf function is used to compare two expressions. There are 3 possible results for NULLIF. 1. If the values are same, NullIf returns NULL. Eg: NULLIF(Prem,Prem) returns NULL 2. If they are...
Prem_p
- Apr 28th, 2011
In SQL, NullIf function is used to compare two expressions. There are 3 possible results for NULLIF.1. If the values are same, NullIf returns NULL. Eg: NULLIF('Prem','Prem') returns NU...
-
Matrix Query
Create a query to display the total number of employees, of that total, the number of employees hired in 1995, 1996, 1997, 1998. Create appropriate column heading from the employees table.
Prem_p
- Apr 28th, 2011
SELECT count(e.empno) Emp_Count,to_char(e.hiredate,'YYYY') hired_On from empoyee ewhere to_char(e.hiredate,'YYYY') in (1995, 1996, 1997, 1998)group by rollup(to_char(e.hiredate,'YYYY')) order by 2
mlodugu
- Apr 10th, 2011
How about this?SELECT SUM(sal) Total_sal, extract(year from hiredate) YrFROM scott.emp GROUP BY Rollup(extract(year from hiredate))
-
What are the advantages of views?
sampra
- Mar 6th, 2012
1) To protect some of the columns of a table from other users.ie for security purpose.
2)To hide the complexity of a query.
3)To hide the complexity of calculations.vivekrajece
- May 19th, 2011
1) To protect some of the columns of a table from other users.ie for security purpose.2)To hide the complexity of a query.3)To hide the complexity of calculations.
-
Reversing String in Oracle without using reverse function
How do I reverse a string using only Oracle SQL without using the reverse function??
What is use of a cursor variable? How it is defined?
A cursor variable is associated with different statements at run time, which can hold different values at run time. Static cursors can only be associated with one run time query. A cursor variable is reference type (like a pointer in C).Declaring a cursor variable:TYPE type_name IS REF CURSOR RETURN return_type type_name is the name of the reference type,return_type is a record type indicating the...
What should be the return type for a cursor variable.Can we use a scalar data type as return type?
The return type for a cursor must be a record type.It can be declared explicitly as a user-defined or %ROWTYPE can be used. eg TYPE t_studentsref IS REF CURSOR RETURN students%ROWTYPE
krishnaindia2007
- May 16th, 2008
No we can not use a scalar data type as return type.The return value of a strongly typed REF CURSOR must be a record which can be defined using the %ROWTYPE and %TYPE attributes or as a record structure.
Can cursor variables be stored in PL/SQL tables.If yes how. If not why?
No, a cursor variable points a row which cannot be stored in a two-dimensional PL/SQL table.
rampratap409
- Dec 12th, 2006
No you can not store the cursor variable into pl/sql tablebut yes the out put or cursor variable can be stored how:set serveroutput ondeclaretype v_table is table of emp%rowtype index by binary_intege...
What is syntax for dropping a procedure and a function .Are these operations possible?
Drop Procedure procedure_nameDrop Function function_name
Can you use a LONG datatype on multiple columns in one table.
Brajesh Singh Bhadoriya
- Apr 19th, 2007
No, a table can have only one column of LONG datatype. If you try to create a table with more than one LONG column it will throgh an error saying that a table can have only one LONG type of coumn.Regards,Brajesh.
What is Local Index and Global Index in Partition Table?What is Prefix Index and Nonprefix Index in table?
madhaviguthula
- Mar 9th, 2007
When you create a partitioned table,you should create an index on the table. The index may be partitioned according to the same range values that were used to partition the table.Local keyword in the ...
Why statistics are different in oracle9i and oracle 10g?
Not sure about this, better we could try again with right samplesize in both the versions. Use DBMS_STATS.AUTO_SAMPLE_SIZE.ExecuteDBMS_STATS.GATHER_SCHEMA_STATS('SCOTT',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,GRANULARITY=>'ALL',CASCADE=>TRUE);
Jian Zhang
- Oct 22nd, 2006
Deafult Optimizer9i: CHOOSE10g: CBO
skumarpenugonda
- Jul 7th, 2006
Hi,I just started reading 10g. What I came to know that there are many features added and w.r.t. statistics, below are the changes, or new features.1. AWR ( Automatics Work Load repository )...
Difference between Optimizer=ALL_ROWS and Optimizer=CHOOSE
Choose - Choose either Rule based or Cost based depend on theavailability of statistics. If statistics is available on the table it usesCBO and if not it uses RBO. ALL_ROWS - Choose based optimization, statistics is needed. U couldpass as hint in ur query.But in the majority of cases with optimizer_mode=choose ( and with a goodstatistics ) the CBO will be able to find a good...
Manoj Seemar
- Nov 2nd, 2007
The OPTIMIZER_MODE initialization parameter establishes the default behavior for choosing an optimization approach for the instance.CHOOSEThe optimizer chooses between a cost-based approach and a rule...
Ans