Wednesday, November 21, 2018

ORA-01031: insufficient privileges in Oracle DB on Linux


If you get
ERROR: ORA-01031: insufficient privileges.
while trying to connect as sysdba in sqlplus:
SQL> connect / as sysdba
after installation of Oracle 11g on CentOS.
One reason might be that you should be connecting as oracle OS user which has the privileges.

That user didn't have a default password so I had to create one:
$ sudo passwd oracle
then
$ su - oracle

$ sqlplus '/ as sysdba'

and create a regular non sysdba user:
SQL> CREATE USER admin IDENTIFIED BY passwd;

Then you might want to open TCP port in the firewall on your CentOS box for remote connect:

Main Oracle Net service port:
$ sudo firewall-cmd --zone=public --add-port=1521/tcp --permanent

This is the default port for admin GUI:

$ sudo firewall-cmd --zone=public --add-port=8080/tcp --permanent

$ sudo firewall-cmd --reload

The GUI is enabled by running this function:
EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);
The GUI URL will be:
http://<CentOS box IP address>:8080/apex/f?p=4950:1

Friday, September 14, 2018

Jasper server errors on evaluating parameters that are commented out

Jasper server might return error like this

org.postgresql.util.PSQLException: The column index is out of range: 15, number of columns: 14. at 
...
org.apache.commons.dbcp.DelegatingPreparedStatement.setNull(DelegatingPreparedStatement.java:104) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.setStatementParameter(JRJdbcQueryExecuter.java:604) at

...
if there's a parameter in the comments of the report's query ($P{param} expression) as the server seems to try to evaluate those expression. The actual error might be different but watch out for parameter, field and variable expressions in the comments. 

Wednesday, June 20, 2018

could not initialize lazy properties error in Jasper Server

Sometimes after uploading or importing a modified version of a report Jasper server returns an error like this:

