Oracle PL/SQL Tutorial/System Tables Data Dictionary/user source

Get source code of procedure and function

SQL>
SQL>
SQL> select text from user_source s, user_objects o
  2  where s.name = o.object_name and o.object_type in ("PROCEDURE" , "FUNCTION")
  3        and rownum < 50
  4  ORDER BY name, line
  5  /
TEXT
--------------------------------------------------------------------------------
procedure delete_cust
(p_Cust_no in number)
as
  l_count number;
begin
   select count(*) into l_count
      from ord
      where cust_no = p_cust_no;
   if l_count != 0 then
TEXT
--------------------------------------------------------------------------------
     raise_application_error(-20000, "cannot delete active cust");
   end if;
end;
PROCEDURE drop_if_exists(aiv_object_type in varchar2,aiv_object_name in varchar2
) is
cursor c_constraint(aiv_table_name in varchar2) is
select f.table_name,
       f.constraint_name
from   SYS.USER_CONSTRAINTS f,
       SYS.USER_CONSTRAINTS p
TEXT
--------------------------------------------------------------------------------
where  f.constraint_type = "R"
and    f.r_owner            = p.owner
and    f.r_constraint_name  = p.constraint_name
and    p.table_name         = aiv_table_name;
n_count                             number;
v_sql                                 varchar2(100);
begin
  select count(1)
  into   n_count
  from   SYS.USER_OBJECTS
  where  object_type = upper(aiv_object_type)
TEXT
--------------------------------------------------------------------------------
  and    object_name = upper(aiv_object_name);
  if n_count > 0 then
    if upper(aiv_object_type) = "TABLE" then
      for r_constraint in c_constraint(upper(aiv_object_name)) loop
        v_sql :=  "alter table "||
          r_constraint.table_name||
          " drop constraint "||
          r_constraint.constraint_name;
        begin
          execute immediate v_sql;
        exception
TEXT
--------------------------------------------------------------------------------
          when OTHERS then
            dbms_output.put_line(SQLERRM||": "||v_sql);
        end;
      end loop;
    end if;
    v_sql :=  "drop "||aiv_object_type||" "||aiv_object_name;
    begin
49 rows selected.
SQL>

Query the Oracle data dictionary view USER_SOURCE

SQL> --
SQL>
SQL> create or replace function getArea (i_rad NUMBER)
  2  return NUMBER
  3  is
  4  begin
  5    return 3.14*(i_rad**2);
  6  end;
  7  /
Function created.
SQL>
SQL>
SQL>
SQL> select text
  2  from user_source
  3  where name = "GETAREA"
  4  order by line;
 /
TEXT
-------------------------------------------------------------------------
function getArea (i_rad NUMBER)
return NUMBER
is
begin
  return 3.14*(i_rad**2);
end;
6 rows selected.
SQL>
SQL>

Query user_source for procedure declaration

SQL>
SQL> CREATE OR REPLACE PROCEDURE compile_error AS
  2       myDateTime timestamp;
  3  BEGIN
  4     SELECT systimestamp INTO myDateTime FROM dual;
  5     DBMS_OUTPUT.PUT_LINE(myDateTime);
  6  EXCEPTION
  7     WHEN OTHERS
  8     THEN
  9        DBMS_OUTPUT.PUT_LINE(SQLERRM);
 10  END;
 11  /
Procedure created.
SQL>
SQL>
SQL> SELECT line||" "||text PROCEDURE FROM user_source WHERE name = "COMPILEERROR";
PROCEDURE
--------------------------------------------------------------------------------
1 PROCEDURE CompileError
2    AS
3      v_timestamp timestamp;
4 BEGIN
5    SELECT systimestamp
6    INTO v_timestamp
7    FROM dual;
8
9    DBMS_OUTPUT.PUT_LINE(v_timestamp);
10 EXCEPTION
11    WHEN OTHERS
12    THEN
13       DBMS_OUTPUT.PUT_LINE(SQLERRM);
14 END;
14 rows selected.
SQL>

Select the source code from USER_SOURCE

SQL>
SQL> SET SERVEROUTPUT ON PAGES 9999
SQL>
SQL> CREATE OR REPLACE PROCEDURE compile_by_version
  2  IS
  3
  4  BEGIN
  5     $IF DBMS_DB_VERSION.VER_LE_10_2
  6     $THEN
  7        DBMS_OUTPUT.PUT_LINE("10.2 and under");
  8     $ELSIF DBMS_DB_VERSION.VER_LE_10_1
  9     $THEN
 10        DBMS_OUTPUT.PUT_LINE("10.1 and under");
 11     $ELSE
 12        DBMS_OUTPUT.PUT_LINE("Not 10g");
 13     $END
 14
 15  END;
 16  /
Procedure created.
SQL>
SQL> SELECT TEXT
  2  FROM USER_SOURCE
  3  WHERE NAME = "COMPILE_BY_VERSION";
TEXT
------------------------------------------------------------------------------------------------------------------------
PROCEDURE compile_by_version
IS
BEGIN
   $IF DBMS_DB_VERSION.VER_LE_10_2
   $THEN
      DBMS_OUTPUT.PUT_LINE("10.2 and under");
   $ELSIF DBMS_DB_VERSION.VER_LE_10_1
   $THEN
      DBMS_OUTPUT.PUT_LINE("10.1 and under");
   $ELSE
      DBMS_OUTPUT.PUT_LINE("Not 10g");
   $END
END;
15 rows selected.

When you do a search in the Oracle data dictionary, all object names are in uppercase

SQL>
SQL>
SQL> create or replace function getArea (i_rad NUMBER)
  2  return NUMBER
  3  is
  4  begin
  5    return 3.14*(i_rad**2);
  6  end;
  7  /
Function created.
SQL>
SQL>
SQL>
SQL> select text
  2  from user_source
  3  where name = "GETAREA"
  4  order by line;
 /
TEXT
-------------------------------
function getArea (i_rad NUMBER)
return NUMBER
is
begin
  return 3.14*(i_rad**2);
end;
6 rows selected.
SQL>
SQL>