в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T10:01:11Z

1 версия

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

==Check user procedure status and text==



<source lang="sql">

SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE Simple AS
2 v_Counter NUMBER;
3 BEGIN
4 v_Counter := 7;
5 END Simple;
6 /
Procedure created.
SQL>
SQL> COLUMN object_name format a20
SQL> COLUMN line format 9999
SQL> COLUMN position format 99999
SQL> COLUMN text format a59
SQL>
SQL>
SQL>
SQL> SELECT object_name, object_type, status
2 FROM user_objects
3 WHERE object_name = "SIMPLE";
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- ----------
SIMPLE PROCEDURE VALID
1 row selected.
SQL>
SQL> SELECT text FROM user_source WHERE name = "SIMPLE" ORDER BY line;
TEXT
-----------------------------------------------------------
PROCEDURE Simple AS
v_Counter NUMBER;
BEGIN
v_Counter := 7;
END Simple;
5 rows selected.
SQL>
SQL> SELECT line, position, text FROM user_errors WHERE name = "SIMPLE" ORDER BY sequence;
no rows selected
SQL>
SQL> CREATE OR REPLACE PROCEDURE Simple AS
2 v_Counter NUMBER;
3 BEGIN
4 v_Counter := 7
5 END Simple;
6 /
Warning: Procedure created with compilation errors.
SQL>
SQL>
SQL> SELECT text FROM user_source WHERE name = "SIMPLE" ORDER BY line;
TEXT
-----------------------------------------------------------
PROCEDURE Simple AS
v_Counter NUMBER;
BEGIN
v_Counter := 7
END Simple;
5 rows selected.
SQL>

</source>

==Get code from user_source table==



<source lang="sql">

SQL>
SQL> prompt set define off
set define off
SQL> select decode( type||"-"||to_char(line,"fm99999"),
2 "PACKAGE BODY-1", "/"||chr(10),
3 null) ||
4 decode(line,1,"create or replace ", "" ) ||
5 text text
6 from user_source
7 where name = upper("&1")
8 order by type, line;
Enter value for 1: prompt /
SQL> prompt set define on
set define on
SQL>
SQL> set feedback on
SQL> set heading on
SQL> set termout on
SQL> set linesize 100
SQL>

</source>

==Query user_source table for a stored procedure name and procedure type==



<source lang="sql">

SQL>
SQL> create table myLogTable(
2 username varchar2(30),
3 date_time timestamp,
4 message varchar2(4000) );
Table created.
SQL>
SQL> create or replace procedure log_message( p_message varchar2 ) as
2 pragma autonomous_transaction;
3 begin
4 insert into myLogTable( username, date_time, message )
5 values ( user, current_date, p_message );
6 commit;
7 end log_message;
8 /
Procedure created.
Procedure created.
SQL> select text
2 from user_source
3 where name = "LOG_MESSAGE"
4 and type = "PROCEDURE"
5 order by line;
TEXT
-----------------------------------------------------------------------------------------
procedure log_message( p_message varchar2 ) as
pragma autonomous_transaction;
begin
insert into myLogTable( username, date_time, message )
values ( user, current_date, p_message );
commit;
end log_message;
7 rows selected.
SQL>
SQL> drop table myLogTable;
Table dropped.
SQL>
SQL>

</source>

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

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

в 13:45, 26 мая 2010

Admin: 1 версия