Oracle knowledge

Oracle Forms 11g Configuration / log Files

noreply@blogger.com (Unknown) — Mon, 03 Nov 2014 07:21:00 +0000

formsweb.cfg
DOMAIN_HOME/config/fmwconfig/servers/WLS_FORMS/applications/formsapp_11.1.2/config

default.env

DOMAIN_HOME/config/fmwconfig/servers/WLS_FORMS/applications/formsapp_11.1.2/config

base(jpi).htm

DOMAIN_HOME/config/fmwconfig/servers/WLS_FORMS/applications/formsapp_11.1.2/config

registry.dat
DOMAIN_HOME/config/FormsComponent/forms/registry/oracle/forms/registry

frmweb.res
UNIX:

ORACLE_INSTANCE/config/FormsComponent/forms/admin/resource/

Windows:

ORACLE_INSTANCE/config/FormsComponent/forms

jvmcontroller.cfg

ORACLE_INSTANCE/config/FRComponent/frcommon/tools/jvm/

forms_ohs.conf

ORACLE_INSTANCE/config/OHS/ohs1/moduleconf

WLS_FORMS.log
DOMAIN_HOME/servers/WLS_FORMS/logs

WLS_FORMS.out
DOMAIN_HOME/servers/WLS_FORMS/logs

Forms trace files
ORACLE_INSTANCE/FormsComponent/forms/trace

Listener Servlet logs
DOMAIN_HOME/servers/WLS_FORMS/logs

cauprefs.ora 11g

noreply@blogger.com (Unknown) — Wed, 06 Aug 2014 13:05:00 +0000

Two Changes to do in 64 bit environmet in:
C:\Oracle\MW\FRinst\config\FRComponent\frcommon\tools\admin\cauprefs.ora

1. Change the values of the attributes to:

Reports.root_x = 0
Reports.root_y = 0

2. Change the attribute:

Reports.PluggableDataSourceFactories = ()

Open reports job - excel, delimeted ...

noreply@blogger.com (Unknown) — Wed, 06 Aug 2014 08:52:00 +0000

web.show_document(v_server_url || '/reports/rwservlet/getjobid' || v_jobid || '?server=' || :GLOBAL.rp2rroReportServer || '&mimetype=application/vnd.ms-excel', '_blank');

Oracle Forms 11g R2 Installation Win64, Getting Ready

noreply@blogger.com (Unknown) — Tue, 08 Jan 2013 12:47:00 +0000

Steps to prepare the machine:

Install:
Notepad++
WinZip / WinRAR / 7Zip
Define TEMP / TMP environment variables
Make Sure PATH have no spaces or ()

Enable UAC:

Run secpol.msc from the command prompt.
Select Security Settings > Local Policies > Security Options.
Set the User Account Control:Run all Administrator in Admin Approval Mode option to enabled.
Restart the host.

Download those files (login to OTN and Metalink first):
Install JDK64

http://www.oracle.com/technetwork/java/javase/downloads/jdk7-downloads-1880260.html

Weblogic 10.3.6
http://www.oracle.com/technetwork/middleware/ias/downloads/wls-main-097127.html

Forms 11.1.2.2
http://www.oracle.com/technetwork/developer-tools/forms/downloads/index.html
Windows (64-bit)

WebUtil:
How to Configure Webutil in Forms 11g
Jacobe for 11g:
http://sourceforge.net/projects/jacob-project/files/jacob-project/1.14.3/jacob-1.14.3.zip/download

sign_webutil.bat: jarsigner: attempt to rename ... failed

noreply@blogger.com (Unknown) — Sun, 06 Jan 2013 14:55:00 +0000

Try to close the Forms Builder.

Weblogic EM timeout

noreply@blogger.com (Unknown) — Sun, 06 Jan 2013 11:52:00 +0000

In the Console:
"Domain Structure" box ->
"em" ->
"Configuration" tab ->
"Session Timeout (in seconds):" = set the value as you like.
Save
"OK" in the "Save Deployment Plan" page
Activate Changes

