Seeking Oracle out of the matrix

Oracle New Articles

Oracle Critical Patches

Oracle jDeveloper News

Oracle Blogs of interest

This blog records my personal experience.

Latest news from the front

Author's note : Thank you all for your supporting letters. You have been a motivation to this web spot's posting. I wish you all the very best for you and your families for the new year.
Interested to know about a next post? You can suscribe to my RSS feed
HOW TO:

DBMS_JOB Cheat sheet

Monday, October 10, 2011

I recently wanted to schedule jobs on my Oracle 9i (9.2.0.8) database so i refreshed my memory on how to do it based on the Oracle's job engine.

I will note down a quick reference post for you to check out:

View all running jobs:

select * from user_jobs

Submit a job (this example analyzes a table every 24 hrs:

VARIABLE jobno NUMBER
BEGIN
DBMS_JOB.SUBMIT(:jobno,
'DBMS_DDL.ANALYZE_OBJECT(''TABLE'',
''HR'', ''EMPLOYEES'',
''ESTIMATE'', NULL, 50);',
SYSDATE, 'SYSDATE + 1');
COMMIT;
END;
/

jobno is returned by the system.
Assuming 14144 for this example.

Remove a job from the job queue:

BEGIN
DBMS_JOB.REMOVE(14144);
END;
/

Change a job:

BEGIN
DBMS_JOB.CHANGE(14144, NULL, NULL, 'SYSDATE + 3');
END;
/

Alter the definition of a job:

BEGIN
DBMS_JOB.WHAT(14144,
'DBMS_DDL.ANALYZE_OBJECT(''TABLE'',
''HR'', ''DEPARTMENTS'',
''ESTIMATE'', NULL, 50);');
END;
/

Alter the next execution day of a job:

BEGIN
DBMS_JOB.NEXT_DATE(14144, SYSDATE + 4);
END;
/

Alter execution interval:

BEGIN
DBMS_JOB.INTERVAL(14144, 'NULL');
END;
/

Brake a scheduled job:

BEGIN
DBMS_JOB.BROKEN(14144, TRUE);
END;
/

Continue to run a previously broken job:

BEGIN
DBMS_JOB.BROKEN(14144, FALSE, NEXT_DAY(SYSDATE, 'MONDAY'));
END;
/

Forcing a job to execute:

BEGIN
DBMS_JOB.RUN(14144);
END;
/

Working with intervals:
'SYSDATE + 7' Exactly seven days from the last execution

'SYSDATE + 1/48' Every half hour

'NEXT_DAY(TRUNC(SYSDATE), ''MONDAY'') + 15/24' Every Monday at 3PM

'NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE, ''Q''), 3), ''THURSDAY'')' First Thursday of each quarter

Reference:
http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm

Labels: broken, DBMS_JOB, next_day, remove, submit, sysdate

posted by Admin @ 7:39 AM 11 comments

Java Server Faces - Export any IteratorBinding to a CSV format file

Sunday, January 06, 2008

Due to a large amount of requests regarding data extraction from JSF, i have compiled a small java class which you can embed in jDeveloper, that allows you to extract any IteratorBinding to a CSV format file.

The file is named according to the current date/time where the request was made.

This class can be used for the Greek character set as well.

Below follows the complete source code. Enjoy :)

import java.io.IOException;
import java.io.PrintWriter;

import java.text.SimpleDateFormat;

import java.util.Calendar;
import java.util.Date;
import java.util.Locale;

import javax.faces.context.ExternalContext;
import javax.faces.context.FacesContext;

import javax.servlet.http.HttpServletResponse;

import oracle.adf.model.binding.DCIteratorBinding;

import oracle.jbo.AttributeDef;
import oracle.jbo.AttributeHints;
import oracle.jbo.LocaleContext;
import oracle.jbo.Row;
import oracle.jbo.RowSetIterator;

import oracle.jbo.common.DefLocaleContext;

