Home » SQL & PL/SQL » SQL & PL/SQL » Select query in procedure!
Select query in procedure! [message #273269] Tue, 09 October 2007 15:24 Go to next message
Debugger
Messages: 4
Registered: October 2007
Location: Karachi
Junior Member
Dear Gurus!

I am trying to fetch one COLUMN from a table. Getting errors as under.

Will appriciate if help/exlain.

,
Deb!

SQL> EDIT
Wrote file afiedt.buf

  1  CREATE OR REPLACE PROCEDURE GETRECORDS
  2  AS
  3     MSG VARCHAR2(500);
  4  BEGIN
  5     SELECT RECD1 INTO MSG FROM TBLQUE;
  6* END;
SQL> /

Procedure created.

SQL> COMMIT;

Commit complete.

SQL> EXECUTE GETRECORDS;
BEGIN GETRECORDS; END;

*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SCOTT.GETRECORDS", line 5
ORA-06512: at line 1


[mod-edit] removed illiterate IM Speak.

[Updated on: Wed, 10 October 2007 07:52] by Moderator

Report message to a moderator

Re: Select query in procedure! [message #273272 is a reply to message #273269] Tue, 09 October 2007 16:07 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:


ORA-01422: exact fetch returns more than requested number of rows
Cause: The number specified in exact fetch is less than the rows returned.
Action: Rewrite the query or change number of rows requested



SQL> desc x;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER

SQL> create or replace procedure test_proc
  2  is
  3  test_val number;
  4  begin
  5  select a into test_val from x;
  6  end;
  7  /

Procedure created.

SQL> select * from x;

         A
----------
         1


SQL> exec test_proc;

PL/SQL procedure successfully completed.

SQL> insert into x values (2);

1 row created.

SQL> exec test_proc;
BEGIN test_proc; END;

*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "RND.TEST_PROC", line 5
ORA-06512: at line 1


Read the orafaq guidelines and also the oracle sql reference manual.

Regards

Raj

[Updated on: Tue, 09 October 2007 16:12]

Report message to a moderator

Re: Select query in procedure! [message #273431 is a reply to message #273269] Wed, 10 October 2007 07:53 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Debugger wrote on Tue, 09 October 2007 16:24


SQL> /

Procedure created.

SQL> COMMIT;

Commit complete.




Commit is unnecessary.
Previous Topic: whats wrong with this delete code?
Next Topic: Function
Goto Forum:
  


Current Time: Sun Dec 04 08:19:22 CST 2016

Total time taken to generate the page: 0.12213 seconds