Enable the next-generation Java Plug-in

noreply@blogger.com (Unknown) — Fri, 16 Nov 2012 11:18:00 +0000

In most cases it is best to deSelect the "Enable the next-generation Java Plug-in" check-box at the Java control panel -> Advaned Tab -> Java Plug-in -> Enable the next-generation Java Plug-in

You can do it automatically:

"C:\Program Files\Java\jre6\bin\ssvagent.exe" -high -jpisetup –old

or revert it back by:

"C:\Program Files\Java\jre6\bin\ssvagent.exe" -high -jpisetup –new

The application's digital signature cannot be verified

noreply@blogger.com (Unknown) — Tue, 13 Mar 2012 15:56:00 +0000

Walkaround:

1) Import the certificate into the user repository

Accept the message on one PC

Copy "trusted.certs" (C:\Documents and Settings\\Application Data\Sun\Java\Deployment\security)

Paste in all profiles on the new PC's

Warning... will overwrite content that individuals may have added to their certificate repository.

2) Import the certificate into the browser repository

Accept the message on one PC

Launch the java control panel -> security tab -> certificates button

Select the certificate & Export the certificate as .cer

In the problematic PC Import the certificate into the browser repository: Internet Explorer -> Tools -> Content -> Certificates -> Trusted Publishers -> Import.

3) Sign jar files using a known 'trusted' certificate signing authority.

See Note 1207184.1 Enhanced Signing of Oracle E-Business Suite JAR Files in Oracle Support

4) Change the order of the JAR files

A workaround is to change the order of the jar files listed in webutilArchive parameter in the formsweb.cfg file. Ensure jacob.jar is listed before frmwebutil.jar. (Please let me know if it worked)

Java has discovered application components that could indicate a security concern

noreply@blogger.com (Unknown) — Wed, 29 Feb 2012 20:52:00 +0000

Here is where I went wrong, maybe you can benefit:

I had an icon.jar file and I needed to add one more icon so I just went and used winrar without thinking.

What you need to do is to use the jar command to wrap up the file:

JAVA_HOME\bin\jar cvf icnFileName.jar *.gif

Don't forget to resign the file.

Tracing A Forms User

noreply@blogger.com (Unknown) — Thu, 23 Feb 2012 10:08:00 +0000

Tracing A Forms User 10g - quick and dirty

Let the user run the regular link + record=forms&tracegroup=0-199
ie:
http://machine:port/forms/frmservlet?config=myConfig&record=forms&tracegroup=0-199

You will get a trace file in the following location:

%ORACLE_HOME%\forms\trace\forms_xxx.trc where xxx is the forms session ID.

Open CMD window and do the following:

set ORACLE_HOME=[oracle home location]

set PATH=%ORACLE_HOME%\jdk\bin;%PATH%

set CLASSPATH=%ORACLE_HOME%\jdbc\lib\classes12.zip;%ORACLE_HOME%\forms\java\frmxlate.jar

java oracle.forms.diagnostics.Xlate datafile=%ORACLE_HOME%\forms\trace\forms_xxx.trc outputfile=%ORACLE_HOME%\forms\trace\html_xxx.html outputclass=WriteOutHTMLoutputclass parameter:
WriteOutHTML = HTML output

WriteOut = XML output

you will get a file html_xxx.html in your %ORACLE_HOME%\forms\trace directory.

In grater details:

http://docs.oracle.com/cd/B25016_04/doc/dl/web/B14032_03/tracing.htm#i1012933

ShowJobs parameters for Oracle Reports

noreply@blogger.com (Unknown) — Mon, 20 Feb 2012 09:28:00 +0000

ShowJobs parameters for Oracle Reports

http://[serverName]/reports/rwservlet/showjobs?queuetype=past&count=100000&startrow=0&statusformat=xml

/reports/rwservlet/showjobs[n] - The refresh number n is optional. When it is specified, the report's queue status will be updated every n seconds.

[?]

[server=server_name]

[&authid=username/password]

[&queuetype={current|past|future}]

