Posts

Showing posts from June, 2007

Mini Tip: Synonyms

Posted in ITToolbox by Lewis The full link for the article is here . Today I am going to talk about synonyms and what you should NOT do with them. Since this is a MINI tip, it will be short and sweet. I will not cover every aspect of synonyms. A quick definition: two words that can be interchanged in a context are said to be synonymous relative to that context. In Oracle terms: An alias for a table, view, materialized view, sequence, procedure, function, package, type, Java class schema object, user-defined object type, or another synonym. So that means that instead of referencing a table like owner.table, you can create a synonym for that owner.table and reference it by the synonym name. For example, SQL> connect system@unixxe Enter password: ****** Connected. SQL> select count(*) from employees; select count(*) from employees * ERROR at line 1: ORA-00942: table or view does not exist . . SQL> select count(*) from hr.employees; . COUNT(*) ---------- 107 . SQL> create publi...

How to kill inactive session from Forms

Technology: Oracle Forms 6i, Oracle Database 9i or greater One of my friend asked how we can kill an inactive session of forms. In an Application server environment this can be attained by modifying none of the application. But as the version is not of our discussion it will not be discussed here. Here is how we have attained such a result: We have to use the D2KWUTIL.PLL. In the WHEN-NEW-FORM-INSTANCE trigger provide the following code: declare lwindow PLS_INTEGER; ltimer TIMER; begin lwindow := get_window_property(FORMS_MDI_WINDOW,WINDOW_HANDLE); Win_API_Session.Timeout_Start_Timer(lwindow); ltimer := Create_Timer('TIMER1',1000,repeat); end; In WHEN-TIMER-EXPIRED trigger provide the following code: begin if upper(get_application_property(TIMER_NAME)) = 'TIMER1' then :control.inactive_time := Win_api_session.Timeout_Get_Inactive_Time; if : control.inactive_time > 120 then Win_API_Session.timeout_delete_timer; exit_form (NO_VALIDATE); end if; end if; end; Control ...

Joins: ANSI/ISO SQL: 1999 standards

Starting from 9i, Oracle has introduced various join syntax that comply with ANSI/ISO SQL: 1999 standards. CROSS JOIN Cross Join, Cross Product or Cartesian Product represents a direct product of records in two or more tables. In other words it lists all rows from all the tables listed in the query. Each row in the first table is paired with all the rows in the second table. That is if the first table contains 20 rows and second table contains 60 rows, the output will consist of 20 * 60 = 1200 rows of data. This is how normally cross product was represented in existing syntax before Oracle 9i: select field_name from first_table, second_table; The ANSI standard of cross joining two tables is as follows: select field_name from first_table CROSS JOIN second_table; NATURAL JOIN A natural join performs a join for all columns with matching names in the tables present in the query list. This is the ANSI variant of giving join conditions for all matching fields explicitly. select field_name f...