could not initialize lazy properties: [com.jaspersoft.jasperserver.api.metadata.common.service.impl.hibernate.persistent.RepoFileResource#9370]
org.hibernate.exception.GenericJDBCException : could not initialize lazy properties: [ com.jaspersoft.jasperserver.api.metadata.common.service.impl.hibernate.persistent.RepoFileResource #9370] at ...

This is most likely due to caching of internal server references and could be resolved by restarting the server:

$ jasperreports-server-cp-6.1.0/ctlscript.sh restart

Thursday, March 15, 2018

If OpenVPN client breaks DNS configuration



Sometimes, Linux OpenVPN client changes DNS configuration and then <VPN server domain> becomes unreachable so you get error messages like:

RESOLVE: Cannot resolve host address: <VPN server domain here> (Name or service not known)

On initial connection you see this output in the console:

dhcp-option DNS <IP here>
dhcp-option DOMAIN <VPN server domain here>
/sbin/ip route add <IP here>/32 via <IP here>


systemd-resolve --status command shows this:

Global
         DNS Servers: <IP from above here>
          DNS Domain: <domain here>
...


cat /etc/resolv.conf command shows new DNS configuration by OpenVPN:
...
search <domain here>


To resolve this issue, just remove this new line from /etc/resolv.conf with an editor, e.g.

sudo nano /etc/resolv.conf

or delete the line with sed:


sudo sed -i.bak '/<domain>/d' /etc/resolv.conf

or restore from a backup but be aware that the file could really be a link, e.g. 
resolv.conf -> /run/resolvconf/resolv.conf

UPDATE: resolv.conf is most likely dynamically generated instead of a static file.

E.g. in Ubuntu check your NetworkManager configuration:
sudo NetworkManager --print-config
if there's
dns=systemd-resolved
then edit settings in
/run/resolvconf/interface/systemd-resolved
and update
sudo resolvconf -u
In my case there was also
/run/resolvconf/interface/tun0.openvpn
file (created by OpenVPN) which can be safely deleted to avoid interference:
sudo rm /run/resolvconf/interface/tun0.openvpn

The DNS settings should be back to normal now, if not, you may need to run

service networking restart

I have this function defined in .bash_aliases which fixes DNS configuration before connecting via OpenVPN:

vpn_fixed_dns() {
    echo "Fixing resolv.conf"
    echo "nameserver 127.0.0.53" | sudo tee /run/resolvconf/interface/systemd-resolved >/dev/null
    sudo rm /run/resolvconf/interface/tun0.openvpn
    echo "Running sudo resolvconf -u"
    sudo resolvconf -u
    cat /etc/resolv.conf
    echo "Running sudo openvpn"
    sudo openvpn --script-security 2 --config /etc/openvpn/config/config.ovpn

}

Wednesday, February 28, 2018

Apply button in Jasper reports doesn't work

You may encounter a weird problem when "Apply" button to run a report in Jasper server doesn't do anything and there's no error in the Jasper log.
In my case the reason for that was missing user attribute which was referenced in the report, e.g. for report parameter LoggedInUserAttribute_regionId, the corresponding user attribute regionId was not set.

Tuesday, October 4, 2016

How to get to the correct position of a syntax error in postgresql

Sometimes position of a syntax returned by PostgreSQL is not correct. You try to go to the specified position in a query (in any text editor) and it shows something totally unrelated. Probably this is due to comments or new lines or something else. And with large queries it can be frustrating to look through it all for some really simple mistake.

In order to get the correct position you will need to execute the query in PostgreSQL's own pgAdmin III tool. The position number will still be incorrectly reported but the cursor will be automatically moved to the position of an error cause. Handy!

Thursday, July 14, 2016

"Error:null" when clicking the "Read Fields" button in the Query Editor

Sometimes the "Error:null" error  appears after clicking the "Read Fields" button in the Query Editor or after autorefresh. One of the causes for this can be usage of literal parameters like $P!{param} in the query which the editor cannot calculate at the time. Just delete the parameter temporarily and restore it after "read fields" completes.

How to detect if Jasper Reports output was generated by a scheduled run

Sometimes it is necessary to have some conditional logic in case a report is being run by a scheduler. In this case it is possible to use _ScheduledTime parameter from the built-in REPORT_PARAMETERS_MAP map.

Example expression:

$P{REPORT_PARAMETERS_MAP}.get("_ScheduledTime") == null ? "Normal" : "Scheduled"

Friday, May 20, 2016

Concatenate full name in reverse order with comma and optional title


Sometimes it is preferrable to output full name as a single field/column like
<last_name>, <title> <first_name>
where title and first name are both optional (as occurs in the real world). Because of the different combinations where title and/or first name are null there should be some conditional logic with concatenation. But you can do correct concatenation in a single expression using the fact that coalesce(right(title,0),', ') will return empty string when title is not null and  separator', ' otherwise:

select 
trim(
upper(family_name) || coalesce(', ' || title || ' ', '') || coalesce(coalesce(right(title,0),', ') || given_names, '')
) as person_name 
from person

Thursday, May 19, 2016

Move cursor to specific text in Word VBA




Sometimes it's necessary to replace placeholders (e.g. <content_placeholder1>) in a Word document with some generated or imported content using VBA macro. Just replacing the placeholder text via Selection.Find.Execute Replace:=wdReplaceAll  will not move a cursor, you need to do it as a separate step like below:

Sub moveToPlaceholder(tableName As String)

    Dim placeholderText As String

    placeholderText = "<" & tableName & ">"
    
    ' first find
    With Selection.Find
      .Text = placeholderText
      .Forward = True
      .Wrap = wdFindContinue
      .Format = False
      .MatchCase = False
      .MatchWholeWord = False
      .MatchWildcards = False
      .Execute
    End With
    
    ' now move cursor at the
    Selection.EndKey Unit:=wdLine
    
    ' now replace
    With Selection.Find
      .Text = placeholderText
      .Replacement.Text = ""
      .Forward = True
      .Wrap = wdFindContinue
      .Format = False
      .MatchCase = False
      .MatchWholeWord = False
      .MatchWildcards = False
      .Execute Replace:=wdReplaceAll
    End With
  
End Sub

Wednesday, July 16, 2014

ERROR: character with byte sequence <...> in encoding "UTF8" has no equivalent in encoding "WIN1252";

I've encountered this error "ERROR: character with byte sequence <...> in encoding "UTF8" has no equivalent in encoding "WIN1252";" while using MySQL Workbench to migrate data from PostgreSQL to MySQL. I was not sure why this was happening because the target MySQL database had UTF8 encoding and I thought that everything can be mapped to it.

The real root cause of this problem turned out to be the PostgreSQL driver I used to connect to the source DB and specified in the Workbench which was PostgreSQL ODBC Driver (ANSI) and after I changed it to PostgreSQL ODBC Driver (UNICODE) everything worked fine.

Monday, May 26, 2014

How to view .oxps (Open XPS) files on Windows 7

I didn't expect this simple thing to be so tricky, mostly because the utility name is not XpsConverter.exe but OxpsConverter.exe instead.  So here's a solution for you for when you cannot find an XpsConverter.exe:

In order to convert and view .oxps files on Windows 7 you need to use OxpsConverter.exe which should be located in C:\Windows\System32 folder, just try to run C:\Windows\System32\OxpsConverter.exe from command line.

If you don't have it, download and install a Windows update for you version from here:
http://support.microsoft.com/kb/2732059

Using the converter is very simple, just run
C:\Windows\System32\OxpsConverter.exe "file_to_convert.oxps"
and it will open a small GUI asking you where to convert the file to .xps format which can be opened by default in Windows 7.

NOTE: If you don't have then try looking for XpsConverter.exe
in %programfiles%\Windows Kits\8.0\bin\ or %programfiles(86)%\Windows Kits\8.0\bin\
if you happen to have Windows Driver Kit 8 (WDK) installed.

Wednesday, May 22, 2013

org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter

I was getting this error from Jasper server when leaving datetime input control blank:


Error Message

org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $3

Error Trace

org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $3 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257) at 

Where clause in the query for java.sql.Timestamp column was like this:
and (time_col > $P{inputStartCreatedDate} or $P{inputStartCreatedDate} is null)

The solution is to add explicit type conversion like this:
and (time_col > $P{inputStartCreatedDate} or $P{inputStartCreatedDate}::timestamp is null)


Thursday, February 21, 2013

Calculation type issue in iReport

I was creating a variable in iReport and requested calculation type didn't work, I was just getting the value of the field. The cause of this turned out to be the wrong variable class for the variable. I.e. java.lang.String instead of java.lang.Double.

Thursday, December 13, 2012

How to update one or several classes in jar or ear file without doing full build

Sometimes you would like to update just one or several classes in jar or ear archive which is created in some lengthy build process and you do not want to do that full build.

In this example I want to update Yourclass.class class file inside Yourjar.jar jar file and then update it inside Yourear.ear ear file. The class file is already built by your IDE in bin folder in this path:

C:\<path to project>\bin\com\<package path>\Yourclass.class

Copy your jar and ear file to some temporary work folder c:\temp.

First, we will copy the class file with its package path to some temporary folder c:\temp because jar packaging tool will not let you to specify exact target path/folder. Second, we will use jar tool to repackage.


xcopy /Y "C:\<path to project>\bin\com\<package path>\Yourclass.class" c:\temp\com\<package path>\Yourclass.class

jar uf Yourjar.jar com\<package path>\Yourclass.class

jar uf Yourear.ear Yourjar.jar

Thursday, November 8, 2012

java.lang.ClassNotFoundException when running a JUnit test


If you get this error when running a JUnit 3 or 4 test in Eclipse:

java.lang.NoClassDefFoundError: <your unit test class name>
    at java.lang.ClassLoader.defineClass1(Native Method)
 ...
    at org.junit.internal.requests.ClassRequest.getRunner(ClassRequest.java:24)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.<init>(JUnit4TestReference.java:32)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestClassReference.<init>(JUnit4TestClassReference.java:25)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestLoader.createTest(JUnit4TestLoader.java:41)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestLoader.loadTests(JUnit4TestLoader.java:31)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:452)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)

