Write a query to retrieve one entire column data of multiple rows into one single column of single row?

hema nagaraju

• Dec 20th, 2014

SELECT listagg (ename, ,) within group( order by ename) FROM emp

Geet

• Dec 4th, 2014

Code
span style="color: #F00;">"EMPLOYEE NAME"

Answer Question  Select Best Answer

Eltonclardy

 Profile Answers by Eltonclardy Questions by Eltonclardy

• Jan 9th, 2015

Yes PL/SQL have different data types compared to C and C++. These two datatypes in SQL i.e scalar and composite are very mush useful in SQL.

Hema nagaraju

• Dec 20th, 2014

Scallar,composite datatypes.
scllar datatypes are varchar2,number
composite datatypes are records,tables,collections which can store multiple values at a time

Answer Question  Select Best Answer

suvijyo

• Feb 2nd, 2015

"c declare s1 char(10):=name; s2 varchar2(10):=name; begin if s1=s2 then dbms_output.put_line(both are equal); else dbms_output.put_line(both are equal); end if; dbms_ou...

chinnu

• Jan 27th, 2015

Char datatype is fixed,but it cant be reused. eq: char(10) -- elena,first 5 characters will be filled remaining field are padded.
varchar2 datatype fixed,but it can be reused. eq: varchar2(10) -- elena, first 5 characters will be filled and remaining fields are reused.

Answer Question  Select Best Answer

Abhijit Musale

• Feb 18th, 2015

To make the operations faster. When we need to do any operation on data like some calculations and when u need to fetch the data repetitively , in that case we can use the collections like PLSQL table.

Answer Question  Select Best Answer

Guys can you please help me in realtime scenario?

Abhijit Musale

• Feb 18th, 2015

Whenever you want to perform some operation on a set of result of rows , then we use the cursor.Cursor is nothing but a work space allocated on the memory. When u fire a query that set of rows is stored in the cursor temporarily.

Answer Question  Select Best Answer

Can we give COMMIT or ROLLBACK within a trigger?

Abhijit Musale

• Feb 19th, 2015

Using PRAGMA AUTONOMOUS_TRANSACTION we can treat the child transaction as a separate transaction, committing which wont affect the parent transaction.

Tushar

• Feb 17th, 2015

No we cant used it.because it affected on logical processing. It is possible using Pragma

Answer Question  Select Best Answer

What are indexes used for ? What are the different types of Indexes?

lakshmi

• Feb 20th, 2015

Index is schema object.which is an entry for each value that are appear index column.index used to give a direct access and also increase the performance .

types of indexes:

simple,composite,function based,bitmap,bit treeindex

Abhijit Musale

• Feb 18th, 2015

It is not necessary that adding an index will always help. When u have less no. of records then there is no need to add index. An index is an additional database object to improve the data i/o operations.

Answer Question  Select Best Answer

How to access cursor output from web application and present the record set in tabular form?

ace1234567

 Profile Answers by ace1234567 Questions by ace1234567

• Mar 4th, 2015

Using ref cursor and returning output in the plsql table we can return the result in tabular format.

Answer Question  Select Best Answer

ace1234567

 Profile Answers by ace1234567 Questions by ace1234567

• Mar 4th, 2015

Please find the template. Using out type parameter we can return more than one value from a function.

Code
 

shyam

• Mar 2nd, 2015

Yes, we return more than one value in function using package. Example::

Code
create package pack
is
type mul_val is record
(emp_id employees.employee_id%type,
emp_name employees.last_name%type);
end pack;

and the return type of function is that pack type.....

Answer Question  Select Best Answer

Explain the real-time situation where would you use Oracle Hits

Abhijit Musale

• Mar 23rd, 2015

To override the flow of execution and priority of the execution we can use oracle optimizer hints.(e.g. PARALLEL,APPEND,etc.)

kalpana

• Oct 28th, 2014

Optimizer hints are used with SQL statements to alter execution plans.We are suggesting oracle how the statement should be executed.
syntax : /*+RULE */

Answer Question  Select Best Answer

What is the difference in CREATE or [REPLACE] Procedure and to drop a procedure and create it again? What does REPLACE do, Does it drop the existing one and create the new one with same name?

Abhijit Musale

• Mar 23rd, 2015

PLSQL objects and views can be replaced if pre-exists using REPLACE keyword.

souvik

• Nov 16th, 2014

If you drop the procedure, then all the privileges are gone which you will have to give again while creating the program. But CREATE OR [REPLACE] command will keep the privileges intact.

Answer Question  Select Best Answer

Abhijit Musale

• Mar 23rd, 2015

We can create a table in procedure by executing a string statement using EXECUTE IMMEDIATE or using DBMS_SQL.EXECUTE.

veera

• Nov 25th, 2013

