My Oracle 10g Tips

Connecting to SQL Developer

2011-08-14T11:24:00.000-04:00

A while ago, the disk on my laptop crashed, and so now I'm trying to download and set up some of the tools I used before. I got SQL Developer installed, but naturally couldn't remember what I did to get the connection. This time I decided to put it in writing. I honestly haven't done any studying of Oracle's material on installation so it probably is all there, but one of the main reasons I

Wanna' Date?

2011-08-11T12:25:00.001-04:00

Just a picture of 99% of the date variations listed in the book, although I'm saving timestamps for another post. The Easy Way Select To_Char(Sysdate,'DL' ) DL /* long date */ , To_Char(Sysdate,'DS' ) DS /* short date */ , To_Char(Sysdate,'TS' ) TS /* short time */ , To_Char(Sysdate,'DS TS') DSTS /* short date, time */ From Dual; DL | DS

My Ah-ha! Moment with Self-Joins

2011-07-24T22:12:00.001-04:00

I was recently taking an online class in SQL from Oracle. I will admit that sometimes self-joins still baffle my mind. So I decided to try what they said. Oracle's statement was: "To join a table to itself, the table is given two names or aliases. This will make the database “think” that there are two tables. Choose alias names that relate to the data's association with that table." So I decided

HR Hierarchy

2011-07-23T10:07:00.000-04:00

A friend asked me to explain the hierarchical query, and when I went back to look at what I had on the blog, there was little explanation, and more of just a sample for me to follow next time I had to write one. So I decided to redo it in the HR schema and add a few comments. All the special words used for hierarchical queries are shown in red. Line 3, 7, 8, 9 have to do with padding the name

SQL*Plus Output -> HTML

2011-07-22T09:10:00.002-04:00

*I saw this on Uwe Hesse's blog, and it was too cool not to keep a copy where I knew I could find it again. The only thing I added to his script was the 'Start' in front of 'Firefox' that's needed by Windows (and the comment about removing the 'l'). HTML.SQL: set termout off set markup HTML ON HEAD " - - " - BODY " " - TABLE "border='1' align='center' summary='Script output'" - SPOOL ON

Oracle Proprietary vs. ANSI-99 Joins

2011-07-13T10:35:00.001-04:00

Oracle Proprietary JoinsANSI-99 SQL Joins To display only rows with matching Keys "Equi-Join" aka "Simple Join" aka "Inner Join" The WHERE clause states how to join the tables Use AND to specify other conditions (i.e., AND City = 'DC') Select L.Key, NameL, NameR From TableL L , TableR R WHERE L.Key = R.Key;“Natural Join” Can't use aliases1) Key Name and Type are the same Select

Multi-Table Inserts -- Just the Basics

2011-07-11T15:16:00.000-04:00

You can use a multi-table insert when data from one table can be used to populate several tables. This is something often seen in a data warehouse. They can be unconditional or conditional, but not both. To do both, you can 'fake' it out by adding 'When 1=1 Then' to unconditional inserts. If you want to see this work, you can copy the code shown below. It will run in the Scott schema.

To Sum It Up...

2011-07-10T19:20:00.000-04:00

ROLLUP, CUBE, GROUPING SETS and GROUPING can be used to subtotal and total aggregates based on the values in the GROUP BY clause. ROLLUP generates subtotals and totals (for example, salary by departments, and the total salary for the report). If there is more than one field listed in the GROUP BY ROLLUP clause, the subtotals move from right to left. This shows the basic 'GROUP BY' which will

RANK vs DENSE_RANK

2011-04-18T16:22:00.000-04:00

If you're not familiar with how RANK works, you may want to check out my previous post by clicking here. This post shows the difference between RANK and DENSE_RANK.Select DeptNo , Sal , RANK() OVER (Partition By DeptNo Order By Sal ) Rank , DENSE_RANK() OVER (Partition By DeptNo Order

Rank and File

2011-04-17T00:41:00.000-04:00

Someone asked me the other day how to pull the top-n number of rows from a table. Of course, I thought of the old way of sorting rows in a subquery and then pulling based on rownum. For example, if you want to show records for the top three salaries, you could code: Select * From ( Select * From Emp Order By Sal Desc ) Where RowNum < 4;RANK is a better option -- and it

Flashback a Table, Index and Trigger Script

2011-02-14T13:48:00.000-05:00

This script performs a flashback of tables, indexes and triggers. If the item already exists, it will be renamed with the base_object number. 1) After displaying a list of tables that are in the Recycle Bin, the script will ask for a table to be flashbacked. 2) If a table with that name already exists, a message is displayed stating that the base number will be added to the name. 3) A report

Documenting SQL Queries

2010-10-22T15:20:00.000-04:00

(This is an update to a previously posted query.) This code will help document a SQL query as it looks through that code to find the list of tables, columns, amper-variables, and other called queries referenced. I added the following to the older version: called queries, a reference to the query name in the spooled file, and also to the spool filename. There are some limitations in this code:

