Mike Moore
Unknownnoreply@blogger.comBlogger20125
Updated: 3 hours 22 min ago
Limit length of listagg
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.
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.
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.
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.
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.
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.
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
--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
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
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.
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
SELECT as many rows as indicated by column value
This select statement will return as many rows as are indicated by the value of a column. For example, if the column value is 7, then 7 copies of that row will be returned.
CREATE TABLE S
(
RNUM NUMBER,
TPART CHAR(10),
SQ NUMBER
)
;
Insert into S
(RNUM, TPART, SQ)
Values
(101, 'TEST_PART1', 1);
Insert into S
(RNUM, TPART, SQ)
Values
(102, 'TEST_PART2', 2);
Insert into S
(RNUM, TPART, SQ)
Values
(103, 'TEST_PART3', 3);
Insert into S
(RNUM, TPART, SQ)
Values
(105, 'TEST_PART5', 5);
COMMIT;
SELECT rnum, tpart, sq
FROM s s_alias, TABLE(SELECT COLLECT(dummy)
FROM DUAL
CONNECT BY LEVEL <= s_alias.sq);
CREATE TABLE S
(
RNUM NUMBER,
TPART CHAR(10),
SQ NUMBER
)
;
Insert into S
(RNUM, TPART, SQ)
Values
(101, 'TEST_PART1', 1);
Insert into S
(RNUM, TPART, SQ)
Values
(102, 'TEST_PART2', 2);
Insert into S
(RNUM, TPART, SQ)
Values
(103, 'TEST_PART3', 3);
Insert into S
(RNUM, TPART, SQ)
Values
(105, 'TEST_PART5', 5);
COMMIT;
SELECT rnum, tpart, sq
FROM s s_alias, TABLE(SELECT COLLECT(dummy)
FROM DUAL
CONNECT BY LEVEL <= s_alias.sq);
ORA-06502: PL/SQL: numeric or value error: host bind array too small
ORA-06502: PL/SQL: numeric or value error: host bind array too small
With 10gR2 dbms_output has been improved to handle more that 255 characters. I ran into this problem when I forgot to consider that it is the CLIENT ( not necessiarily the db server) that must be 10gR2. The PL/SQL procedure would run fine for me because I happened to have the 10gR2 client installed on my PC. Our QA had an older version of the client and kept running into this ora-06502.
So, it's probably not a good idea to drop those string chunking procedures until you are sure that everybody is off of the older clients.
With 10gR2 dbms_output has been improved to handle more that 255 characters. I ran into this problem when I forgot to consider that it is the CLIENT ( not necessiarily the db server) that must be 10gR2. The PL/SQL procedure would run fine for me because I happened to have the 10gR2 client installed on my PC. Our QA had an older version of the client and kept running into this ora-06502.
So, it's probably not a good idea to drop those string chunking procedures until you are sure that everybody is off of the older clients.
MERGE and Database Link Problem
Using 10g R2 database.
When trying to do a MERGE where the target table is on the remote database and the source table is a sub-select on the local database, the following error was raised:
ORA-01008: not all variables bound
The MERGE statement in question had no bind variables.
Furthermore, if I change the target table to an identical local table it works just fine, so it's not a syntax problem.
I searched all over the web and all over technet and oracle.com but found only a few scant references to this problem. I was unable to find any restriction documented under DISTRIBUTED TRANSACTIONS in the Oracle documentation. I now believe that MERGE will only work over a DB Link when the target is on the local database and the source table is on the remote database.
If you have information about this restriction, please leave a comment.
When trying to do a MERGE where the target table is on the remote database and the source table is a sub-select on the local database, the following error was raised:
ORA-01008: not all variables bound
The MERGE statement in question had no bind variables.
Furthermore, if I change the target table to an identical local table it works just fine, so it's not a syntax problem.
I searched all over the web and all over technet and oracle.com but found only a few scant references to this problem. I was unable to find any restriction documented under DISTRIBUTED TRANSACTIONS in the Oracle documentation. I now believe that MERGE will only work over a DB Link when the target is on the local database and the source table is on the remote database.
If you have information about this restriction, please leave a comment.
Using Dynamic Sql to work around PL/SQL bugs
A coworker encountered this problem. We search the Internet for a solution, but nothing was found. I am hoping that by posting this, others having the same problem will quickly find a solution. Furthermore, this example illustrates a more general principle which is that sometimes things work in SQL which do not work in PL/SQL and a simple solution is to use dynamic sql.
This problem is encountered when trying to access an NVARCHAR2 using a TABLE function in PL/SQL.
ORA-12714: invalid national character set specified
Originally written as:
FOR c IN ( SELECT * FROM TABLE (p_question_tab_i) ) LOOP
Processing….
END LOOP;
Raises error: ORA-12714: invalid national character set specified
Then re-written using dynamic SQL:
OPEN que_cursor FOR 'SELECT * FROM TABLE (:question_tab)'
USING p_question_tab_i;
The above example works correctly…
question_tab TABLE OF QUESTION_OBJis
SELECT_TYPE VARCHAR2(10)
DISPLAY_TEXT NVARCHAR2(4000)
PROMPT_TYPE_CODE VARCHAR2(50)
Technically, this is not a PL/SQL bug. It is, however, a feature that is supported in SQL but not PL/SQL. This often happens with new SQL features where PL/SQL has not 'caught up' yet.