[&startrow=start_position_in_job_queue]

[&count=number_of_jobs_to_display]

[&statusformat={html|xml|xmldtd}]

Separate the Java client instances

noreply@blogger.com (Unknown) — Mon, 20 Feb 2012 09:17:00 +0000

Those parameters will separate the Java client instances so that they will not depend on each other:

[PARAM NAME="separate_jvm" value="true"]

separate_jvm="true"

Oracle forms Fonts explained

noreply@blogger.com (Unknown) — Mon, 16 Jan 2012 10:41:00 +0000

Oracle forms Fonts explained:
http://docs.oracle.com/javase/1.3/docs/guide/intl/addingfonts.html

Oracle Forms Hierarchical Tree

noreply@blogger.com (Unknown) — Mon, 05 Dec 2011 07:42:00 +0000

SELECT 1, LEVEL, last_name ||' '|| first_name , '', employee_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id

Select 5 columns:
I - Status (0, 1)
II - Hierarchical Level (Oracle LEVEL pseudo column)
III - Label
IV - Icon file name (or null)
V - Value

Get the new frmwebutil.jar after 10.1.2.3 patch

noreply@blogger.com (Unknown) — Thu, 13 Jan 2011 09:49:00 +0000

Find the patch instalation directory

open CMD and go to:
[patchDir]\Disk1\stage\Patches\oracle.developer.forms.builder\10.1.2.3.0\1\DataFiles

run:
[$ORACLE_HOME$]\jdk\bin\jar xf webutil2.2.jar frmwebutil.jar

rename frmwebutil.jar0 to frmwebutil.jar

backup:
[$ORACLE_HOME$]\forms\java\frmwebutil.jar

copy the new frmwebutil.jar from
[patchDir]\Disk1\stage\Patches\oracle.developer.forms.builder\10.1.2.3.0\1\DataFiles
to
[$ORACLE_HOME$]\forms\java\

in the CMD go to:
[$ORACLE_HOME$]\jdk\bin

make sure to take down the apps server (using the EM)

run:
[$ORACLE_HOME$]\forms\webutil\sign_webutil.bat [$ORACLE_HOME$]\forms\java\frmwebutil.jar

restart the app server

Change ias_admin password

noreply@blogger.com (Unknown) — Mon, 29 Nov 2010 10:15:00 +0000

Open file in:

%ORACLE_HOME%\sysman\j2ee\config

file name:

jazn-data.xml

find: ias_admin credentials

Make sure you put ! befor the new password.

Restart the EM:

%ORACLE_HOME%\bin\emctl stop iasconsole

%ORACLE_HOME%\bin\emctl start iasconsole

Find Bind Data

noreply@blogger.com (Unknown) — Tue, 24 Aug 2010 09:55:00 +0000

10g:
SELECT sql_text, NAME, value_string, datatype_string
FROM v$sql_bind_capture
JOIN v$sql
USING (hash_value)
WHERE sql_text LIKE '%select *%';

ClientDPI

noreply@blogger.com (Unknown) — Thu, 29 Apr 2010 08:51:00 +0000

ClientDPI

Specifies the dots per inch (DPI) and overrides the DPI setting returned by the JVM, allowing you to manage varying DPI settings per platform. For example, a form developed on the Win32 platform may not display properly on the UNIX platform due to varying DPI values. The clientDPI value can be any positive integer. Oracle recommends that you use an integer between 50 and 200.

Remove an Oracle Reports server

noreply@blogger.com (Unknown) — Mon, 26 Oct 2009 14:59:00 +0000

Assume %OMH% is Oracle Middle Tier Home

Go to %OMH% /sysman/emd

Open file targets.xml for editing.

Check for [Target] tag where its attribute "NAME" should matches as follows:
“[ias_instance_name]_Reports_Server: [reports_server_to_be_removed]”
In our example it is oracleas.dbserver_Reports_anand
Where
"oracleas.dbserver" is ias_instance_name
"anand" is reports server name
Select from Starting Tag [Target..] to ending tag [/Target] and delete selected lines.
[Target TYPE=”oracle_repserv” NAME=”[ias_instance_name]_Reports_Server: [reports_server_to_be_removed]” …]…[/Target]

