Good Morning SQL/PLSQL Experts;
I have written sample program for PL/SQL records.
without cursors - how can finish my error query (please see 2nd program)
SQL> DECLARE 2 val_disp customers%rowtype; 3 BEGIN 4 select * into val_disp from customers where no=100; 5 DBMS_OUTPUT.PUT_LINE(val_disp.no); 6 DBMS_OUTPUT.PUT_LINE(val_disp.name); 7 DBMS_OUTPUT.PUT_LINE(val_disp.mobile); 8 DBMS_OUTPUT.PUT_LINE(val_disp.city); 9 END; 10 / 100
PL/SQL procedure successfully completed.
Why I am getting an error here
customers table having 5 records.
SQL> DECLARE 2 val_disp customers%rowtype; 3 i number :=1; 4 BEGIN 5 for i in 1..5 loop 6 select * into val_disp from customers; 7 DBMS_OUTPUT.PUT_LINE(val_disp.no); 8 DBMS_OUTPUT.PUT_LINE(val_disp.name); 9 DBMS_OUTPUT.PUT_LINE(val_disp.mobile); 10 DBMS_OUTPUT.PUT_LINE(val_disp.city); 11 end loop; 12 END; 13 /
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 6
please answer this question and clear my doubt.
Whenever you have a question, please post a compelte test script that the people who want to help you can run to re-create the problem and test their ideas. If you can't show what the problem is using commonly available tables (such as those in the scott schema), then include CREATE TABLE and INSERT statements for your own tables.
No matter where the input data comes from, post the output you want from that data.
Always say which version of Oracle you're using (e.g. 188.8.131.52.0).
See the forum FAQ: https://forums.oracle.com/message/9362002
Do you really need to use PL/SQL? If so, explain why.
Oteherwise, just run your query
where no = 100;
in SQL*Plus, or any other front end.
If you really, really must do this in PL/SQL, here's a simple way:
FOR d IN (
WHERE deptno > 10
dbms_output.put_line (d.dname); -- or whatever you need to do
The cursor d is defined in the FOR statement itself; you don't need a separate declaration. In fact, if you do have a separate declaration, it will create a separate cursor, which will not be used in the FOR ... LOOP.
This will be much slower than simply using SQL.