How to substitute oracle pl/sql variable in where clause of select

Question

Inside the function ,

I am having the following function,

create or replace 
FUNCTION DiffMaxMinPrice
 return double precision
is 
 diffprice double precision := 0;
 minprice long; 
 maxprice long;
 value long;
 indexid number(19,0);
begin

for row in 
 (SELECT  x.* into minprice , maxprice 
       FROM sampletable ,
            XMLTABLE ('//book'
                      PASSING sampletable.xmlcol
                      COLUMNS maxprice VARCHAR2(30) PATH '@maxprice',
                              minprice VARCHAR2(30) PATH '@minprice') x 
                              where sampletable.indexid = 2)
 LOOP
  ....
 END LOOP;


 return 1;
end;

Instead of hard coding with 2, i would like to substiute the variable idxid. I keep getting invalid number when I substituted a variable there.

indexid :=2 And the where part of the select statement as

sampletable.indexid = indexid

Jorge Campos · Answer 1 · 2014-07-15T14:01:25.487

0

You can make it as a cursor with a parameter, like this:

cursor cs ( pIndexId IN number ) is 
(SELECT  x.* into minprice , maxprice 
           FROM sampletable ,
                XMLTABLE ('//book'
                          PASSING sampletable.xmlcol
                          COLUMNS maxprice VARCHAR2(30) PATH '@maxprice',
                                  minprice VARCHAR2(30) PATH '@minprice') x 
                                  where sampletable.indexid = pIndexId );

csrom cs%rowtype;

And then you use it as:

open cs( someVariableYouNeed );
loop
    fetch cs into csrom
    exit when cs%NOT_FOUND;

    --do whatever you need here

end loop;
close cs;

If you have this variable in your code you can also use it on your code. It will work fine.

for row in 
  (SELECT  x.* into minprice , maxprice 
       FROM sampletable ,
            XMLTABLE ('//book'
                      PASSING sampletable.xmlcol
                      COLUMNS maxprice VARCHAR2(30) PATH '@maxprice',
                              minprice VARCHAR2(30) PATH '@minprice') x 
                              where sampletable.indexid = someVariableYouNeed )

If you are getting: Invalid number that means that either the column is not of type number or your variable has a value that is not a number.

edited Jul 15 '14 at 14:01

answered Jul 15 '14 at 13:53

Jorge Campos

22,647
7
56
87

I want to substitute a variable for that indexid. The value of indexid is from another query. – Nishanth Lawrence Reginold Jul 15 '14 at 13:57
Thats is just an example. You can use instead of `1` what ever variable you want. I will edit it. – Jorge Campos Jul 15 '14 at 13:58
It is not working even if I give assign it before like this, `indexid := 2;` and then use it – Nishanth Lawrence Reginold Jul 15 '14 at 14:15
What is the type of `sampletable.indexid` column? If you are getting this error it means that this column is not a number or has a value that cannot be implicit converted to a number. – Jorge Campos Jul 15 '14 at 14:18
how is the types of minprice , maxprice defined? of `into minprice , maxprice` – Jorge Campos Jul 15 '14 at 14:28
Yes they are all defined . It is working fine when I give as sampletable.indexid = 2; (Or any number) . – Nishanth Lawrence Reginold Jul 15 '14 at 14:51
So I'm guessing that you have this code inside a loop and when you use a variable to bind that value, at some point you are having this error, is that it? – Jorge Campos Jul 15 '14 at 14:57
If I hard code the values it is working, but when I substitute some variables it is not working. – Nishanth Lawrence Reginold Jul 16 '14 at 05:35
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/57369/discussion-between-nishanth-lawrence-and-jorge-campos). – Nishanth Lawrence Reginold Jul 16 '14 at 06:10

How to substitute oracle pl/sql variable in where clause of select

1 Answers1