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

458726
You choose the desired location on the first time, and the next time the SQL Developer will suggest the location that you chose earlier.
chrisis
From the help file

To specify a nondefault SQLDEVELOPER_USER_DIR location, do either of the following:

Set the SQLDEVELOPER_USER_DIR environment variable to specify another directory path.

Edit the <sqldeveloper_install>\sqldeveloper\sqldeveloper\bin\sqldeveloper.conf file and substitute the desired directory path for SQLDEVELOPER_USER_DIR in the following line:
SetUserHomeVariable SQLDEVELOPER_USER_DIR


HTH

Chris
Stuart Fleming
A bit Late, but thank you!
DavidZ
Is there an equivalent option for SQL Developer 3.1? I would like to have both 3.1 and 3.0 installed on my machine, and run either. I share reports with others in the company, and those will need to stay at 3.0.

I tried including the following in sqldeveloper.conf
SetUserHomeVariable C:\Users\[myuserid]\AppData\Roaming\SQLDeveloper31
This resulted in a warning:
WARNING: Unknown directive: SetUserHomeVariable
(Replacing back slashes with forward slashes made no difference)

Windows 7
SQL Developer 3.1 with bundled JDK.
Gary Graham-Oracle
Hi David,

According to documentation, the IDE_USER_DIR is picked up automatically if set, so there should be no need for an additional conf file set statement in any recent SQL Developer release. There is another way, however:

A similar case occurs when one wishes to run SQL Developer from a flash-drive and also keep user-related information on that drive, e.g.,

1. Unzip SQL Developer into E:\sqldeveloper
2. Add the following line to one of your conf files: ide\bin\jdk.conf, ide\bin\ide.conf, or sqldeveloper\bin\sqldeveloper.conf
AddVMOption -Dide.user.dir=../../.sqldeveloper

The path given is relative to a starting directory of E:\sqldeveloper\sqldeveloper\bin, 
so the directory containing user-related data will be E:\sqldeveloper\.sqldeveloper 
3. Note that the "include" sequence for these conf files is jdk.conf -> ide.conf -> sqldeveloper.conf

If you look in Help|About|Properties, you can scroll to find the ide.user.dir property and other related properties. The Help also documents which files or types of files will be stored there. See SQL Developer Concepts and Usages|SQL Developer Preferences, then Location of User-Related Information.

Regards,
Gary
SQL Developer Team
1 - 5

Post Details

Added on Dec 7 2022
1 comment
224 views