Home » SQL & PL/SQL » SQL & PL/SQL » checking value of %rowtype (Oracle 9)
checking value of %rowtype [message #366203] Fri, 12 December 2008 02:20 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #366238 is a reply to message #366203] Fri, 12 December 2008 04:13 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
But fetching a cursor here is again a problem as here one record will be there in the cursor, so what can i do now to assign it to variable.

[Updated on: Fri, 12 December 2008 04:13]

Report message to a moderator

Re: checking value of %rowtype [message #366240 is a reply to message #366203] Fri, 12 December 2008 04:22 Go to previous messageGo to next message
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 #366245 is a reply to message #366203] Fri, 12 December 2008 04:50 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you're doing a SELECT INTO and you don't get a record you'll get a no_data_found error.
Re: checking value of %rowtype [message #366268 is a reply to message #366203] Fri, 12 December 2008 09:17 Go to previous messageGo to next message
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;
Re: checking value of %rowtype [message #375872 is a reply to message #366203] Mon, 15 December 2008 03:34 Go to previous message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Thanks a lot c_stenersen , that was really helpful and now i am able to work with my code.
Previous Topic: figure to words
Next Topic: Display datatype double until 2 precision number
Goto Forum:
  


Current Time: Sun Dec 11 06:09:11 CST 2016

Total time taken to generate the page: 0.04083 seconds