в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:08:12Z

1 версия

Новая страница

== Get source code of procedure and function==



<source lang="sql">
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></source>

== Query the Oracle data dictionary view USER_SOURCE==



<source lang="sql">
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></source>

== Query user_source for procedure declaration==



<source lang="sql">
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></source>

== Select the source code from USER_SOURCE==



<source lang="sql">
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.</source>

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



<source lang="sql">
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></source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/System Tables Data Dictionary/user source - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия