Plsql Interview Questions And Answers

Plsql interview questions and answers on advance and basic Plsql with example so this page for both freshers and experienced condidate. Fill the form below we will send the all interview questions on Plsql also add your Questions if any you have to ask and for apply in Plsql Tutorials and Training course just send a mail on info@pcds.co.in in detail about your self.


Top Plsql interview questions and answers for freshers and experienced

What is Plsql ?

Answer :

Questions : 1 :: Can you SELECT everything, but 1 or 2 fields, without writer's cramp?

No - you either get all fields (*) OR specify the fields you want.

Questions : 2 :: Can We use threading in PL/SQL?

 Submit it in a DBMS_JOB like so:declare  ln_dummy number;begin  DBMS_JOB.SUBMIT(ln_dummy, 'begin myProc(1,100); end;');  DBMS_JOB.SUBMIT(ln_dummy, 'begin myProc(101,200);...View answers

Questions : 3 :: Why cannot I use bind variables in DDL/SCL statements in dynamic SQL?


The statement that you cannot use bind variables in DDL/SCL commands is not the whole truth. Actually there are cases you cannot use bind variables in DML statements as well.ExamplesSure, bind...View answers

Questions : 4 :: How to return multiple rows from the stored procedure? (Oracle PL/SQL)

Here is how to build a function that returns a result set that can be queried as if it were a table:SQL> create type emp_obj is object (empno number, ename varchar2(10));  2  /Type...View answers

Questions : 5 :: How to raise User-Defined Exception With Custom SQLERRM ?

Yes. You just have to use the RAISE_APPLICATION_ERROR function. If you also want to name your exception, you'll need to use the EXCEPTION_INIT pragma in order to associate the error number to the...View answers

Questions : 6 :: How to convert comma separated string to array in PL/SQL?


Unfortunately, this one doesn't work with numbers:SQL> declare  2    l_input varchar2(4000) := '1,2,3';  3    l_count binary_integer; ...View answers

Questions : 7 :: How can I get the number of records affected by a stored procedure?

Register an out parameter for the stored procedure, and set the value based on @@ROWCOUNT if using SQL Server. Use SQL%ROWCOUNT if you are using Oracle.Mind that if you have multiple...View answers

Questions : 8 :: What is the difference between function and procedure in PL/SQL?

A procedure does not have a return value, whereas a function has.Example:CREATE OR REPLACE PROCEDURE my_proc   (p_name IN VARCHAR2 := 'John') as begin ... endCREATE OR REPLACE FUNCTION...View answers

Questions : 9 :: How to use BOOLEAN type in SELECT statement?


You can build a wrapper function like this:function get_something(name in varchar2,                  ...View answers

Questions : 10 :: How to create a menu in SQLPlus or PL/SQL?

Here is a SQL Plus script to do that:prompt Please make a selection:prompt 1: Do script aprompt 2: Do script bprompt 3: Do script caccept selection prompt "Enter option 1-3: "set term offcolumn...View answers

Questions : 11 :: What is the difference between varchar and varchar2?

As for now, they are synonyms.VARCHAR is reserved by Oracle to support distinction between NULL and empty string in future, as ANSI standard prescribes.VARCHAR2 does not distinguish between a NULL...View answers

Questions : 12 :: Does Oracle roll back the transaction on an error?

"User process" in this context is referring to the process running on the client machine that creates the connection to Oracle. In other words, if you are using Application A (SQL*Plus, TOAD, etc.)...View answers

Questions : 13 :: What is the difference between explicit and implicit cursors in Oracle?

An implicit cursor is one created "automatically" for you by Oracle when you execute a query. It is simpler to code, but suffers from    inefficiency (the ANSI standard specifies...View answers

Questions : 14 :: Can %NOTFOUND return null after a fetch?

I can find a situation where a fetch can fail:declare  i integer;  cursor c is    select 1 / 0 from dual;begin  open c;  begin    fetch...View answers

Questions : 15 :: Determine if Oracle date is on a weekend?

As of Oracle 11g, yes. The only viable region agnostic alternative that I've seen is as follows:SELECT *FROM mytableWHERE MOD(TO_CHAR(my_date, 'J'), 7) + 1 IN (6,...View answers

Questions : 16 :: Is there a PL/SQL pragma similar to DETERMINISTIC, but for the scope of one single SQL SELECT?

If you do this:select t.x, t.y, (select my_function(t.x) from dual)from tthen Oracle can use subquery caching to achieve reduced function calls.

Questions : 17 :: Can I copy :OLD and :NEW pseudo-records in/to an Oracle stored procedure?

The reasons it can't/doesn't work automatically include:    the :old and :new are default conventions; you can name the :old and :new references to be whatever you want through the...View answers

Questions : 18 :: MODIFY COLUMN in oracle - How to check if a column is nullable before setting to nullable?

You could do this in PL/SQL:declare  l_nullable varchar2(1);begin  select nullable into l_nullable  from user_tab_columns  where table_name = 'MYTABLE'  and  ...View answers

Questions : 19 :: How to return an array from Java to PL/SQL?

/* The type has to be SQL type so that it is also visible for Java. */create or replace type widgets_t is table of varchar2(32767);/create or replace and compile java source named "so19ja" asimport...View answers

Questions : 20 :: Difference between Table Function and Pipelined Function?

Pipelined functions, a very classic example is where you do a SELECT * FROM table name in SQL*Plus. What happens is, Oracle streams the data from the table..    Like watching a video...View answers

Questions : 21 :: DDL statements in PL/SQL?

I assume you're doing something like the following:declare   v_temp varchar2(20);begin   execute immediate 'create table temp(name varchar(20))';   execute immediate...View answers

Questions : 22 :: What steps server process has to take to execute an update statement?

 When a user issues a update statement the server process checks for weather the same type of command is executed recently or not if that command is executed very recently then it directly...View answers

Questions : 23 :: What is the Life of an SQL Statement?

Dear All,LIFE OF SQL STATEMENTStep 1: Oracle Create a CursorFor every SQL, first a cursor is created (cursor creation can occur implicitly, or explicitly by declaring a cursor). Step 2: Oracle Parse...View answers
More Question

Ask your interview questions on Plsql

Write Your comment or Questions if you want the answers on Plsql from Plsql Experts
Name* :
Email Id* :
Mob no* :
Question
Or
Comment* :
 

  






--------- Tutorials ---