kiran_marla

 Profile Answers by kiran_marla Questions by kiran_marla

• Mar 10th, 2010

There is no difference between function and package, except function must and should return a value while procedure do not. But it is not a good practice to use out mode in functions.However a default...

vishadk

 Profile Answers by vishadk Questions by vishadk

• Mar 7th, 2010

I believe you are asking about the use of 'out' in the function declaration.One can use 'Out' mode in the functions in the manner simlar to procedure i.e.Create or Replace function fun_out_example(par...

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

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

What is Force View. Why Oracle Give Facility to create force view? What is the use of force View

sonam kanungo

• Oct 15th, 2011

As per my knowledge when view is created without base table.then the view is called as forced view.

in this type of view is created with compilation error.

mohan

• Oct 14th, 2011

If we create a view without having base table, that view called as forced view. it will create with compile error. it will be in invalid till base table create.

Answer Question  Select Best Answer

Abdul

• Oct 1st, 2016

We can use DBMS_job.isubmit procedure to schedule the job automatically..

Pran

• Jul 12th, 2016

I am trying this, but it says that procedure name is already exist.

Answer Question  Select Best Answer

No answer

samareshp

 Profile Answers by samareshp Questions by samareshp

• Feb 13th, 2010

While creating a package, we can use Show error Command to get the line number thats has error. And can be corrected. Even we can check in the data-dictonary view i.e. user_errors to figure out the er...

gem023

 Profile Answers by gem023 Questions by gem023

• Feb 11th, 2010

On SQL Plus you can give the command - show errors.   This would show the line number and reason for the compilation error.

Answer Question  Select Best Answer

Ashish

• Jun 12th, 2014

You can use MERGE statements in Oracle to avoid looping through Conventional cursors. It will save lot of time

KSPRADEEP

• Oct 17th, 2012

Yes we can ..
Instead of cursor we using bulk collection or bulk binding or using %type and %rowtype

Answer Question  Select Best Answer

PL/SQL  is  a  procedural  language  that  has  both  interactive  SQL  and procedural  programming  language constructs such as iteration, conditional branching.

surya72446

 Profile Answers by surya72446 Questions by surya72446

• Sep 30th, 2009

PL/SQL stands for procedural languages extensions to SQL and it's theme is to hold a bunch of SQL statements as a single function and return a single output.PL/SQL is advantageous compared to other pr...

g_sidhu

 Profile Answers by g_sidhu Questions by g_sidhu

• Feb 16th, 2008

PL/SQL is the procedural extension to SQL with design features of programming languages. Data manipulation and query statements of SQL are included within procedural units of code. PL/SQL offers moder...

Answer Question  Select Best Answer

 Cursor is a named private SQL area from where information can be accessed. Cursors  are  required  to  process rows individually for queries returning multiple rows.

Rounak037

• Jan 22nd, 2020

Cursor is used when the user needed to update record in a single or in row buy row manner in a database table, Cursor is required to process rows individually for queries returning multiple rows.
Basically cursor are two types:
1. Implicit Cursor.
2. Explicit Cursor.

rahulg5211

 Profile Answers by rahulg5211 Questions by rahulg5211

• Oct 4th, 2009

Cursor is an small memory allocation or workstation where operation or processing is done on the data.Types of attribute of cursor are-:1)ROWCOUNT2)ISOPEN3)FOUND4)NOTFOUND

Answer Question  Select Best Answer

Suppose Input table is COL1 COL2 COL 3A 1 -3B -2 6c 3 -4D 9 10In sql query I want the desired output as follows COL1 COL nA 1B 6C 3D 10Please write the query to get this output

fire_starter

 Profile Answers by fire_starter Questions by fire_starter

• Sep 15th, 2011

Hello, For my query example I have used the table test_tbl, and the columns col, col1, col2. It also does consider NULL value in comparison, so it will show value on comparing with NULL, and for ev...

BhushanNag

 Profile Answers by BhushanNag Questions by BhushanNag

• Aug 24th, 2011

SELECT COL1, GREATEST(COL2, COL3) FROM max_value_column

Regards
Bhushan

Answer Question  Select Best Answer

Divisha

• Dec 30th, 2019

Can You please elaborate in what way can we do it ?

psingla

 Profile Answers by psingla Questions by psingla

• May 6th, 2010

In 10g we can

Answer Question  Select Best Answer

NehaChanda

 Profile Answers by NehaChanda Questions by NehaChanda

• May 22nd, 2009

