-
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 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.
-
Find null value in a table
What are all the different ways available to find the null value in a table
-
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
-
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
-
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...
-
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.
-
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
-
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
-
How to display loop counter at the screen using PL/SQL loop ?
Showstopper
- Jun 21st, 2010
Begin for loopcounter in 1..10 loop dbms_output.put_line(loopcounter); end loop; end; / This piece of code will display the loopcounter in a PL/SQL loop on the screen
-
Pipe Function and Mutating Trigger
1) Why we use pl/sql array rather than cursor?2) In which condition we use function overloading? 3) What is pipe function?4) What is the mutating trigger? What should be the condition?
renu_db_dev
- Jul 2nd, 2010
1) Why we use PL/SQL array rather than cursor?PL/SQL array is a collection through which we can iterate just the way we can through cursor. But in case of Cursor the context switching is for every row...
avis_sarkar
- Oct 14th, 2009
Triggers are PLSQL block or Procedures which get executed whenever:1) an INSERT, DELETE or UPDATE happens on a table. 2) DDL like ALTER or CREATE happens on a table. 3) an INSTEAD OF trigger on a view...
-
Explicit cursor and Select into statement
What is the difference between an explicit cursor and select into statement?
neerajalatha
- Jul 5th, 2010
Select statement can return multiples rows at a time and there exists only one network round trip.Explicit cursors are memory areas which acts as a handle or pointer to context area and allows to fetc...
rashlikesu
- May 12th, 2010
When select into query returns just one row, Oracle uses implicit cursor to execute and fetch the results for the query.But if the select statement returns more than one row, an Explicit cursor is created to fetch the data.
-
What is PL/SQL table ?
Objects of type TABLE are called "PL/SQL tables", which are modeled as (but not the same as) database tables, PL/SQL tables use a primary PL/SQL tables can have one column and a primary key. Cursors
Read Best Answer
Editorial / Best Answer
rkanth18- Member Since Sep-2005 | Nov 27th, 2005
A PL/SQL table is a one-dimensional, unbounded, sparse collection of homogenous elements, indexed by integers
- One-dimensional
A PL/SQL table can have only one column. It is, in this way, similar to a one-dimensional array.
- Unbounded or Unconstrained
There is no predefined limit to the number of rows in a PL/SQL table. The PL/SQL table grows dynamically as you add more rows to the table. The PL/SQL table is, in this way, very different from an array.
Related to this definition, no rows for PL/SQL tables are allocated for this structure when it is defined.
- Sparse
In a PL/SQL table, a row exists in the table only when a value is assigned to that row. Rows do not have to be defined sequentially. Instead you can assign a value to any row in the table. So row 15 could have a value of `Fox' and row 15446 a value of `Red', with no other rows defined in between.
- Homogeneous elements
Because a PL/SQL table can have only a single column, all rows in a PL/SQL table contain values of the same datatype. It is, therefore, homogeneous.
With PL/SQL Release 2.3, you can have PL/SQL tables of records. The resulting table is still, however, homogeneous. Each row simply contains the same set of columns.
- Indexed by integers
PL/SQL tables currently support a single indexing mode: by BINARY_INTEGER. This number acts as the "primary key" of the PL/SQL table. The range of a BINARY_INTEGER is from -231-1 to 231-1, so you have an awful lot of rows with which to work
neerajalatha
- Jul 5th, 2010
A PL/SQL table is a database table having one column and rows are stored not in predefined order. Associative arrays and nested tables comes under category of PL/SQL.
babu.sridharan
- Jul 14th, 2008
Example for PL/SQL table:==================In the below block "typlsql" and "ty_plsql2" are PL/SQL tablesdeclaretype ty_plsql is record ( empno number, ename varchar2(50));TYPE ty_...
-
Can any one explain Perforance Tuning in PL/SQL
neerajalatha
- Jul 5th, 2010
Performance tuning can be improved by using FOR ALL and BULK COLLECT clauses in place of FOR LOOP and CURSORS which makes the application faster and better.
ORA_CRAZY
- Nov 19th, 2009
Using Bulk binding concepts, we can tune the PL/SQL job that takes long time due to millions of records.
-
What is the output of the following pl/sql block ?declare v_empno emp.empno%type;begin select empno into v_empno from emp where empno = 10;exception when others then dbms_output.put_line ( 'no data found'); when no_data_found then dbms_output.put_line ( 'ther is no data found ');end;
when others then *ERROR at line 6:ORA-06550: line 6, column 2:PLS-00370: OTHERS handler must be last among the exception handlers of a blockORA-06550: line 0, column 0:PL/SQL: Compilation unit analysis terminated
kiran_marla
- Jul 7th, 2010
Error occurs, why because "when others" must and should be placed at last in exception block
neerajalatha
- Jul 5th, 2010
when others then*ERROR at line 6:ORA-06550: line 6, column 2:PLS-00370: OTHERS handler must be last among the exception handlers of a blockORA-06550: line 0, column 0:PL/SQL: Compilation unit analysis terminatedThis is right answer
-
SQL Statments in Packages
If you have to call same piece of code at many places in a package, How do you achieve this without calling the function many times?
-
What are the advantages and disadvantages of DBMS-SQL
neerajalatha
- Jul 7th, 2010
Disadvantages:It is slow when compared to native dynamic SQL and coding is also difficult when compared to native dynamic SQL.
stshekhar
- Jun 22nd, 2008
Disadvantage : 1) Executing DDL statements through dbms_sql sometime can result in deadlock. example : we are trying to drop one object at the time it is being accessed by other user.
-
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...
-
PL/SQL Synonym
What are the advantage of Synonym in PL/SQL
mukesh1009
- Jul 9th, 2010
Dear Kanchan Maheshwari There are two categories of synonyms, public and private. A public synonym can be accessed by any system user. The individual creating a public synonym does not own the sy...
-
Package Without Body
What is the use of a package without body?
Gaurav_Vashishtha
- Jul 27th, 2010
Package without body is used to declare global cursors and constants which can be used by other function, procedures or packages
PL/SQL Interview Questions
Read Best Answer
Ans