-
What is PL/SQL table? SNO MARK ------- ------------------1 592 403 ‘A’4 60 Write a single query to I) Sorted Marks II)First mark III) replace the mark ‘A’ with 0(zero)?
NehaChanda
- May 22nd, 2009
We can not use MAX on PLSQL table. Check if it works.Create a PLSQL Table Sorted_Mark.SNO_MARK(3) := 0; - Replace ASorted in another Collection Typeselect culumn_valuebulk collect into Sorted_MARKfrom table (cast(SNO_MARK as PLSQL_DATATYPE))order by column_valueSorted_MARK(1) - First Value
samareshp
- Apr 15th, 2009
I guess the the question should be replace a with zero then sort the mark and
then show first mark, Let me know if i am wrong.
select max(mark)
from (
SELECT sno, mark
FROM (select sno, decode(mark, 'A', 0, mark) x from t) xt
ORDER BY x
)
-
PL/SQL UTL_FILE Package
Which exception is used in UTL_FILE package in PL/SQL?
dinesh.smhdr
- May 19th, 2011
The package specification of UTL_FILE defines seven exceptions.INVALID_PATHINVALID_MODEINVALID_FILEHANDLEINVALID_OPERATIONREAD_ERRORWRITE_ERRORINTERNAL_ERRORNO_DATA_FOUNDVALUE_ERRORINVALID_MAXLINESIZE
-
">
Talk about "Exception Handling" in PL/SQL?
krishnaindia2007
- May 7th, 2008
A warning or error condition is called an exception. An exception may raise in a pl/sql block due to designing faults, coding mistakes or hardware failure. If an exception raises in a block ...
g_sidhu
- Jan 31st, 2008
You can handle exception in two ways:– Trap it with a handler : If the exception is raised in the executable section of the block, processing branches to the corresponding exception handler...
-
Can procedures have parameters
krishnaindia2007
- May 7th, 2008
Procedures Can have Parameters. But parameters is optional .i.e. we may defined procedures without parameters also.It can take three types of parametersIN , OUT and INOUT.
kanchan Patra
- Aug 7th, 2007
Yes, Procedures can have parameters.
-
What is Pragma EXECPTION_INIT ? Explain the usage ?
The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error. To get an error message of a specific oracle error. e.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)
g_sidhu
- Jan 31st, 2008
PRAGMA EXCEPTION_INIT statement associate the declared exception with the standard Oracle server error number. PRAGMA EXCEPTION_INIT tells the compiler to associate an exception name with an Oracle er...
-
Oracle Locks Concept
Please explain How to set locktime out in Oracle.
-
What is dense_rank function and it's usage ?
Teju_hassan
- Apr 20th, 2011
See the below example to understand better.select dense_rank,rank,salfrom empsorder by sal descdense_rank|rank|sal1 &...
g_sidhu
- Feb 5th, 2008
Rank Function can cause non-consecutive rankings if the tested values are the same. Whereas, the dense_rank function will always result in consecutive rankings.
-
Can we call a procedure into another procedure?If yes means how you can pass the perameters for the two procedures?
debasisdas
- Apr 11th, 2011
I believe an example would be best way to explain this.The scenario is whan user passes an employee_no to be inserted into EMP table i wan that value to be check against the existing record. If the em...
Arun
- Oct 27th, 2006
Hi friendit is easyExamplecreate or replace procedure first_proc (v_name1 in varchar, v_age in number) isbegindbms_output.put_line(v_name||' is '||v_age||' years old!');end;create or replace procedure...
-
Why and When would you use a package instead of standalone procedures? Which one increases performence - package or standalone procedure?
mailtonagaraja
- Mar 24th, 2011
Hi , 1) In Package we can combile all related objects into one component, whenever you refer or call any object in the package , full package will be copied to the memory , so that next ti...
-
User Defined Exception Handler
Suppose a programmer creates a user defined exception handler Invalid_Number in declare block with type EXCEPTION and raise this exception in begin block. Then how will you access the user defined exception and same system defined exception (i.e Inavlid_Number)
promisinganuj
- Feb 10th, 2011
The system defined exceptions in Oracle are tied to Oracle error codes. For this case, system defined error INVALID_NUMBER is tied with error code -01722.Now, when a user defines the sa...
-
PL SQL Procedure in Package
Procedure in package perfomes fastly over normal procedure, Explain.
monoranjang
- Jan 24th, 2011
A Storeprocedure is a Set of transactional SQL statements which will do some operation. Storedprocedure is precomplied . When we use sp's in our application it will reduce network traffic because ...
muraligrandhi
- Jun 8th, 2010
It's true. Procedures in the packages are faster than the stand alone procedures, because all the procedures in the package are compiled once and stored in the SGA at shared pool area. If we call the ...
-
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...
-
DATATYPES
1-WHAT IS THE MAX LENGHT OF VARCHAR2 DATATYPE IN PL/SQL?2-WHAT IS THE USE OF UROWID?3-WHAT ARE THE SUBTYPES?GIVE ANY 2 EXAMPLE?4- WHAT IS THE GENERAL STANDARD FOR DECLARATION?
pravesh28
- Dec 27th, 2010
1... max length is 327672 rowid means row plus id actuall its psedocolmn that store the value of each rowwith the help of rowid>>> we can remove duplicate value>>> we can dispa...
muraligrandhi
- Nov 9th, 2010
1. Maximum length of a varible in PL/SQL is 32767 bytes2. The UROWID or Universal Rowid is useful for storing logical addresses of all the rows of Index-organized tables. It can store both the logical...
-
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. ...
-
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...
-
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
-
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...
-
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...
-
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...
-
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.
PL/SQL Interview Questions
Ans