*Filter candidates using TestYourCandidate.com and save 80% time ( saving time for recruiters and interviewers)
*More than 50% candidates can be filtered on Candidate Screening online tests
*Inbuilt exams are available in library, created and tested by experts Try Today...
ORA-06502: PL/SQL: numeric or value error in Database Version: 10.2.0.3 view answer
Subtracting values in PL/SQL and merging result into cursor or table
-----------------------
I am trying to pull in values from two different dates on a table (start and end) and subtract one from the
other and store it into my result set which I'll be passing back via a cursor with my PL/SQL procedure.
I'm a little stuck on how to accomplish this.
Here's what I have so far.
The FOR loop doesn't work and it's obviously not the way to go but it represents what I'm trying to get to.
CODE:
create or replace
PROCEDURE BLMROUTING (startDate IN DATE, endDate IN DATE, series IN VARCHAR,
p_cursor OUT SYS_REFCURSOR) AS
TYPE rec IS RECORD
(integ diag.blm_acct.integ_over_val%TYPE,
signal diag.PV_SGNL_ID_ASSC.sgnl_id%TYPE,
sampleDate diag.blm_acct.smpl_dte%TYPE);
TYPE tableResults IS TABLE OF rec;
ResultTableStart tableResults;
ResultTableEnd tableResults;
ResultTable tableResults;
BEGIN
SELECT tc.integ_over_val, ta.sgnl_id, MAX(startDate) as MaxStartDate
BULK COLLECT INTO ResultTableStart
FROM diag.pv_sgnl_id_assc ta
INNER JOIN diag.series_sgnl_rec_asgn tb
ON ta.sgnl_id = tb.sgnl_id
INNER JOIN diag.blm_acct tc on ta.pv_id = tc.pv_id
WHERE tb.series_id = series ORDER BY disp_ord_nbr;
SELECT tc.integ_over_val, ta.sgnl_id, MAX(endDate) as MaxEndDate
BULK COLLECT INTO ResultTableEnd
FROM diag.pv_sgnl_id_assc ta
INNER JOIN diag.series_sgnl_rec_asgn tb
ON ta.sgnl_id = tb.sgnl_id
INNER JOIN diag.blm_acct tc on ta.pv_id = tc.pv_id
WHERE tb.series_id = series ORDER BY disp_ord_nbr;
FOR j IN ResultTableStart.FIRST..ResultTableStart.LAST LOOP
INSERT INTO ResultTable VALUES (ResultTableStart(j).integ_over_val -
ResultTableEnd(j).integ_over_val,
ResultTableStart(j).sgnl_id, ResultTableStart(j).MaxStartDate);
END LOOP;
END BLMROUTING; view answer
Please explain the concepts of transaction, commit and rollback view answer
Emp table had an employee with salary 2000. I issued an update statement to set the salary to 3000. Then I issued a create table statement. However the create table command errored out. I want to rollback the earlier update statement. Can I do that? view answer
What is the difference between Rule based optimization and Cost based optimization in Oracle SQL queries? view answer
You get a call from you backup DBA while you are on vacation. He has corrupted all of the control files while playing with the ALTER DATABASE BACKUP CONTROLFILE command. What do you do? view answer
Your users get the following error:
ORA-00055 maximum number of DML locks exceeded
What is the problem and how do you fix it?
view answer
You look at your fragmentation report and see that smon hasn?t coalesced any of you tablespaces, even though you know several have large chunks of contiguous free extents. What is the problem? view answer
You attempt to add a datafile and get:
ORA-01118: cannot add anymore datafiles: limit of 40 exceeded
What is the problem and how can you fix it? view answer
Where would you look to find out if a redo log was corrupted assuming you are using Oracle mirrored redo logs? view answer
Users aren?t being allowed on the system. The following message is received:
ORA-00257 archiver is stuck. Connect internal only, until freed
What is the problem? view answer
What file will give you Oracle instance status information? Where is it located? view answer
How can you determine if the SQLNET process is running for SQLNET V1? How about V2? view answer
Users from the PC clients are getting the following error stack: view answer
Users from the PC clients are getting messages indicating : view answer
How can you determine if an Oracle instance is up from the operating system level? view answer
If you aren?t sure what command does a particular UNIX function what is the best way to determine the command? view answer
The system has a program that always includes the word nocomp in its name, how can you determine the number of processes that are using this program? view answer
What is the purpose of the grep command? view answer
How do you create a decision tree in a shell script? view answer
You have a simple application with no ?hot? tables (i.e. uniform IO and access requirements). How many disks should you have assuming standard layout for SYSTEM, USER, TEMP and ROLLBACK tablespaces? view answer
How many redo logs should you have and how should they be configured for maximum recoverability? view answer
How many control files should you have? Where should they be located? view answer
When should the default values for Oracle initialization parameters be used as is? view answer
OS user should be used for the first part of an Oracle installation (on UNIX)? view answer
You have just started a new instance with a large SGA on a busy existing server. Performance is terrible, what should you check for? view answer
What must be installed with ODBC on the client in order for it to work with Oracle? view answer
When configuring SQLNET on the client what files need to be set up? view answer
When configuring SQLNET on the server what files must be set up? view answer
You have installed Oracle and you are now setting up the actual instance. You have been waiting an hour for the initialization script to finish, what should you check first to determine if there is a problem? view answer
What should be done prior to installing Oracle (for the OS and the disks)? view answer
How do you set up your tablespace on installation? view answer
You see multiple extents in the temporary tablespace. Is this a problem? view answer
In a system with an average of 40 concurrent users you get the following from a query on rollback extents:
ROLLBACK CUR EXTENTS
--------------------- --------------------------
R01 11
R02 8
R03 12
R04 9
SYSTEM 4
You have room for each to grow by 20 more extents each. Is there a problem? Should you take any action?
view answer
You look at the dba_rollback_segs view and see that you have a large number of wraps is this a problem? view answer
You look at the dba_rollback_segs view and see that there is a large number of shrinks and they are of relatively small size, is this a problem? How can it be fixed if it is a problem? view answer
If you see the value for reloads is high in the estat library cache report is this a matter for concern? view answer
you see the value for reloads is high in the estat library cache report is this a matter for concern? view answer
If you see a pin hit ratio of less than 0.8 in the estat library cache report is this a problem? If so, how do you fix it? view answer
What can cause a high value for recursive calls? How can this be fixed? view answer
You see the following on a status report:
redo log space requests 23
redo log space wait time 0
Is this something to worry about? What if redo log space wait time is high? How can you fix this?
view answer
How can you tell if a tablespace has excessive fragmentation? view answer
If a tablespace shows excessive fragmentation what are some methods to defragment the tablespace? (7.1,7.2 and 7.3 only) view answer
If a tablespace has a default pctincrease of zero what will this cause (in relationship to the smon process)? view answer
If you see statistics that deal with ?undo? what are they really talking about? view answer
you see contention for library caches how can you fix it? view answer
When looking at the estat events report you see that you are getting busy buffer waits. Is this bad? How can you find what is causing it? view answer
Discuss row chaining, how does it happen? How can you reduce it? How do you correct it? view answer
Describe hit ratio as it pertains to the database buffers. What is the difference between instantaneous and cumulative hit ratio and which should be used for tuning? view answer
Where can you get a list of all initialization parameters for your instance? How about an indication if they are default settings or have been changed? view answer
When should you increase copy latches? What parameters control copy latches? view answer
When looking at v$sysstat you see that sorts (disk) is high. Is this bad or good? If bad -How do you correct it? view answer
Explain the use of TKPROF? What initialization parameter should be turned on to get full TKPROF output? view answer
What is the fastest query method for a table? view answer
What is the general guideline for sizing db_block_size and db_multi_block_read for an application that does many full table scans? view answer
What are some indications that you need to increase the SHARED_POOL_SIZE parameter? view answer
You see multiple fragments in the SYSTEM tablespace, what should you check first? view answer
How do you set up tablespaces during an Oracle installation? view answer
A tablespace has a table with 30 extents in it. Is this bad? Why or why not. view answer
How do you generate file output from SQL? view answer
How do you prevent Oracle from giving you informational messages during and after a SQL statement execution? view answer
How do you prevent output from coming to the screen? view answer
How do you set the number of lines on a page of output? The width? view answer
What is explain plan and how is it used? view answer
What is the default ordering of an ORDER BY clause in a SELECT statement? view answer
You are joining a local and a remote table, the network manager complains about the traffic involved, how can you reduce the network traffic? view answer
You want to determine the location of identical rows in a table before attempting to place a unique index on the table, how can this be done? view answer
What special Oracle feature allows you to specify how the cost based system treats a SQL statement? view answer
You want to group the following set of select returns, what can you group on?
Max(sum_of_cost), min(sum_of_cost), count(item_no), item_no
view answer
What SQLPlus command is used to format output from a select? view answer
You want to use SQL to build SQL, what is this called and give an example view answer
How do you execute a host operating system command from within SQL? view answer
can you call a PL/SQL procedure from SQL? view answer
You want to include a carriage return/linefeed in your output from a SQL script, how can you do this? view answer
How can variables be passed to a SQL routine? view answer
How can you determine if an index needs to be dropped and rebuilt? view answer
A user selects from a sequence and gets back two values, his select is:
SELECT pk_seq.nextval FROM dual;
What is the problem?
view answer
How can you find out how many users are currently logged into the database? How can you find their operating system id? view answer
If you have an example table, what is the best way to get sizing data for the production table implementation? view answer
A developer is trying to create a view and the database won?t let him. He has the ?DEVELOPER? role which has the ?CREATE VIEW? system privilege and SELECT grants on the tables he is using, what is the problem? view answer
A user is getting an ORA-00942 error yet you know you have granted them permission on the table, what else should you check? view answer
How can you tell if a database object is invalid? view answer
What causes the ?snapshot too old? error? How can this be prevented or mitigated? view answer
You are using hot backup without being in archivelog mode, can you recover in the event of a failure? Why or why not? view answer
(On UNIX) When should more than one DB writer process be used? How many should be used? view answer
What happens if a primary key constraint is disabled and then enabled without fully specifying the index clause? view answer
What is the proper method for disabling and re-enabling a primary key constraint? view answer
What happens if a tablespace clause is left off of a primary key constraint clause? view answer
What happens if the constraint name is left out of a constraint clause? view answer
What are some of the Oracle provided packages that DBAs should be aware of? view answer
If the DEFAULT and TEMPORARY tablespace clauses are left out of a CREATE USER command what happens? Is this bad or good? Why? view answer
You have a rollback segment in a version 7.2 database that has expanded beyond optimal, how can it be restored to optimal? view answer
What is the purpose of the IMPORT option IGNORE? What is it?s default setting? view answer
Give one method for transferring a table from one schema to another: view answer
How can you generate debugging output from PL/SQL? view answer
How can you find within a PL/SQL block, if a cursor is open? view answer
What are SQLCODE and SQLERRM and why are they important for PL/SQL developers? view answer
In what order should a open/fetch/loop set of commands in a PL/SQL block be implemented if you use the %NOTFOUND cursor variable in the exit when statement? Why? view answer
What is a join and explain different types of joins? view answer
What are cursors? Explain different types of cursors. What are the disadvantages of cursors?
How can you avoid cursors?
view answer
How to determine the service pack currently installed on SQL Server?
view answer
What is database replication? What are the different types of replication you can set up in SQL Server?
view answer
Explain different types of BACKUPs avaialabe in SQL Server? Given a particular scenario, how would you go about choosing a backup plan?
view answer
What are the different ways of moving data/databases between servers and databases in SQL Server?
view answer
What are statistics, under what circumstances they go out of date, how do you update them?
view answer
How to restart SQL Server in single user mode? How to start SQL Server in minimal configuration mode?
view answer
What is blocking and how would you troubleshoot it?
view answer
What is a deadlock and what is a live lock? How will you go about resolving deadlocks?
view answer
What are the steps you will take, if you are tasked with securing an SQL Server?
view answer
What are the steps you will take to improve performance of a poor performing query?
view answer
What is RAID and what are different types of RAID configurations?
view answer
What is an index? What are the types of indexes? How many clustered indexes can be created on a table? I create a separate index on each column of a table. What are the advantages and disadvantages of this approach?
view answer
What are constraints? Explain different types of constraints.
view answer
What are the new features introduced in SQL Server 2000 (or the latest release of SQL Server at the time of your interview)? What changed between the previous version of SQL Server and the current version? view answer
What is bit datatype and what?s the information that can be stored inside a bit column?
view answer
What are user defined datatypes and when you should go for them?
view answer
What?s the difference between a primary key and a unique key?
view answer
How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables?
view answer
What is denormalization and when would you go for it?
view answer
What is normalization? Explain different levels of normalization?
view answer
Explain the difference between $ORACLE_HOME and $ORACLE_BASE. view answer
Explain an ORA-01555 (snapshot too old ) view answer
How would you go about increasing the buffer cache hit ratio? view answer
How would you go about generating an EXPLAIN plan? view answer
What column differentiates the V$ views to the GV$ views and how ? view answer
Give the stages of instance startup to a usable state where normal users may access it. view answer
What command would you use to create a backup control file? view answer
Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages to each. view answer
A table is classified as a parent table and you want to drop and re-create it. How would you do this without affecting the children tables? view answer
Give two examples of referential integrity constraints. view answer
What type of index should you use on a fact table? view answer
Give the two types of tables involved in producing a star schema and the type of data they hold. view answer
Give the reasoning behind using an index. view answer
Compare and contrast TRUNCATE and DELETE for a table. view answer
Where would you look for errors from the database engine? view answer
Give two examples of how you might determine the structure of the table DEPT. view answer
Explain the difference between a data block, an extent and a segment. view answer
How do you switch from an init.ora file to a spfile? view answer
You have just had to restore from backup and do not have any control files. How would you go about bringing up this database? view answer
Explain the difference between a hot backup and a cold backup and the benefits associated with each. view answer
How do you view version information in Oracle? view answer
The NVL function only allows the same data type. But here?s the task: if the commission field is null, then the text ?Not Applicable? should be displayed, instead of blank space. How do you write the query? view answer
How do you display the records within a given range? view answer
How do I display row number with records? view answer
How do I eliminate duplicate rows in an Oracle database? view answer
How do you switch to DOS prompt from SQL prompt? view answer
What?s the command to change the SQL prompt name? view answer
What?s the command to see the current user name? view answer
What are the pros and cons of using triggers?
Expected answer: view answer
When a query is sent to the database and an index is not being used, what type of execution is taking place?
view answer
What is the difference of a LEFT JOIN and an INNER JOIN statement?
view answer
What is an advantage to using a stored procedure as opposed to passing an SQL query from an application.
view answer
What is a Cartesian product? What causes it? view answer
Name the operators that cannot be overloaded. sizeof, ., .*, .->, ::, ?: view answer
How to use " in Oracle data ?
Question -- We are encountering db values that contain both double quotes and apostrophes. Has anyone found a work-around for this situation? view answer
What is Over Loading and what are its restrictions ?
view answer
What are the various types of parameter modes in a procedure ?
view answer
Can you have two stored functions with the same name ? view answer
Can you have two functions with the same name in a PL/SQL block ? view answer
What is the difference between a procedure and a function ? view answer
Can we define exceptions twice in same block ? view answer
What are the various types of Exceptions ? view answer
PROCEDURE TO PICK RANDOM SET OF NUMBERS FROM A GIVEN RANGE OF NUMBERS view answer
FUNCTIONS AS STORED PROGRAM UNITS :
insert a record into the table emp1 for job = 'MGR'
and sal >5000 - if sal <5000 return false and don't insert view answer
Calling a stored procedure within the main procedure view answer
Procedure Non-Stored Program Units with the "IN OUT PARAMETER"
view answer
Procedure Non-Stored Program Units with the "OUT PARAMETER" view answer
Procedure Non-Stored Program Units
with the "IN PARAMETER"
## IN parameter data cannot be manipulated
## IN parameter can only receive a constant value from the main program
## IN parameter does not return any value to the calling program view answer
Query to find the #week from the date given view answer
Query as follows
/* Query*/
need to split a string into seperate values.
eg.
col1 col2
----------
100 - 'a,b,c'
200 - 'a,x,b,d,e'
300 - 'c'
result:
value count
-------------
a - 2
b - 1
c - 2
etc. view answer
Query to display date in 3 different formats view answer
Query to delete duplicate records from a table view answer
Query as follows
/* query*/
iam having data in column called
col1
------
23.23456
456.7894
123.3456
324.67845
like this i have 20000 records
here in data after the dot i want only 2 digits with out rounding view answer
Query to insert ?&? into a varchar2 datatype column view answer
Query as follows
I have a query as below:
select quantity from material_table.
quantity
---------
20
40
10
I want the query to return only first two records (i.e. to stop the query when the sum of quantity is 60 in this case). view answer
Query as below
/* query asked*/
If an employee taken continues leave from 25-Feb-2006 to 03-Apr-2006, we have to display the monthly leave breakup like Feb ? 4 Days, Mar ? 31 Days and Apr ? 3 Days.
How to write a procedure, Please advice us.
Record it will be store in
Start Date : 25-Feb-2006
End Date : 03-Apr-2006
Notification Date : 25-Feb-2006
No of Days : 38
view answer
Query as follows
/* query asked */
Say in Table ?T? I have two colums ?A?, ? B?
I want to get the sum of A based on values of B.
I should get the sum in two buckets
For B=0
And B not equal zero.
Example
A B
___ ___
1 0
2 1
3 0
4 3
5 2
6 0
So I should get the result set as
SUM (A) B
______ __________
10 For B=ZERO
11 For B=NONZERO view answer
Following is the query
SQL> DESC PATH
Name Null? Type
------------------------------- -------- ----
COL1 CHAR(1)
COL2 CHAR(1)
SQL> select * from path;
Col1 Col2
------ ------
A B
B C
B D
C M
D G
M H
R Y
7 rows selected.
the result to be as follows
B,C,C,M,M,H
view answer
Write a Query to display the Lowest & Highest in Sal column
with following Conditions
1. Display 'LOW' in column SAL If SAL < 4000
2. Display 'HIGH' in column SAL if SAL > 4000
view answer
Following is the Query
IF I HAVE TWO COLUMNS LIKE THAT
ID COLUMN A COLUMN B
1 A 10000
2 Z 20000
3 I 30000
4 Z 40000
5 P 50000
6 D 60000
7 A 70000
8 P 80000
9 I 90000
Find the Query which will SUM THE COLUMN B, GROUP BY COLUMN A
WITHOUT DISTRUBING THE SEQUENCE
Output shud be as
A 80000
Z 60000
I 12000
P 13000
D 6000
view answer
The Follwing is the Query
id date
---------------
1 NULL
1 NULL
1 NULL
2 NULL
2 21.03.2004
3 22.04.2003
3 10.04.2003
Want the id when the date is never filled
Output shud return as
ID 1
view answer
Following is the Query?O/P Wanted !
Table A
-------
SNo Sname
1 a
2 b
3 c
Table B
--------
SNo Course
1 x
1 y
1 k
1 z
2 x
2 z
2 d
3 y
3 z
3 e
I need the output in the following format where course is x or y or z.
1 x y z
2 x z
3 y z
view answer
. Following is the query
Table 1
Col1
2
5
7
9
4
6
Query output to be as follows
(row1+row2) 2+5=7
(row2+row3) 5+7 =12
(row3+row4) 7+9=16 etc etc
view answer
Query to extract the given string 'a;b;c;d;e;f' as (a b c d e f) view answer
Query to get the number of days between two dates is easy to get view answer
sql query to retrieve the first day of the month... view answer
How to find the number of commas in the given String ?
view answer
How to move a file from one folder to other folder using SQLPLUS? view answer
What are the different types of Data models? view answer
What is the difference between hibernate and spring JDBC template? List any advantages and disadvantages view answer
What is the difference between TRUNCATE between DELETE IN Oracle? view answer
!!! ORACLE Frequently Asked Questions !!!
The NVL function only allows the same data type. But here?s the task: if the commission field is null, then the text ?Not Applicable? should be displayed, instead of blank space. How do you write the query?
