Thanks for the question, santhosh rreddy.

Asked: September 09, 2016 - 4:23 am UTC

Last updated: November 17, 2016 - 2:20 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Hi Tom,

Iam Trying to create a procedure to print source code of stored porgrams below is the procedure i created.

create or replace PROCEDURE pr_source(p_obj_name IN VARCHAR2)
IS
l_name all_objects.object_name%type;
CURSOR c_cur IS select * from all_source where name = UPPER(p_obj_name);
BEGIN
BEGIN
select object_name into l_name from all_objects WHERE object_name =upper(p_obj_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('OBJECT NOT EXIST OR MIGHT NOT BE A STORED PROGRAM');
RETURN;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
RETURN;
END;
BEGIN
FOR rec_cur IN c_cur LOOP
SELECT text INTO rec_cur.text FROM all_source WHERE name =rec_cur.name;
DBMS_OUTPUT.PUT_LINE(rec_cur.text);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
RETURN;
END;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
RETURN;
END;

i did not get any errors when i try ti excute i am getting below errors

anonymous block completed
ORA-01422: exact fetch returns more than requested number of rows

so please help me.
and i want know one thing about cursors, does they can handle duplicate rows to print?

Thank You.

and Chris said...

A "select ... into ..." cursor gives you the ORA-1422 error if it returns more than one row. If you want to process more than one row, you have a couple of options:

Cursor for loops

Here you loop through the values, similar to how you've done with "for rec_cur in c_cur".

You don't need another "select into" though - you already have the values! You can just print them:

    for rec_cur in c_cur
    loop
      dbms_output.put_line ( rec_cur.text ) ;
    end loop;

Bulk collection

This could be an implicit cursor:

select ...
bulk collect into ...
from ...

Or an explicit one:

open c;
loop
  fetch c bulk collect into r limit 100;
  exit when r.count = 0;
  ...
end loop;
close c;

Either way it gives you an array you can loop through. For example:

create or replace procedure p is
  code_lines dbms_sql.varchar2a;
begin
  select text
  bulk   collect into code_lines
  from   user_source
  where  name = 'P';
  
  for i in 1 .. code_lines.count loop
    dbms_output.put_line(code_lines(i));
  end loop;
  
end p;
/      

exec p;

PL/SQL procedure successfully completed.
procedure p is

  code_lines dbms_sql.varchar2a;

begin

  select text

  bulk   collect into code_lines

  from   user_source

  where  name = 'P';

  

  for i in 1 .. code_lines.count loop

    dbms_output.put_line(code_lines(i));

  end loop;

  

end p;

For more info on cursors, read:

http://stevenfeuersteinonplsql.blogspot.com/2016/05/types-of-cursors-available-in-plsql.html

And please, stop using "when others" without a raise!

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

santhosh, November 07, 2016 - 4:54 am UTC

Do we get in problems with code if we use when others exception with our raise?

November 07, 2016 - 11:26 am UTC

"when others" without a raise is enormously risky. You can corrupt data.

santhoshreddy podduturi, November 10, 2016 - 6:47 am UTC

Would you mind giving small example on How to use WHEN OTHERS THEN with RAISE And how it would corrupt the data with out RAISE

Thank you.

November 10, 2016 - 11:18 am UTC

Say you have a transaction inserting into two tables. Either both inserts must succeed or both fail.

If the second insert fails and you bury the exception in a when others, the first insert is still present!

SQL> create table par (
  2    x int
  3  );

Table created.

SQL> create table chld (
  2    y int
  3  );

Table created.

SQL>
SQL> begin
  2    insert into par values (1);
  3     insert into chld values ('a');
  4  exception
  5    when others then
  6       null;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> select * from par;

         X
----------
         1

SQL> select * from chld;

no rows selected

So you can have "incomplete" transactions, leading to inconsistent data. If you raise the exception, this doesn't happen:

SQL> rollback;

Rollback complete.

SQL>
SQL> begin
  2    insert into par values (1);
  3     insert into chld values ('a');
  4  exception
  5    when others then
  6       raise;
  7  end;
  8  /
begin
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 6


SQL> select * from par;

no rows selected

SQL> select * from chld;

no rows selected

santhoshreddy podduturi, November 16, 2016 - 7:33 am UTC

The RAISE will Work with userdefined and predefined wxception like NO_ROWS_FOUND,TOO_MANY_ROWS?

November 17, 2016 - 2:20 am UTC

Yes, "raise;" simple re-raises whatever exception was encountered.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs

handling duplicate rows with cursors

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs