Execute Immediate in Oracle Reports


Execute Immediate cannot use in Oracle Reports, if you tried to use, i.e. EXECUTE IMMEDIATE ‘CREATE TABLE TEST AS SELECT ‘A’ A FROM DUAL’,

this error message will occured,  “this feature is not supported in client-side programs”. EXECUTE IMMEDIATE is only supported on the server side.

Some substitution could be use, such as create a function on the database and make a simple call.  The other way is use DBMS_SQL.

Here the codes,

DECLARE

l_cursor INTEGER DEFAULT 0;
rc INTEGER DEFAULT 0;

COMPILATION_ERROR EXCEPTION;
PRAGMA EXCEPTION_INIT(COMPILATION_ERROR,-24344);

VWHERE  VARCHAR2(100);

BEGIN

VWHERE := ‘CREATE TABLE TEST AS SELECT ‘A’ A FROM DUAL’ ;
l_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(l_cursor, VWHERE, 1);
/*DBMS_SQL.NATIVE cannot be use, replace it by the numeric literal 1*/
rc := DBMS_SQL.EXECUTE(l_cursor);
DBMS_SQL.CLOSE_CURSOR(l_cursor);

EXCEPTION
WHEN COMPILATION_ERROR THEN
DBMS_SQL.CLOSE_CURSOR(l_cursor);
WHEN OTHERS THEN
BEGIN
DBMS_SQL.CLOSE_CURSOR(l_cursor);
raise_application_error(-20101,sqlerrm || ‘ when executing ”’ || VWHERE || ”’ ‘);
END;
END;

Advertisements

2 Responses

  1. Hi,
    I want to generate report using struts2.0,jsp
    it is generating but when i want to generate report in client side the preview is not generated in client side its generating in my system(i.e, Server)
    how to generate report in client side…
    and my code for generating report is……
    ClassicEngineBoot.getInstance().start();
    ResourceManager manager = new ResourceManager();
    manager.registerDefaults();
    URLConnection uc;
    URL url = null;
    String s = request.getContextPath();
    final String path = System.getProperty(“catalina.home”) + “/webapps” + s + “/reports/”+”job_card.prpt” ;
    url = new URL(“http://localhost:8080/”+s+”/reports/job_card.prpt”);
    // url = new URL(pathimage);
    try {
    uc = url.openConnection();
    } catch (IOException e) {
    e.printStackTrace();
    }
    org.pentaho.reporting.libraries.resourceloader.Resource res = manager.createDirectly(url, MasterReport.class);

    System.out.println(“before pdf report creation”);
    try {
    HtmlReportUtil.createDirectoryHTML((MasterReport) ((org.pentaho.reporting.libraries.resourceloader.Resource) res).getResource(), “http://localhost:8080/LvhtGre1/reports/reportgre.pdf”);
    } catch (ReportProcessingException e) {
    log.setError(e, “Print Preview in GRE”);
    e.printStackTrace();
    } catch (IOException e) {
    log.setError(e, “Print Preview in GRE”);
    e.printStackTrace();
    }
    MasterReport report = (MasterReport) ((org.pentaho.reporting.libraries.resourceloader.Resource) res).getResource();
    ReportParameterValues paramValues = report.getParameterValues();

    int code = Integer.parseInt(goodsReceivingEntry.getCode().toString());
    paramValues.put(“jobcode”, code);
    paramValues.put(“plant_id”, plant_id);
    // Set Dimension
    Toolkit tk = Toolkit.getDefaultToolkit();
    Dimension d = tk.getScreenSize();
    int width = d.width – 400;
    int height = d.height – 100;
    int X = (d.width / 2) – (width / 2); // Center horizontally.
    int Y = (d.height / 2) – (height / 2); // Center vertically.

    try {

    HtmlReportUtil.createStreamHTML(report, “http://localhost:8080/LvhtGre1/reports/reportgre.html”);

    } catch (ReportProcessingException e) {
    log.setError(e, “Print Preview in GRE”);
    e.printStackTrace();
    } catch (IOException e) {
    log.setError(e, “Print Preview in GRE”);
    e.printStackTrace();
    }

    final PreviewDialog preview = new PreviewDialog(report);
    preview.setBounds(X, Y, width, height);
    preview.setPreferredSize(new Dimension(width, height));
    preview.setMaximumSize(new Dimension(width, height));
    preview.setMinimumSize(new Dimension(width, height));
    // preview.setVisible(false);
    preview.pack();
    preview.setVisible(true);

  2. This article will help the internet viewers for building up new blog
    or even a weblog from start to end.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: