FUNCTION: DVSYS.DVLANG

Source

1 FUNCTION       dvlang(lid IN NUMBER, langtab_no IN NUMBER)
2 RETURN VARCHAR2
3 AS
4   l_lcnt NUMBER default 0;
5   l_lang VARCHAR2(3);
6   l_tab  VARCHAR2(30);
7   l_stmt VARCHAR2(256);
8   l_cursor    int;
9   l_status    int;
10 BEGIN
11   l_lang := LOWER(SYS_CONTEXT('USERENV','LANG'));
12   l_tab :=
13     CASE langtab_no
14       WHEN 1 THEN 'CODE_T$'
15       WHEN 2 THEN 'FACTOR_T$'
16       WHEN 3 THEN 'FACTOR_TYPE_T$'
17       WHEN 4 THEN 'RULE_T$'
18       WHEN 5 THEN 'RULE_SET_T$'
19       WHEN 6 THEN 'REALM_T$'
20     END;
21 
22   l_stmt := 'SELECT COUNT(*) FROM ' || l_tab || ' WHERE id# = :id AND language = :lang';
23   l_cursor := sys.dbms_sql.open_cursor;
24   sys.dbms_sql.parse( l_cursor, l_stmt, sys.dbms_sql.native );
25   sys.dbms_sql.bind_variable( l_cursor, ':id', lid );
26   sys.dbms_sql.bind_variable( l_cursor, ':lang', l_lang );
27   sys.dbms_sql.define_column( l_cursor, 1, l_lcnt );
28   l_status := sys.dbms_sql.execute( l_cursor );
29   if ( sys.dbms_sql.fetch_rows(l_cursor) > 0 )
30     then
31         sys.dbms_sql.column_value( l_cursor, 1, l_lcnt );
32   end if;
33   sys.dbms_sql.close_cursor(l_cursor);
34 
35   if (l_lcnt = 0) then
36     return 'us';
37   else
38     return l_lang;
39   end if;
40 END;