Performance Tuning :: Avoiding Bind Variable From Sql In Java Code?

Jun 15, 2010

We are on oracle 10.2.0.4 on Solaris 10 and have a perf. issue with a bind variable using query. The query is in java application. I want to test its performance when the query doesn't use bind variable and instead uses the passed value as literal. How can it be done?

As example lets say the query is:

SQL> variable vn varchar2(20);
SQL> EXEC :vn :='ADAMS';

PL/SQL procedure successfully completed.

SQL> select * from emp where ename=:vn;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7876 ADAMS CLERK 7788 12-JAN-83 1100 20

1 row selected.

SQL> EXEC :vn :='KING';

PL/SQL procedure successfully completed.

SQL> select * from emp where ename=:vn;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10

1 row selected.

But the statements will be taken as similar statements by oracle (due to :vn). Now I want oracle to take it as literal and the change for this has to be done in java code in my actual scenario which has a different query (but conceptually it uses bind variable and I want it to use passed value as literal). How can it be done?

View 9 Replies


ADVERTISEMENT

Performance Tuning :: Difficulty In Using Bind Variable To Check Explain Plan

Dec 12, 2011

since the optimizer (during explain plan) assumes all bind variable to be of varchar type, while checking plan for SQL statement using bind variable of numeric and date type shall we convert (typecast) it as following?

variable n_sal number
variable dt_joining date
exec n_sal:= 1000
exec dt_joining := '12-dec-2005'
select first_name from emp_data where sal=to_number(n_sal) and joining=to_date(dt_joining);

View 12 Replies View Related

SQL & PL/SQL :: Tuning Code Performance - UNION ALL?

Oct 11, 2009

I m looking for any other way that i can code without using union all for my case.my data example is like this

table1
--------
col1 col2 Segment1 Segment2 Segment3
-------------------------------------------------------------
A11 B11 John Jhonny Johnathan
A12 B12 Melisa Amy Abagial

I need to create view of above record as below:

table2
------
col1 col2 col3 col4
--------------------------------------------------------------
A11 B11 Segment1 John
A11 B11 Segment2 Jhonny
A11 B11 Segment3 Johnathan
A12 B12 Segment1 Melisa
A12 B12 Segment2 Amy
A12 B12 Segment3 Abagial

now my code is using UNION ALL to get output as in table2
select col1,col1,'Segment1' col3,Segment1 col4 from table1
union all
select col1,col1,'Segment2' col3,Segment2 from table1
union all
select col1,col1,'Segment3' col3,Segment3 from table1

But the problem is the performance is realy bad.Is there any way i can do this without using union all? The time that take to execute this is not exceptable.

View 4 Replies View Related

Performance Tuning :: How To Clear Cache For Testing Tuned Code

Jul 4, 2011

i am testing a proc after tuning it but the problem is, it is taking a very very less time which it shouldn't. I know that it is because of the buffer cache and the shared pool. that why i need to clean the cache to retest it.

I cannot bounce the database as other schemas are part of it. so is there any way to clean the cache for that particular schema i.e bouncing any particular schema(i know that the term is not appropriate).

View 4 Replies View Related

SQL & PL/SQL :: How To Use Bind Variable

Jan 12, 2012

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;

View 2 Replies View Related

SQL & PL/SQL :: Bind Variable OLD Not Declared

Nov 28, 2011

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..

View 1 Replies View Related

SQL & PL/SQL :: Bind Variable In Views?

Dec 27, 2010

how to avoid the bind variable in view.

The query is correct but it contains bind variable Based on this query, View has to be created for report

like example select * from emp where deptno = :deptno

How to get the correct result by avoiding bind variable because view does not accept bind variable.

View 2 Replies View Related

Bind Variable In Parsing?

Aug 13, 2013

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.

View 3 Replies View Related

PL/SQL :: Bind And Define Variable

Apr 2, 2013

What is the difference between these two variables and their usage?

View 6 Replies View Related

SQL & PL/SQL :: PLS-00049 Bad Bind Variable?

Nov 2, 2011

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

[code]...

View 1 Replies View Related

SQL & PL/SQL :: Bind Variable - Invalid Table Name

Jul 13, 2010

create table t (
col1 varchar2(2),
col2 varchar2(1),
tab_name varchar2(50));

[Code]....

In this case :v will be replaced with t1. I got the error invalid table name.

where table t1 has its own structure.

View 5 Replies View Related