Save and Close targets.xml file.

Now open console and go to %OMH%/dcm/bin

Give following commands to update and resync console with new configuration:

1. dcmctl updateconfig -ct opmn -v -d
2. dcmctl resyncinstance -v -d

Once done without any error, go to %OMH%/bin
Give following commands to restart the console:

1. emctl stop iasconsole
2. emctl start iasconsole
That's it, Report Server is removed now!

You can also delete from:
ORACLE_HOME/opmn/conf/opmn.xml
the:
ias-component id="MyRepServerName" .....

Create a New Report Server

noreply@blogger.com (Unknown) — Mon, 07 Sep 2009 14:28:00 +0000

%OMH% = Oracle Middle Tier Home
Open command prompt:
C:\>rwserver server=%newreportserver_name% start
Shut down the opmn:
C:\>%OMH%\opmn\bin\opmnctl stopall
Add new server target to opmn.xlm
C:\>%OMH%\bin\addNewServerTarget.bat %newreportserver_name%
The DOS window will close automatically.

Open command prompt again:

Update the config with new settings
C:\>%OMH%\dcm\bin\dcmctl.bat updateconfig -ct opmn -v -d
C:\>%OMH%\dcm\bin\dcmctl.bat resyncinstance -v -d
Close the report server window if it's opened

Start the opmnC:\>%OMH%\opmn\bin\opmnctl startall

Find the Binary value of a number (<1024)

noreply@blogger.com (Unknown) — Thu, 11 Jun 2009 08:52:00 +0000

SELECT e.val,
nullif(sign(bitand(e.val, power(2, 0))), 0) b0,
nullif(sign(bitand(e.val, power(2, 1))), 0) b1,
nullif(sign(bitand(e.val, power(2, 2))), 0) b2,
nullif(sign(bitand(e.val, power(2, 3))), 0) b3,
nullif(sign(bitand(e.val, power(2, 4))), 0) b4,
nullif(sign(bitand(e.val, power(2, 5))), 0) b5,
nullif(sign(bitand(e.val, power(2, 6))), 0) b6,
nullif(sign(bitand(e.val, power(2, 7))), 0) b7,
nullif(sign(bitand(e.val, power(2, 8))), 0) b8
FROM (SELECT 55 val
FROM dual) e;

recompile function by - Solomon Yakobson

noreply@blogger.com (Unknown) — Sun, 07 Jun 2009 09:23:00 +0000

CREATE OR REPLACE FUNCTION recompile(o_owner IN VARCHAR2 := '%',

o_name IN VARCHAR2 := '%',

o_type IN VARCHAR2 := '%',

o_status IN VARCHAR2 := 'INVALID',

display IN BOOLEAN := TRUE)

RETURN NUMBER AUTHID CURRENT_USER IS

-- _____________________________________________________________________

--

--
Recompile Utility

--
____________________________________________________________________

--

-- FILE: recompile.sql

-- TITLE: Recompile Utility

-- TYPE: ORACLE PL/SQL Stored Function

-- VERSION: 2.0

-- CREATED: August 3, 1998

-- AUTHOR: Solomon Yakobson

-- WARNING:

-- SCOPE: Recompile Utility can be used for Oracle 7.3 and above object compilation.

--

-- MODIFICATION

-- HISTORY: September 9, 1998 - fixed obj_cursor to include objects

-- with no dependencies.

--

-- May 12, 1999 - fix for DBMS_SQL behavior change in

-- Oracle 8 (most likely it is a bug).

-- If object recompilation has errors,

-- ORACLE 8 DBMS_SQL raises exception:

-- ORA-24333: success with compilation

-- error, followed by host environment

-- (e.g. SQL*Plus) internal error and

-- Unsafe to proceed message.

--

-- May 12, 1999 - added COMPILE_ERRORS return code.

