How to query nested table column of a table without using table function?

Pooja

• Sep 3rd, 2012

There are two general ways to query a table that contains a collection type as a column or attribute. Nest the collections in the result rows that contain them. Distribute or unnest collections so t...

Answer Question  Select Best Answer

What are the advantage of Synonym in PL/SQL

mukesh1009

 Profile Answers by mukesh1009 Questions by mukesh1009

• Jul 9th, 2010

Dear Kanchan Maheshwari There are two categories of synonyms, public and private. A public synonym can be accessed by any system user. The individual creating a public synonym does not own the sy...

Answer Question  Select Best Answer

What is the use of a package without body?

Gaurav_Vashishtha

 Profile Answers by Gaurav_Vashishtha Questions by Gaurav_Vashishtha

• Jul 27th, 2010

Package without body is used to declare global cursors and constants which can be used by other function, procedures or packages

Answer Question  Select Best Answer

How does index work? How to check whether Index is pickup?

z_ashwini

 Profile Answers by z_ashwini Questions by z_ashwini

• Jan 13th, 2012

Indexes are created for better performance of the DB ; for faster retrival of data from DB. There are diffierent types of indexes... (1) B tree index (2) Bitmap map index (3) Function based index (4) ...

Answer Question  Select Best Answer

Suppose a programmer creates a user defined exception handler Invalid_Number in declare block with type EXCEPTION and raise this exception in begin block. Then how will you access the user defined exception and same system defined exception (i.e Inavlid_Number)

promisinganuj

 Profile Answers by promisinganuj Questions by promisinganuj

• Feb 10th, 2011

The system defined exceptions in Oracle are tied to Oracle error codes. For this case, system defined error INVALID_NUMBER is tied with error code -01722.Now, when a user defines the sa...

Answer Question  Select Best Answer

How to call an external-procedure from a package?

manju_kambar

 Profile Answers by manju_kambar Questions by manju_kambar

• Sep 13th, 2014

Calling procedure from other package should have following format

package_name.procedure_name;

if you are calling package in other schema, you need to create synonyms for that particular

Answer Question  Select Best Answer

mailtonagaraja

 Profile Answers by mailtonagaraja Questions by mailtonagaraja

• Mar 24th, 2011

Hi ,   1) In Package we can combile all related objects into one component, whenever you refer or call any object in the package , full package will be copied to the memory , so that next ti...

Answer Question  Select Best Answer

bhanu priya

• Jul 4th, 2014

1. function result cache : stores the retured value in a cache and can b used again n again instead of storing it in memory.
2. continue statement : passes controle from one iteration to other rather than exiting it.
3.regexp.count is introduced.
4.dbms_parallel_execute is introduced.

Answer Question  Select Best Answer

DEPARTMENT_ID EMP_COUNT DEPT_PERCENTAGE

lviswanath

 Profile Answers by lviswanath Questions by lviswanath

• Jul 23rd, 2013

Ask the question clearly

what is DEPT_PERCENTAGE

on what basis we have to calculate DEPT_PERCENTAGE?

Answer Question  Select Best Answer

If there are 2 overloaded procedure, one among then have 1 IN parameter and another have 2 parameters. Both procedures parameters are of default type.

What will happen when you will call package.procedure without any parameter?

Khamar

• Jul 22nd, 2013

You will get an error message "PLS-0037: too many declarations of procedure_name match this call"

Answer Question  Select Best Answer

pavankumar_greek

 Profile Answers by pavankumar_greek Questions by pavankumar_greek

• Sep 15th, 2011

Using packages we can run concurrently two programs .
place the programs in two files and create a package which is having the code can open those two files. next we have to register this package to apps.

Answer Question  Select Best Answer

How to display all rows and all the columns of employee table?

ram

• Aug 8th, 2012

Select *from emp;
here * retriving all records from the db

Answer Question  Select Best Answer

hi
I have to write a query to fetch 5 columns from the data. columns are title,description,msgs_count,alerts and severity.
msgs_count is count(title) over (partition by ) and alerts is sum(title partition by) over.

in case the duplicates of title are present in data. I want the title to come only once in the output but the msgs_count and alerts must not be affected. is it...

raghavender

• Oct 7th, 2012

Analytic functions are used to fetch all rows with the required aggregate data, if duplicates are needed, then you can use distinct or you can use group by clause instead of analytic functions which will have better performance.

Answer Question  Select Best Answer

In what ways can data be copied from one table to another?

Neeraj Saxena

• Apr 12th, 2013

You can copy the data from one table to another with the help of below Query:

Suppose dummy is the main table and we need to copy the data from dummy_120413 table.

Create tabel dummy_120413 as Select * from dummy;

Answer Question  Select Best Answer

What is a global temporary table?

Aijaz

• Oct 14th, 2012

Global temporary tables are the permanently created tables for a timed session.
Only the data in the table is temporary but the table is permanent in the Oracle database.

Answer Question  Select Best Answer

What are the types of collection in PL/SQL? What is the advantage of nested tables?

pawan

• Nov 6th, 2012

Varrays,nested tables,index by tables are oracle collection.Index by tables also known as associative array similar to hashtables in java. Varrays are like arrays in java static in nature need to d...

Answer Question  Select Best Answer

Can a type body be dropped without dropping the type specification?

ramya

• Oct 31st, 2012

When you drop a type body, the object type specification still exists, and you can re-create the type body. Prior to re-creating the body, you can still use the object type, although you cannot call the member functions.

Answer Question  Select Best Answer

How can we find out the dependencies on a table?

srikanth goduru

• Oct 10th, 2012

Check the table dba_dependencies and see what other tables are affected.

Answer Question  Select Best Answer

With what ways can we find out instance locks?

Jose George

• Oct 22nd, 2012

V$lock contains details of locks

Answer Question  Select Best Answer

While testing a website, an image that is supposed to appear on a page does not load (for example in IE, you would see an empty box with a small red cross icon). What type of investigation could you carry out to find out what the cause of the problem is? What information would you include when raising the defect

harmeet rehal

• Oct 13th, 2014

Web testing is the name given to software testing that focuses on web applications. Complete testing of a web-based system before going live can help address issues before the system is revealed to th...

Answer Question  Select Best Answer