SQL & PL/SQL :: Dynamic Where Clauses And Bind Variables?
Jun 6, 2012
I have a requirement where I have to return data as per filter clauses provided on the front end, which may or may not be filled as per the users' choice.
To create a test case, I have created a query joining the emp and dept tables and I add the where clauses dynamically depending on whether the filter has been provided or not.
CREATE OR REPLACE TYPE emp_ot AS OBJECT (
empno NUMBER(4),
ename VARCHAR2(10),
[Code]....
It works very well, the 'literals' are being converted into bind variables (due to CURSOR_SHARING being set to SIMILAR) and the optimizer is able to figure out the correct execution path every time, although it is true that potentially 5 versions of this query will be sitting in the shared pool, but the DBAs are happy to live with that.
Going forward in version 12, CURSOR_SHARING=SIMILAR will be deprecated and the DBAs are not happy for us to write this sort of code anymore.
Is there a suitable way to achieve what this piece of code does?
I have tried and tested this method:
SELECT emp.empno, emp.ename, emp.job, dept.deptno, dept.dname
FROM emp, dept
WHERE emp.deptno = dept.deptno
AND emp.empno = NVL(p_empno, emp.empno)
AND emp.ename LIKE NVL(p_ename, emp.ename)||'%'
...
but the query takes far longer to execute in this manner (that is using my real tables).
Which of the below is considered a bind variable. In example one proc. Test the parameter p1 is directly used in the query, so this can be considered as a bind variable.
Is that true about the second proc. where p1 is assigned to a local variable v1 , or this needs hard parsing because v1 is not a bind variable ?
Create or replace procedure test(p1 IN VARCHAR2,p_refcursor OUT SYS_REFCURSOR) IS BEGIN OPEN p_refcursor FOR select * from Test_tab WHERE item=p1; END; ------------ Create or replace procedure test1(p1 IN VARCHAR2,p_refcursor OUT SYS_REFCURSOR) IS v1 varchar2(100):=p1; BEGIN OPEN p_refcursor FOR select * from Test_tab WHERE item=v1; END;
I have written a small code while going through the PL/SQL guide but I got a message for the BIND VARIABLE. I don't think I have used any bind variable in this code.
<<outer>> declare v_sal1 number(7,2) := 60000; v_comm number(7,2) : v_sal1 * 0.20; v_message varchar2(2000) := 'eligible for commission'; begin [code]........
Create and invoke the GET_JOB function to return a job title. a.Create and compile a function called GET_JOB to return a job title. b.Create a VARCHAR2 host variable called b_title, allowing a length of 35 characters. Invoke the function with job ID SA_REP to return the value in the host variable, and then print the host variable to view the result.
This is my FUNCTION: CREATE OR REPLACE FUNCTION GET_JOB( p_jobid IN jobs.job_id%TYPE) RETURN VARCHAR2
[code]...
This is how I invoked the FUNCTION but WHILE DECLARING THE BIND VARIABLE IT IS GIVING ME AN ERROR!!!!!
VARIABLE b_title VARCHAR2(35) set serveroutput on DECLARE P_JOBID VARCHAR2(10); v_jobtitle VARCHAR2(200);
CODE Select Nvl(Sum(DbAmt),0), Nvl(Sum(CrAmt),0) From FnTrantt A Where A.GrpCode=:1 And A.CmpCode=:2 And'; A.DiviCode=:3 And'; A.SubDiVCd=:4 And'; A.FinYear>=''0001'' And'; A.VchDate Between :5 And :6' And A.GlCode In :7; [code]....
In The Above mentioned Code, am using bind variables, In variable no. 7 in passing character string ('05124','05125')I am not able to fetch the value,
i have been looking at a query that uses a wrong plan. db=11.2.0.3 with user bind peeking enabled. this is a simplified version demonstrating the problem:
select egp.bsn,egp.klantnummer as "Persoonsnummer", egp.samengesteldenaam as "Samengesteldenaam", egp.geboortedatum as "Geboortedatum"from pr_eigenschappen2 egpwhere(egp.bsn = :b1 or :b2 is null)and rownum < 51 egp.bsn is varchar2(10) and has high selectivity (almost unique), and is btree-indexed. table and index have adequate statistics. when run with b1:=928818 and b2:=928818 (both bound as varchar2(10)) a full table scan+filter is used on pr_eigenschappen2. if the query is changed to select egp.bsn,egp.klantnummer as "Persoons nummer", egp.samengesteldenaam as "Samengesteldenaam", egp.geboortedatum as "Geboortedatum"from pr_eigenschappen2 egpwhere(egp.bsn = :b1 or 928818 is null)
and rownum < 51the index on bsn is used, and the query is not taking 3.9 seconds but 1 millisecond.if i would have a choice, the query would be different. i don't want to talk about the raison d'etre of the query, i would like to know why the optimizer is not using the index in the first case.
create or replace procedure my_proc(p_user in varchar2) is l_cursor sys_refcursor; l_query constant varchar2(1000) := 'select a' || 'from ' || p_user || '.user_table' || 'where param0 = :x' || 'and param1 = :x' || 'and param2 = :x'
[Code]...
Suppose I execute my_proc many times and for multiple values of p_user. For performance reasons, will l_query be stored in the cache as I am using bind variables or it will not since I have the concatenation with p_user value ?
I am trying to create table using bind variable in EXECUTE IMMEDIATE.. I want to know whether oracle allows to create table using bind variable in EXECUTE IMMEDIATE..
Following is the example :
Declare test_tab varchar2(10) := 'tab_test'; sql_stm varchar2(100); Begin sql_stm := 'create table ' || :a || ' (col1 NUMBER)'; dbms_output.put_line(sql_stm); EXECUTE IMMEDIATE sql_stm using test_tab; Exception WHEN OTHERS THEN dbms_output.put_line(sqlerrm || ' ' || sqlcode); End;
After running above block it is giving error : ORA-01008: not all variables bound.
We have recently upgraded application (from Oracle Applications 11.5.9 to 12.1.3) and database (from 9.2.0.5.0 to 11.2.0.3.0).Since we are confronting to performances issues, i try to analyse some queries which Explains plans seems strange (in my opinion).Studying one of them i discover the next case (which according to my logic, i can't explain): --
Just bind variable --select *from MTL_MATERIAL_TRANSACTIONS mmtwhere 1 = 1and mmt.INVENTORY_ITEM_ID = :p1and mmt.ORGANIZATION_ID = :p2and mmt.TRANSACTION_REFERENCE = :p3--and mmt.SUBINVENTORY_CODE = :p4 PlanSELECT STATEMENT ALL_ROWS Cost: 5 Bytes: 361 Cardinality: 1 2 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_MATERIAL_TRANSACTIONS Cost: 5 Bytes: 361 Cardinality: 1 1 INDEX RANGE SCAN INDEX XXSPE.XXSPE_MTL_MATERIAL_TRANSAC_N99 Cost: 3 Cardinality: 2-- Nvl on bind variable --select *from MTL_MATERIAL_TRANSACTIONS mmtwhere 1 = 1and mmt.INVENTORY_ITEM_ID = :p1and
I am trying to create a collection using bind variables in APEX 4.1. I have the following procedure but all I get is an error in the debug page DOH ORA-20104:
create_collection_from_query_b Error:ORA-01006: bind variable does not exist
Am using Oracle 9i and developing stored procedures in SQL Developer. Have a situation where one stored procedure A is calling another B. Stored proc B builds the SQL based on parameters passed in. For one of the parameters i would like to use a bind variable in the SQL that proc B builds. The bind var is passed back to proc A as a part of the SQL string. In proc A, i then try to bind that variable to a parameter(value), however, the bind does not seeem to work.
The SQL string contained in v_SQLStatement defined as VARCHAR(4000) that is passed back to proc A looks like:
SELECT em.event_title AS event_name, e.start_date AS start_date, e.end_date AS end_date FROM d_event_ml em inner join d_event e ON em.event_id = e.event_id WHERE em.language_id = 46 AND e.end_date >= SYSDATE AND e.stream_id IN ( :v_x1 ) AND e.event_id IN (SELECT event_id FROM d_events_seas [code]....
and o_EventList is defined as REF CURSOR. i'm experiencing is that :v_x1 stays as :v_x1 and does not change.This is my first attempt at using bind vars. URL....
I have to use bind variable for dynamic sql in a procedure. Is there a way to have control on these values. Say for example:
Procedur MyProc ( In_EmpID Number default null, In_EmpName Varchar2 default null, in_JoinDate Date default null [code]....
I have more than 5 In parameters, all 5 is not compulsory by default they are null and sql formation is also dynamic with in the procedure.I need to map bind variable to a proper one.. Is there a way to handle bind variable.
I have been asked for a query to write by using all sql clauses like group by, where, having, order by in a single query. I just wrote the below query but I did not get out put. select deptno, max(sal) from emp group by deptno having deptno > ( select distinct deptno from emp where deptno >= 20)
I have a table name as angdata77 having attributes like asigno..i want to retrieve data from angdata77 by using both group by & order by clauses.. for total count..am using the query as
select asigno,count(*) from angdata77 group by asigno order by asigno;
Is there any other query for retrieving the data from angdata77
I'm using dynamic sql (DBMS_SQL) to define columns of ref cursor. It works Ok but the problem is when i'm using PL/SQL CURSOR in the REF CURSOR. Then,I'm getting :
Error at line 3 ORA-00932: inconsistent datatypes: expected NUMBER got CURSER ORA-06512: at "SYS.DBMS_SQL", line 1830 ORA-06512: at "TW.PRINT_REF_CURSOR", line 28 ORA-06512: at line 9
Here is my code: set serveroutput on exec DBMS_OUTPUT.ENABLE(1000000); declare l_cursor sys_refcursor; begin [code]....
I have the below cursor 1 which is working already.For my requirement i want to use bind variable like second cursor.But its telling Bind Variable "p_col_list" is NOT DECLARED.
How to use bind variable Here.
Cursor1: DECLARE emp_cv sys_refcursor; iid NUMBER := 1; i_sql varchar2(100); p_col_list varchar2(2000) := 'aaa,bbb,ccc,ddd'; BEGIN i_sql := 'select '''||REPLACE(p_col_list, ',', ''',''')||''' from dual '||CHR(10) ; dbms_output.put_line(i_sql); OPEN emp_cv FOR i_sql ; END;
Cursor2: DECLARE emp_cv sys_refcursor; iid NUMBER := 1; i_sql varchar2(100); p_col_list varchar2(2000) := 'aaa,bbb,ccc,ddd'; BEGIN i_sql := 'select '''||REPLACE(:p_col_list, ',', ''',''')||''' from dual '||CHR(10) ; dbms_output.put_line(i_sql); OPEN emp_cv FOR i_sql using p_col_list; END;
Whenever I try to create this trigger, it asks for values for :OLD and :NEW for some reason, and then when I attempt to put those in, it just says
Unable to bind ":OLD" Unable to bind ":NEW"
CREATE OR REPLACE TRIGGER audit_grade_change BEFORE UPDATE OF final_letter_grade ON enrollments FOR EACH ROW BEGIN UPDATE grade_change_history SET old_final_grade=:old.final_letter_grade, new_final_grade=:new.final_letter_grade; end;
I am facing the same problem: SP2-0552: Bind variable "OLD" not declared. When my script create_trigger.sql is executed,there is no error but when i execute it inside a pl/sql block it get above error...In the trigger we are using if conditions
if(:new.sumthing=1)and (:old.sumthing=0)the do this..
Identical statements from this link : Parsing in Oracle — DatabaseJournal.com d. The bind variable types of the new statement should be of same type as the identified matching statement. i am getting confuse here .. when parsing occurs some links saying about bind variable.but official document never said about bind variables.
PLS-00049 BAD BIND VAIRABLE 'OLD.REMARKS' When i create or replace the following trigger
CREATE OR REPLACE TRIGGER T_TASKHISTORY AFTER UPDATE ON S_TASK FOR EACH ROW DECLARE BEGIN INSERT INTO S_TASKHIS (HIS_DATE,SUBJECT,DESP, SCHEDULED_DATE, SCHE_TIME ,USER_MOB_NO
- ProdPL/SQL Release 10.2.0.5.0 - ProductionCORE 10.2.0.5.0 ProductionTNS for Linux: Version 10.2.0.5.0 - ProductionNLSRTL Version 10.2.0.5.0 - Production.
I have a problem when creating a dynamic statement. The problem is in the bind variable ':OLD.CUST_NAME' ,..my question is is there an escape character to treat the bind variable as a literal string?
{code}v_str2 := '''CUST_NAMES='''||'||'|| ':OLD.CUST_NAME' ; EXECUTE IMMEDIATE 'create or replace trigger trg_' || SUBSTR (rec_cur.table_name, 1, 26) || ' before insert or update or delete on ' || rec_cur.owner || '.' || rec_cur.table_name || ' declare begin if UPDATING then FIFAPPS.ibug.log_errors('|| v_str2 ||' ); end if; end;';
{code}
I want the output in a trigger something like this:{code}
if UPDATING then FIFAPPS.ibug.log_errors('CUST_NAMES='||:OLD.CUST_NAME );{code}
