-
What are different data structures supported by PL/SQL.??
Eltonclardy
- Jan 9th, 2015
Yes PL/SQL have different data types compared to C and C++. These two datatypes in SQL i.e scalar and composite are very mush useful in SQL.
Hema nagaraju
- Dec 20th, 2014
Scallar,composite datatypes.
scllar datatypes are varchar2,number
composite datatypes are records,tables,collections which can store multiple values at a time -
Trigger - insert into one table update into another table
If i insert record in table A and these record should update in table B by using Trigger.How to achieve this.
Trigger - insert into one table update into another table -
-
Why do we need Package?
If I create N number of small procedures with one PL/SQL block (or) procedure why we need package?
-
What is the basic structure of PL/SQL ?
PL/SQL uses block structure as its basic structure. Anonymous blocks or nested blocks can be used in PL/SQL.
g_sidhu
- Feb 16th, 2008
PL/SQL is a block-structured language, meaning that programs can be divided into logical blocks. A PL/SQL block consists of up to three sections: declarative (optional), executable (required), and exception handling (optional).
Me
- Oct 5th, 2007
DeclarationBeginException HandlingEnd
-
What are the components of a PL/SQL Block ?
Declarative part, Executable part and Exception part. Datatypes PL/SQL
satyam_Ora
- May 14th, 2008
All has given the right answer and I am repeating the same.There are the three component of PL/SQL program block1) Declarative( Where you can decalre the variable which would be used as a part of exce...
g_sidhu
- Feb 16th, 2008
A PL/SQL block consists of up to three sections: declarative (optional), executable (required), and exception handling (optional).
-
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_...
-
What are the cursor attributes used in PL/SQL ?
%ISOPEN - to check whether cursor is open or not % ROWCOUNT - number of rows fetched/updated/deleted. % FOUND - to check whether cursor has fetched any row. True if rows are fetched. % NOT FOUND - to check whether cursor has fetched any row. True if no rows are featched.These attributes are proceeded with SQL for Implicit Cursors...
Lakshmi84
- Jul 31st, 2009
%ISOPEN - To check whether cursor is open or not.Returns true if cursor?or cursor variable is open or not otherwise?false.%FOUND - To check wheteher cursor is found or not. Returns true if fetch re...
samareshp
- Jul 30th, 2009
There are five cursor attributes: %isopen, %found, %notfound, %rowcount and %bulk_rowcount.%isopenWith %isopen it is possible to test whether a cursor was opened: %found%found returns true when the la...
-
What is Overloading of procedures ?
The Same procedure name is repeated with parameters of different datatypes and parameters in different positions, varying number of parameters is called overloading of procedures. e.g. DBMS_OUTPUT put_line What is a package ? What are the advantages of packages ?
krishnaindia2007
- May 7th, 2008
Definining two or more procedures with same name is called overloading of procedures. But they must differ in no or order or datatype family of formal arguments.
g_sidhu
- Jan 31st, 2008
Overloading: Multiple subprograms of the same name• Enables you to use the same name for different subprograms inside a PL/SQL block, a subprogram, or a package• Requires the formal paramete...
-
Can e truncate some of the rows from the table instead of truncating the full table.
Vishal R Bhadange
- Aug 12th, 2012
Yes.
Yes we can truncate some of the rows from the table, But table should be partitioned & all the rows to be deleted should be present in one single partition... -
What steps should a programmer should follow for better tunning of the PL/SQL blocks?Difference between procedure and function?What is the use of ref cursor return type?
Aniruddha Das
- Jun 11th, 2012
If you want to take care of performance tuning you need to take care of sub-queries as they takes a lot of time to executing in side the pl-sql blocks.
ptmich
- May 28th, 2012
Ref cursor is used to declare a pointer variable. When you return a ref cursor, you are returning a pointer variable.
-
What is the difference between User-level, Statement-level and System-level Rollback? Can you please give me example of each?
g_sidhu
- Feb 1st, 2008
Statement-level: Part of a transaction can be discarded by an implicit rollback if a statement execution error is detected. If a single DML statement fails during execution of a transaction, it s effe...
kameshakundy
- Nov 13th, 2006
Dear Kamini,May I know what do you mean by PG
-
What is the difference between In, Out, InOut Parameters. Can we pass value or reference or both to the In Out Parameter.
saishradha
- Nov 27th, 2008
Here is the answer in 1 lineIN-- value is used in a program. It cannot be a variable .COuld be literal,expression,value .OUT-- value is returned back from the progr...
raghav_sy
- Jan 15th, 2007
hi friends,To make it simple, keep in mind,IN : It is a CONSTANT in the sub-program and u can not modify its value in sub- program. if its value is modified in the s...
-
What is autonomous Transaction? Where are they used?
shinehere
- Feb 24th, 2008
An autonomous transaction is a transaction that is started within the context of another transaction, called the parent transaction, but is independent of it.An autonomous transaction can be committed...
g_sidhu
- Feb 18th, 2008
A autonomous transaction starts with the first sql statement of the pl/sql block and ends with a commit. It starts within the context of an another transaction called parent transaction and independen...
-
Why do we need to create a force view?what is its purpose?give some examples?
askvenki
- Jul 19th, 2007
The force view can be created before base table is created .when the table is createdit is automatically activated.we can create base table with any structure
Mohamed Irfan
- Dec 26th, 2006
Hi, Force views are created in real time environment as per the business needs because of two specific reasons1. If the structure of the base table is not completely known (ie) ...
-
How do i write a function that returnsmore than 1 records from a tablefor example in sql server there is a solution as create function f1() return table asselect * from emphow do i do the same in oracle. plz help
rajivgupta780184
- Nov 24th, 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 for ...
pardhu323
- Jul 20th, 2008
create or replace function f1 return sys_refcursor as r1 sys_refcursor;begin open r1 for select * from emp; return(r1); end;call the funtion as :select f1 from dual;then it returns :CURSOR STATEMENT :...
-
What is HIGH WATERMARK?I got to know that it is reset when the TRUNCATE command is executed on a table.
Sudhir Sorout
- Feb 5th, 2008
Yes when we truncate instead of delete, for deleting records , truncate set the HWM to 'zero' but if we use only delete then HWM remain same ......Sudhir Sorout
g_sidhu
- Feb 2nd, 2008
Within a segment, the high water mark indicates the amount of used space, or space that had been formatted to receive data.You cannot release space below the high water mark (even if there is no data ...
-
How we can create a table in PL/SQL block. insert records into it??? is it possible by some procedure or function?? please give example...
dineshchaini
- Sep 9th, 2006
Dear Maria... I traied with the DBA previlage but... again the same prob.. proc created but table is not thea... ok I tried u r proc and mail back to you...
tdas2004
- Sep 7th, 2006
You are right Maria Antony, but the question is, why is it not possible to create the table thru the procedure while you can create the sametable using create table SQL command.The answer is, to creat...
-
If there is an index including three columns A, B and C. And if we issue a query in which where clause uses only column B....will the index be useful??and what if the where clause only has coulmn A..will the index b useful??
madhaviguthula
- Mar 8th, 2007
Hi Hemant, Please go through the following code for better understanding.First, create and populate a test table with a concatenated index.CREATE TABLE test_objects ASSELECT * FROM all_objects;CREATE...
hemant.agarwal03
- Mar 8th, 2007
Hi Madhvi..How we will use that one special feature?Regards,Hemant
-
1)any one can tell me,suppose we have 1000 of records,ok.then we want to update only 500 records,how can we solve this problem?2)how many types of "explicit cursors" we have?
satyam_Ora
- May 8th, 2008
Sql> update set col_name='value' where rownum<=500;Note: rowid will not work instead of rownum for updating the 500 records.
PL/SQL Interview Questions
Read Best Answer
Ans