-
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
-
How can i get the procedure's, function's name from a package if it is wrapped(both spec & body).
Gaurav_Vashishtha
- Jul 27th, 2010
You can get the source code of the wrapped pakage from dba_source table.
ravikumarzee@gmail.com
- Mar 9th, 2007
Desc e.g., If u want to know the procedures and functions in a package called xyz thendesc xyz;
-
Status of Procedure
If a procedure is referring a table and if the structure of the table is changed, lets say data type of the any column is changed, then is it possible that the status of the procedure remains valid? Explain
Read Best Answer
Editorial / Best Answer
tar.goyal- Member Since Aug-2010 | Aug 4th, 2010
It is possible that a procedure remain valid even when the data type of columns of a table on which it is operating change.
This can be achieved by using Anchor declaration i.e. %TYPE and %ROWTYPE while defining reference to such table columns and rows respectively in the procedure.tar.goyal
- Aug 4th, 2010
It is possible that a procedure remain valid even when the data type of columns of a table on which it is operating change.This can be achieved by using Anchor declaration i.e. %TYPE and %ROWTYPE while defining reference to such table columns and rows respectively in the procedure.
anironic
- Jul 21st, 2010
If PL/SQL stored SP %type and does not explicity declare a datatype, then it is quite possible that Stored procedure remains valid, even after changing the datatype in the table. Ex, you have a declar...
-
Package Function
You have a package called A and one function in that packgae called XYZ.If you need to call that function in second packaged B, How will you call?
Raj_P
- Sep 1st, 2010
1) VarName := PackageName.FunctionName(<Parameter List>).2) Select PackageName.FunctionName(<Parameter List>) From <TableName? You cannot run this statement if y...
shekhar2010
- Aug 30th, 2010
Yes we can call a function which is there in a Package 'A' from Package 'B'. And it is very simple as stated belowPackage_Name.Function_Name(Parameter_List);
-
What is a purity level? How it is should be taken into consideration when your writing any database objects i.e., trigger,function, procedure etc.,
asrafpa
- Sep 26th, 2010
The function purity level defines what structures the function reads or modifies. Following are the purity levels 1) WNDS - Writes No Database State i.e. Function does not modify any database tables ...
pari
- Mar 15th, 2007
pragma_restrict_references is a compiler directive to check the purity level of functions used in packages.The restriction on a function that it cannot do update, insert or delete when used in select ...
-
How would you hide a table in sql. ie the table can be only visible to its maker.
Not known yet
ankurlibra
- Sep 26th, 2010
We are misunderstanding the question and I assume that Abhishek want to say that "How we can hide a table info from user?".To hide table info or as per question make it invisible to other user. A tabl...
pravelu
- Apr 23rd, 2010
It can be done using role grants. Grant view or debug privileges to only the set of users who can see these tables. The other users who are not granted the privilege cannot view this table. Every single user is a Schema in Oracle.It is by default, that the table cannot be seen by others.
-
Write sample code that can create a hierachical set of data without using a start with and connect by clause in PL/SQL
Art11
- Sep 27th, 2011
My example is not perfect but close."sql SELECT deptno, job, ename, sal ,CURSOR( SELECT ename, deptno, job, sal , DENSE_RANK () OVER (ORDER BY sal desc) rnk ...
dipanjan80
- Oct 6th, 2010
?Using LEAD analytical function its possible to get hierarchical set of data. Here is an example:?SELECT deptno, empno, sal,?LEAD(sal, 1, 0) OVER (PARTITION BY dept ORDER BY sal DESC NULLS LAST) N...
-
Debugging Dynamic SQL
What is the best technique of debugging large and fragmented dynamic SQL.
shivaranjani.p
- Oct 6th, 2010
Hi,You need to store each and every line of the dynamic sql in a file using UTL_FILE Package before you could execute it, so that you can refer the same file after executing the code, this here serves...
-
How can u create session variable in pakages?
asraf_pa
- Oct 10th, 2010
You can use DBMS_SESSION to set & get session variable. Below is the example for the sameSQL> EXEC DBMS_SESSION.SET_CONTEXT('CLIENTCONTEXT', 'myvar', 'myvalue');PL/SQL procedure successfully co...
rampratap409
- Dec 11th, 2006
session variable can not be declared in package, variable declared in package are called global variables. create package mypack isnum number := 7;num2 number;end ;here num, num2 are global vari...
-
Difference between pre-query & post-query
kalpana2u95
- Oct 12th, 2010
Pre SQL query means that the integration service runs SQL commands against the source database before it reads the data from source. Post SQL query means IS runs SQL commands against target database after it writes to the target.
Pawan Ahuja
- Dec 4th, 2006
Pre-query trigger fires only ones while post-query trigger fires each time.the Post-Query trigger fires each time Form Builder fetches a record into a block.RegardsPawan Ahuja
-
Bulk Data Operations
You are developing a PL/SQL block designed for bulk data operations. When attemping to store table data for multiple rows in a PL/SQL variable, which one of the following choices identifies the mechanism best suited for this task (choose one)? A. cursorB. varchar2C. recordD. table of records
rahulg5211
- Oct 25th, 2010
Table of record type is best option to perform this operation.becouse first thing pl/table has unlimited size.second thing for fetching bunch of record into pl/table,besides of INTO clause we ne...
shukla.prashant76
- Apr 28th, 2010
Answer is D. We need multiple rows on a variable so we use here Table of records .it uses just like as collection of records.Cursor is used for selecting multiple rows from stored table.Var...
-
How packaged procedures and functions are called from the following?
a. Stored procedure or anonymous block b. an application program such a PRC *C, PRO* COBOL c. SQL *PLUS a. PACKAGE NAME.PROCEDURE NAME (parameters); variable := PACKAGE NAME.FUNCTION NAME (arguments); EXEC SQL EXECUTE ...
shi_fu
- Nov 30th, 2010
a. procedure is called by its name:pack_name.proc_name;function is called from a expression:a=b*(function(param));b.(same as above)procedure is called by its name:pack_name.proc_name;function is calle...
g_sidhu
- Feb 17th, 2008
a. PACKAGE NAME.PROCEDURE NAME (parameters); variable := PACKAGE NAME.FUNCTION NAME (arguments); b. ...
PL/SQL Interview Questions
Read Best Answer
Ans