1 PACKAGE BODY OKC_P_UTIL AS
2 /* $Header: OKCPUTLB.pls 120.0 2005/05/26 09:27:03 appldev noship $ */
3 -- Sub-Program Units
4 /* Convert major and minor version numbers into a string for the veiws */
5 FUNCTION VERSION_STRING
6 (P_MAJOR IN NUMBER
7 ,P_MINOR IN NUMBER
8 )
9 RETURN VARCHAR2
10 IS
11 begin
12 return ( ltrim(rtrim(to_char(p_major)||'.'||to_char(p_minor))));
13 end;
14
15 /* Convert raw value to number */
16 FUNCTION RAW_TO_NUMBER
17 (P_RAWID IN RAW
18 )
19 RETURN NUMBER
20 IS
21 v_raw_str varchar2(100);
22 v_raw_int number := 0;
23 begin
24 v_raw_str := rawtohex(p_rawid);
25 for i in 1..length(v_raw_str) loop
26 v_raw_int := v_raw_int * 16;
27 v_raw_int := v_raw_int +
28 instr('0123456789ABCDEF',substr(v_raw_str,i,1))+1;
29 end loop;
30 return(v_raw_int);
31 end;
32
33 /* Execute any sql via dynamic sql */
34 FUNCTION EXECUTE_SQL
35 (P_SQL IN VARCHAR2
36 )
37 RETURN INTEGER
38 IS
39 v_cursor integer; -- the cursor
40 v_error_text varchar2(4000); -- error message
41 v_num_rows integer; -- number of rows affected by sql
42
43 begin
44 -- create savepoint
45 savepoint execute_sql_savept;
46
47 -- create cursor
48 v_cursor := dbms_sql.open_cursor;
49
50 -- parse statement
51 dbms_sql.parse(v_cursor, p_sql, DBMS_SQL.NATIVE);
52
53 -- execute
54 v_num_rows := dbms_sql.execute(v_cursor);
55
56 -- close cursor
57 dbms_sql.close_cursor(v_cursor);
58
59 -- return success
60 return v_num_rows;
61
62 exception
63 when others then
64 -- capture error message
65 v_error_text := sqlerrm;
66
67 -- rollback to before statement
68 rollback to execute_sql_savept;
69
70 -- close cursor
71 dbms_sql.close_cursor(v_cursor);
72
73 -- take care of error
74 -- do something here
75
76 -- return failure
77 return -1;
78 end;
79
80 /* Logic to run in view instead of triggers */
81 PROCEDURE INSTEAD_OF_TRG
82 IS
83 begin
84 /* should call fnd_messages, but for now call raise_application_error */
85 raise_application_error(-20000,'The API must be used for all inserts, updates, and deletes');
86 end;
87
88 -- PL/SQL Block
89 END OKC_P_UTIL;
90
[Home] [Help]