-
Paging In Oracle
Hi All,I have requirement of sending the records based on the input received from web.I have a ref_cursor returning 30 rows..Here I am supposed to do paging on the set of records present in the above ref cursor.If web gives 1 , then I should send first 10 records in the ref cursor..If gives 2 , then I should send next 10 records in the ref cursor..similarly�..I will get some sort of indicator from...
muraligrandhi
- Jun 9th, 2010
With the help of Ref cursor you can get particular no of records(means 1=10 records,2=20 records like that), for that you can use rownum in the filter condition.If you add below query in Ref cursor yo...
pvidhya
- Sep 3rd, 2008
First you have to filter the no of records you need totally (i.e., if you receive input as 2 and if the no of records you need to return are 20, then you need to filter 20 rows) using where rownum
-
Purpose of Ref cursor and OUT parametr in PLSQL Function?
What is the purpose of Ref Cursor and OUT Parameter in PLSQL Function? Give Examples for each?
muraligrandhi
- Jun 9th, 2010
Main advantage of Ref cursor is you can get the output as record set. For example if you are using Java or .Net or cristal reports for reporting purpose then you can use Ref cursors and write a subpro...
pratap557
- Oct 13th, 2009
By Ref cursor we can send record set to the calling programLike .NET VB page can get recordset as datatable from ref cursor returned by a procedure.OUT parameter sends only one value from procedure
-
How to find error line of a package at run time
How can the error line can be identified for a package at run time ?
muraligrandhi
- Jun 9th, 2010
There are two ways for finding error line in a package
1. SHOW ERROR
2. DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
First method is useful for all the versions but second one is 10G or above versions only.tripathi.atul
- Sep 9th, 2008
Starting from Oracle 10G we can use DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function to get the line from where the error originated.
-
DeCode Performance
Performance wise which is a better option if-else construct or a decode? Why
muraligrandhi
- Jun 7th, 2010
DECODE is faster than IF-THEN-ELSEREASON:Decode is an Oracle built-in function, so no need to pass parameters to this function. Because all the parameters are internally defined but If-then-else statements we should define explicitly, so takes time for execution and converting into P-code.
sanjeevlabh
- Jan 22nd, 2010
The answer to this question would actually depend on the type of applicability of the usage that is being done. i.e. is it required in a query or in a PLSQL block. If it is in a query we cannot use if...
-
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
-
How to change owner of a table?
chaitunitk
- Jun 3rd, 2010
Owner of a table is the schema name which holds the table. To change the owner just recreate the table in the new schema Using create table XXX AS select * from oldschemaname.XXXand drop the previous table in oldschema
Das
- Dec 21st, 2006
Owner of a table is the schema name which holds the table. To change the owner just recreate the table in the new schema and drop the previous table
-
Find null value in a table
What are all the different ways available to find the null value in a table
-
What are the characteristics of autonomous transactions?
ajeeteng
- May 5th, 2010
Autonomous transactions refer to the ability of PL/SQL temporarily suspend the current transaction and begin another transaction. The second transaction is known as an autonomous transaction, an...
uday
- Jun 29th, 2007
Autonomous transaction defined and executed?independent of calling program. u need to define PRAGMA AUTONOMOUS_TRANSACTION at declare section. stored procedure, function can be defined as autonomous transaction.
-
What is the use of PL/SQL procedure DBMS_TTS.TRANSPORT_SET_CHECK?
ajeeteng
- May 5th, 2010
The DBMS_TTS package checks if the transportable set is self-contained. TRANSPORT_SET_CHECK Procedure This procedure checks if a set of tablespaces (to be transported) is self-contained. After calling this procedure, the user may select from a view to see a list of violations, if there are any.
-
What are Hash, Nested loop and merge-sorted Join.
psingla
- Apr 27th, 2010
There are three types of alogrithm used by SQL compiler for joining purpose:1) Nested join algo2) Hash join algo3) Merge join algoComplier choose any one of these algo based on the best suited for per...
norman
- May 8th, 2007
In hash join hash table gets build using the inputs. Nested loops as the name implies uses looping construct strategy for storing and retrieving. In terms of performance comparison while retrieving da...
-
Help me to write a procedure for:verify a table for record if it is exists need to update or else insert that record( using cursor write a procedure )
ss
- Sep 25th, 2007
Insert the row you want you as if it doesn't exist,?in the exception? write the update statementBegin//Inser the rowExceptionwhen< row already exists>//update the rowEnd
-
When using a count(distinct) is it better to use a self-join or temp table to find redundant data, and provide an example?
manish.baehal
- Apr 5th, 2009
COUNT(Distinct) is better than using Self Join to find redundant data as it is faster and shorter. Beside this COUNT(Distinct) provide more flexibility.
-
How PL SQL is different from T-SQL
psingla
- Apr 28th, 2010
PL/SQL is procedural language.It is built upon sql with extra added functionalities.It is used by ORACLE.robust and consistentexception handling is great the concept of package is greatdisallow bad co...
Yaseen
- May 12th, 2007
SQL (Structures Query Language)PL/SQL (Procedure Language / SQL) is the Oracle's version with lots of additional proprietary functions.T-SQL : Transact-SQL is Microsoft's proprietary version of SQL.
-
Call SOAP Web Service from PL/SQL
How a SOAP web service can be called from PL/SQL block?How can we handle the result set returned by the SOAP service?
-
What is the maximum number of data string can be returned from DBMS_OUTPUT? andHow do you call a trigger from a table?
psingla
- Apr 27th, 2010
2) Triggers cannot be called rather they are fired automatically based upon their specification.
MuralidharanR
- Mar 17th, 2007
The maximum buffer size of DBMS_OUTPUT IS 1,000,000. A database trigger is procedural code that is automatically executed in response to certain events on a particular table in a database. Trigger can not be executed manually
-
Querying Customer Details from a Table
How to query the customer details from a Table who have purchased No. of items greater than 5?
psingla
- Apr 27th, 2010
I guess the table structure would be liketable_name (custname,item_purchased)so query will be:select custname from table_name group by custname having count(*)>5
rajivgupta780184
- Aug 12th, 2008
Select column 1,...column n from table namewhere purchaseno 5 order by purchaseno;try this one .....ThanksRAJIV GUPTA YOGIK TECHNOLOGIES PVT LTD.JAMSHEDPUR
-
Load White Space Delimited Data
How to load whitespace delimited data using SQL*Loader?
psingla
- Apr 21st, 2010
1. Create a ontrol file with extn (.ctrl). This file will map the flat file data with the Oracle table columns.2. Pass control file as one of the parameter of SQL Loader.
-
Call SQL Script
How to call SQL script file from PL/SQL?
dj_dj_dj
- Mar 18th, 2010
You can Call SQL Script by using @ and then giving path of the source fileFor ex: @c:/sqlscript.txt;RegardsDharmendra Jaiswal
prasadreddi
- Mar 17th, 2010
@<path>filename.sqlgive the path where the file is located extactly.Ex :- Let us say our file is located at c:tempabc.sqlSQL> @"c:tempabc.sql" It is better practice to use double qu...
-
Plz clarryfy this.Scinario is whether we can use out mode in functions?.I thought it's not possible. But in interview they are asking this.if i am telling no,they are not accepting me.Plz clarify this anyone.
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
- 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...
-
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.
PL/SQL Interview Questions
Ans