Databases

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: CREATE SEQUENCE TMP START WITH 10001 MAXVALUE 9999999999999999999999 MINVALUE 1 Enter the following in a DB-Tool editor: 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; …

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

Login to the database and execute the following: select * from v$version; –OR: select * from product_component_version; Result: 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     …

RegEx use in Find with PLSQL Developer

The following are supported regular expression operators for use in PL/SQL Developer by Allround Automations: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 …

How to check the java version of an Oracle Database

Create a function in the DB to retrive the java properties: 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’; Then, run the following command to output the Java version: SELECT get_java_property(‘java.version’) FROM dual; …

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 d2kwut60.dll ffisamp.dll forms_base_ie.js forms_ie.js frmwebutil.jar jacob.dll jacob.jar JNIsharedstubs.dll sign_webutil.bat *Create …

How to set PL/SQL Developer to use a different HOME and OCI library

From within PL/SQL Developer: Tools > Preferences, Under Oracle > Connection * Oracle Home… (point to where tnsnames.ora file is, under the Oracle Client Installation) c:\Users\<username>\oracle\instantclient\network\admin\ * OCI Library… (point to where the OCI library is placed of the same Oracle Installation) c:\Users\<username>\oracle\instantclient\bin\OCI.dll   …

How to build a Java app and load it into an Oracle DB

Create the Java class public class Hello2 { public static String hello() { return “Hello World.”; } public static String hello(String name) { return “Hello ” + name + “.”; } public static void main(String args) { System.out.println(Hello2.hello()); System.out.println(Hello2.hello(“Java”)); } } Test the output (console): java Hello Output: Hello World. Hello Java. After compilation, run …

How to find the ORACLE_HOME path for Oracle DB 10g

Login to the server which you have the DB installed on sqlplus /nolog #login to SQL*Plus console From SQL*Plus console: conn user/pass as sysdba var ohp varchar2(100);  –set a variable ‘ohp’ exec dbms_system.get_env(‘ORACLE_HOME’, &#58ohp) ; –call the get_env system-procedure print ohp; –prinout the result You should then see an output print of the ORACLE_HOME path …

how to create a cursor to read through table rows

declare counter1 number := 0; counter2 number := 0; cursor cur_t is SELECT * from emp where e_name = ‘Ptere’; –set cursor, a select statement rec_t emp%rowtype; –defines this is a row from the database begin counter1 := 0; counter2 := 0; –some counters to log the records being changed open cur_t; –open cursor loop …

how to find specific text in oracle schema using a procedure and save to table

The following function will find any text matching the variable set within the procedure. (the output will display on screen -dbms_output) To execute: <SQL> execute update_found_records_proc; We could also add a parameter on calling the procedure as: CREATE OR REPLACE PROCEDURE FIND_IN_RECORDS_PROC(val IN varchar2) IS… note: ‘val’ is set once and used anywhere later in …

how to find specific text in oracle schema using a function

The following function will find any text matching the arguments given at command line. (output may not display on screen, but data will be inserted in the relevant table) To execute: select find_in_schema(‘ABC_553’) from dual; Credit:: Rene Nyffenneger on Oracle PL/SQL code: …

outer joins

Say we have two tables: tab1  tab2 1          2 2         3 4         4 5 select t1.col1, t2.col2 from tab1 t1, tab2 t2 where t1.col1 = t2.col2(+) –pl/sql –Informix SQL: …from tab1 t1, outer tab2 t2 Result: t1 t2 1   – 2   2 4   4 5   – –this will take primary table tab1 rows and match …

useful pl/sql code snippets

Create a “New Line” within a PL/SQL formula, use the following: /* Function to calculate the retirement date of a person based on person’s date of birth Parameter: p_birthdate, date Returns: ret_date, date */ CREATE OR REPLACE FUNCTION get_retire_date(p_birthdate IN DATE) RETURN date IS ret_date date; BEGIN — Calculates with months: 756 = 63 * …

Get user-account and limit his/her access in FM10

Limiting access of an FM user. Set a variable and get the account name Set Variable From the script where you want the set user limitation: Perform Script //the script created in step 1 If Perform Script Exit Script else …proceed normally …

Import/Export an oracle db/schema

Prerequisites: Import: Oracle DB setup with default DB the username that has access to the import DB Remember to set the character set if importing Unicode Import/Export: ip-address, SID, user/pass (sysDBA) …

how to start an Oracle Database and em console (10g)

Starting an Oracle Database and EM console (10g) login/su as the user who installed the oracle software (i.e. oracle) wins: set ORACLE_SID=sid_name  #the name SID given when creating database linx: export ORACLE_SID=sid_name sqlplus /nolog #just log onto the sqlplus console connect user/passw as sysdba #log-in as a sysdba with user | default: system/manager #output: Connected …

how to find and identify duplicates

The following snippet finds all duplicates in a table and then separates them: SELECT ID, LastName, FirstName FROM (SELECT ID, LastName, FirstName, RANK() OVER(PARTITION BY LastName, FirstName ORDER BY ID) AS SeqNumber FROM Customers) WHERE SeqNumber > 1; RANK() assigns a number value in SeqNumber column which separate duplicate rows (SeqNumber > 1), or …

how to use exception handling in PL/SQL

Exception handling is followed after an sql command, using ‘Exception’ i.e.: begin DELETE FROM TABLE1 t1 WHERE t1.table_id = variable1; EXCEPTION WHEN OTHERS THEN INSERT INTO ERR_DELETE (table_id, description) VALUES (t1.table_id, variable1); end; …

how to enable output in Oracle SQL Developer

To enable output to console window in Oracle SQL Developer (as of 2.1.1.64) From the menu go to: View > Dbms Output From the new window (popped-up in the log area) click on the plus sign to enable output Enter the output you want i.e.: DBMS_OUTPUT.PUT_LINE(‘CLA_CLA_ID: ‘ || V_ASY_CLA_ID2 || ‘, ‘ ||V_ASY_SURNAME2); This should …

how to find duplicates/singles in PL/SQL

SELECT emp, COUNT(emp) AS NumOccurrences FROM users GROUP BY emp HAVING ( COUNT(emp) > 1 ) also, find only one record: SELECT emp FROM users GROUP BY emp HAVING ( COUNT(emp) = 1 ) …

useful SQL-Plus commands via a Lunix shell/Windows cmd

In bash/shell: sqlplus /nolog #just log onto sqlplus console connect as sysdba #connect as sysdba and prompt for login connect user/passw as sysdba #log-in as a sysdba with user | default: system/manager In <bash><sql>: startup –startup a database shutdown –shutdown a database exit –disconnect from sqlplus SELECT * FROM NLS_SESSION_PARAMETERS; –query nls_lang params of session …

create a related global list-of-values in FM10

The logic is to have the various list-of-values (or combo-values) in a related global table. in global table: create the table that will hold the values -i.e. ‘city’ in related table: add an ‘unrelated’ relationship from global of the new ‘city’ table from layout to be used: – Manage > Value Lists… > New… > …

create a relationship using global var in FM10 (view or new rec)

Creating a relationship using a global variable in FM10: create the relationship in the graph of FM (i.e. person: person_pk to history: person_fk) from inside a script, if you need to view the relationship: Set Variable If Go to Related Record …