Oracle Functions, Procedures, Queries, Scripts, Views...

QUERY: Select EOs by Surveysite
/* This query returns EOs from a specific surveysite. To change the surveysite name, move down to the WHERE clause */ SELECT SCIENTIFIC_NAME.SCIENTIFIC_NAM...
Mon, 9 Apr, 2018 at 3:26 PM
QUERY: Remove duplicate values
This is a example of a self join in SQL to find duplicate values and remove them. The first statement will select the duplicate items and the second will re...
Mon, 9 Apr, 2018 at 3:28 PM
QUERY/VIEW: Environmental Review View
This view is from Kee in Tennessee. Thanks!!! CREATE OR REPLACE VIEW ENV_REVIEW_VW AS SELECT EO.EO_ID, EO.SHAPE_ID, EOCODE_VW.EOCODE, D_NAME_CATEGORY.NAME_...
Mon, 9 Apr, 2018 at 3:31 PM
QUERY/VIEW: Element_Global taxonomy view
Here's a new view for higher taxonomy, called element_global_tax_search_vw. It replicates what the original element_global_tax_vw does, but it's mor...
Mon, 9 Apr, 2018 at 3:32 PM
Demystifying DELIMLIST - basics and examples
Using a delimlist allows a query to return multiple values per record, concatenated into a single field. This is necessary in the case of one-to-many relati...
Tue, 26 Mar, 2019 at 10:30 AM
QUERY/VIEW: Handy EO View
--This view was created by NatureServe for the --Kentucky State Nature Preserves Commission CREATE OR REPLACE VIEW HANDY_EO_VW (EO_ID, SHAPE_ID, EOCODE, SC...
Mon, 9 Apr, 2018 at 3:33 PM
PROCEDURE: Populate ELEMENT_MANAGED_AREA based on EO_MANAGED_AREA
The attached stored procedure populates the Elements grid within the Managed Area record (i.e. the element_managed_area table) based on data within the Mana...
Fri, 28 Jun, 2019 at 10:34 AM
PROCEDURE: Check_next_seq
The attached stored procedure checks your NEXT_SEQ table's values against the maximum primary key for each table in the NEXT_SEQ table. Install it by ru...
Tue, 25 Jun, 2024 at 2:41 PM
QUERY: Compare two CLOB fields to each other or one CLOB field with a VARCHAR field
In Oracle 8i, you can use the functions in the DBMS_LOB package to do these comparisons. To compare two clob fields, here's the syntax: dbms_lob.compar...
Mon, 9 Apr, 2018 at 3:41 PM
QUERY: Query the deleted schema
I want to get some data out of the database on deleted records (EOs). Is that what the "deleted schema" is for? You want the deleted schema all r...
Mon, 9 Apr, 2018 at 11:44 AM