Archive for August, 2012

How to call stored procedure from solr


Problem: If you want to take advantage of solr data import handler to index the data, but unable to find a way to call oracle stored procedure or a function, here you go

Solution:

1. First thing to understand is to fill the index of solr, I use the dataimporthandler of solr

2. DataImportHandler does not directly support calling regular stored procedures

3. Instead, create oracle pipelined functions as shown in my earlier post at

https://javatoj2ee.wordpress.com/2012/08/30/oracle-pipelined-table-functions/

4. Now prepare the data-config.xml file as below

Configuring DataSources

Add the tag ‘dataSource’ directly under the ‘dataConfig’ tag, for example.

<dataSource name="jdbcds" type="JdbcDataSource" driver="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:port/dbname" user="db_username" password="db_password" responseBuffering="adaptive" batchSize="0" autoCommit="true"/>
  • The datasource configuration can also be done in solr config xml 
  • The attribute ‘type’ specifies the implementation class. It is optional. The default value is 'JdbcDataSource'
  • The attribute ‘name’ can be used if there are multiple datasources used by multiple entities
  • All other attributes in the <dataSource> tag are specific to the particular dataSource implementation being configured.

    You might need to download and install the Oracle JDBC Driver in the /lib directory of your Solr installation.

    • The data-config.xml used for this example is:
    <dataConfig>
    <dataSource name="jdbcds" driver="org.jdbc.driver.OracleDriver" url="jdbc:oracle:thin:@hostname:port/dbname" user="sa" password="" responseBuffering="adaptive" batchSize="0" autoCommit="true"/>
    
    <document name="products">
     <entity name="item" datasource="jdbcds" pk="id"  query="SELECT * FROM TABLE(PACKAGE_NAME.PIPELINED_FUNC_NAME)" transformer="RegexTransformer,DateFomatTransformer,TemplateTransformer" 
    </entity>
    </document> 
    </dataConfig>

,

Leave a comment

oracle pipelined table functions


Problem: How to create pipelined table functions in oracle

Solution: 

Table functions are used to return PL/SQL collections that mimic tables. They can be queried like a regular table by using the TABLE function in the FROM clause. Regular table functions require collections to be fully populated before they are returned. Since collections are held in memory, this can be a problem as large collections can waste a lot of memory and take a long time to return the first row. These potential bottlenecks make regular table functions unsuitable for large Extraction Transformation Load (ETL) operations. Regular table functions require named row and table types to be created as database objects.

Typically pipelined functions are useful if there is a need for a data source other than a table in a select statement as shown below

SELECT * FROM TABLE(PACKAGE_NAME.PIPELINED_FUNC_NAME)

Lets see how to create a pipelined function 

1. If you are defining these in a package, below definitions goes into the spec of the package, first thing is to specify the columns that are returned along with the type of the columns

create or replace PACKAGE package_name 

AS 

TYPE pipelinedDataRecord IS RECORD (id varchar2(100),firstname CUSTOMER.FIRST_NAME%type);

END package_name

The above definition declares the format of the rows that are returned

2. Add the below collection type for the above defined type

TYPE pipelinedDataRecordCol IS TABLE OF pipelinedDataRecord;

So the entire package declaration looks like 

 

create or replace PACKAGE package_name 

AS 

TYPE pipelinedDataRecord IS RECORD (id varchar2(100),firstname CUSTOMER.FIRST_NAME%type);

TYPE pipelinedDataRecordCol IS TABLE OF pipelinedDataRecord;

END package_name

 

3. Now define the body of the package

 

create or replace PACKAGE BODY package_name

AS

FUNCTION func_name return pipelinedDataRecordCol 

PIPELINED IS

out_record pipelinedDataRecord ;

BEGIN

FOR in_record IN(

********select statements goes here********

) LOOP

out_record := in_record;

PIPE_ROW (out_record);

END LOOP;

END func_name;

 

END package_name;

4. How to call the above pipelined function? 

SELECT * FROM TABLE(PACKAGE_NAME.FUNC_NAME);

2 Comments

ORA-00942: table or view does not exist


Problem: You tried to execute an SQL statement that references a table or view that either does not exist, that you do not have access to, or that belongs to another schema and you didn’t reference the table by the schema name.

Solution:

Lets suppose there are two schemas, one for all the tables and one for all the procedures and functions.

1. In order to make it clear, lets name them to be TABLE_SCHEMA and PROC_SCHEMA.

2. TABLE_SCHEMA has your business domain tables, say customer table, address table etc

