GeekInterview.com
Series: Subject:

PL/SQL FAQ

Showing Questions 1 - 20 of 195 Questions
First | Prev | | Next | Last Page
Sort by: 
 | 

When we can declare a column as unique and not null both at the same time. What is the use PF primary key then?

Asked By: Gupta | Asked On: Aug 7th, 2006

Answered by: Vinod Tuteja on: 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 .

Answered by: z_ashwini on: 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...

How to reverse string without using string function?

Asked By: vinit0180 | Asked On: May 9th, 2013

Answered by: Madhava Reddy on: May 23rd, 2013

"sql begin declare @inputstring varchar(100),@var1 int,@reversestring varchar(100),@string2 varchar(100) select @inputstring = REVERSE select @var1 = LEN(@inputstring) WHILE (@var1...

Write only one SQL statement which produces the following output

Asked By: sashelp | Asked On: May 20th, 2013

Department_id emp_count dept_percentage

Flashback concept ? What is it?

Asked By: balanagi | Asked On: Apr 11th, 2013

Can any one help me on flashback concept ? What is it?

Answered by: raiyaans on: May 6th, 2013

From Oracle 9i Oracle introduced Flashback query feature. It is useful to recover the data which is accidentally deleted. Suppose a user accidentally deletes rows from any table and commits it, by usi...

Can we create a table by using a procedure or a function?

Asked By: sarada | Asked On: Jan 11th, 2007

Answered by: Sneha Shrivastava on: May 3rd, 2013

DDL is not possible inside procedure body

Answered by: prakash chandra patra on: Mar 28th, 2013

EXECUTE IMMEDIATE
*
ERROR at line 1:
ORA-00900: invalid SQL statement

How to get 1st date of a month ?

Asked By: dinaballavsahoo | Asked On: Apr 19th, 2012

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 ?

Answered by: Krishna on: Apr 3rd, 2013

Select Trunc(SYSDATE,MON) From Dual;

Answered by: shivigupta on: Oct 9th, 2012

select last_day(add_months(trunc(sysdate),-1)) + 1 from dual;

OR

select trunc(sysdate,MON) from dual;

Group by

Asked By: mikeslazak | Asked On: Mar 2nd, 2013

Create a query using “ group by” that shows maximum salary for each department?

If we call Oracle package inside another package

Asked By: santosh.rdg | Asked On: Nov 21st, 2012

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?

Answered by: Rohit on: 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;

Web testing

Asked By: Ramu.B | Asked On: Oct 24th, 2012

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...

Difference between a cursor and reference cursor

Asked By: Interview Candidate | Asked On: Sep 2nd, 2005

Answered by: PRADEEP on: Oct 16th, 2012

Ref cursor is having return type where as cursor does not have .
syntax of ref cursor:- type ref_type_name is ref cursor [return return_type].

syntax of cursor:- declare
cursor cursor name select statement..

Answered by: pradeep on: Jan 8th, 2012

normal cursors do have a return type. ex:

Code
  1. CREATE package pkg_test IS
  2. cursor c1 RETURN emp%rowtype;
  3. end pkg_test;
  4.  
  5. CREATE package body pkg_test IS
  6. cursor c1 RETURN emp%rowtype IS SELECT * FROM emp WHERE empno = 10;
  7. begin
  8. FOR emp_cv IN c1 loop
  9. ..
  10. end pkg_test;
  11.  

Oracle mass update

Asked By: galactus911 | Asked On: Jul 31st, 2008

How do we do mass updates in Oracle pl SQL?

Answered by: shivigupta on: Oct 9th, 2012

Code
  1. declare
  2. type id_table IS TABLE of testemp.emp_id%type;
  3.  v_id_table id_table;
  4.  
  5.  begin
  6.  UPDATE testemp SET subject = d1 WHERE emp_id = 1
  7.  returning emp_id bulk collect INTO v_id_table;
  8.  
  9.  commit;
  10.  dbms_output.put_line(count IS:==||v_id_table.count);
  11.  end;

Answered by: GeekQA123 on: Aug 15th, 2012

Use Bulk Update stmt.

What is the main purpose of return in functions?

Asked By: sudnic | Asked On: Apr 10th, 2012

What is the main purpose of return in functions when we can calculate a value and pass it through using out mode in procedure or functions?

Answered by: shivigupta on: Oct 9th, 2012