--

-- May 12, 1999 - added TYPE and TYPE BODY objects.

--

-- Jan 10, 2006 - upgraded by Steven Feuerstein

--

-- DESCRIPTION: Recompile Utility is designed to compile the following

-- types of objects:

--

-- PROCEDURE (ORACLE 7 & 8),

-- FUNCTION (ORACLE 7 & 8),

-- PACKAGE - specification and body (ORACLE 7 & 8),

-- PACKAGE BODY - body only (ORACLE 7 & 8),

-- TRIGGER (ORACLE 7 & 8),

-- VIEW (ORACLE 7 & 8),

-- TYPE - specification only (ORACLE 8),

-- TYPE BODY - body only (ORACLE 8).

--

-- Objects are recompiled based on object dependencies and

-- therefore compiling all requested objects in one path.

-- Recompile Utility skips every object which is either of

-- unsupported object type or depends on INVALID object(s)

-- outside of current request (which means we know upfront

-- compilation will fail anyway). If object recompilation

-- is not successful, Recompile Utility continues with the

-- next object. Recompile Utility has five parameters:

--

-- o_owner - IN mode parameter is a VARCHAR2 defining

-- owner of to be recompiled objects. It

-- accepts operator LIKE widcards. Backslash

-- (\) is used for escaping wildcards. If

-- omitted, parameter defaults to USER.

-- o_name - IN mode parameter is a VARCHAR2 defining

-- names of to be recompiled objects. It

-- accepts operator LIKE widcards. Backslash

-- (\) is used for escaping wildcards. If

-- omitted, it defaults to '%' - any name.

-- o_type - IN mode parameter is a VARCHAR2 defining

-- types of to be recompiled objects. It

-- accepts operator LIKE widcards. Backslash

-- (\) is used for escaping wildcards. If

-- omitted, it defaults to '%' - any type.

-- o_status - IN mode parameter is a VARCHAR2 defining

-- status of to be recompiled objects. It

-- accepts operator LIKE widcards. Backslash

-- (\) is used for escaping wildcards. If

-- omitted, it defaults to 'INVALID'.

-- display - IN mode parameter is a BOOLEAN defining

-- whether object recompile status is written

-- to DBMS_OUTPUT buffer. If omitted, it

-- defaults to TRUE.

--

-- Recompile Utility returns the following values or their

-- combinations:

--

-- 0 - Success. All requested objects are recompiled and

-- are VALID.

-- 1 - INVALID_TYPE. At least one of to be recompiled

-- objects is not of supported object type.

-- 2 - INVALID_PARENT. At least one of to be recompiled

-- objects depends on an invalid object outside of

-- current request.

-- 4 - COMPILE_ERRORS. At least one of to be recompiled

-- objects was compiled with errors and is INVALID.

--

-- If parameter display is set to TRUE, Recompile Utility

-- writes the following information to DBMS_OUTPUT buffer:

--

-- RECOMPILING OBJECTS

--

-- Object Owner is o_owner

-- Object Name is o_name

-- Object Type is o_type

-- Object Status is o_status

--

-- TTT OOO.NNN is recompiled. Object status is SSS.

-- TTT OOO.NNN references invalid object(s) outside of

-- this request.

-- OOO.NNN is TTT and can not be recompiled.

--

-- where o_owner is parameter o_owner value, o_name is

-- parameter o_name value, o_type is parameter o_type

-- value and o_status is is parameter o_status value. TTT

-- is object type, OOO is object owner, NNN is object name

-- and SSS is object status after compilation.

--

-- NOTES: If parameter display is set to TRUE, you MUST ensure

-- DBMS_OUTPUT buffer is large enough for produced output.

-- Otherwise Recompile Utility will not recompile all the

-- objects. If used in SQL*Plus, issue:

--

-- SET SERVEROUTPUT ON SIZE xxx FORMAT WRAPPED

--

-- FORMAT WRAPPED is needed for text alignment.

-- ______________________________________________________________________

--

-- Exceptions