It is possible by using Dynamic SQL.
By using EXECUTE IMMEDIATE.

Answer Question  Select Best Answer

A table has both before insert and after insert trigger. Also we have declared both the trigger as pragma autonomous transaction for saving the error record alone in the error table if any exception occurs. Now if we have a error in the trigger will the before insert trigger will get executed and the error data will be stored in the error table?. what is the exact use of before and after insert trigger...

DInesh Shukla

• Apr 16th, 2015

As we declared pragma here. It will insert the record in error table if got fail during execution.

sukrathik

 Profile Answers by sukrathik Questions by sukrathik

• Mar 6th, 2015

If an autonomous transaction attempts to access a resource held by the main transaction, a deadlock can occur. Oracle raises an exception in the autonomous transaction, which is rolled back if the exc...

Answer Question  Select Best Answer

We have a oracle package that package is calling in user defined package ?from this user defined package how will u debug the oracle package?

ChandraSekhar thimmapthruni

• Apr 16th, 2015

Use DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

Rohit

• Jan 6th, 2013

Using trace_on utility

eg:- For HRMS hr_utility.trace_on ;
expression(here call the package which you want to debug);
HRMS hr_utility.trace_off;

Answer Question  Select Best Answer

What is the impact of using a stored proc or a function on the performance? Which has a better performance? Also, why is it not possible to use DML in the functions called from a select query?

Nupur

• Apr 24th, 2015

Functions and procedures are both named blocks of statement . A function must return a value . Performance wise if you want the program to return a?single value use a ?function . You can use out par...

Suchith Shivali

• Apr 17th, 2015

1)In stored procedure you may or may not return value but in the function return is compulsory. 2)If you want to return more than one value go for Stored procedure. 3)Once SP or function is created,...

Answer Question  Select Best Answer

During preparation of report in *.RDF , there are two things on date & to date

on date is the i/p value , but to date = 1st day of this month on date

how ?

pradeep

• Apr 24th, 2015

SELECT TRUNC(SYSDATE,MM) FROM DUAL

Praveen

• Apr 22nd, 2015

SELECT ADD_MONTHS(LAST_DAY(SYSDATE),-1)+1 FROM DUAL;

Answer Question  Select Best Answer

 %  ROWTYPE  is to be used whenever query returns a entire row of a table or view. TYPE  rec  RECORD is to be used whenever query returns columns of differenttable or views and variables.      E.g.  TYPE  r_emp is RECORD (eno emp.empno% type,ename emp ename %type);     e_rec emp% ROWTYPE     cursor...

Nupur

• Apr 30th, 2015

This is my understanding: Declare : eno employee.emp_id%type - specifically tells you that the variable type or data that it will be holding will be same as th e employee_id type in the employee tabl...

Nupur

• Apr 24th, 2015

When we declare a var as %row type, then the variable holds all the column type attributes. Developer does not need to know what the individual column types are. Type record is used when we declare a variable to be of the records of the table.?

Answer Question  Select Best Answer

Is it possible to define a RETURN statement in the exception part of a function?
Explain it with example.

Aryan

• May 26th, 2015

Yes it is possible to use Return in exception block of pl/sql. Example: "oracle8 CREATE OR REPLACE FUNCTION [(input/output variable declarations)] RETURN return_type [AUTHID ...

Kamal

• May 23rd, 2015

Yes, you can return from exception block. example: create or replace function TestReturn return varchar2 is Result varchar2(1);--size 1 begin Result := 123;--size 3 return Test; except...

Answer Question  Select Best Answer

igornem

• Jun 6th, 2015

There is no term "Atomic transaction". There is term "Transaction" which is must have ACID properties, and first "A" in "ACID" is - "Atomicity", which is defines that series of operations included in ...

madhaviguthula

 Profile Answers by madhaviguthula Questions by madhaviguthula

• Feb 28th, 2007

Autonomous transactions are independent transactions that can be called from within another transaction. An autonomous transaction lets you leave the context of the calling transaction, perform some S...

Answer Question  Select Best Answer

Read Best Answer

Editorial / Best Answer

Answered by: answertoyourquery

• Nov 9th, 2005

---

Yes, we can use the TCL commands(commit/rollback) in the exception block of a stored procedure/function. The code in this part of the program gets executed like those in the body without any restriction. You can include any business functionality whenever a condition in main block(body of a proc/func) fails and requires a follow-thru process to terminate the execution gracefully!

Leena Roja

• Jul 13th, 2015

Correct Subhash..

Susil Kumar Nagarajan

• Jan 21st, 2012

Yes. You can very well use COMMIT or ROLLBACK in exception block. But it is not a good idea to use, as it will commit the previous transactions in the same PLSQL block. Make use of creating SAVEPOINTS and commiting them when required.

Improve Answer