I've tried cleaning and building projects, restarting Eclipse and other suggested things to do in this case.

It turned out that errors in the project were responsible for that error. The project has another project with JRE specified incorrectly. As soon as I fixed this, unit test worked, even though the project still had some errors.

Monday, October 22, 2012

How to export java class or java resource from Oracle database

If you need to export java class or java resource loaded to Oracle database with loadjava use this:

1) get fully qualified name of a class/resource:


SELECT DBMS_JAVA.longname (object_name), user_objects.*
  FROM SYS.user_objects
 WHERE object_type IN ('JAVA CLASS', 'JAVA RESOURCE')

2) use DBMS_JAVA.export_resource or DBMS_JAVA.export_resource accordingly. This example outputs first 4000 chars only but you get the idea once you have the BLOB:

DECLARE
   v_blob      BLOB;
   stringvar   VARCHAR2 (4000);
BEGIN
   DBMS_LOB.createtemporary (v_blob, TRUE, DBMS_LOB.SESSION);

   --dbms_java.export_class( 'com/mysource/MyClass', v_Blob ) ;

   DBMS_JAVA.export_resource ('resources/resource.xml', v_blob);
   stringvar := UTL_RAW.cast_to_varchar2 (DBMS_LOB.SUBSTR (v_blob, 4000, 1));
   DBMS_OUTPUT.put_line (stringvar);
END;

Execute DDL statement over dynamic DB link


If you want to execute DDL statement <sql_statement> over DB link <db_link_name> try using this:


  DECLARE
    t_cursor PLS_INTEGER;
  BEGIN
    t_cursor := dbms_sql.open_cursor'||db_link_name||';
    dbms_sql.parse'||db_link_name||'(t_cursor, '''||sql_statement||''', dbms_sql.NATIVE);
    dbms_sql.close_cursor'||db_link_name||'(t_cursor);
  END;

This solution is quite tricky and I guess this wouldn't work for every DDL statement but it worked for me in 10g for enabling/disabling constraints and indexes. Another good thing about it is that DBlink name is dynamic and not hard-coded.

Search for a string in files in SunOS