successwithcompilationerror EXCEPTION;

PRAGMA EXCEPTION_INIT(successwithcompilationerror, -24344);

-- Return Codes

invalid_type CONSTANT INTEGER := 1;

invalid_parent CONSTANT INTEGER := 2;

compile_errors CONSTANT INTEGER := 4;

cnt NUMBER;

dyncur INTEGER;

type_status INTEGER := 0;

parent_status INTEGER := 0;

recompile_status INTEGER := 0;

object_status VARCHAR2(30);

CURSOR invalid_parent_cursor(oowner VARCHAR2, oname VARCHAR2, otype VARCHAR2, ostatus VARCHAR2, OID NUMBER) IS

SELECT /*+ RULE */

o.object_id

FROM public_dependency d, all_objects o

WHERE d.object_id = OID

AND o.object_id = d.referenced_object_id

AND o.status != 'VALID'

MINUS

SELECT /*+ RULE */

object_id

FROM all_objects

WHERE owner LIKE upper(oowner)

AND object_name LIKE upper(oname)

AND object_type LIKE upper(otype)

AND status LIKE upper(ostatus);

CURSOR recompile_cursor(OID NUMBER) IS

SELECT /*+ RULE */

'ALTER '

decode(object_type,

'PACKAGE BODY',

'PACKAGE',

'TYPE BODY',

'TYPE',

object_type)

' '

owner

'.'

object_name

' COMPILE '

decode(object_type,

'PACKAGE BODY',

' BODY',

'TYPE BODY',

'BODY',

'TYPE',

'SPECIFICATION',

'')

decode(object_type, 'VIEW', '', ' REUSE SETTINGS') stmt,

object_type,

owner,

object_name

FROM all_objects

WHERE object_id = OID;

recompile_record recompile_cursor%ROWTYPE;

CURSOR obj_cursor(oowner VARCHAR2, oname VARCHAR2, otype VARCHAR2, ostatus VARCHAR2) IS

SELECT /*+ RULE */

MAX(LEVEL) dlevel, object_id

FROM sys.public_dependency

START WITH object_id IN

(SELECT object_id

FROM all_objects

WHERE owner LIKE upper(oowner)

AND object_name LIKE upper(oname)

AND object_type LIKE upper(otype)

AND status LIKE upper(ostatus))

CONNECT BY NOCYCLE object_id = PRIOR referenced_object_id

GROUP BY object_id

HAVING MIN(LEVEL) = 1

UNION ALL

SELECT 1 dlevel, object_id

FROM all_objects o

WHERE owner LIKE upper(oowner)

AND object_name LIKE upper(oname)

AND object_type LIKE upper(otype)

AND status LIKE upper(ostatus)

AND NOT EXISTS (SELECT 1

FROM sys.public_dependency d

WHERE d.object_id = o.object_id)

ORDER BY 1 DESC;

TYPE integer_tt IS TABLE OF PLS_INTEGER INDEX BY BINARY_INTEGER;

l_dlevel integer_tt;

l_object_id integer_tt;

CURSOR status_cursor(OID NUMBER) IS

SELECT /*+ RULE */

status

FROM all_objects

WHERE object_id = OID;

BEGIN

-- Recompile requested objects based on their dependency levels.

IF display THEN

dbms_output.put_line(chr(0));

dbms_output.put_line(' RECOMPILING OBJECTS');

dbms_output.put_line(chr(0));

dbms_output.put_line(' Object Owner is '

o_owner);

dbms_output.put_line(' Object Name is '

o_name);

dbms_output.put_line(' Object Type is '

o_type);

dbms_output.put_line(' Object Status is '

o_status);

dbms_output.put_line(chr(0));

END IF;

dyncur := dbms_sql.open_cursor;

OPEN obj_cursor(o_owner, o_name, o_type, o_status);

FETCH obj_cursor BULK COLLECT

INTO l_dlevel, l_object_id;

FOR indx IN 1 .. l_dlevel.COUNT LOOP

OPEN recompile_cursor(l_object_id(indx));

FETCH recompile_cursor

INTO recompile_record;

CLOSE recompile_cursor;

-- We can recompile only Functions, Packages, Package Bodies,

-- Procedures, Triggers, Views, Types and Type Bodies.

IF recompile_record.object_type IN

('FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'TRIGGER',

'VIEW', 'TYPE', 'TYPE BODY') THEN

-- There is no sense to recompile an object that depends on

-- invalid objects outside of the current recompile request.

OPEN invalid_parent_cursor(o_owner,

o_name,

o_type,

o_status,

l_object_id(indx));

FETCH invalid_parent_cursor

INTO cnt;

IF invalid_parent_cursor%NOTFOUND THEN

-- Recompile object.

BEGIN

dbms_sql.parse(dyncur, recompile_record.stmt, dbms_sql.native);

EXCEPTION

WHEN successwithcompilationerror THEN

NULL;

END;

OPEN status_cursor(l_object_id(indx));

FETCH status_cursor

INTO object_status;

CLOSE status_cursor;

IF display THEN

dbms_output.put_line(recompile_record.object_type

' '

recompile_record.owner

'.'

recompile_record.object_name

' is recompiled. Object status is '

object_status

'.');

END IF;

IF object_status <> 'VALID' THEN

recompile_status := compile_errors;

END IF;

ELSE

IF display THEN

dbms_output.put_line(recompile_record.object_type

' '

recompile_record.owner

'.'

recompile_record.object_name

' references invalid object(s)'

' outside of this request.');

END IF;

parent_status := invalid_parent;

END IF;

CLOSE invalid_parent_cursor;

ELSE

IF display THEN

dbms_output.put_line(recompile_record.owner

'.'

recompile_record.object_name

' is a '

recompile_record.object_type

' and can not be recompiled.');

END IF;

type_status := invalid_type;

END IF;

END LOOP;

dbms_sql.close_cursor(dyncur);

RETURN type_status + parent_status + recompile_status;

EXCEPTION

WHEN OTHERS THEN

IF obj_cursor%ISOPEN THEN

CLOSE obj_cursor;

END IF;

IF recompile_cursor%ISOPEN THEN

CLOSE recompile_cursor;

END IF;

IF invalid_parent_cursor%ISOPEN THEN

CLOSE invalid_parent_cursor;

END IF;

IF status_cursor%ISOPEN THEN

CLOSE status_cursor;

END IF;

IF dbms_sql.is_open(dyncur) THEN

dbms_sql.close_cursor(dyncur);

END IF;

RAISE;

END;

/

sys_context - userenv

noreply@blogger.com (Unknown) — Tue, 21 Apr 2009 08:13:00 +0000

