November 2013

Steps to Install Forms 11g server on Oracle Linux

This is just a note for myself, but if it helps anyone out, have at it: Forms and Reports 11gR2 (11.1.2.1.0) installation & configuration using Oracle Linux 6.4 on VB box Software Required: 1. WebLogic 11gR1 generic 64-bit platforms version – wls1036_generic.jar 1b. Welogic batch 10.3.6.0.3 – p14736139_1036_Generic.zip 2. JRockit JDK latest Linux x86-64 version […]

Read more →

Read an Oracle Ref Cursor Dynamically

DECLARE    v_ref_cursor SYS_REFCURSOR;  v_cursor_id                  INTEGER;  v_column_count               NUMBER;  v_desc_tab                   DBMS_SQL.desc_tab;  v_fetch                      NUMBER;      v_date_value                 DATE;  v_number_value               NUMBER;  v_char_value                 VARCHAR2(4000);     BEGIN    OPEN v_ref_cursor FOR    SELECT *      FROM user_tables     WHERE ROWNUM  0       THEN       FOR i IN 1..v_column_count LOOP        IF v_desc_tab (i).col_type IN (12,178,179,180,181,231)           THEN           DBMS_SQL.COLUMN_VALUE (v_cursor_id, i, v_date_value);           dbms_output.put_line(‘v_date_value: ‘||v_date_value);        ELSIF v_desc_tab (i).col_type IN (2)           THEN           DBMS_SQL.COLUMN_VALUE (v_cursor_id, i, v_number_value);           dbms_output.put_line(‘v_number_value: ‘||v_number_value);        ELSE           DBMS_SQL.COLUMN_VALUE (v_cursor_id, i, v_char_value);           dbms_output.put_line(‘v_char_value: ‘||v_char_value);        END IF;              END LOOP;   END IF; END LOOP;    DBMS_SQL.CLOSE_CURSOR(v_cursor_id); END;

Read more →

Clear a single query from the shared pool

When the cost based optimizer (CBO) comes up with a less optimal plan than expected (probably because of an unfortunate case of bind variable peeking), the solution is to flush the query from the shared pool. Instead of clearing the entire shared pool and impacting the entire application, that query can be cleared: –Get the […]

Read more →

DBMS_METADATA – how to recreate the DDL for PL/SQL objects

DBMS_METADATA.get_ddl works great when looking for the DDL of a table or a view: select dbms_metadata.get_ddl(‘VIEW’,’TEST_DDL_VW’) from dual; CREATE OR REPLACE FORCE VIEW “GBONYATA”.”TEST_DDL_VW” (“CHAR_COL”, “NUM_COL”, “DATE_COL”) AS select char_col, num_col, date_col from test_ddl But not so much for some objects: select dbms_metadata.get_ddl(‘SCHEDULE’,’DEMO_SCHEDULE’) from dual; ORA-31600: invalid input value SCHEDULE for parameter OBJECT_TYPE in function […]

Read more →