public class ExportDataClass {
public ExportDataClass() {
}

public void exportToCSV(DCIteratorBinding tableContent) throws IOException {

ExternalContext ectx = FacesContext.getCurrentInstance().getExternalContext();
HttpServletResponse response = (HttpServletResponse)ectx.getResponse();
Date now = Calendar.getInstance().getTime();
SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMdd-hhmmss");
String filename = formatter.format(now.getTime()) + "file.csv";
String label = "";
String strBuffer = "";

//define the encoding of the returned data. Critical for proper Greek Language exported data.
String contentType = "text/csv; charset=windows-1253";

try {
response.setContentType(contentType);
response.setHeader("Content-disposition", "attachment; filename=" + filename);

RowSetIterator rsi = tableContent.getRowSetIterator();
String[] attNames = rsi.getRowAtRangeIndex(0).getAttributeNames();
AttributeDef[] attr = tableContent.getAttributeDefs(attNames);
for(int i=0; i < attr.length; i++){
AttributeHints hints = attr[i].getUIHelper();
label = hints.getLabel(this.getLocaleContext());
strBuffer = strBuffer + label + ";";

}
strBuffer = strBuffer + "\n";

rsi.first();

for (int i = 0; i < rsi.getFetchedRowCount() ; i++)
{
Row currentRow = rsi.getRowAtRangeIndex(i);
Object[] attValues = currentRow.getAttributeValues();
for (int j = 0; j < attValues.length; j++)
{

strBuffer = strBuffer + attValues[j] + ";";
}
strBuffer = strBuffer + "\n";
}

Row[] ArrayRows = rsi.getNextRangeSet() ;

while (ArrayRows.length != 0){

for (int i = 0; i < ArrayRows.length; i++)
{
Row currentRow = ArrayRows[i];
Object[] attVals = currentRow.getAttributeValues( );
for (int j = 0; j < attVals.length; j++){
strBuffer = strBuffer + attVals[j] + ";";
}
strBuffer = strBuffer + "\n";
}

ArrayRows = rsi.getNextRangeSet() ;
}

PrintWriter out = response.getWriter();
response.setContentLength(strBuffer.length() + 1);
out.write(strBuffer);
out.flush();
out.close();
if (response.isCommitted() == false){
response.reset();
}
}
catch(IOException ex){
ex.printStackTrace();
throw ex;
}
finally {
FacesContext.getCurrentInstance().responseComplete();
}
}

private LocaleContext getLocaleContext(){
Locale locale = FacesContext.getCurrentInstance().getViewRoot().getLocale();
LocaleContext myLocale = new DefLocaleContext(locale);
return myLocale;
}

}

Labels: character set, CSV, Export, Export to CSV, Greek encoding, Java Server Faces, jDeveloper, jsf

posted by Admin @ 12:45 PM 1 comments

A typo can knock you out

Tuesday, December 11, 2007

While trying to implement Note:270160.1 Single Sign-On Accessibility Through a Firewall
Oracle Application Server 10g (9.0.4), i realized that a simple typo can make your mood for the day.

To be more specific, in the part where the author of the Note writes :

Listen 8001
NameVirtualHost *:8001 # You may prefer IP Address instead of *
<VirtualHost *:8001> # You may prefer IP Address instead of *
ServerName portal.<yourname>.com
# Network entry-point/webcache:
Port 80
# IMPORTANT - Must inherit any Portal rewrites
RewriteEngine on
RewriteOptions inherit
# Other rewrites optional, but may improve functionality
# To send direct requests on this virtual host to Portal
RewriteCond %{HTTP_HOST} !^portal\.<yourname>\.com [NC]
RewriteRule ^/$ http://portal.<yourname>/pls/portal/$1 [L,R]
LogLevel error
ErrorLog "|/path/to/oracle/904mid/Apache/Apache/bin/rotatelogs /path/to/oracle/904mid/Apache/Apache/logs/portal_error_log 43200"
</VirtualHost>

Did you see it? Me either

RewriteRule ^/$ http://portal.<yourname>/pls/portal/$1 [L,R]

The [R] is after the [L] meaning that the redirection will not occur!

The line should be

RewriteRule ^/$ http://portal.<yourname>/pls/portal/$1 [R,L]

I simply lost a server with a k.o.

Labels: firewall, host, Single Sign-On, SSO, virtual

posted by Admin @ 10:03 AM 1 comments

Resolution to the ORA-02262 error

Thursday, May 10, 2007

If you have ever faced this error message

ORA-02262: ORA-%05d occurs while type-checking column default value expression

while trying to alter the column definition of a table to NVARCHAR2 with Default Value just remember :

A 'string' literal is of type VARCHAR2
A N'string' literal is of type NVARCHAR2 !!

So now, you can correct your syntax and alter your table properly like this :

ALTER TABLE mytbl MODIFY mycolumn NVARCHAR2(3) DEFAULT N'A';

Labels: ORA-02262

posted by Admin @ 11:39 PM 2 comments

| Next post »

About Me

Name: John Galanopoulos
Home: The NeverLands
About Me: A source code wonderer since the early 80s with my first ZX81 by Sinclair, home computer.
See my complete profile