Creating a simple procedure in Oracle

Packermann · May 22, 2000

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?

yaffle · May 26, 2000

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;

Packermann · May 26, 2000

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?

yaffle · May 26, 2000

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.

MikeLacey · May 26, 2000

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 <a href=mailto:Mike_Lacey@Cargill.Com>Mike_Lacey@Cargill.Com</a> <a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a> 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?"

yaffle · May 26, 2000

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.

Guest_imported · May 30, 2000

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

Guest_imported · Jun 5, 2000

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).

Packermann · Jun 5, 2000

Yes, both the dbms_output print or using exception seems to work. Thanks guys Paul <a href=mailto

ackermann@niku.com>packermann@niku.com</a> <a href= > </a>

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Creating a simple procedure in Oracle

Packermann

Programmer

yaffle

MIS

Packermann

Programmer

yaffle

MIS

MikeLacey

MIS

yaffle

MIS

Guest_imported

New member

Guest_imported

New member

Packermann

Programmer

Similar threads

Part and Inventory Search

Sponsor