Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Dynamic WHERE clause

Spike HouseDec 7 2022 — edited Dec 7 2022

Hi All.
Oracle Version: 19.15
All DDL/DML below.

Along with EMP table, consider we have an additional application table called user_filt_details, which stores report filters created by application users. For simplicity, assume all filter conditions retrieve only the ename, I need to generate an exploded report with the filter along with the enames retrieved by it .

_user_filt_details l_ooks like below:
image.png
Final Expected output is as below:
image.png
The below solution I thought of requires creating a table and populating it through a batch job every day, which would not capture any new filters added by users post batch run. I would appreciate if someone could advise a more Realtime/dynamic way of generating this output.

SET SERVEROUTPUT ON;
DECLARE
  sql_stmt CLOB := NULL;
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE filt_exploded';
FOR cur IN ( SELECT filt_name, filt_cond from user_filt_details)
LOOP
  sql_stmt := q'{ INSERT INTO filt_exploded
            SELECT vh.filt_name, vh.filt_cond, lat.ename
            FROM user_filt_details vh, LATERAL( SELECT l.ename
                               FROM emp l
                            WHERE }' || cur.filt_cond
                         || q'{ ) lat
            WHERE vh.filt_name = '}' || cur.filt_name ||q'{'}'
        ;
  DBMS_OUTPUT.PUT_LINE(sql_stmt); 
  EXECUTE IMMEDIATE sql_stmt;
  COMMIT;
END LOOP;
END;
/

DDL/DMLs

CREATE TABLE user_filt_details (filt_name VARCHAR2(200), filt_cond CLOB);
INSERT INTO user_filt_details VALUES('my_jobs' , q'{JOB IN ('PRESIDENT', 'ANALYST')}');
INSERT INTO user_filt_details VALUES('sal_review' , q'{SAL <= 1000}');
INSERT INTO user_filt_details VALUES('dept_30_emps' , q'{DEPTNO = 30}');
COMMIT;

CREATE TABLE filt_exploded(filt_name VARCHAR2(200) , filt_cond CLOB, ename  VARCHAR2(10) );

Please let me know if additional information is required.
Thank You!

Comments

800346
Try using a PrintRequestAttributeSet. Example:
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.print.*;
import java.text.MessageFormat;
import javax.print.attribute.*;
import javax.print.attribute.standard.*;
import javax.swing.*;

public class PrintDemo extends JFrame {

    private JButton btPrint;
    private JCheckBox cbPortrait;
    private JToolBar toolbar;
    private JTextArea textarea;
    static private PrintRequestAttributeSet attr;

    public PrintDemo() {
        super("PrintDemo");
        setDefaultCloseOperation(WindowConstants.EXIT_ON_CLOSE);
        setSize(600, 300);
        setLocationRelativeTo(null);
        textarea = new JTextArea();
        toolbar = new JToolBar();
        btPrint = new JButton("Print");
        cbPortrait = new JCheckBox("Portrait");
        textarea.setText("print(textarea.getPrintable(new MessageFormat(" +
                "\"PrintDemo\"), null), cbPortrait.isSelected());");
        getContentPane().add(new JScrollPane(textarea), BorderLayout.CENTER);
        toolbar.add(btPrint);
        cbPortrait.setSelected(true);
        toolbar.add(cbPortrait);
        getContentPane().add(toolbar, BorderLayout.PAGE_START);
        btPrint.addActionListener(new ActionListener() {

            public void actionPerformed(final ActionEvent evt) {
                print(textarea.getPrintable(new MessageFormat("PrintDemo"), null), cbPortrait.isSelected());
            }
        });
        cbPortrait.addActionListener(new ActionListener() {

            public void actionPerformed(final ActionEvent evt) {
                attr = null;
            }
        });
    }

    static public void print(final Printable printable) {
        print(printable, true);
    }

    static public void print(final Printable printable, final boolean portrait) {
        print(printable, portrait, new Insets(10, 10, 10, 10));
    }

    static public void print(final Printable printable, final boolean portrait, final Insets insets) {
        PrinterJob pjob = PrinterJob.getPrinterJob();
        pjob.setPrintable(printable);
        // create an attribute set to store attributes from the print dialog
        if (attr == null) {
            attr = new HashPrintRequestAttributeSet();
            float leftMargin = insets.left;
            float rightMargin = insets.right;
            float topMargin = insets.top;
            float bottomMargin = insets.bottom;
            if (portrait) {
                attr.add(OrientationRequested.PORTRAIT);
            } else {
                attr.add(OrientationRequested.LANDSCAPE);
                leftMargin = insets.top;
                rightMargin = insets.bottom;
                topMargin = insets.right;
                bottomMargin = insets.left;
            }
            attr.add(MediaSizeName.ISO_A4);
            MediaSize mediaSize = MediaSize.ISO.A4;
            float mediaWidth = mediaSize.getX(Size2DSyntax.MM);
            float mediaHeight = mediaSize.getY(Size2DSyntax.MM);
            attr.add(new MediaPrintableArea(
                    leftMargin, topMargin,
                    (mediaWidth - leftMargin - rightMargin),
                    (mediaHeight - topMargin - bottomMargin), Size2DSyntax.MM));
        }
//        if (pjob.printDialog(attr)) {
            try {
                pjob.print(attr);
            } catch (PrinterException ex) {
                ex.printStackTrace();
            }
//        }
    }

    public static void main(final String args[]) {
        EventQueue.invokeLater(new Runnable() {

            public void run() {
                new PrintDemo().setVisible(true);
            }
        });
    }
}
darrylburke
Note: This thread was originally posted in the [New To Java|http://forums.sun.com/forum.jspa?forumID=54] forum, but moved to this forum for closer topic alignment.
843807
Dear Mr. Andre Uhres,

Thanks for your response.

Though I was expecting a much shorter solution, I will check based on your inputs and keep you all posted of the outcome.

Regards,
Sudheendran T L
843807
Dear Andre,
Dear All,

I am trying to use the print method of the JTextComponent class. (Ref [http://java.sun.com/docs/books/tutorial/uiswing/misc/printtext.html|http://java.sun.com/docs/books/tutorial/uiswing/misc/printtext.html]
).

I want to use the following method especially:
boolean complete = textComponent.print(MessageFormat headerFormat,
          		               MessageFormat footerFormat, 
                                               boolean showPrintDialog,
                                               PrintService service
                                               PrintRequestAttributeSet attributes,
                                               boolean interactive);
I have already saved the pageFormat using the pageDialog method of PrinterJob. My question is how do I translate ALL the content of the pageFormat into the PrintRequestAttributeSet? For example, I have used the orientation from the PageFormat saved to set the apporpriate attribute. See below code. How do I set other attributes like the Paper size and margins?

PrintRequestAttributeSet pras = new HashPrintRequestAttributeSet();
if ( pageFormat.getOrientation() == PageFormat.LANDSCAPE ){
    pras.add(OrientationRequested.LANDSCAPE);
} else {
    pras.add(OrientationRequested.PORTRAIT);
}
Will appreciate any help.

Regards,
Sudheendran T L
800346
The sample code that I posted above looks a bit complicated only because the page setup is done programmatically. If you want to display the standard page dialog (which might however look complicated to the user), you simply pass the "PrintRequestAttributeSet" that you also use for printing:
pjob.pageDialog(attr);
textComponent.print(null, null, true, pjob.getPrintService(), attr, true);
1 - 5

Post Details

Added on Dec 7 2022
1 comment
206 views