SELECT sys_context('USERENV', 'ACTION') action,
sys_context('USERENV', 'AUDITED_CURSORID') audited_cursorid,
sys_context('USERENV', 'AUTHENTICATED_IDENTITY') authenticated_identity,
sys_context('USERENV', 'AUTHENTICATION_DATA') authentication_data,
sys_context('USERENV', 'AUTHENTICATION_METHOD') authentication_method,
sys_context('USERENV', 'BG_JOB_ID') bg_job_id,
sys_context('USERENV', 'CLIENT_IDENTIFIER') client_identifier,
sys_context('USERENV', 'CLIENT_INFO') client_info,
sys_context('USERENV', 'CURRENT_BIND') current_bind,
sys_context('USERENV', 'CURRENT_SCHEMA') "current_schema",
sys_context('USERENV', 'CURRENT_SCHEMAID') current_schemaid,
sys_context('USERENV', 'CURRENT_SQL') current_sql,
sys_context('USERENV', 'CURRENT_SQL_LENGTH') current_sql_length,
sys_context('USERENV', 'DB_DOMAIN') db_domain,
sys_context('USERENV', 'DB_NAME') db_name,
sys_context('USERENV', 'DB_UNIQUE_NAME') db_unique_name,
sys_context('USERENV', 'ENTRYID') entryid,
sys_context('USERENV', 'ENTERPRISE_IDENTITY') enterprise_identity,
sys_context('USERENV', 'FG_JOB_ID') fg_job_id,
sys_context('USERENV', 'GLOBAL_CONTEXT_MEMORY') global_context_memory,
sys_context('USERENV', 'GLOBAL_UID') global_uid,
sys_context('USERENV', 'HOST') host,
sys_context('USERENV', 'IDENTIFICATION_TYPE') identification_type,
sys_context('USERENV', 'INSTANCE') "instance",
sys_context('USERENV', 'INSTANCE_NAME') instance_name,
sys_context('USERENV', 'IP_ADDRESS') ip_address,
sys_context('USERENV', 'ISDBA') isdba,
sys_context('USERENV', 'LANG') lang,
sys_context('USERENV', 'LANGUAGE') "language",
sys_context('USERENV', 'MODULE') "module",
sys_context('USERENV', 'NETWORK_PROTOCOL') network_protocol,
sys_context('USERENV', 'NLS_CALENDAR') nls_calendar,
sys_context('USERENV', 'NLS_CURRENCY') nls_currency,
sys_context('USERENV', 'NLS_DATE_FORMAT') nls_date_format,
sys_context('USERENV', 'NLS_DATE_LANGUAGE') nls_date_language,
sys_context('USERENV', 'NLS_SORT') nls_sort,
sys_context('USERENV', 'NLS_TERRITORY') nls_territory,
sys_context('USERENV', 'OS_USER') os_user,
sys_context('USERENV', 'POLICY_INVOKER') policy_invoker,
sys_context('USERENV', 'PROXY_ENTERPRISE_IDENTITY') proxy_enterprise_identity,
-- sys_context('USERENV', 'PROXY_GLOBAL_UID') proxy_global_uid,
sys_context('USERENV', 'PROXY_USER') proxy_user,
sys_context('USERENV', 'PROXY_USERID') proxy_userid,
sys_context('USERENV', 'SERVER_HOST') server_host,
sys_context('USERENV', 'SERVICE_NAME') service_name,
sys_context('USERENV', 'SESSION_USER') session_user,
sys_context('USERENV', 'SESSION_USERID') session_userid,
sys_context('USERENV', 'SESSIONID') sessionid,
sys_context('USERENV', 'SID') sid,
sys_context('USERENV', 'STATEMENTID') statementid,
sys_context('USERENV', 'TERMINAL') terminal
FROM dual;

tkprof GUI tool

noreply@blogger.com (Unknown) — Tue, 04 Nov 2008 14:24:00 +0000

tkprof GUI tool

http://www.dirisala.net/oracle/tkprof_trace_viewer.do

Simple example for FORALL loop performance

noreply@blogger.com (Unknown) — Sun, 04 May 2008 08:42:00 +0000

CREATE TABLE parts (pnum NUMBER(4), pname CHAR(15));

DECLARE
TYPE numtab IS TABLE OF NUMBER(4) INDEX BY BINARY_INTEGER;
TYPE nametab IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER;
pnums numtab;
pnames nametab;
t1 NUMBER(9);
t2 NUMBER(9);
t3 NUMBER(9);
BEGIN
FOR j IN 1..5000 LOOP -- load index-by tables
pnums(j) := j;
pnames(j) := 'Part No. ' TO_CHAR(j);
END LOOP;

t1 := dbms_utility.get_time;
FOR i IN 1..5000 LOOP -- use FOR loop
INSERT INTO parts VALUES (pnums(i), pnames(i));
END LOOP;
t2 := dbms_utility.get_time;

FORALL i IN 1..5000 -- use FORALL statement
INSERT INTO parts VALUES (pnums(i), pnames(i));
t3 := dbms_utility.get_time;

dbms_output.put_line('Execution Time (secs)');
dbms_output.put_line('---------------------');
dbms_output.put_line('FOR loop: ' TO_CHAR(t2 - t1));
dbms_output.put_line('FORALL: ' TO_CHAR(t3 - t2));
END;