Mike's PL/SQL Notes

Friday, August 14, 2015

Limit the number of list entries that listagg will consume.

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.

Tuesday, September 3, 2013

How to disable Firefox mixed content blocking (in one simple graphic)

Pertains to Firefox 23.0.1 and maybe later.
I was surprised this information was so hard to find, so hopefully this will make it easier for you.


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

This document demonstrates some of the problems I've encountered when using SQLPlus to insert text that has multiple lines. You can run the INSERT statements below, to demonstrate the specific problem which I state in the actual text being inserted.

--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
set sqlblanklines off
set define off
spool multiline_text.log

-------- In these examples I am using Oracle 'Q' strings: aka Quoted Strings
-------- For example, instead of quoting a string like this:
--------   'Joe''s Garage is where the ''action'' is'
-------- I do it like this:
--------   q'~Joe's Garage is where the 'action' is~'
-------- In a quoted string, the character after the initial q'
-------- can be any character that is not in the actual string content.
-------- I've chosen to use the tilde character.

---------- THE BLANK LINE --------
INSERT INTO txt ( str) 
VALUES (q'~This shows it is NOT okay to have a 

blank line unless you 'set sqlblanklines on'.~');

set sqlblanklines on

INSERT INTO txt ( str) 
VALUES (q'~This shows it is okay to have a 

blank line after you 'set sqlblanklines on'.~');


---------- THE SLASH CHARACTER --------

INSERT INTO txt ( str) 
VALUES (q'~This shows it is NOT okay to have a 
/
slash by itself on a line~');


INSERT INTO txt ( str) 
VALUES (q'~This shows it is [NOT] okay to have a 
slash followed by a space character. You can't see it, but it's there.~');


INSERT INTO txt ( str) 
VALUES (q'~This shows it is okay to have a 
/slash as a starting character provided that the
slash is [not the only] character~');


INSERT INTO txt ( str) 
VALUES (q'~This shows it is okay to have a 
/ slash as a starting character
even if it is followed by a blank.~');



-----------THE DOT CHARACTER----------

INSERT INTO txt ( str) 
VALUES (q'~This shows it is [not] okay to have a 
.
dot on a blank line by itself.~');


INSERT INTO txt ( str) 
VALUES (q'~This shows it is [not] okay to have a 
.  
dot plus some trailing blanks on a line by itself.~');


INSERT INTO txt ( str) 
VALUES (q'~This shows it is okay to have a 
. dot plus more text
even when dot is the first character~');


---------THE SEMI-COLON CHARACTER ------

INSERT INTO txt ( str) 
VALUES (q'~This shows it is okay to have a; 

blank line provided you set sqlblanklines on~');

INSERT INTO txt ( str) 
VALUES (q'~This shows it is okay to have a; plus more

blank line provided you set sqlblanklines on~');


---------THE POUND SIGN CHARACTER ------


INSERT INTO txt ( str) 
VALUES (q'~This shows it [IS] okay to have a 
#
pound-sign on a line by itself~');

INSERT INTO txt ( str) 
VALUES (q'~This shows it is [not] okay to have a 
# plus more
text when the pound sign is the first character~');

INSERT INTO txt ( str) 
VALUES (q'~This shows it is  okay to have a 
a pound-sign # so long
as the #is not the first character~');

-- ;

rollback;
spool off
--------------------------------RESULTS----------------------

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.










      



Tuesday, July 8, 2008

Performing a JOIN on groups of values using nested tables

-- Join on a nested table example:

-- this example takes 2 tables (c & d). On each table it does
-- a group by, and the details of that group are collected into a nested table.
-- tables c & d are then joined on that nested table

-- I probably would never do this, but I do think it is interesting.

--DROP TABLE D CASCADE CONSTRAINTS;

CREATE TABLE d (animal VARCHAR2(5 BYTE), grp NUMBER(6));

SET DEFINE OFF;

INSERT INTO d (animal, grp) VALUES ('cat', 4);
INSERT INTO d (animal, grp) VALUES ('cat', 7);
INSERT INTO d (animal, grp) VALUES ('toad', 7);
INSERT INTO d (animal, grp) VALUES ('frog', 4);
INSERT INTO d (animal, grp) VALUES ('frog', 7);
COMMIT ;

-- DROP TABLE c CASCADE CONSTRAINTS;

CREATE TABLE c( animal VARCHAR2(5 BYTE), grp NUMBER(6));

SET DEFINE OFF;
INSERT INTO c (animal, grp) VALUES ('cat', 1);
INSERT INTO c (animal, grp) VALUES ('frog', 1);
INSERT INTO c (animal, grp) VALUES ('frog', 66);
INSERT INTO c (animal, grp) VALUES ('cat', 33);
INSERT INTO c (animal, grp) VALUES ('cat', 44);
INSERT INTO c (animal, grp) VALUES ('cat', 66);
INSERT INTO c (animal, grp) VALUES ('frog', 44);
INSERT INTO c (animal, grp) VALUES ('toad', 44);
COMMIT ;

SELECT grp, animal FROM c ORDER BY grp, animal;
SELECT grp, animal FROM d ORDER BY grp, animal;

CREATE OR REPLACE TYPE string20_table IS TABLE OF VARCHAR2(20);

DECLARE
mystr VARCHAR2(80);
BEGIN
FOR cur1 IN
(SELECT *
FROM (SELECT grp grp1, CAST(COLLECT(animal) AS string20_table) tab1
FROM c GROUP BY grp)
JOIN
(SELECT grp grp2, CAST(COLLECT(animal) AS string20_table) tab2
FROM d GROUP BY grp)
ON tab1 = tab2
)
LOOP
DBMS_OUTPUT.put_line( TO_CHAR(cur1.grp1)
|| '='
|| TO_CHAR(cur1.grp2));
END LOOP;
END;
-- final output
-- 1=4
-- 44=7
-- 66=4

Labels