3. PROC_SCHEMA has the procedures PROC_NAME that access these tables

 

If you try to connect to proc_schema to execute the procedures, you will get a message ORA-00942: table or view does not exist, even though the tables exist. In this case, proper grant permissions are not provided and synonyms are not created. Follow the below indicative steps to potentially resolve this issue

1. If you are using SQLDeveloper, connect to the proper schema to grant access as described below.

2. First connect to the table_schema where all the tables exists and run the below command

3. grant select,insert,update,delete, references on CUSTOMER to PROC_SCHEMA

4. Now DISCONNECT from TABLE_SCHEMA and connect to PROC_SCHEMA and run the below command to create synonym for the table in PROC_SCHEMA

5. create or replace SYNONYM PROC_SCHEMA.CUSTOMER FOR TABLE_SCHEMA.CUSTOMER

 

Lets extend the above concept and see what steps to take if we decide to execute the stored procedure PROC_NAME available in PROC_SCHEMA from another schema, say ANOTHER_SCHEMA

1. Connect to PROC_SCHEMA and run the below command

2. grant execute on PROC_SCHEMA.PROC_NAME to ANOTHER_SCHEMA

3. Disconnect from PROC_SCHEMA and connect to ANOTHER_SCHEMA and run the below command

4. create or replace SYNONYM ANOTHER_SCHEMA.PROC_NAME FOR PROC_SCHEMA.PROC_NAME

 

Also, take a look at the below tutorial, for further explanation

http://www.adp-gmbh.ch/ora/err/ora_00942.html

,

Leave a comment

Tools


Blogging here my favorite list of tools, please add if you know more and are useful  on regular basis

1. JEdit – jEdit is a mature programmer’s text editor http://www.jedit.org/

2. Beyond compare -Beyond Compare 3 is the ideal tool for comparing files and folders on your Windows or Linux system. Visualize changes in your code and carefully reconcile them.  http://www.scootersoftware.com/

3. WinSCP – Free SFTP,SCP and FTP Client for windows http://winscp.net/eng/download.php

4. Notepad++ Notepad++ is a free (as in “free speech” and also as in “free beer”) source code editor and Notepad replacement that supports several languages.  http://notepad-plus-plus.org/

5. cygwin terminal – a collection of tools which provide a Linux look and feel environment for Windows. http://www.cygwin.com/ 6. Stylus studio http://www.stylusstudio.com/

7. SOAPUI soapUI is a free and open source cross-platform Functional Testing solution. With an easy-to-use graphical interface, and enterprise-class features, soapUI allows you to easily and rapidly create and execute automated functional, regression, compliance, and load tests. http://www.soapui.org/

8. Nitroshare – A network file-sharing application that makes sending a file to another machine on the local network as easy as dragging-and-dropping. https://launchpad.net/nitroshare/

9. Virtual CloneDrive – Virtual CloneDrive works and behaves just like a physical CD/DVD drive, however it exists only virtually. Image files generated with CloneDVD or CloneCD can be mounted onto a virtual drive from your hard-disk or from a network drive and used in the same manner as inserting them into a normal CD/DVD drive. http://www.slysoft.com/en/virtual-clonedrive.html

10. Azhagi+ : Native language typing software – http://www.azhagi.com/downloads.html

11. CutePDF: This enables virtually any Windows applications (must be able to print) to convert to professional quality PDF documents – with just a push of a button!  http://www.cutepdf.com/

12. Irfanview IrfanView is a very fast, small, compact and innovative FREEWARE (for non-commercial use) graphic viewer for Windows http://www.irfanview.com/

13. WinMerge

Leave a comment

How to install jmeter plugins


Problem: How to Install additional plug-ins for JMeter, for example if you want to view Response Times vs Threads or Transaction throughputs vs Threads

Solution:

If you do not have Jmeter installed, download Jmeter at http://jmeter.apache.org/download_jmeter.cgi

Download latest JMeter plugins at http://code.google.com/p/jmeter-plugins/

Installation instructions:

To install the JMeter Plugins, you simply have to copy the JMeterPlugins.jar file from JMeterPlugins-VERSION.zip inside JMETER_INSTALL_DIR/lib/ext, for example to c:\jmeter\lib\ext in case of windows installation. Now you can restart your JMeter and see jp@gc-prefixed plugins in menus.

If you plan to use the Servers Monitoring Listener, you must also install the server agent on all servers as described here.

Installation instructions can also be found at http://code.google.com/p/jmeter-plugins/wiki/PluginInstall

1 Comment