All posts by caulfiek

Debugging Views that don’t compile

If you’re running SQLDeveloper and you compile a View, it will show a little red X next to it if there are issues.

However it won’t actually tell you what the problem is!

Go into sqlplus and enter this command:

 show errors view my_schema.kc_tradedetails

You should then see something like this

 Errors for VIEW MY_SCHEMA.KC_TRADEDETAILS:
 LINE/COL ERROR -------- ----------------------------------------------------------------- 0/0 ORA-00904: "TRADEFIELDS"."COMPANY_SECTOR_CODE": invalid identifier

Which is now a straightforward debugging task

Formatting JIRA Issues List with XSL

I use JIRA allot but I don’t like how it formats it’s Issues List, and I’d rather provide my own formatting.

Now I believe you can apply templates to the actual JIRA website if you are an administrator, or if you know one, but in many occasions you might be out on a client site where you can’t do this.

However all is not lost you can simply Export the List as XML and work on it independently with XSL.

Jira Export to XML

 

I’ve created some XSL that will transform the XML into a nice webpage with comments included in the list.

Before you can apply the XSL you need to remove the top 11 line of the XML as this contains stuff you don’t need and that interferes with the XSLT process.

 more +11 %recent% > MyJiraReport.xml

Then you use msxsl.exe to transform you xml into a html file.

 msxsl.exe MyJiraReport.xml JiraIssues.xsl -o MyJiraReport.html 

You can download msxsl.exe from here

http://www.microsoft.com/en-gb/download/details.aspx?id=21714

I’ve created a small DOS batch file that will do all this for you, it even picks up the most recent xml file in your download area. The xsl is there too.

https://github.com/caulfiek/XSLT

Once opened in Chrome you can Print -> Save to PDF, and your report comes out really nice.

Patently, as this is html you can add all sorts of nice CSS to it. At the moment it’s quite plain but I’ve left a comment in the XSL,  so you can put the CCS reference in, if you want.

 

SVN Scripts for managing several folders

You may find that you have several directories all connected to (Subversion) SVN so that their code, scripts, xml, ini files etc can all be managed under Subversion source control (http://subversion.apache.org/).
 
It’s possible that you have this repeated on several servers!
 
Here’s a nice script that will check your directories to see if they are currently managed by SVN.
If they are, then the script will svn update the directory.
If they aren’t, then the script will svn checkout the directory.
 
You can add as many directories as you want to this.
 
This saves me loads of time when building new app servers.
 
<pre>
@echo off & setlocal enableextensions
cls
 
rem ******************************************************
rem * Update directory structure for all SVN repos
rem *
rem * Kieran Caulfield, June 2012. Excelian Ltd.
rem *
rem ******************************************************
 
echo This will update all svn attached directories.
rem pause
 
Set drive=D:
 
%drive%
echo Drive is set to %drive%
 
Set SVN_User=”kieran.caulfield@mycompany.com”
 
echo Updating SVN Directories…
rem *********************************
 
set myFolder=D:\MyScripts
set svn_URL=”https://mycompany.jira.com/svn/MYPROJ/trunk/MyScripts”
Call :SVN_Folder
 
 
set myFolder=D:\MyScripts2
set svn_URL=”https://mycompany.jira.com/svn/MYPROJ/trunk/MyScripts2″
Call :SVN_Folder
 
set myFolder=D:\CrystalReports
set svn_URL=”https://mycompany.jira.com/svn/MYPROJ/trunk/CrystalReports”
Call :SVN_Folder
 
echo Please check results and press any key to end….
pause
 
GOTO End
 
rem *****************************
:SVN_Folder
rem *****************************
 
if exist “%myFolder%\.svn” (
echo folder %myFolder%\.svn already exists – SVN UPDATE
echo D:\TortoiseSVN\bin\svn update “%myFolder%”
svn update “%myFolder%”
)
 
if not exist “%myFolder%\.svn” (
  echo folder %myFolder%\.svn not found – SVN CHECKOUT %SVN_URL%
  echo svn checkout %SVN_URL% “%myFolder%” –username %SVN_User%
svn checkout %SVN_URL% “%myFolder%” –username %SVN_User%
)
 
GOTO :EOF
rem *******************************
 
:End
</pre>
 
 
Note, I use the Tortoise SVN Client, it’s brilliant.
http://tortoisesvn.net/

RDCMan manage multiple mstsc sessions

If you need to manage several servers at the same time your screen can get rather cluttered with lots of MSTSC sessions running.

Try using RDCMan, get it here

http://www.microsoft.com/en-us/download/confirmation.aspx?id=21101

After install, open it up and File->New and create a group container for your specific servers (say you might have 4 UAT servers so call the group “UAT”).

The simply add each server you need to the group.

Then right click and “Connect to Server” on each server in your group.

πŸ™‚ 

SQL Plus Spool as CSV

To get a CSV output from SQL Plus you might be inclined to concatenate the SELECT columns together with commas:

SPOOL MyOutputFile.csv

SELECT col1 ||’,’|| col2 ||’,’|| col3 from table;

SPOOL OFF;

This can get quite messy and doesn’t read well.

But you don’t have to do this, if you set COLSEP to β€œ,” you will get a csv file with tab style spacing and you can keep your SQL formatted with a regular SELECT statement.

SET COLSEP ,

You can then easily suck out the whitespace afterwards with a Perl one liner

                perl -pi.bak -e “s/ *,/,/g;s/, */,/g” MyOutputFile.csv

This makes a backup of the file and does in inline replacement, taking out all spaces following or preceeding a comma.

This will ignore spaces embedded in a string, say “xxx,Mr Joe Bloggs,xxxx”.

SQL Developer cannot find JVM

I installed the Oracle 11gR2 client (32 bit) on to a Win 2008 R2 64bit server, as we have 32 bit apps to support.

When I ‘point’ SQLDeveloper at the Java 6 SE JVM, I get “Unable to find Java Virtual Machine” message.

This is because I have Java 6 SE 64 bit version installed.

You need to install the 32 bit JDK , so that it lives in “C:\Programs Files (x86)”.

Then repoint the variable “SetJavaHome” that is in sqldeveloper.conf, and restart.

Unable to open excel macro from Control-M

I’ve been trying to get several Control-M jobs to work on our new Win 2008 R2 x64 Servers, but they always fail as they are trying to open scripts that call Excel macros as part of their function.

2012-08-14 17 : 24: 59 Error generating Sample Report [SampleRptBatch][CreateFileNames] Microsoft Office Excel cannot access the file ‘D:\SampleReport\SampleFormat.xlsm’. There are several possible reasons:

β€’ The file name or path does not exist.

β€’ The file is being used by another program.

β€’ The workbook you are trying to save has the same name as a currently open workbook.

On analysis, I can see that the Control-M Agent (ver 7) is running jobs as a Service, and not as a TCP or RDP session as I had thought.

This all comes down to an issue with Services, Win 2008 R2 x64 and Excel 2007. For Excel to open, when called from a service, it requires a “Desktop” folder to be placed in

    C:\Windows\SysWOW64\config\systemprofile\Desktop

Qgawa’s reply here, helped me sort this out.

http://social.msdn.microsoft.com/Forums/en/innovateonoffice/thread/b81a3c4e-62db-488b-af06-44421818ef91