×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Creating a simple procedure in Oracle

Creating a simple procedure in Oracle

Creating a simple procedure in Oracle

(OP)
I am a novice in Oracle. I need to create a procedure that inserts a value into a table, if the insert is successful, to return all the rows in that table. Particularly I am having trouble determining whether the insert is successful and I cannot use a SELECT without an INTO
clause.

I am much more experienced in Microsoft SQLServer . This is how I would write the procedure SQLServer:


CREATE PROCEDURE FOR sp_test
AS
INSERT INTO testtable VALUES ('test')
IF @@error = 0
  SELECT * FROM testtable


I am actually surprised that such a simple procedure in MSS seems to be difficult in Oracle. We have a resident expert in Oracle who could not help. Could anybody out there help me?

RE: Creating a simple procedure in Oracle

In oracle if an error occurs an exception is raised, which you may wish to trap to avoid your program exitting. If no error occurs you may simply perform the next step in your logic.

create or replace procedure my_proc as
   my_value  varchar2(20) := null;
begin
   insert into test_table (col1)
   values ('abc');

   for my_cursor in
      (select col1 from test_table)
   loop
      my_value := my_cursor.col1;
      -- add other code ...
   end loop;
exception
   when others then null; -- error handler
end my_proc;

RE: Creating a simple procedure in Oracle

(OP)
The exception handling part is fine, but what does not work is SELECT * FROM test_table. This gives an error that you need to use an INTO with a SELECT. And yet the sql statement is perectly valid if not in a procedure. How do I display the contents of a table from within a procedure?

RE: Creating a simple procedure in Oracle

The short answer is you can't. A procedure runs in the RDBMS, and thus doesn't have any direct connection with a display device. All you can do is return the information retrieved to the invoking application via parameters or global variables.
There is a package called DBMS_OUTPUT however, which can be used to log information if certain session settings are defined (usually for debugging purposes). I'll try and get some more info on this and post it here.

RE: Creating a simple procedure in Oracle

This might be an interesting approach, it's from a Perl DBI mailing list. I've not tried it but it *should* work.

The basic idea is to fill an array with with the info you want (populate()) and write a function (get_line()) that returns a single element of that array each time it is called.

It should (might?) be possible to declare a cursor in the package and return data from a single row each time get_line() is called. Not sure, don't know enough about what types an oracle function can return.


CREATE OR REPLACE PACKAGE FOO AS TYPE varchar_array IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;
 
  my_array varchar_array;
  current_row NUMBER:=0;
 
  PROCEDURE target(data OUT varchar_array);
  PROCEDURE populate;
  PROCEDURE get_line(data OUT VARCHAR2);
 END;
 /
 CREATE OR REPLACE PACKAGE BODY FOO AS
 
  PROCEDURE target(data OUT varchar_array) IS
  BEGIN
  data(0):='Line 0';
  data(1):='Line 1';
  data(2):='Line 2';
  END;
 
  PROCEDURE populate IS
  BEGIN
  target(my_array);
  current_row:=0;
  END;
 
  PROCEDURE get_line(data OUT VARCHAR2) IS
  BEGIN
  IF (current_row <= my_array.COUNT) THEN
  data:=my_array(current_row);
  current_row:=current_row+1;
  END IF;
  END;
 END;
/

Mike
Mike_Lacey@Cargill.Com
Cargill's Corporate Web Site
Please don't send me email questions without posting them in Tek-Tips as well. Better yet -- Post the question in Tek-Tips and send me a note saying "Have a look at so-and-so in the thingy forum would you?"

RE: Creating a simple procedure in Oracle

Packermann, I've put the DBMS_OUTPUT stuff in a FAQ, as it's a bit long. If you plug this in to my_proc in my previous post it should meet the case if you're invoking the procedure from SQL*Plus or similar. If you're invoking it from an application you're writing, Mike's idea of returning an array (and then letting your app do the display) may suit you better.

RE: Creating a simple procedure in Oracle

We had another solution: Use Oracle's internal EXCEPTIONS table to handle this.

Step 1: Cate your own exceptions table, say mb_exceptions:

drop table mb_exceptions;
create table mb_exceptions(
   row_id     rowid,
   owner      varchar2(30),
   table_name varchar2(30),
   constraint varchar2(30));

(This is taken from script $ORACLE_HOME/rdbms/admin/utlexcpt.sql)

Step 2: Create your source table:

create table t2 (c2 number);

insert into t2 values (1);
insert into t2 values (2);
insert into t2 values (100);
commit;


Step 3: Create your destination table:

create table t1 (c1 number);


Step 4: Insert from source to destination
(NOTE: Make sure to disable the constraints (if any) that would cause the violation.  None here yet.)


insert into t1 select * from t2;

Nothing bad yet.

Step 5: Now, enable the constraint to check the violation.  Here assume the constraint is column C1 must be <10.

alter table t1 add constraints ck_t1 check (c1 <10)
  exceptions into mb_exceptions;


When enabling this constraints, Oracle creates a record in the exception table for every record in T1 which fails the constraint.  Note that it does not move the bad records out of T1 and the constraints still not enabled yet.

Step 6: All your bad records are now in table mb_exceptions. Just display it:

prompt Table t1:
select rowid, c1  from t1;

prompt Table mb_exceptions:
select * from mb_exceptions;


Their results are:

Table t1:
ROWID                      C1
------------------ ----------
AAQVUAACUAAAPYbAAA          1
AAQVUAACUAAAPYbAAB          2
AAQVUAACUAAAPYbAAC        100


Table mb_exceptions:

ROW_ID             OWNER   TABLE_NAME   CONSTRAINT
------------------ ------- ------------ ------------
AAQVUAACUAAAPYbAAC MDBUI   T1           CK_T1


Here you see the rowid of the bad record.  You can now issue a delete statement based on this rowid to delete this offending record.  Or you may want to save it in some other tables for further analysis, such as display these bad records:

select  t1.*
from t1, mb_exceptions e
where e.row_id = t1.rowid;

Its results are:

        C1
----------
       100

RE: Creating a simple procedure in Oracle

Isn't there a dbms_utility.write_file procedure that could be used? I haven't used it in years, but that may be a better solution (if I got the name wrong, pardon).

RE: Creating a simple procedure in Oracle

(OP)
Yes, both the dbms_output print or using exception seems to work.

Thanks guys

Paul
packermann@niku.com

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close