checking value of %rowtype [message #366203] |
Fri, 12 December 2008 02:20  |
shaksing
Messages: 115 Registered: November 2008
|
Senior Member |
|
|
In declare section i have V_RECORD NPPI_PURGADO_S%ROWTYPE;
And after that in code i am doing this.
SELECT * INTO V_RECORD FROM NPPI_PURGADO_S WHERE DETALLE LIKE '%RECONSTRUIDAS LAS TABLAS%' AND FECHA_INICIO IN (SELECT MAX(FECHA_INICIO) FROM NPPI_PURGADO_S);
after that i want to check v_record is null or not
IF V_RECORD%NOTFOUND THEN.. Is that possible.
Please suggest me .
|
|
|
Re: checking value of %rowtype [message #366208 is a reply to message #366203] |
Fri, 12 December 2008 02:35   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
v_record is NEVER null.
Explain what you really want, and read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).
Regards
Michel
[Updated on: Fri, 12 December 2008 02:36] Report message to a moderator
|
|
|
Re: checking value of %rowtype [message #366215 is a reply to message #366203] |
Fri, 12 December 2008 02:52   |
rap.fernandes
Messages: 4 Registered: June 2008
|
Junior Member |
|
|
Hi,
I think if the query does not fetch any records, you will get a no data found exception and your code will never reach IF V_RECORD%NOTFOUND THEN..
Instead you can create a cursor of the query, fetch it in the variable you have declared and then check if the cursor found any records or not,
OPEN cursor;
FETCH cursor INTO variable;
IF cursor%FOUND
The cursor has to be declared in the declaration section.
Hope it helps.
Regards
Raphael.
|
|
|
Re: checking value of %rowtype [message #366216 is a reply to message #366203] |
Fri, 12 December 2008 02:52   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
If the table that you're fetching a row from has a primary key or a not null column, then check whether that column has a value. If it does, then a row must have been fetched from the table.
Alternatively, check SQL%ROWCOUNT immediately after the query - that will tell you if a row was retrieved.
|
|
|
|
Re: checking value of %rowtype [message #366240 is a reply to message #366203] |
Fri, 12 December 2008 04:22   |
shaksing
Messages: 115 Registered: November 2008
|
Senior Member |
|
|
Actually my need is that , I want to do select * from the table with the specified where condition (as you saw earlier in the code), which will give me a record, after that i want to check that whether any record is fetched or not, so experts please tell me how can i proceed?
|
|
|
|
Re: checking value of %rowtype [message #366268 is a reply to message #366203] |
Fri, 12 December 2008 09:17   |
c_stenersen
Messages: 255 Registered: August 2007
|
Senior Member |
|
|
As said by someone else before, create a cursor.
declare
cursor my_cursor is
SELECT *
FROM NPPI_PURGADO_S
WHERE DETALLE LIKE '%RECONSTRUIDAS LAS TABLAS%'
AND FECHA_INICIO IN (
SELECT MAX(FECHA_INICIO)
FROM NPPI_PURGADO_S);
v_record my_cursor%rowtype;
begin
open my_cursor;
fetch my_cursor into v_record;
if my_cursor%notfound then
--I didn't find a result
else
--A result was found
end if;
close my_cursor;
end;
Or if you want the select into variant you'll have to catch the no_data_found exception, and you'll know that no matching row was found.
declare
v_record NPPI_PURGADO_S%rowtype;
begin
SELECT * INTO V_RECORD
FROM NPPI_PURGADO_S
WHERE DETALLE LIKE '%RECONSTRUIDAS LAS TABLAS%'
AND FECHA_INICIO IN (
SELECT MAX(FECHA_INICIO)
FROM NPPI_PURGADO_S);
exception
when no_data_found then
--I didn't find a result
end;
|
|
|
|