SQL & PL/SQL :: SP2-0552 / Bind Variable NEW Not Declared

May 7, 2013

I am trying to create a simple trigger for practice. I am getting SP2-0552: Bind variable "NEW" not declared. error.

Below is the trigger script:

create or replace User_Cxt_Trigger
AFTER
INSERT or UPDATE OR DELETE ON NAME_VALUE_PREFS
referencing new as new old as old
FOR EACH ROW

[code]....

View 17 Replies View Related

PL/SQL :: Bind Variable As A Literal String Value

Nov 5, 2013

Oracle Database 10g

Enterprise Edition Release 10.2.0.5.0

- 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}

View 11 Replies View Related

Forms :: Bad Bind Variable Error?

Jun 29, 2013

Whenever I am compiling the code, following error is coming.

BLOCK: WHEN-NEW-FORM-INSTANCE(Form),5 errors
Error 49 at line 42, column 1
bad bind variable 'tool.dsp_heading'
Error 49 at line 42, column 1

[code]...

View 6 Replies View Related

SQL & PL/SQL :: Bind Variable Inside Procedure?

Jun 18, 2013

I have two procedure , from first procedure having some ref cursor output.

from second procedure I need to call first procedure and i need to process ref cursor output from first procedure so I decide to use bind variable to process ref cursor output but it showing error .

can I define bind variable inside the procedure , then how can I define it .

SQL> CREATE OR REPLACE PROCEDURE emp_by_job (
2 p_job VARCHAR2,
3 p_emp_refcur OUT SYS_REFCURSOR
4 )
5 IS
6 BEGIN

[code].....

View 10 Replies View Related

Forms :: Error 49 Bad Bind Variable?

Dec 14, 2011