The problem seems to be very easy to google for but many options for grep I was able to find didn't work for me. So if you want to get a list of file names containing some specific string in SunOS or Solaris you can use this command:

find . -type f | xargs grep -l "<string to search>"

use this if you want to ignore access/file read errors:

find . -type f 2>/dev/null | xargs grep -l "<string to search>"  2>/dev/null


oracle loadjava error: Unexpected Signal : EXCEPTION_ACCESS_VIOLATION (0xc0000005) occurred at PC=0x8077E76

After installing latest updates for Windows 7 64-bit recently, I've found out Oracle's loadjava is now giving me this error:



Unexpected Signal : EXCEPTION_ACCESS_VIOLATION (0xc0000005) occurred at PC=0x8077E76
Function=[Unknown.]
Library=C:\oracle\product\10.2.0\client_1\jdk\jre\bin\client\jvm.dll

...

Current Java thread:
at oracle.jdbc.driver.T2CConnection.t2cCreateState(Native Method)
at oracle.jdbc.driver.T2CConnection.logon(T2CConnection.java:346)
at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:413)
at oracle.jdbc.driver.T2CConnection.<init>(T2CConnection.java:131)
at oracle.jdbc.driver.T2CDriverExtension.getConnection(T2CDriverExtension.java:77)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:752)
at java.sql.DriverManager.getConnection(DriverManager.java:512)
- locked <0x141ae388> (a java.lang.Class)
at java.sql.DriverManager.getConnection(DriverManager.java:140)
- locked <0x141ae388> (a java.lang.Class)
at oracle.aurora.server.tools.loadjava.DatabaseOptions.getConnection(DatabaseOptions.java:180)
at oracle.aurora.server.tools.loadjava.DatabaseOptions.connect(DatabaseOptions.java:123)
at oracle.aurora.server.tools.loadjava.LoadJavaState.getConnection(LoadJavaState.java:409)
at oracle.aurora.server.tools.loadjava.LoadJavaState.getOldMD5(LoadJavaState.java:696)
at oracle.aurora.server.tools.loadjava.ClientSchemaObject.getOldMD5(ClientSchemaObject.java:52)
at oracle.aurora.server.tools.loadjava.SchemaObject.doCreate(SchemaObject.java:172)
at oracle.aurora.server.tools.loadjava.SchemaObject.process1(SchemaObject.java:215)
at oracle.aurora.server.tools.loadjava.LoadJava.add(LoadJava.java:532)
at oracle.aurora.server.tools.loadjava.LoadJava.addObject(LoadJava.java:513)
at oracle.aurora.server.tools.loadjava.LoadJava.add(LoadJava.java:451)
at oracle.aurora.server.tools.loadjava.LoadJava.add(LoadJava.java:380)
at oracle.aurora.server.tools.loadjava.LoadJava.addJar(LoadJava.java:727)
at oracle.aurora.server.tools.loadjava.LoadJava.add(LoadJava.java:432)
at oracle.aurora.server.tools.loadjava.LoadJava.add(LoadJava.java:380)
at oracle.aurora.server.tools.loadjava.LoadJava.add(LoadJava.java:349)
at oracle.aurora.server.tools.loadjava.LoadJava.add(LoadJava.java:652)
at oracle.aurora.server.tools.loadjava.LoadJava.processDeferredFiles(LoadJava.java:617)
at oracle.aurora.server.tools.loadjava.LoadJava.process(LoadJava.java:818)
at oracle.aurora.server.tools.loadjava.LoadJava.command(LoadJava.java:251)
at oracle.aurora.server.tools.loadjava.LoadJavaMain.run(LoadJavaMain.java:103)
at oracle.aurora.server.tools.loadjava.LoadJavaMain.main(LoadJavaMain.java:28)

Dynamic libraries:
0x00400000 - 0x0040B000 C:\oracle\product\10.2.0\client_1\jdk\jre\bin\java.exe
0x77C80000 - 0x77E00000 C:\Windows\SysWOW64\ntdll.dll
...

Heap at VM Abort:
Heap
 def new generation   total 576K, used 391K [0x10010000, 0x100b0000, 0x104f0000)
...

#
# HotSpot Virtual Machine Error : EXCEPTION_ACCESS_VIOLATION
# Error ID : 4F530E43505002EF
# Please report this error at
# http://java.sun.com/cgi-bin/bugreport.cgi
#
# Java VM: Java HotSpot(TM) Client VM (1.4.2_08-b03 interpreted mode)
#

As the solution to this problem I found to be installing Oracle 11g (v. 11.1.0) client instead of existing 10g (v. 10.2.0) one, specifically win32_11gR1_client. Funnily enough, I wasn't even able to quickly find the client distributive on Oracle website so here's the link for you: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win32soft-098987.html