Generate Code to Create a CSV File

2010-10-16T10:56:00.000-04:00

I was continually reworking the code to create CSV files for a particular application, so I wrote a query to do it for me. (It is long enough that I stored it as an attachment on the following page Cr_Csv.Sql. You'll have to scroll to the bottom to see the attachment.) It will read through the User_Tab_Columns table, and pull all columns (except those in tables in the recycle bin). The data

11g: New Regular Expression Functions

2010-08-17T19:28:00.000-04:00

RegExp_Count has been added in 11g. It's pretty straight forward. In this example, I'm looking through this popular tongue-twister for the occurrences of the letters 'pic', and not surprisingly it will tell me '2.' SELECT REGEXP_COUNT ( 'Peter Piper picked a peck of pickled peppers' -- Searched string , 'pic' -- Pattern to find , 1

An Entity By Any Other Name...

2010-07-04T14:50:00.003-04:00

Logical/ RelationalLogical/ Object-OrientedPhysical Implementation EntityClassTable AttributeAttributeColumn InstanceObjectRow

SQL Statement Types

2010-07-03T16:26:00.002-04:00

AcronymLanguagePurposeStatementsComments DDLData DefinitionDefine Database· Create · Alter · Drop · Truncate · Comment · Rename · DeleteAutocommit DMLData ManipulationManaging data· Select · Insert · Update · Delete · Merge · Call · Explain Plan · Lock TableCan rollback DCLData ControlSecurity· Grant · Revoke TCLTransaction Control Manage DML· Commit · Savepoint · Rollback · Set Transaction

11g: Mixed Name + Positional Notations

2010-06-23T18:23:00.000-04:00

Changes with 11g: you can mix named with positional parameters in a call. the SQL code matches the PL/SQL code. Notes: Exclusionary Notation: one or more can be excluded if optional. (When formal parameters have defaults they are optional.) You still can't call a function from SQL when any of its formal parameters are defined as IN OUT or OUT mode–only variables. Mixed must list all

11g: SIMPLE_INTEGER is faster than PLS_INTEGER

2010-06-19T15:09:00.000-04:00

Reading the improvements on 11g, I came across these new data types. They have NOT NULL constraints and should be faster. SIMPLE_INTEGER - based on PLS_INTEGER SIMPLE_FLOAT - based on BINARY_FLOAT SIMPLE_DOUBLE - based on BINARY_DOUBLE. With a basic time test, I did find SIMPLE_INTEGER to be faster, and I don't know where I go wrong on the others, but they're are actually running slower for me.

Writing Generated Code with Q-Quotes

2010-03-10T17:56:00.009-05:00

Q-quotes have just made generating code too easy. It used to take several tries for me to get it right, but no more! I'm usually generating code because I want to pull some different values from tables (i.e., table_name).Here are the steps:1) Write out a sample of the code you want generated. Obviously, you'll want to run it first to make sure you're getting the final results needed.Select 'Table

Finding Names in SQL Queries

2009-12-22T11:52:00.012-05:00

Delete Based on Another Table

2009-07-23T06:27:00.008-04:00

I recently had to delete records in one table based on the data in another table. This isn't particularly tricky, but the only examples I could find online ended up deleting all the records in the table.The code at the beginning is just to set up a table I can use as an example.Conn Scott/TigerSet Feedback On Echo OnSelect *From Dept;Drop Table Dept2 Purge;Create Table Dept2 AsSelect * From Dept

Timestamp in an External Table

2009-07-08T17:36:00.006-04:00

Since this wasn't particularly easy to find, and it took several tries, I decided to post it here. As most things, once you know how to write it, it's not particularly tricky.Input Data from External Table:1,Jan 8 1998 12:43:27:012AM2,Feb 19 1998 2:43:27:111PM3,Jan 8 2008 3:49:27:693PMCreate Table Ext_Dt( Item Number , Needed_By Timestamp)Organization External( Type Oracle_Loader Default

Database Terminology

2009-05-20T11:40:00.003-04:00

Physical Logical/Relational Logical/Obj.Orient Definition Examples Table Entity Class Something you want to store data for Department, Employees ColumnAttributeAttributeAn item that helps describes the entityDepartment Location, Employee Birthdate RowInstanceObjectA specific example of the entityPittsburgh department, Lynn's birthdate: 01/01/1970

Selecting from a Variable TableName

2009-05-11T08:42:00.004-04:00

A question was posted on ITToolBox asking how to select from a table based on a column. You can't use a CASE clause in the FROM statement, but the following -- though convoluted -- does work.'New_Value' in the 'Column' statement will create a new variable named 'Table' that can then be used in the final query.Set Verify Off Echo Off Linesize 200Accept Key Prompt 'Enter the key for the table you

How to Code Single Quotes with Q-Quote

2009-03-14T15:40:00.001-04:00

Starting in 10g, use a 'q', a pair of single quotes, and a pair of brackets to code a literal that uses a single quote. With this method, you can code it exactly as it is.Select q'!He's ok!' As Single_Quote_In_Middle , q'['I like him.']' As Single_Quote_At_Ends , q'(')' As Single_Quote_Alone , q'<''>' As Two_Single_Quotes From Dual;To delete or

Oracle Exceptions found in DBA_Source

2009-03-12T09:40:00.002-04:00

Line Breaks

2009-03-11T18:21:00.005-04:00

To get a line break in your output data, use 'Chr(10).' It must be concatenated to the columns it's between 1* Select 'Hi' || Chr(10) || 'There' From DualSQL> /HiThere

Regular Expressions

2009-03-08T20:39:00.015-04:00

The create table statement and all the code to produce this report can be found here. Scroll down to the end to find the attachment: RegExp.SqlThis is the data the queries run against:Select * From Fam;NAME PHONE DSCR----- --------------- --------------------------------------------------------------------------------Me 555-123-0987 Fun-loving aunt with a lot of nieces and

Sequence Basics

2009-03-03T21:48:00.008-05:00

This shows how to create a basic sequence. CurrVal will show what number was last used. Whenever NextVal is referenced, the sequence gets incremented.Set Feedback Off Heading OffDrop Table TestTbl;Create Table TestTbl ( Id Number(4), Note Varchar2(20) );Drop Sequence Test_Seq;Create Sequence Test_Seq Increment By 1 Start With 1 NoMaxValue NoCache NoCycle;Insert

Trigger - Check for Positive Values

2009-03-03T20:24:00.010-05:00

Create Or Replace Trigger PositiveOnly Before Insert Or Update Of DeptNo On Dept For Each RowBegin If :new.DeptNo -20100, 'Please insert a positive value'); End If;End PositiveOnly;/

Range & List-Partitioned Tables

2009-02-27T12:30:00.005-05:00

A range partition allows Oracle to place data in a partition based on the input value being between two values. 'MaxValue' is used to hold everything higher than the previous value listed. A list partition allows Oracle to place data in a partition based on the input value matching a value in the list. The 'Default' is used to hold everything not mentioned in the list. For instance, in the

How to Find a Linking Table

2009-02-19T18:22:00.006-05:00

What do you do if you need to select data from two tables, but discover they have no common column? You look for the table that links them. As an example, I'm using the Oracle-supplied HR schema. This query will find the table (if there is one) that has a field in Locations, and also one in Regions. (This is based on column names so if a column was named 'RegID', it would not match to '

Printing Labels from SQL through HTML

2009-02-16T21:14:00.017-05:00

I recently needed to print 3-up address labels with the same information on each. I used a combination of padding, and adjusting the margins in the browser 'Page Setup' to get these to print in the perfs.This title shows in the browser title, and tab: Head "Test Labels Where there are four options, they are in this order: top right bottom left.Styles are Dotted Dashed Solid

List Rows with at Least One Alpha Character

2009-02-10T20:09:00.005-05:00

How this is done is based on the version. If you're running 10 or greater, you can use a regular expression. The first example looks for any alphabetic character; the second for anything not numeric.10g Example 1:Select Postal_Code From Hr.Locations Where Regexp_Like(Postal_Code,'[[:alpha:]]{1}');POSTAL_CODE------------M5V 2L7YSW 9T2OX9 9ZB3029SK10g Example 2:Select Postal_Code From

Creating Constraints When Creating a Table

2009-01-21T17:28:00.007-05:00

Have you ever created a table with 'Create ... as Select ...', and that was so easy and automatic, you wondered why all the constraints didn't just pop into place? Well, after coding for a bit, I realized it was quite a bit more complicated than I initially thought. In fact, the query got long enough that I stored it as an attachment: Create_Constraints.SQL.As shown in the screen print, you

Column Information

2008-12-24T00:38:00.005-05:00

I use this code for investigating fields in the database. It switches to the system user when you provide the password. (It stores the old user so it can be restored at the end.)All the information for that column is read from DBA_Tab_Columns and written in HTML format. Firefox will load to display the data. (It is assumed that the program can be found at C:\Program Files\Mozilla Firefox1\

Create Hot Backup Script

2008-12-22T23:17:00.004-05:00

This script creates another script that will back up an active database. It prompts for the system password, and then make a directory on the root of the C drive called 'Backup'+ today's date.It writes a file called HotBkUp+today's date into the default directory, which will then need to be run to perform the actual backup.This makes two copies of the control file -- one is written to the root.

Discrepancies Between Schemas

2008-12-21T17:52:00.006-05:00

This report shows discrepancies between schemas. It can be helpful when you are in development, and trying to keep a test schema matching the production one. This report is actually three reports that show the following:1) Missing tables in one or the other2) Discrepancies found in Tables and Columns3) Discrepancies in the Number of Columns in TablesThe first line of code is 'Define OnOff = Off

Generating Column Formatting for SQL+

2008-12-20T19:01:00.007-05:00

This code sets up column formatting for every column in User_Tab_Columns. The column names are stacked, breaking on the underscore. Since alpha titles aren't displayed in full if not enough space is allocated, a decision is made as to whether a column part name or if the data field is longer.As a place to start, it sets up any field longer than 30 characters to A30, but adds 'Word_wrap.' Any

Pivot with Connect_By

2008-12-18T10:07:00.006-05:00

This is a different version of the pivot that I hadn't seen. I wanted to be able to find it so I listed it here. The original code can be found in an article by Younes Naguib.Column Name_List Format A100 Select Deptno, -- use the substring to get rid of the initial comma ---------------------------------------------------- Substr(Sys_Connect_By_Path(

How to Loop in SQL

2008-11-20T10:19:00.005-05:00

I recently saw a request for a For Loop executed with plain old SQL so I decided to create my own version. The report you see was built by running the code once for each character in the name. This required four queries: one to set it up, and one to end it. The other two are the actual code, and then one to decide whether to loop or not.(If you want to see everything behind the scenes, set

Validate a Date

2008-11-14T06:09:00.014-05:00

This function validates a date and returns a boolean value. The date must be in one of these formats:d-mmm-yy dd-mmm-yy d-mmm-yyyy dd-mmm-yyyyIt will return either True for a valid date or False for an invalid date. The month can be capitalized or not.An example of a call to the function:Set Serveroutput OnBegin Dbms_Output.Put_Line(Case When ValDt('&test_Date')

Create a New User

2008-11-13T23:01:00.001-05:00

--+--------------------------------------------------------------| CrUser.Sql--| Script To Create New User--+------------------------------------------------------------Accept UserName Prompt 'Enter name of user to create: 'Drop User &UserName Cascade;Create User &UserName Identified By &UserName Default Tablespace Users --Temporary Tablespace Temp /* automatic in 10 and 11 */ Quota 0

Display Active Database Sessions

2008-11-12T11:37:00.004-05:00

The code was just a tad long, and nothing particularly interesting, so it can be downloaded from my web site. It is named Active_DB.sql

Oracle's HR Schema

2008-11-02T05:16:00.006-05:00

I'm trying to break up with Scott. This is one of the new schemas Oracle creates as a learning tool. Note all table names are plural.HR is for Human Resources.I have this shown as a model from the new Oracle SQL Developer Data Modeling.For more detail, see Oracle documentation on HR.

Update a Table Based on Data from Another Table

2008-11-02T01:11:00.005-04:00

This first update is supplying data to TableA from TableB if either field is missing.Update TableA a Set ( a.Field1 , a.Field2 ) = ( Select b.Field1 , b.Field2 From TableB b Where b.Key = a.Key )Where Field1 Is Null Or Field2 Is Null;The second example updates TableA with the data from TableB. The 'Where Exists'

Flashback Table to One Minute Ago

2008-11-01T19:13:00.001-04:00

What to do when you've messed up?Alter Table table1 Enable Row Movement;Flashback Table table1 To Timestamp (Systimestamp - Interval '1' Minute);

Run a SQL Query from DOS

2008-10-31T14:12:00.005-04:00

This is a DOS batch file that will be run at the DOS prompt. You must supply the user, password, and the SQL query name you wish to run. For example:Since the SQL query must have an 'EXIT' statement at the end, I've actually had this batch file call a two-line query that calls the query you requested, and then exits. This batch file assumes that all your code is in a directory called 'Data',

Execute Immediate: Count Rows in All Tables

2008-10-25T12:59:00.006-04:00

The EXECUTE IMMEDIATE statement allows you to build a statement on-the-fly. This is useful if you don't know the name of a table or the where conditions in advance. In this case, we'll get each table name in the schema and then ask for a count. Since this list will vary from time to time, the Execute Immediate makes sure we get a complete list.Clear ScreenPrompt This will display the record

HR Tables: Keys Used in Joins

2008-10-24T23:54:00.004-04:00

Column Countries Format A15Column Departments Format A15Column Employees Format A15Column Jobs Format A15Column Job_History Format A15Column Locations Format A15Column Regions Format A15Set Markup Html On Entmap On Spool On Preformat OffSet Term OffSpool Fk.HtmSet Heading OffColumn Title Entmap OffSelect '

Hr Tables : Keys Used In Joins

' As Title From

Finding Discrepancies in Column Definitions

2008-10-24T14:09:00.007-04:00

When setting up tables in a schema, you may want to assure that your column definitions are the same as those already defined. This query will count how many times each column is found, and how many unique variations there are in the type and length. If the counts are identical, they are omitted from the report. In the above example, there are three fields named 'CATEGORY'; a length of 20 was

Reserved Words

2008-10-22T14:22:00.005-04:00

[A]AllAlterAndAnyAsAsc[B]BetweenBy[C]CharCheckClusterCompressConnectCreate[D]DateDecimalDefaultDeleteDescDistinctDrop[E]ElseExclusiveExists[F]FloatForFrom[G]GrantGroup[H]Having[I]IdentifiedInIndexInsertIntegerIntersectIntoIs[L]LikeLockLong[M]MinusMode[N]NocompressNotNowaitNullNumber[O]OfOnOptionOrOrder[P]PctfreePriorPublic[R]RawRenameResourceRevoke[S]SelectSetShareSizeSmallintStartSynonym[T]

Display a Calendar and Prompt for a Date

2008-10-22T13:05:00.007-04:00

The code for the calendar procedure was found on OTN and was created by Pablo Rovedo, DBA at ADEA S.A., in Buenos Aires, Argentina.My script gets the current month and year, and passes it to the procedure. I then prompt the user for the day of the month. The goal of this is to run a report based on a date. If the users picks something later than the Sysdate, they are prompted with an error

Schema Space Consumption

2008-10-22T11:40:00.005-04:00

Set Pagesize 100Column GB Format 99.99Column Owner Format A30Break on ReportCompute Sum of GB on ReportSelect RPad(Owner,30,' .') As Owner, ------------------------------------------------------- Round ( Sum(Sum_Bytes) / 1024 / 1024 / 1024, 2 ) As Gb From --+---------------------------------------------------- (

An Example of Ansi Joins with Multiple Tables

2008-10-21T18:57:00.004-04:00

These tables are from the HR schema. I'm showing two examples, one uses 'Using' and the other with the 'On.' Select r.Region_Name , c.Country_Name , l.State_Province , d.Department_Name , e.Last_Name , e.Job_Id From Regions R Join Countries C On r.Region_Id = c.Region_IdFull Outer Join

List All Constraints in a Schema

2008-10-21T17:20:00.007-04:00

This report was modified so it could fit in this blog. The actual code can be found on my web site as an attachment: UserCons.Sql

Show the Percentage of Records that Meet a Criteria

2008-10-21T12:15:00.001-04:00

Select Round(Part/Whole*100,1)||'%' PercentageFrom (Select Count(*) Whole From Emp ), (Select Count(*) Part From Emp Where Deptno = 30);Percentage----------42.9%

Where: How to Search for Various Conditions

2008-10-21T11:52:00.004-04:00

This query presents the operator with a choice of how to find data. After selecting a search field, and entering a value, a decode is used to generate SQL code. The New_Value clause sets that equal to &xWhere, and it is used with the 'Where' clause. (I have tried incorporating it directly into the Where with no luck.)PromptPrompt +------------------------------------------+

Data Samples

2008-10-21T11:14:00.003-04:00

SAMPLE [ BLOCK ] (sample_percent) [ SEED (seed_value) ]See the Select statement in the Oracle® SQL Reference for exceptions, refinements, and cautions regarding 'Block', 'Seed', etc.SQL> Select Count(*) From Employees; COUNT(*)---------- 107 1 Select Employee_Id, First_Name, Last_Name 2* From Employees Sample(10);EMPLOYEE_ID FIRST_NAME LAST_NAME----------- --------------------

Delete Duplicate Records

2008-10-21T10:31:00.006-04:00

This logic was found on PL/SQL Pipeline:To test, insert a duplicate 'SMITH' on the EMP TABLE:Insert Into Emp Values(9999,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);Commit;You can see what is happening in the above report, and how a record is being chosen for deletion by running the following query. Records are being ranked (and a RowId saved) by breaking all the employees into partitions based

Case vs. Decode

2008-10-21T10:08:00.003-04:00

The DECODE and CASE statements can generate the same report as shown on the right. The Case statement (which has two variations) is newer and has several advantages over the Decode.offers more flexibility and logical power (see Case Examples)easier to read than DECODEoffers better performance as wellDECODE(value,if1,then1[,if2,then2,]...,else)value can be any column or the result of a

Oracle User and Schema

2008-10-21T09:22:00.003-04:00

Who am I?SQL> Select Instance_Name From v$Instance;INSTANCE_NAME----------------orcl10SQL> Select Name From v$Database;NAME---------ORCL10SQL> Select User From Dual;USER------------------------------SCOTT

Nullif

2008-10-20T23:39:00.005-04:00

The NullIf statement compares one value with another. If they match, it returns null, otherwise it returns the first. In this example, the NullIf, and the following Case statement get the same results.Column Employee_Id Heading Employee|IdColumn NullIf_JobId Heading NullIf|(E_Job_Id)Column Case_JobID Heading Case|(E_Job_Id)Column Equals Heading '='Column E_Job_Id Heading E.|

Find Discrepancies with 'Distinct Count Over'

2008-10-20T20:56:00.004-04:00

In this query, I'm looking for discrepancies in data. In particular, I want to find column names in the data dictionary which have more than one type of data assigned. This is done by asking for a distinct count of data_types within a partition based on column_names. The code then eliminates anything that has a count of one.Select * From ( Select Table_Name,

Using Colors and Fonts in HTML Reports

2008-10-20T19:15:00.011-04:00

This is absolutely a sign of a person with too much time on their hands! Colors & fonts? Who knows where this will lead. Anyway, I usually use

to indicate a title, but you can't if you want to set size, color or face; it will override these.There are many internet sites that give a list of all the colors, faces and sizes so I'm not going to list them here again. I did want to make a note

Connect By Displayed as HTML

2008-10-20T16:14:00.017-04:00

The Connect By displays the hierarchy of data. To generate the above report, we've indented the name by padding with three spaces for each level deep it is. Since we don't want to pad the 1st level, subtracting one puts it in the right place. Select Empno, Lpad(' ' -- Print a space ,3*(Level-1) -- Pad with spaces for 3 times one less than the level ) || Ename -- Join spaces with

A Way to Resolve a Lock Conflict

2008-10-20T13:49:00.005-04:00

The following code generates two lines. The first is a prompt to say what is going to happen, and the second is a 'KILL SESSION' statement. Which session needs to be killed is determined by the Blocking_Session from V$Session. This query does not actually run that line -- it just generates it. After running this, the DBA can copy and paste the line as shown on the screen, or run KL.SQL to

Data Modeler

2008-10-17T14:10:00.011-04:00

To reverse engineer a schema: download the beta-version here. Select File Select Import Select DB Catalog Click 'Add' on the 'Connect to Database' window, and type in the requested info. Select the schema. Select the items you want. To generate the DDL:Select View Select DDL File Editor Click Ok Click Generate A very complete example of reverse engineering by Lewis C Oracle Users Guide

Run EM and iSQL*Plus From Windows XP

2008-10-16T19:26:00.007-04:00

Enterprise Manager:Type into the browser's address bar:http://localhost:1158/em/iSQL*Plus:Start the service and then type into the browser's address bar: http://localhost:5560/isqlplus/

Java - A Way to Manually Sort an Array

2008-10-14T22:28:00.012-04:00

public class mySort{ public static void main(String[] str) { int[] array1 = {53,43,33}; int idx2, idx1, temp; //---------------------------------------------- // The 1st index is set to 0. On each loop it's // compared against the length, and incremented. //---------------------------------------------- for (idx1 = 0; idx1 < array1.length; idx1++) {

Java - If / Else vs. Switch

2008-10-14T16:20:00.006-04:00

Both of these produce the exact same results: if (opt == 1) { funcInst.func1(); }else if (opt == 2) { funcInst.func2(); }else { System.out.print("Invalid Option"); } switch(opt){ case 1 : funcInst.func1() ; break; case 2 : funcInst.func2() ; break; default :

Java - Change Code to Object-Oriented

2008-10-14T14:00:00.006-04:00

1. Move the main method to a script that will call the functions.2. Create a method for each step.3. Declare an object of the class.4. Call functions thru the object name.

To Conditionally Call Code in SQL*Plus

2008-10-13T13:57:00.007-04:00

Since SQL*Plus has no 'If' options (outside of queries) you need a different way to conditionally call another query. One way to do that is to have a query that checks for the condition you're looking for. If it is found, &Ex_If will be set to the name of the query I wish to run. It is first undefined so we're ready for the next execution. I use this when there are some statements I need to

Finding Names in Code

2008-10-12T13:46:00.010-04:00

This query will find column, table and variable names. The column and table name are based on the data dictionary; variable names are anything starting with '&'.For example, the following code will generate the report as shown:Because of the length, the code is located on my web site as an attachment named NamesInCode.Sql.It gets the name of a query from the user.Creates an external table to

Searching a Long Field

2008-10-11T11:36:00.010-04:00

As you've probably found out, the Long field type (soon to be expired) is hard to work with. You can't concatenate, use 'Like' or a host of other things. See the error posted below when I try to find which views are based on the EMP table:SQL> Set Linesize 2000 Pagesize 60SQL> Column Text Format A100 Word_WrappedSQL>SQL> Select View_Name,2 Text3 From User_Views4 Where Upper(Text)

Display Column Names Not Found In Procedures

2008-10-10T20:02:00.006-04:00

This was written as a solution to a question posed on ITToolBox. Because of it's length, it is stored as an attachment: Cols_Procs.SqlIt accepts a table name from the user.It finds all the source names where the table name supplied is found in the text.Based on that list, it creates a list of all the words in the text.These words are spooled, and then an external table is created.This external

Java - Arrays

2008-10-09T23:01:00.004-04:00

Calling program: public class runmyArray3{ public static void main(String[] str) { myArray3 m1 = new myArray3(); m1.getData(); m1.findLargest(); }} Called program: public class myArray3{ int[] n = new int[3]; int largest; public void getData() { Scanner input = new Scanner(System.in); for (int k = 0; k < n.length; k++) {

Java - JOptionPane

2008-10-09T16:46:00.002-04:00

import javax.swing.JOptionPane;public class jop{ public static void main( String args[] ) { String output = "Hi There \n\n"; JOptionPane.showMessageDialog( null, output, "JOptionPane Test", JOptionPane.INFORMATION_MESSAGE ); System.exit( 0 ); }}

Java - Check String 2

2008-10-09T16:40:00.001-04:00

public class cks{ public static void main(String args[]) { String s1, s2, s3, s4, output; s1 = new String("x"); s2 = new String("y"); s3 = new String("abc xyz"); s4 = new String("Abc Xyz"); output = "s1 = " + s1 + "\n" + "s2 = " + s2 + "\n" + "s3 = " + s3 + "\n" + "s4 = " + s4 + "\n\n"; //------------------

Java - Check String

2008-10-09T16:37:00.002-04:00

//// CheckString.java//public class CheckString{ public static void main(String arguments[]) { // create an object //String str = "Roses are red" ; String str = new String("Roses are red"); System.out.println(" 1 "); System.out.println("0123456789012"); System.out.println("Roses are red"); System.out.println(" "); System.out.println("

Java - Loops

2008-10-09T16:30:00.001-04:00

FOR LOOP for (int k=0; k < 3; k++) { s1.readData(); }WHILE LOOP int k = 0; while (k < 3) { s1.readData(); k++; }INFINITE LOOP for (;;) { s1.readData(); System.out.printf("Continue? Y/N "); answer = userInput.nextLine(); if (answer.equalsIgnoreCase("y")) continue; else break; }

Java - Calling Functions

2008-10-09T16:26:00.002-04:00

This is the program that is executed:public class callingClass{ public static void main(String[] args) { calledClass calledClassInstance = new calledClass(); calledClassInstance.function1(); calledClassInstance.staticFunction2(); //call static function with calledClass.staticFunction2(); //instance name or class name }}This is called:public class calledClass{

How to Start and Stop Oracle Services on the PC

2008-10-07T16:09:00.005-04:00

Select 'Start' in WindowsChooseControl PanelAdministrative ToolsServicesFind the name of the Oracle services and create two batch files. StartAll.Batnet start OracleDBConsoleorcl10net start OracleOraDb10g_home1TNSListenernet start OracleServiceORCL10StopAll.Batnet stop OracleDBConsoleorcl10net stop OracleOraDb10g_home1TNSListenernet stop OracleServiceORCL10OracleServiceSID = (the Oracle Database

Hints

2008-10-07T14:49:00.000-04:00

SQL hints (the things between /*+ */ ) 1) There is no error posted if you type them wrong -- they just don't work. 2) If you use an alias in your FROM statement, you must use that and not the actual table name in the hint.

How to Put HTML in a Blog

2008-10-06T17:10:00.003-04:00

To post html code to the blog1) make sure there is no valid use of '~'. If there is, switch to another character.2) search and replace the following in the order shown.FIND REPLACE< ~lt;> ~gt; ~amp;nbsp;& ~amp;~ &

ORA-01722: invalid number

2008-10-05T05:57:00.004-04:00

Normally, I have no problem renaming a column its original column name after some function. This time, it's posting 'invalid number', but only after I inserted the 'Order by'. 1 Select To_Char(Trunc(Datetime) + (Trunc((Datetime - Trunc(Datetime)) *48) / 48), 'dd/Mm/Yy Hh:Mi') As Datetime 2 , Count(*) 3 From Ext_Ittbl1 4 Group By To_Char(Trunc(Datetime) + (Trunc((Datetime - Trunc

Time Intervals

2008-10-05T05:30:00.006-04:00

This request came from the ITToolBox. Based on the following dataID DATETIME-- ----------- 1 10/03 09:00 2 10/03 09:01 3 10/03 09:20 4 10/03 09:31 5 10/03 10:00 6 10/03 10:10 7 10/03 10:40They wanted a total count in half-hour intervals. Tom Falconer put together the logic for this. I made a few refinements, and fixed a minor error, but had to keep this for future reference. The ID field shown

Display All the Digits in a String (pre 10)

2008-10-04T02:26:00.011-04:00

Conn hr/hrSelect Translate(Phone_Number,' !"#$%&''()*+,-./:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~' ,' ') As Digits, Phone_Number From Employees;With version 10, you can use Regexp_Replace as shown:Select Regexp_Replace('abcABC123^&*','[^[:alnum:]]') From DualSQL> /REGEXP_RE---------abcABC123

HTML Formatting

2008-09-30T18:41:00.005-04:00

--+-------------------------------------------------------------------------------| Set Escape On To Use \ (space) \& (&) \> (>) \< (<)--+-----------------------------------------------------------------------------Set Markup Html On Entmap On Spool On Preformat Off Set Feedback Off Term Off Echo Off Verify Off Linesize 2000 Escape On --+----------------

Create a New Instance on the PC

2008-09-27T22:12:00.006-04:00

1) SQL> Shutdown2) SQL> EXIT3) Windows Start Control Panel Administrative Tools Services - Stop all Oracle Services4) Start All Programs Oracle - OraDB10gHome Configuration & Migration Tools Database Configuration Assistant5) Delete database (PC only supports one instance)6) Create database Ignore OK Yes

Deleted Data Stored in Rollback Segments

2008-09-23T17:06:00.003-04:00

clear screenset echo on feedback on heading on pagesize 50variable emps refcursorbegin open :emps for select empno, ename from emp where empno > 7800;end;/------------------------------------------------ deleted data is saved in a rollback segment----------------------------------------------delete from emp;commit;print emps

Lock Table

2008-09-22T19:58:00.020-04:00

window['frozRowHTable']=""window['rowHTables']="1234567"{var z={};z.id=18;z.nm='Locks';z.fx=[];z.rIds=[,0,1,3,5,8,101,16];z.cIds=[,20,6,7,8,9,10,11,12];z.eom='';var a;var x=[];x[0]=[];x[1]=[];x[2]=[];x[3]=[];x[4]=[];x[5]=[];x[6]=[];z.ci=x;window['_gi']=z;}.tblGenFixed td {padding:0 3px;overflow:hidden;white-space:normal;letter-spacing:0;word-spacing:0;background-color:#fff;z-index:1;border-top:

DICT - Data Dictionary

2008-09-17T10:33:00.008-04:00

This query actually generates two reports. The first shows all tables starting with 'User_' (indicated by the 'U'), 'All_' (indicated by the 'A') or 'DBA_' (indicated by the 'D'). The 'base_name' is what I consider the portion past User/All/DBA. The report is sorted by this name. A page break occurs based on the first letter. A line break occurs based on the second part of this name,

Regular Expressions

2008-09-16T21:43:00.003-04:00

--Regular Expression Operators and Metasymbols-------------------------------------- Anchor to:------------------------------------ ^ the beginning of a string ^A finds Alice, Alan-- $ the end of a string g$ finds dog, lag------------------------------------ Match # times------------------------------------ * >= 0 12*3 finds 13 123 1223 12223 -- + >

To Separate Each Record With a Line

2008-09-16T17:20:00.004-04:00

Set RecSep EachSet RecSepChar '-'Select * From Emp Where DeptNo = 20;

Remove Line Breaks from Data

2008-09-16T17:07:00.007-04:00

If you have a column fornatted to hold the longest value, but data is still falling to a second line, it probably has a line break character in it. This is coded as 'CHR(10).' It can be replaced with a space to keep the value on one line as shown in the screen to the right.Column Comments Format A80Select Comments From DictWhere Table_Name = 'ALL_REFRESH_DEPENDENCIES';Select Replace(

Schemas

2008-09-16T14:56:00.005-04:00

SYScontains the data dictionarySYSTEMcontains additional tables and views that store administrative informationSample SchemasThe names are in alphabetical order, and specifies the order of creation.HR - Human Resourcesbasic topicsan extension supports Oracle Internet DirectoryOE - Order EntryHR must be installed and you have the password to itintermediate topicsmultitude of datatypesOracle

PL/SQL Loop Creates Pivot

2008-09-16T12:02:00.002-04:00

Declare All_Depts Varchar2 (50) := ''; Cursor c_Loc Is ----------------------------------- -- don't put ',' in front of 1st ----------------------------------- Select Case When Rownum <> 1 Then ',' End || Loc As Loc From Dept;Begin For c_Each

Bind Variables in SQL Code

2008-09-16T11:11:00.007-04:00

@bind----------------------------------------------------------- Bind variables can be declared in your SQL*Plus script---------------------------------------------------------VARIABLE s_table_name varchar2(30)----------------------------------------------------------- Preface a bind variable with a colon to reference it-- in a PL/SQL block.---------------------------------------------------

Who's Running What?

2008-09-16T10:49:00.007-04:00

Ttitle Left "Who's Running What" Skip 2Set Linesize 200 Pagesize 200Column Sid Format 999Column Serial# Format 99999 Heading Serial|# Column Audsid Format 99999 Heading Aud|SidColumn Username Format A10 Heading Db|UserColumn Osuser Format A20 Heading Os|UserColumn Machine Format A20 Heading MachineColumn Terminal Format A12 Heading TerminalColumn Process Format A10 Heading