SQL> select student_name, course_id from studentx order by student_name
STUDENT_NAME COURSE_ID
------------ ---------
Chris Jones A102
Chris Jones C102
Chris Jones C102
Chris Jones A102
Chris Jones A103
Chris Jones A103
Joe Rogers B103
Joe Rogers A222
Joe Rogers A222
Kathy Smith B102
Kathy Smith A102
Kathy Smith A103
Kathy Smith B102
Kathy Smith A103
Kathy Smith A102
Mark Robert B103
16 rows selected.
SQL> WITH x AS
(SELECT student_name,
course_id,
ROW_NUMBER () OVER (PARTITION BY student_name ORDER BY 1) AS grouprownum
FROM studentx)
SELECT student_name,
LISTAGG (CASE WHEN grouprownum < 5 THEN course_id ELSE NULL END, ',')
WITHIN GROUP (ORDER BY student_name)
courses
FROM x
GROUP BY student_name
STUDENT_NAME
------------
COURSES
--------------------------------------------------------------------------------
Chris Jones
A102,A102,C102,C102
Joe Rogers
A222,A222,B103
Kathy Smith
A102,A103,B102,B102
Mark Robert
B103
4 rows selected.
Friday, August 14, 2015
Limit the number of list entries that listagg will consume.
Tuesday, September 3, 2013
How to disable Firefox mixed content blocking (in one simple graphic)
At the company I work for we have an HTTP iframe inside an HTTPS web page. The new version of Firefox objects to this mixed content and forces the user to respond by clicking on a little shield icon in order to allow the mixed content. However Firefox does not "remember" this override, nor does it allow a "white list" of domains where you don't care about mixed content. You can however disable the checking entirely by changing these options shown in the graphic above. PLEASE do not do this unless you completely understand the security implications.
If this is what you were looking for, please leave a comment, just say Hi or something.
Tuesday, April 16, 2013
Windows Search (Windows 7) IS Broken - indexer quits parsing after 39k
This post has nothing to do with PL/SQL. It's about the Windows 7 search feature called "Windows Search" The bottom line is it stops indexing text files (maybe other types too) at about the 39K mark. This is NOT a problem with my set-up or configuration of Windows Search. I know this because I created a simple text file with a unique 10-digit number on each line. Those numbers which were towards the top of the file, say lines 1 thru 3414 (approximate, I don't recall the exact cut-off point) were properly indexed. I could type in any one of those numbers on the search bar and the proper file name which contained that number would be shown. Numbers towards the bottom of the text file, were NOT indexed. i.e. the Search erroneously found that no file contained that value.
So, you are not going mad, it's truly broken. I searched for hours trying to find a solution, maybe a registry parameter, or something, ... but nothing. There was actually a registry parameter, but it was for the old indexer on windows XP. maxTextFilterBytes I finally gave up on trying to find a solution and instead split my files into smaller files.
If anybody finds a solution, please let me know.
Monday, May 7, 2012
Convert Excel Julian date to Oracle Date datatype
SQL> select to_date(to_char(2415019+41029),'J') dd from dual
DD
---------
30-APR-12
1 row selected
Where 41029 is the Excel Julian Date.
Friday, April 13, 2012
SQLPLUS multi-line quoted text
--Note that these problems occur only with SQLPlus, Using TOAD, (and I assume SQL Developer), there are no errors with any of the inserts.
I used SQLPLus 11g client on Windows 7. Different configurations may produce different results.
I'd be interested in hearing about any other anomalies you find with multi-line text.
CREATE TABLE TXT (str varchar2(500));
set echo on
Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.
C:\Users\mmoore>sqlplus
SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 16 11:16:11 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter user-name: / @mydb
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set echo on
SQL> set sqlblanklines off
SQL> set define off
SQL> spool multiline_text.log
SQL>
SQL> -------- In these examples I am using Oracle 'Q' strings: aka Quoted Strings
SQL> -------- For example, instead of quoting a string like this:
SQL> -------- 'Joe''s Garage is where the ''action'' is'
SQL> -------- I do it like this:
SQL> -------- q'~Joe's Garage is where the 'action' is~'
SQL> -------- In a quoted string, the character after the initial q'
SQL> -------- can be any character that is not in the actual string content.
SQL> -------- I've chosen to use the tilde character.
SQL>
SQL> ---------- THE BLANK LINE --------
SQL> INSERT INTO txt ( str)
2 VALUES (q'~This shows it is NOT okay to have a
3
SQL> blank line unless you 'set sqlblanklines on'.~');
SP2-0734: unknown command beginning "blank line..." - rest of line ignored.
SQL>
SQL> set sqlblanklines on
SQL>
SQL> INSERT INTO txt ( str)
2 VALUES (q'~This shows it is okay to have a
3
4 blank line after you 'set sqlblanklines on'.~');
1 row created.
SQL>
SQL>
SQL> ---------- THE SLASH CHARACTER --------
SQL>
SQL> INSERT INTO txt ( str)
2 VALUES (q'~This shows it is NOT okay to have a
3 /
ERROR:
ORA-01756: quoted string not properly terminated
SQL> slash by itself on a line~');
SP2-0734: unknown command beginning "slash by i..." - rest of line ignored.
SQL>
SQL>
SQL> INSERT INTO txt ( str)
2 VALUES (q'~This shows it is [NOT] okay to have a
3 /
ERROR:
ORA-01756: quoted string not properly terminated
SQL> slash followed by a space character. You can't see it, but it's there.~');
SP2-0734: unknown command beginning "slash foll..." - rest of line ignored.
SQL>
SQL>
SQL> INSERT INTO txt ( str)
2 VALUES (q'~This shows it is okay to have a
3 /slash as a starting character provided that the
4 slash is [not the only] character~');
1 row created.
SQL>
SQL>
SQL> INSERT INTO txt ( str)
2 VALUES (q'~This shows it is okay to have a
3 / slash as a starting character
4 even if it is followed by a blank.~');
1 row created.
SQL>
SQL>
SQL>
SQL> -----------THE DOT CHARACTER----------
SQL>
SQL> INSERT INTO txt ( str)
2 VALUES (q'~This shows it is [not] okay to have a
3 .
SQL> dot on a blank line by itself.~');
SP2-0734: unknown command beginning "dot on a b..." - rest of line ignored.
SQL>
SQL>
SQL> INSERT INTO txt ( str)
2 VALUES (q'~This shows it is [not] okay to have a
3 .
SQL> dot plus some trailing blanks on a line by itself.~');
SP2-0734: unknown command beginning "dot plus s..." - rest of line ignored.
SQL>
SQL>
SQL> INSERT INTO txt ( str)
2 VALUES (q'~This shows it is okay to have a
3 . dot plus more text
4 even when dot is the first character~');
1 row created.
SQL>
SQL>
SQL> ---------THE SEMI-COLON CHARACTER ------
SQL>
SQL> INSERT INTO txt ( str)
2 VALUES (q'~This shows it is okay to have a;
ERROR:
ORA-01756: quoted string not properly terminated
SQL>
SQL> blank line provided you set sqlblanklines on~');
SP2-0734: unknown command beginning "blank line..." - rest of line ignored.
SQL>
SQL> INSERT INTO txt ( str)
2 VALUES (q'~This shows it is okay to have a; plus more
3
4 blank line provided you set sqlblanklines on~');
1 row created.
SQL>
SQL>
SQL> ---------THE POUND SIGN CHARACTER ------
SQL>
SQL>
SQL> INSERT INTO txt ( str)
2 VALUES (q'~This shows it [IS] okay to have a
3 #
3 pound-sign on a line by itself~');
1 row created.
SQL>
SQL> INSERT INTO txt ( str)
2 VALUES (q'~This shows it is [not] okay to have a
3 # plus more
SP2-0042: unknown command "plus more" - rest of line ignored.
3 text when the pound sign is the first character~');
1 row created.
SQL>
SQL> INSERT INTO txt ( str)
2 VALUES (q'~This shows it is okay to have a
3 a pound-sign # so long
4 as the #is not the first character~');
1 row created.
SQL>
SQL> -- ;
SQL>
SQL> rollback;
Rollback complete.
SQL> spool off
Wednesday, April 11, 2012
Extract from SalesForce, Load to Oracle
I did it this way for 2 reasons.
1)These are the tools I know how to use.
2)There are things I am not allowed to do at work due to access rights.
This is not intended to be a perfect document, it's just intended to be helpful.
No doubt, you will find problems which I did not encounter. If you do, please leave notes in the comments to help the next guy out.
---------------------OVERVIEW--------------------------------------------------------
NOTE: When I mean something literally I use the left-bracket and right-bracket
character like this:
[that's literally what "I" mean]
This is the results of dozens of failed attempts due to SQLPLUS idiosyncrasies of dealing with
multi-line text. If you don't have multi-line text, many of the following edits won't be applicable.
The process I use is basically
1) extract data from Salesforce into a file
2) Use Excel to format salesforce data into Oracle INSERT statements
3) edit all the things out of the file which SQLPLUS will find objectionable
and put in a few additional script commands like COMMIT
4) run SQLPLUS
This document assumes that the columns you extract from SalesForce will match exactly with the Oracle
DB table you want to load.
Software you will need.
1) The Salesforce Data Loader program
2) TextPad (get it off the web. It's the only text editor that won't screw up your files.)
3) Excel 2007 or later (maybe earlier versions would work too)
4) An Oracle database.
-----------------------INSTRUCTIONS---------------------------------------------------------------
1) Use Salesforce Data Loader to create the extract .csv file (hence forth called 'the extract file').
If you don't know how to do this, Google it. There is much better documentation on this than
I could ever provide.
2) At this point, I copy all of the column headers in the .cvs file and use them to create an Oracle table.
I define all the columns as VARCHAR2(4000) just for ease but you can use whatever matches the data
if you want.
3) Use Excel to open the extract file, from step 1, and do SAVE AS sf_extract_edited.xlxs. In other words
immediately save it back under a new name so that you don't overwrite the extract file in step 1.
It does not really matter what you name it but it is a good idea to include the word "edited" so that
you know that this is an edited version.
3.1) Rename the first worksheet tab (at the bottom) to [raw_data].
3.2) Do a FIND on [#NAME] using the following options ...
3.2.1) ctrl-f
3.2.2) click on [Options] button on the FIND-dialog pop-up window.
3.2.3) for [Look in:] dropdown, choose [Values] <--IMPORTANT !
3.2.4) make sure the entire sheet will be searched and click on Find Next
3.2.5) if the find gets a hit, note that in for formula field you will
probably see a value that starts with [=]
3.2.6) on the formula field, insert a ['] before the [=] (put a single quote before the equal sign)
this will prevent excel from interpreting the equal sign as a formula indicator.
3.2.7) repeat this process until no more hits on the FIND.
3.3) Insert an empty column to the left of column A (this empty column will become the new column A)
I'm doing this so that the cells line up with the cells of the next sheet which we are going to create
3.4) create a new "Sheet", name the new sheet 'inserts'
3.5) Create an insert statement that matches the table you will be loading then
position cursor at cell A2 and paste in the first part of your INSERT statement, for example:
INSERT INTO
ZD_OPPORTUNITY (ID, ISDELETED, ACCOUNTID, RECORDTYPEID, NAME,
DESCRIPTION, STAGENAME, AMOUNT, PROBABILITY, EXPECTEDREVENUE,
CLOSEDATE, TYPE, NEXTSTEP, LEADSOURCE, ISCLOSED, ISWON, FORECASTCATEGORY,
FORECASTCATEGORYNAME, HASOPPORTUNITYLINEITEM,
PRICEBOOK2ID, OWNERID, CREATEDDATE, CREATEDBYID, LASTMODIFIEDDATE,
LASTMODIFIEDBYID, SYSTEMMODSTAMP, LASTACTIVITYDATE,
FISCALQUARTER, FISCALYEAR, FISCAL, SPLIT_TOTAL__C) VALUES (
3.6) select cell B2 and enter the formula [=CONCATENATE("q'~",raw_data!B2,"~',")] without
the outer brackets, on the formula line
3.6.1) If any text has more than 4000 characters, modify the above formula for that column to be ..
[=CONCATENATE("q'~", MID( raw_data!N11409,1,3200),"~',")]
excel and sqlplus count chars differently hence 3200 for a safe margin.
3.7) drag the cell out to the right to cover all of the columns that have data in the raw_data sheet
3.8) edit the right-most cell's formula so that your INSERT statement will end with [);] and not [,]
3.8.1) i.e. change this: [=CONCATENATE("q'~",raw_data!CQ2,"~',")]
to this: [=CONCATENATE("q'~",raw_data!CQ2,"~');")]
3.8) select all cells of column 2 and drag down to cover all rows in the raw_data sheet
3.9) save your work
4.0) open a new (empty) file using TextPad. You can get TextPad for free trial off the internet.
4.1) copy-paste the entire excel content of the "inserts" tab to the empty TextPad window.
4.2) insert the following at the top of the textpad window:
set sqlblanklines on
set define off
spool opp.log
4.3) insert SPOOL OFF at the bottom
4.4) save and quit out of EXCEL, we won't need it any more after this.
4.5) Save the TextPad file now, just for safety. Several edits will now need to be made to the TextPad file.
4.5.1) n/a
4.5.2) Replace all ["INSERT] with [INSERT] i.e. remove the quote mark
4.5.3) Replace all [VALUES ("] with [VALUES (] i.e. remove the quote mark
4.5.4) This one is a little tricky and requires using a regular expression for the Replace.
The intent is that no text line should start with a pound-sign so we are inserting a blank.
On the Replace dialog box, put a check mark in the Regular expression option, then do
Replace all [^#] with [ #]
Now un-check Regular express option.
4.5.6) Replace all ["q'~] with [q'~]
4.5.7) Replace all [~',"] with [~',]
4.5.8) Replace all [);"] with [);]
4.5.9) Next we will change all lines that end with [;], except for ends with [');], to end with [:]
This is to pick up cases such as the following multi-line text example:
q'~A Multi-line text;
where a text line ends with semi-colon
will cause SQLPLUS to think the line has ended and you will get
ERROR:
ORA-01756: quoted string not properly terminated~'
1) first replace all [');] with [````]. So they won't be picked up by our next replace.
2) then replace all [; *$] with [:] using regular expression
This says, If the line ends with semi-colon, or semi-colon and a bunch of spaces,
replace it with a colon. If it is not acceptable for you to make slight changes
like this, then you will have to figure something else out.
WARNING: If you already coded COMMIT; it is now COMMIT:, go fix it.
3) change all [````] back to [');]
4) Replace all [^/$] with [ ] using regular expression
SQLPLUS does not like a text line that only contains a slash.
5) Replace all [^\.$] with [ ] using regular expression
SQLPLUS does not like a text line that only contains a dot.
4.5.9) optional - remove all tab characters
1) check the Hex box and replace [09] with []
4.5.10) Save the file and close TextPad
5.0) Get into SQLPLUS and run the INSERTs you just created.
One last thing, after loading the table, carefully check any columns that are supposed to have SalesForce dates. Excel sometimes turns these into Julian dates. If you end up with excel julian dates in your oracle tables, you can translate by doing:
SELECT to_date(to_char(2415019+closedate),'J') as closedate
where closeddate is the Excel julian date.