Category Archives: Databases

ORACLE: How to escape special characters

We often need to escape special characters while retrieving the rows. To do that there are two ways;

  1. Use “SET ESCAPE ‘{ESC CHAR}’
  2. End the select statement with ‘ESCAPE ‘{ESC CHAR}’

For example;

SELECT SL.SYSTEM_CODE, SL.SYSTEM_DESCRIPTION, SL.SYSTEM_PARTICULARS FROM SYSTEMS_LIBRARY SL WHERE SL.SYSTEM_CODE LIKE 'UOTE\_%' ESCAPE '\'

the above statement returns all the systems with system code like ‘UOTE_’. But i cannot use the underscore directly in the quotes. Therefore I want to escape it with \ and tell Oracle to treat it as escape character by typing “ESCAPE ‘\'” at the end of the select statement.

Advertisements

ORACLE: View compiled with errors

Many times when ever we try to create the views under Oracle, often end up with messages like “View created with warnings” or ” View compiled with errors”. But SQL Developer does not reveal the error. In such cases to know the error actually generated fire the following query in SQL Developed or SQL Plus prompt;

SELECT TEXT FROM DBA_ERRORS WHERE NAME='VIEW_NAME_HERE'

If you have same view name in more than one schema then put that target schema name as

AND OWNER='SCHEMA_NAME'

This table always contains most recent error raised on that object.

Windchill: Database Configratation

In the event of disaster recovery, if the database instance name was changed then the Windchill configuration file must be updated with those changes. Following is the way we changed our Windchill configuration;

    1. Stop Windchill by issuing “Windchill stop” command at Windchill command prompt.
    2. Issue the following commands at windhill prompt
      1. Xconfmanager –s wt.pom.jdbc.host=<Host Name / IP Address> –t codebase\wt.properties -p
      2. Xconfmanager –s wt.pom.jdbc.port=<Oracle Listener Port> –t codebase\wt.properties -p
      3. Xconfmanager –s wt.pom.jdbc.host=<Oracle Service Name> –t codebase\wt.properties -p
    3. Now start windchill by issuing  “Windchill start” at Windchill command prompt

Oracle, Installation issues – Part 1 – Error in invoking target ‘install’ of makefile SomeName.mk

This is one of the common issues that I get during the installation of Oracle on Linux box. Though there are enough resources available to get through this issue, I am just putting them together. I normally get such issue on two files when they are getting processed.

  1. ins_emagent.mk
  2. ins_ctx.mk

ins_emagent.mk, To fix this issue, do as mentioned below;

  • Open the file in VI editor or any other editor located at, “$ORACLE_HOME/sysman/lib/ins_emagent.mk
  • Search for “$(MK_EMAGENT_NMECTL)
  • Replace it as “$(MK_EMAGENT_NMECTL) -innz11″
  • Save the file
  • Click on “Retry” on the pop-up from Oracle installation.

You will find many useful things in the forum thread located here

ins_ctx.mk, To fix this issue, follow the below lines;

  • Remove any file or folder under “$ORACLE_HOME/lib/stubs” by issuing the command “rm -rf $ORACLE_HOME/lib/stubs/*
  • Take a backup of existing ins_ctx.mk file by issuing the command “cp $ORACLE_HOME/ctx/lib/env_ctx.mk $ORACLE_HOME/ctx/lib/env_ctx.mk.orig
  • Open the ins_ctx.mk file in VI or any editor
  • Find “LINK=$(LDCCOM)” at around line no 154
  • Change it into “LINK=$(LDCCOM) –Wl,–no-as-needed
  • Save the file
  • Click on “Retry” on the pop-up from Oracle installation.

The source oracle thread for this post is located here