-
Writing Pl/SQL function
Write a PL/SQL fuction named say TRUN, which does the same work as the oracle predefined function "TRUNCATE".
ajaz_11i
- Jul 9th, 2008
It will truncate/delete all the rows in the table emp very fast as compared of delete command, but the table must have no Foriegn keyMr. Ajaz Ahmad Kumar
sgbang
- Jul 7th, 2008
Hi,Please check out this function:create or replace function trun(p_table_name varchar2) return number asbeginexecute immediate ' truncate table '|| p_table_name;return 0 ;end; /How to ex...
-
Is the order in which the conditions are given in the 'WHERE' clause are important?
Mean I heard from Oracle 8i onwards the order of where clause condition does not matter which oracle creates the explain plan...and execute..Is it true?
manoj_behera82
- Aug 7th, 2008
Yes the order is important. But it is important for the performance of the query?and not for the final result. The output of the query will be the same irrespective of the order in which the join con...
gtomar
- Aug 7th, 2008
Hi, Where Clause conditions execution are independent of order in explain plan but it reads top to bottom.Thanks,Gunjan
-
How to take DATE as user input in PL/SQL block?
raghuprasad
- Aug 25th, 2008
Inputting the date in PL/SQL is same as in SQL. Simply pass the date as a string in the format DD-MON-YYYY.Eg: SELECT * FROM TABLE WHERE HIRE_DATE='dd-mon-yyyy'.OrYou can also use Date functions TO_DATE('25-aug-2008','......date formate......');Hope this is the answer, you are looking for.Raghu.
rajivgupta780184
- Aug 22nd, 2008
Create or replace function get_period (pDate varchar2) return varchar2is pday varchar(20);begin SELECT TO_CHAR(to_date('&DATE','MM/DD/YYYY'),'DAY') into pday FROM DUAL;return pday;end get_period;to call the function select get_period(08/03/2008) from dual;
-
Oracle PRAGMA'S
Outline the List of PRAGMA'S used in Oracle.
amrapali
- 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...
bobbyiiit
- Apr 9th, 2010
PRAGMAPragma is a keyword in Oracle PL/SQL that is used to provide an instruction to the compile.Types Of Pragmas :-1 - AUTONOMOUS_TRANSACTION2 - EXCEPTION_INIT3 - RESTRICT_REFERENCES4 - SERIALLY_REUSABLE
-
Autonomous Transcation
What are advantages of Autonomous Transcation?
raghunadhn
- Mar 3rd, 2010
Autonomous transactions are independent of the main transactions.You can commit the changes in the other tables using these transactions, even though the main transaction is not a success. Depending on one transaction, you can commit/rollback the other transactions.
namita04
- Dec 23rd, 2009
A autonomous transaction starts with the first SQL statement of the PL/SQL block and ends with a commit.
-
Handle Multiple Rows Without Loops
How can we handle multiple rows without using loops in PL SQL?
sen_sam86
- Aug 18th, 2009
This can be done by a function called TABLE FUNCTION by means of this you can return more number of rows and columns this is more effective than using ref cursorregards sen-prakash
rajivgupta780184
- Oct 31st, 2008
If we want to return multiple row from a function then we can use ref cursor function .Example is given below: create or replace function f1 return sys_refcursor as r1 sys_refcursor;begin open r1 fo...
-
Infinite Loop
How will you stop an infinite loop without closing the program?
muraligrandhi
- Jun 7th, 2010
For terminating the INFINITE LOOP we can use EXIT statement.Example: LOOP monthly_sales:=daily_sales*31; EXIT when monthly_sales > 3000; END LOOP;
joe0082
- Apr 13th, 2009
Use the?EXIT statementThis will stop the looping and proceed to the next executable statement.or EXIT WHEN condition
-
Recreating and Rebuilding Indexes
What is recreating and rebuiding of indexes?
raghunadhn
- Mar 3rd, 2010
Rebuilding IndexSyntax: Alter index index_name rebuild.If you rebuild the index, the existing index will be dropped and created immediately, and any overheads existing on the index wil be removed. The...
-
SQL Optimization
How to optimize SQL Statements?
Sudhir Kumar
- Jan 20th, 2016
Try to make use of mqts or temporary tables if possible ..try to transfer the conditions from non key columns to key columns
ace1234567
- Mar 4th, 2015
update the statistics of all the tables used in the query. Check the explain plan. Check whether query is using the existing indexes. If required add/modify new indexes. Check for the sequence of cond...
-
Types of Lock
Explain share and exclusive type of lock.
hmounir
- Jan 25th, 2010
Oracle uses two modes of locking in a multi-user database:Exclusive lock mode (X) prevents the associates resource from being shared. This lock mode is obtained to modify data. The first transaction t...
rahulmca
- Jan 24th, 2010
The two basic types of locks are DML (table) locks, and DDL (dictionary) locks. DML locks are designed to guarantee data integrity in a multi-user environment. DML locks prevent conflicting DML and DD...
-
Execute Immediately
What is the meaning of execute immediately?
hmounir
- Jan 25th, 2010
EXECUTE IMMEDIATE is the replacement for DBMS_SQL package from Oracle 8i onwards. It parses and immediately executes a dynamic SQL statement or a PL/SQL block created on the fly. Dynamically created a...
Brajesh Singh Bhadoriya
- Jan 25th, 2010
Execute immediate is used to prepare dynamic SQL. There might be scenario where one has to prepare the SQL statement at run time in a PL/SQL object. Execute immediate requires one SQL statement which ...
-
Rectify Deadlock
If a deadlock has occured and if you have given with the dump, how will you identify that where the deadlock has occured, how the deadlock occured and how to rectify the same.
kari
- Oct 16th, 2011
the steps necessary to identify and rectify code causing deadlocks are: * Locate the error messages in the alert log. * Locate the relevant trace file(s). * Identify the SQL statements i...
skumar.t15
- Aug 5th, 2010
Each user session might have one or more tasks running on its behalf where each task might acquire or wait to acquire a variety of resources. The following types of resources can cause blocking that c...
-
Unconstrained Loop
What is unconstrained loop?suggest some example.
vikz.pal
- Jan 23rd, 2011
Unconstrained loop means loop that can't be handled as per our will.Basic loop is a simple example of unconstrained loop - if we ignore proper exit statement in basic loop then it becomes an infin...
-
-
Autonomous transactions
What is an autonomous transaction?
-
If we call oracle package inside another package
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?
-
Can we use function inside a trigger
-
PL/SQL table vs Global Temporary Table
Difference between PL/SQL table and Global Temporary Table. why we use Global Temporary Table without using PL/SQL Table.
nivi
- Sep 3rd, 2013
1.Global Temporary table is stored in Temporary tablespace while PL/SQL table is stored in PGA. 2.SQL operations can not be performed on pl/sql tables whereas it can be performed on GTT as any normal...
plsqlgeek
- Aug 16th, 2013
PL/SQL table or associated array is a collection (data type). Where as GTT is a temp table created to store/process data for a particular session. Once you log out of the session the data is gone, not the table structure.
-
Reference cursor from a procedure & pass cursor as a paramater
How to call/reference a cursor defined in a procedure A from another procedure B?
How to pass a cursor as a parameter of a cursor? -
Bind variabales
What are bind variables in sql? explain in detail?
pradipta
- Feb 8th, 2014
The way to get Oracle to reuse the execution plans for "the statements having different hard coded value in where clause" there should use bind variables. Bind variables are ?substitution? variable...
Aarti
- Jan 15th, 2014
Bind variable is session specific variable. It is declared using variable keyword. Bind variable cant be used in named block. It cant have default value.
PL/SQL Interview Questions
Ans