create or replace procedure proc1 as begin dbms_output.put_line(Before Return Clause); return; dbms_output.put_line(After Return Clause); end; set serveroutput on; begin proc1; dbms_output.put_line(I...

Answered by: shivigupta on: Oct 4th, 2012

The RETURN statement immediately ends the execution of a subprogram and returns control to the caller. Execution continues with the statement following the subprogram call. (Do not confuse the RETURN ...

What is cursor? Explain the types of cursor and write one implicit cursor program to check how many records deleted from table?

Asked By: indranil | Asked On: Feb 7th, 2006

Answered by: shivigupta on: Oct 4th, 2012

set serveroutput on;
begin
delete from employ where empid = 1;
commit;
dbms_output.put_line(NO of records deleted||sql%rowcount);
end;

Answered by: Vidya Khamkar on: Sep 26th, 2012

Cursor - A cursor is like a virtual table, with row and columns specified by the query. A cursor also has a nation of current row, which is essentially a pointer to a row in the virtual table.
Types of cursors-
1) Static
2) Dynamic
3) Forwardonly
4) Keyset-driven.

What is the difference between procedure and function and package, which is the fastest

Asked By: Suyog | Asked On: Apr 28th, 2006

Answered by: Jay on: Sep 9th, 2012

I do not agree with this, Even a function can perform an action.

Answered by: Kiran Shelar on: Dec 29th, 2011

Hi Everybody, I dont see much update on why package is more faster than function or procedure. Of corse package is must faster than procedure or function The reason being whenvever package is called f...

Query nested table data

Asked By: sandip01 | Asked On: May 25th, 2010

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

Answered by: Pooja on: 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...

What is mutating trigger? How do you resolve it?If a trigger body is trying to update dependent tables, will it get a mutating error?

Asked By: be17be | Asked On: Dec 1st, 2006

Answered by: ASHOK on: Aug 31st, 2012

MUTATING : MUTATING TRIGGER IS TRIGGER WHICH IS USED TO PERFORM DML OPERATIONS WE CAN RESOLVE BY REGARDS ASHOK

Code
  1. CREATE OR REPLACE TRIGGER TRG_UPDATE_SAL
  2. BEFORE UPDATE ON EMP
  3. FOR EACH ROW
  4. IF :NEW.SAL<:OLD.SAL THEN
  5. UPDATE EMP SET COMM =2000;
  6. ELSIF :NEW.SAL<:OLD.SAL THEN
  7. UPDATE EMP SET COMM=1000;

Answered by: sabrishandyou on: Jan 26th, 2010

There are cases when we have mutating table error inspite of using an "AFTER TRIGGER at ROW LEVEL".  This happens when you are using an after delete trigger.  For instance a delete...

SQL statements syntax

Asked By: SAUMYA SRIVASTAVA | Asked On: Oct 23rd, 2011

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

Answered by: ram on: Aug 8th, 2012

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

Oracle pragma's

Asked By: Nishant.Patel | Asked On: Jul 27th, 2008

Outline the list of pragma's used in Oracle.

Answered by: amrapali on: Apr 21st, 2012

PRAGMA The instruction is a statement that provides some instructions to the compiler. Pragmas are defined in the declarative section in PL/SQL. The following pragmas are available: AUTONOMOUS_TRA...

Answered by: bobbyiiit on: Apr 8th, 2010

PRAGMA

Pragma is a keyword in Oracle PL/SQL that is used to provide an instruction to the compile.

Types Of Pragmas :-

1 - AUTONOMOUS_TRANSACTION

2 - EXCEPTION_INIT

3 - RESTRICT_REFERENCES

4 - SERIALLY_REUSABLE

What is pragma autonomous transaction? Plz can u let me know it in a simple understandable way.Thanks in advance.Byee

Asked By: raj | Asked On: Oct 17th, 2006

Answered by: pragadeesh on: Apr 19th, 2012

Very simple meaning is...
1) Pragama means force (like it says to compiler forcible do this operation)

2) It is used for committing the particular block.

3) We can't rollback this Pragama autonomous block

Answered by: lijina on: Nov 15th, 2011

before that statement that is pragma auto_tra only will save ...

What is difference in defining constraints at table level and column level what is main purpose of making constraints at table level....Pls ans me

Asked By: basukolur014 | Asked On: Nov 5th, 2006

Answered by: Manjesh on: Apr 17th, 2012

yes, it is possible, but u have to use triggers not constraints

Answered by: Dhruva Joshi on: Apr 4th, 2012

To define a constraint at a table level simply reduces the typing part of the
Program...logically
But. The thing is when defining constraint at a column level the rules of it just stay for the particular column but when we want it for one or many column table level
Is to be given

First | Prev | | Next | Last Page

 

 

Ads

Connect

twitter fb Linkedin GPlus RSS

Ads

Interview Question

 Ask Interview Question?

 

Latest Questions

Ads

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.