create procedure

[Yarka]

Hi,

First, sorry for my basic questions but I am new in databases. I would like to know if when I create a procedure, I must do 'commit'. I mean:

CREATE OR REPLACE Procedure my_procedure
...
END
commit;

Also, I would like to know what view or how I can see the content of my procedure.

Thank you very much!

 

[carp]

Welcome, Yarka!

To your first question - no, you do not need to commit. CREATE, ALTER, DROP are called Data Definiition Language (DDL) statements and Oracle does an implicit commit when you execute them. Commits are needed after Data Manipulation Language (DML) statements such as INSERT, UPDATE, and DELETE.

To see your code, take a look in USER_SOURCE.

Good luck!

 

[SantaMufasa]

Yes, Yarka, Welcome !

As a supplement to Carp's excellent suggestion (to look at your procedure contents from the Oracle Data Dictionary object, USER_SOURCE), here is a sample:

set serveroutput on format wrap
create or replace procedure yarka is
begin
    dbms_output.put_line ('Welcome, Yarka.');
end;
/

Procedure created.

SQL> execute yarka
Welcome, Yarka.

PL/SQL procedure successfully completed.

SQL> desc user_source

 Name                          Null?    Type
 ----------------------------- -------- --------------
 NAME                                   VARCHAR2(30)
 TYPE                                   VARCHAR2(12)
 LINE                                   NUMBER
 TEXT                                   VARCHAR2(4000)

select text from user_source where name = 'YARKA';

TEXT
---------------------------------------------
procedure yarka is
begin
    dbms_output.put_line ('Welcome, Yarka.');
end;

4 rows selected.

The "set serveroutput on format wrap" statement is a directive to SQL*Plus to allow screen output from the "dbms_output.put_line" display procedure.

Another method that I like to use to see the Data Definition Language SQL code for any Oracle object is to query using Oracle's DBMS_METADATA.GET_DDL function:

SET LONG 1000000
SET TRIMSPOOL ON
COL X FORMAT 55000
select REPLACE(dbms_metadata.get_ddl('PROCEDURE','YARKA')||'/',' /','/') X FROM DUAL;

X
-------------------------------------------------

  CREATE OR REPLACE PROCEDURE "YARKA" is
begin
    dbms_output.put_line ('Welcome, Yarka.');
end;
/


1 row selected.

The nice thing about the above use of DBMS_METADATA.GET_DDL is that you can use it to re-create the DDL for not only a PROCEDURE, but also for a TABLE, an INDEX, a FUNCTION, ...any Oracle object. So you may wish to save the code and re-use it whenever you want Oracle to generate the code for any object.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.