i am getting this error when compiling (error 49 bad bind variable) is there anything wrong in this
(

declare
total_rec number(2);
begin
:new_id:=null; :new_sal:=null;

[code]...

the code in the add-new command when_button_pressed trigger am adding employee to the employee table by this form

View 1 Replies View Related

PL / SQL - Constant Recognized As Bind Variable By Oracle?

Jul 24, 2013

The idea is to use some constant value in PL/SQL code with requirement to feed it to Oracle as value but not bind variable. Such constants used in multiple places in the code, so wants to declare it but from DB point of view it should be value. In my case Oracle will choose much better execution plan with real value for the table.

I tried to use constant, e.g:
CODEdeclare
const1 constant number := 1;
beging

[Code].....

But in sqlarea it represented as: SELECT SUBSCRIBER_ID FROM SUBSCRIBERS WHERE STATUS = :B1

View 5 Replies View Related

SQL & PL/SQL :: Bind Variable For Dynamic Query In Procedure

May 14, 2013

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.

View 2 Replies View Related

SQL & PL/SQL :: Print Message Just Before Asking For Input To Bind Variable?

Apr 17, 2013

i want to print a message just before asking for input to a bind variable

SQL> SET serveroutput on;
SQL> DECLARE
2 pname varchar2(20);

[Code].....

but i want to print 'hello world' before asking value for bind variable like:

hello world
Enter value for num: 1
old 9: WHERE ID = #
new 9: WHERE ID = 1;

then how to achive it?

View 4 Replies View Related

Forms :: Error During Compilation - Bad Bind Variable

Jul 28, 2008

I have developed a form based on a database of books. The information displayed during run time is Sr No, Book name,Author, Copies, Description and Image(of the book)

I have written a PL/SQL code to display the image of the respective book for every new record. E.g Book1 should display image1, book2 displays image2 and so on. This should happen at runtime. The code is:

declare
gif_image varchar2(80):='c:ProjectBooks';
photo_filename varchar2(80);
begin
photo_filename := gif_image||lower(:books.sr_no)||'.gif';
[code].......

The error i get during compilation is

Error 49 at Line 5, column 37
bad bind variable 'books.sr_no'

View 4 Replies View Related

PL/SQL :: Open Ref Cursor Using Collection As Bind Variable

Feb 26, 2013

Is it possible to bind collection while opening a ref cursor. Find below the code that I am trying. My goal is to open cursor once using collection variable. Can it be done using DBMS_SQL ?

DECLARE
TYPE typ_emp_rec_in IS RECORD
(
deptno NUMBER,
sal NUMBER
[code]......

View 4 Replies View Related

PL/SQL :: How To Use Bind Variable In The Query To Avoid Hard Parsing

Jul 27, 2012

I have a query which is using literals

strquery:='SELECT SUMTOTAL FROM tab1 WHERE BATCHNO = '''
      || gBNo
      || ''' AND A_ID = '''
      || g_id
      || ''' AND L_ID = '''
      || g_LId
      || '''  AND S_Code = ''C_3'' ';

execute immediate strquery; I have been asked to use a bind variable to avoid hard parsing.How do i do it?

View 2 Replies View Related

SQL & PL/SQL :: Cannot Use Variable In Code For Timezone

Oct 10, 2012

I've written this code to present the problem:

declare
region_date varchar2(100);
region varchar2(100);
offset_result varchar2(100);
begin
region_date := '2013-04-07 01:59 Australia/Sydney';
region := 'Australia/Sydney';
select
to_char(

[code]....

What I want to do is to use the variable "region" for the timezone specification as well,And I seem to be unable to, and don't understand why.....

why when I get an "ORA-00907: missing right parenthesis" with this value,Whereas, it works when I put "AT TIME ZONE 'Australia/Sydney'" there?

View 3 Replies View Related

SQL & PL/SQL :: HTML Code In Local Variable?

Mar 14, 2012

I am working on a procedure which will insert a concatenated field that will have HTML code and a paramater value into a procedure. For some reason I am having issues inserting the HTML code into the local variable for process. I am not getting an error but instead the insert is not including the value in the variable when code when it runs. Do I need to enclose anything around the HTML to make this work?

This is an example of how I am populating the variable:

Begin
Select '<a href="URL>...l;
End;

View 4 Replies View Related

JDeveloper, Java & XML :: Read Some Values From XML Code

Sep 3, 2013

I would like to read some values from XML code, I try to do some test sql query but not successfully.The values what I need from xml are:

<currency>EUR</currency>
<amount>64.93</amount>

And there is a one example of xml:

CREATE TABLE "XML_TAB"
("ID" NUMBER(10,0),
"FILENAME" VARCHAR2(100 BYTE),
"XML" "XMLTYPE"
)
[code]..

how to write sql which get me a needed values?

View 13 Replies View Related

SQL & PL/SQL :: How To Use A Java Class File Into Oracle Code

May 16, 2010

I am using Developer 10g R2. I have a form with two fields named input_value and output_value and a button named ok. I have a java class named Factorial which return the factorial of the input value. Now i want to use this class into pl/sql in ok button.

View 4 Replies View Related

Server Utilities :: Can't Read Bad File From Java Code

Apr 25, 2012

We are using sqlldr query to load data into a table. This is handled in a java code. We are uploading an xls file which gets converted into csv and then the sqlloader call works, which in turn creates a bad and log file for error messages.

But I am not able to locate the bad file(in filesystem) within java code.

Below piece of used.

File file =new File(destfilePath);
if(file.exists()){
FileReader fr = new FileReader(file);
LineNumberReader lnr = new LineNumberReader(fr);
linenumber=0;

[code]....

The above code is able to locate CSV ,but not the bad file(whereas both files created in same path).

View 1 Replies View Related

Performance Tuning :: Tools For Database Tuning And Instance Tuning

Jul 12, 2010

Looking to understand the difference between instance tuning and database tuning.

What is the difference between these two tuning exercises? I understand that an instance is memory based structures (logical) where as database consists of physical structures.

However, how does one tune a database the physical structure? Does it have to do with file placements/block sizes etc. Would you agree that a lot of that is taken care by ASM now in 11g? What tools are required/available (third party as well as oracle supplied) for these types of tuning scenarios?

View 1 Replies View Related

JDeveloper, Java & XML :: Select Records From Table Where XML Code Complies With Requirement

Dec 7, 2010

I got table with column of XML codes like this:

select xmltype ('<parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
<parameter name="result"><value>success</value></parameter>
<parameter name="showBirthday"><value>false</value></parameter>
<parameter name="_wrCommand"><value>clearCacheBefore</value></parameter>
</parameters>'
) val
from dual;

And I need to select records from table where xml code complies with requirement:

name="result" equals success.
name="result"><value>success</value>

View 25 Replies View Related

JDeveloper, Java & XML :: Add New Field To Cater GL Account Code Into Employee Self Service Page?

May 23, 2012

I would like to add another filed into the Employee Self Service page (OAF) Expense claim portion.Basically the field would require to grab an existing GL account code based on the element link setup from expense element.

The main point to add this additional field is to capture the expenses type and displaying its GL account code based on element link account code setup.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved