Home » SQL & PL/SQL » SQL & PL/SQL » No data found. Is it a bug?
icon9.gif  No data found. Is it a bug? [message #251721] Mon, 16 July 2007 04:13 Go to next message
dandyus
Messages: 3
Registered: July 2007
Junior Member
create or replace procedure p_get_pk(P_PART_NO IN VARCHAR2) is
/*
Remember to type "Set SERVEROUTPUT ON" Before running this program
in order to see your output.
*/
Tpart_kind_id pro010.part_kind_id%type;
tp_part_no pro010.part_no%type;
BEGIN
tp_part_no := P_PART_NO;
DBMS_OUTPUT.PUT_LINE('The parameter value of P_PART_NO is ' || P_PART_NO || '.');
--
select part_kind_id into Tpart_kind_id from pro010 where part_no=tp_part_no;
DBMS_OUTPUT.PUT_LINE('Tpart_kind_id which query with LOCAL_VAR is ' || Tpart_kind_id || '.');
--
select part_kind_id into Tpart_kind_id from pro010 where part_no=P_PART_NO;
DBMS_OUTPUT.PUT_LINE('Tpart_kind_id which query with PARAMETER is ' || Tpart_kind_id || '.');
EXCEPTION
WHEN NO_DATA_FOUND
THEN DBMS_OUTPUT.PUT_LINE('Exception occurs: No data found');
END;


-- =======================================================
SQL> exec p_get_pk('0022');

The parameter value of P_PART_NO is 0022.


Tpart_kind_id which query with LOCAL_VAR is 12.
Exception occurs: No data found

PL/SQL procedure successfully completed

SQL>

--**********************************
Why occur this condition?
1.Recreate a database, then import the data --->It doesn't work.
2.Change from 9i to 9.2i --->It doesn't work.

I had spend a week to try to resolve this problem, but it does
not work ...

Does any one can help me!





Re: No data found. Is it a bug? [message #251724 is a reply to message #251721] Mon, 16 July 2007 04:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts
Make sure that lines of code do not exceed 80 or 100 characters when you format.

Is "pro010.part_no" of VARCHAR2 datatype?

Post a message, just after "tp_part_no := P_PART_NO" to display the value of each variable.
Post a test case we can reproduce.

Regards
Michel

Re: No data found. Is it a bug? [message #251761 is a reply to message #251724] Mon, 16 July 2007 10:17 Go to previous messageGo to next message
dandyus
Messages: 3
Registered: July 2007
Junior Member
Michel,
Tks for your response.
yes, "pro010.part_no" is also a VARCHAR2.


CREATE OR REPLACE PROCEDURE P_GET_PK(P_PART_NO IN VARCHAR2) is
  /*
    Remember to type "Set SERVEROUTPUT ON" Before running this program
    in order to  see your output. 
   */
  Tpart_kind_id   pro010.part_kind_id%type;
  tp_part_no      pro010.part_no%type; -- VARCHAR2(15).

BEGIN
  tp_part_no := P_PART_NO;
 
  DBMS_OUTPUT.PUT_LINE('The value of P_PART_NO  is ' || P_PART_NO  || '.');
  DBMS_OUTPUT.PUT_LINE('The value of tp_part_no is ' || tp_part_no || '.');

  --
  select part_kind_id into Tpart_kind_id from pro010 where part_no=tp_part_no;
  DBMS_OUTPUT.PUT_LINE('Tpart_kind_id which query with LOCAL_VAR is '
                       || Tpart_kind_id || '.');
  --
  select part_kind_id into Tpart_kind_id from pro010 where part_no=P_PART_NO;
  DBMS_OUTPUT.PUT_LINE('Tpart_kind_id which query with PARAMETER is ' 
                       || Tpart_kind_id || '.');
EXCEPTION
  WHEN NO_DATA_FOUND
  THEN DBMS_OUTPUT.PUT_LINE('Exception occurs: No data found');
  
END;



-- ====================================
The output as following:

SQL> exec P_GET_PK('0022');

The value of P_PART_NO is 0022.
The value of tp_part_no is 0022.
Tpart_kind_id which query with LOCAL_VAR is 12.
Exception occurs: No data found

PL/SQL procedure successfully completed

SQL>
Re: No data found. Is it a bug? [message #251764 is a reply to message #251721] Mon, 16 July 2007 10:38 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Something is odd here.

SQL> create table pro010(part_no varchar2(15),          part_kind_id number);

Table created

SQL> insert into pro010 (part_no,part_kind_id) values ('0022',12);

1 row inserted

SQL> commit;

Commit complete

SQL> select * from pro010;

PART_NO         PART_KIND_ID
--------------- ------------
0022                      12


When I execute the procedure you have posted I get the following results:

SQL> set serveroutput on;
SQL> exec p_get_pk('0022');
The value of P_PART_NO  is 0022.
The value of tp_part_no is 0022.
Tpart_kind_id which query with LOCAL_VAR is 12.
Tpart_kind_id which query with PARAMETER is 12.

PL/SQL procedure successfully completed.

SQL> 


Notice the alignment of the dbms_output, which matches what is in your procedure.

The output you posted is not aligned the same. Have you compiled recently ?

[Updated on: Mon, 16 July 2007 10:40]

Report message to a moderator

Re: No data found. Is it a bug? [message #251776 is a reply to message #251764] Mon, 16 July 2007 11:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good question from Marc.
Format you output.
Query user_source to display the actual procedure.

Regards
Michel
Re: No data found. Is it a bug? [message #251796 is a reply to message #251721] Mon, 16 July 2007 12:27 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Post definition of the referenced table as well (pro010).

Michael
Re: No data found. Is it a bug? [message #251805 is a reply to message #251721] Mon, 16 July 2007 13:08 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
does your table pro010 contain a column named P_PART_NO?
icon10.gif  Re: No data found. Is it a bug? [message #251842 is a reply to message #251805] Mon, 16 July 2007 19:35 Go to previous message
dandyus
Messages: 3
Registered: July 2007
Junior Member
Hi Bill B,
Yes,
The table pro010 contains a column named P_PART_NO.
The field was added by out-sourcing engineer last week!

I just rename it and try the procedure again
Everything is going to fine! Laughing

Thank you very much!
Previous Topic: Trigger creation in a loop
Next Topic: About 'ORA-02292' Error
Goto Forum:
  


Current Time: Thu Dec 08 10:18:13 CST 2016

Total time taken to generate the page: 0.04907 seconds