We can not use MAX on PLSQL table. Check if it works.Create a PLSQL Table Sorted_Mark.SNO_MARK(3) := 0; - Replace ASorted in another Collection Typeselect culumn_valuebulk collect into Sorted_MARKfrom table (cast(SNO_MARK as PLSQL_DATATYPE))order by column_valueSorted_MARK(1) - First Value

samareshp

 Profile Answers by samareshp Questions by samareshp

• Apr 15th, 2009

I guess the the question should be replace a with zero then sort the mark and
then show first mark, Let me know if i am wrong.

select max(mark)
from (
SELECT sno, mark
FROM (select sno, decode(mark, 'A', 0, mark) x from t) xt
ORDER BY x
)

Answer Question  Select Best Answer

chiatanya

• Nov 5th, 2012

Implicit cursors are automatically generated,when a sql statement is processed where as

Explicit cursors are user defined.

VASU

• Oct 20th, 2012

Implicit cursors returns only single record..where as explicit cursor multiple records. But it process record by record.

Answer Question  Select Best Answer

Vinod Tuteja

• Jun 14th, 2013

Main Difference : Referential
Index is created in both Cases (Unique and Primary key Constraint )

But can't references Columns with combination of UNIQUE and NOT NULL. Like in Primary key ,we Can references primary key to make foreign key constraint .

z_ashwini

 Profile Answers by z_ashwini Questions by z_ashwini

• Jan 13th, 2012

Eg: Consider table emp with EMP_ID,EM|NAME,ADD columns You can define the column e_name as Unique constraint. DECLARE e_name number(10) NOT NULL; BEGIN ... .. END: Primary key cannot have NU...

Answer Question  Select Best Answer

binurajnair

 Profile Answers by binurajnair Questions by binurajnair

• Aug 2nd, 2008

We can overload functions/procedures inside packages.

jagadeesh9

 Profile Answers by jagadeesh9 Questions by jagadeesh9

• Feb 24th, 2008

Package: It is an collection of related variables, cursors,procedures and functions stored at one location. They supports OOPS features like function over loading, encapsulation.ADV:   ...

Answer Question  Select Best Answer

nade_rs

 Profile Answers by nade_rs Questions by nade_rs

• Jun 25th, 2011

We can also  make use of compound trigger (new feature in  11 g to avoid mutating error)

Baji Shaik

 Profile Answers by Baji Shaik Questions by Baji Shaik

• Jun 5th, 2011

The? row level trigger based on a table,the trigger body can't read thedata? from same table, if you are trying to this it gives mutating errorto avoid that you must use statement level trigger

Answer Question  Select Best Answer

Hi,My questions is " Can function return more than one value".I have gone through the all answers relevant to my question was been posted earlier. But all answers are confusing me.Few people said directly that function can't return more than one value and few are telling that function can return more that one value using OUT parameter or ref cursor.My understanding is if out can't use as a parameter...

sen_sam86

 Profile Answers by sen_sam86 Questions by sen_sam86

• Jul 23rd, 2009

Yes this can be possible by TABLE FUNCTION STEP - 1    CREATE TYPE ex_tabl_fun_obj AS OBJECT (emp_name varchar2(20), emp_id NUMBER(2));STEP - 2     CREATE TYPE ex_tabl_fu...

javedans

 Profile Answers by javedans Questions by javedans

• Jul 8th, 2009

Its a universal truth that function can return only one value, but there are some tricks by using that you can get more than one value , while function returning only one value.When you define functio...

Answer Question  Select Best Answer

What are the uses of Cursor?
What are its advantage and disadvantage?

Habib Ali

• May 10th, 2016

Advantage: Gets multiple-data inside the buffer cache, for faster DML operations

Disadvantage: Consumes memory, has to be freed properly to avoid Buffer cache congestion

WebSpider

• Feb 11th, 2016

Performance is reduced using cursor

Answer Question  Select Best Answer

What is the best technique of debugging large and fragmented dynamic SQL.

kumardba

 Profile Answers by kumardba Questions by kumardba

• Oct 7th, 2010

You can try to debug Dynamic SQL using SQL Advisor Tool or TKPROF tool

shivaranjani.p

 Profile Answers by shivaranjani.p Questions by shivaranjani.p

• Oct 6th, 2010

Hi,You need to store each and every line of the dynamic sql in a file using UTL_FILE Package before you could execute it, so that you can refer the same file after executing the code, this here serves...

Answer Question  Select Best Answer

What are the advantages of packages over standalone procedure ?

Habib Ali

• May 9th, 2016

Packages can be cached inside the SGA, resulting in faster execution than a normal stored procedure.

abhijit musale

• Feb 18th, 2015

You can wrap/encrypt the package code .

Answer Question  Select Best Answer