Create a Data Generator using sequence and a loop in Oracle DB

Create a sequence (i.e. “tmp” starting with 10001) to be used in statements:

[sql]CREATE SEQUENCE TMP
START WITH 10001
MAXVALUE 9999999999999999999999
MINVALUE 1[/sql]

Enter the following in a DB-Tool editor:

[sql]begin
FOR Lcntr IN 1..10000
LOOP
insert into customer (surname, forname, some_id, some_field)
values (‘SN’ || tmp.nextval, ‘FN’ || tmp.nextval, 123456, ‘abcdef’);
END LOOP;
commit;
end;[/sql]

Output should be something like this:

[text]SN50076,FN50076,123456,abcdef
SN50077,FN50077,123456,abcdef
SN50078,FN50078,123456,abcdef
SN50079,FN50079,123456,abcdef
SN50080,FN50080,123456,abcdef
SN50081,FN50081,123456,abcdef[/text]

How to check the version of an Oracle Database with PL/SQL

Login to the database and execute the following:

[sql]select * from v$version;
–OR:
select * from product_component_version;[/sql]

Result:

[text]Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
PL/SQL Release 11.1.0.7.0 – Production
CORE    11.1.0.7.0    Production
TNS for 64-bit Windows: Version 11.1.0.7.0 – Production
NLSRTL Version 11.1.0.7.0 – Production[/text]

 

 

RegEx use in Find with PLSQL Developer

The following are supported regular expression operators for use in PL/SQL Developer by Allround Automations:[text]a+ One or more occurrences of a
a* Zero or more occurences of a
a? Zero or one (i.e. optional) occurence of a
a{n} Exactly n occurences of a
a{n,} n or more occurences of a
a{,m} Zero or at most m occurences of a
a{n,m} At least n but not more than m occurences of a
a|b Either a or b
a||b a or b or both a and b in any order
abc a followed by b followed by c
[abc] A single character, one of a or b or c
[a-b] A single character, ranging in value from a to b inclusive
[^abc] A single character, any except a, b or c
(abc) a followed by b followed by c
“abc” The letters a followed by b followed by c with no special significance attached to a, b or c
. Any character except a newline
\a The letter a, with no special significance attached to a, special forms:\t The tab character
\n The newline character
\r The return character
\f The formfeed character
\b The backspace character
\xNN The hex character NN
\0ooo The octal character ooo
\w A single character, one of [a-zA-Z0-9_]
\W Any single character not matching \w
\d A single character [0-9]
\D A single character not matching \d
\s A whitespace character [\t\r\n\f\b\ ]
\S A single character not matching \s[/text]
_________________________
All credits to Marco Kalter (Allround Automations) for this tip!

How to check the java version of an Oracle Database

Create a function in the DB to retrive the java properties:

[sql]CREATE OR REPLACE FUNCTION get_java_property(prop IN VARCHAR2)
RETURN VARCHAR2 IS
LANGUAGE JAVA name ‘java.lang.System.getProperty(java.lang.String) return java.lang.String’;[/sql]

Then, run the following command to output the Java version:

[sql]SELECT get_java_property(‘java.version’) FROM dual;[/sql]

How to setup Webutil for forms 11g R1 (11.1.1.2.0)

Notes:
– frmwebutil.jar already exists/is signed on 11g
– tested with JRE version 1.6.0_25, webutil 1.0.6, jacob 1.10.1_b17
– windows 7, 32bit

1. Download webutil_1.0.6  (or from oracle’s index page) and jacob_1.10.1_b17 (or from project page) jar files.

2. Copy files:
=========
path = c:\Oracle\Middleware\as_1\forms\java
[text]d2kwut60.dll
ffisamp.dll
forms_base_ie.js
forms_ie.js
frmwebutil.jar
jacob.dll
jacob.jar
JNIsharedstubs.dll
sign_webutil.bat[\text]

*Create a folder “server” under forms\:
path = c:\Oracle\Middleware\as_1\forms\server\
[text]base.htm
basejpi.htm
forms.conf.backup
ftrace.cfg
webutil.cfg
webutilbase.htm
webutiljini.htm
webutiljpi.htm[/text]

*May also need the following folder:
path = c:\Oracle\Middleware\as_1\forms\webutil\
[text]d2kwut60.dll
ffisamp.dll
jacob.dll
JNIsharedstubs.dll
sign_webutil.bat[/text] (more…)