1 2 Previous Next 15 Replies Latest reply: Mar 16, 2012 10:51 AM by 832745 RSS

    Parameterized Query not returning result

    832745
      Hi,

      I need a parametrized query to return all the customers for the given locality.

      The locality name is being provided as a parameter.

      The problem with me is that the query is not returning even a single row when it should return atleast fifty!!. Locality is not unique and there are many consumers with the same value for LOCALITY. The query that I am using is given below:

      SELECT ROWID, GEOM FROM NOT_MY_CONSUMERS WHERE 's'='s' and LOCALITY=:var1;

      When I try using ObjectID as a parameter the result is being returned though. Objectid is unique for every row and thus only a single row is returned
      in the result.

      Is it not possible to use parameterized query for non unique parameters?

      Please Help!!!!
      I am stuck with my project.

      Thanks a lot!!

      Neha

      Edited by: Neha39 on Mar 16, 2012 4:53 AM

      Edited by: Neha39 on Mar 16, 2012 5:15 AM
        • 1. Re: Parameterized Query not returning result
          Devid
          At-least give an dummy query and expectation of query......
          • 2. Re: Parameterized Query not returning result
            832745
            Hi,

            The qurey being used by me is:

            SELECT ROWID, GEOM FROM NOT_MY_CONSUMERS WHERE 's'='s' and LOCALITY=:var1;


            I give the value for var1 as 'SHAKARPUR'. The table NOT_MY_CONSUMERS has 55 entries with LOCALITY='SHAKARPUR'. I expect all these rows to be returned by the query.

            Edited by: Neha39 on Mar 16, 2012 5:15 AM
            • 3. Re: Parameterized Query not returning result
              Devid
              IF your query
              SELECT ROWID, GEOM FROM NOT_MY_CONSUMERS WHERE 's'='s' and LOCALITY='SHAKARPUR';
              is returning data then 100% you can get data by using.....
              SELECT ROWID, GEOM FROM NOT_MY_CONSUMERS WHERE 's'='s' and LOCALITY=:var1

              if you want to take ROWID then you must give an column alias for ROWID, and must sure you passed the value for prompt is : SHAKARPUR
              not 'SHAKARPUR'

              so, please check this query,

              SELECT ROWID rid, GEOM FROM NOT_MY_CONSUMERS WHERE 's'='s' and LOCALITY=:var1

              and pass the prompt values is : SHAKARPUR
              not 'SHAKARPUR' (do not required the single quota with string)
              • 4. Re: Parameterized Query not returning result
                832745
                Thanks for your reply.

                But the problem is still unsolved. I removed ROWID and tried using both SHAKARPUR as well as 'SHAKARPUR' for parameter values.

                None of them returned any result.

                And the query still returns perfect result when I use LOCALITY='SHAKARPUR' but fails when I write LOCALITY=:var1

                This is so absurd. :(

                Edited by: Neha39 on Mar 16, 2012 5:47 AM
                • 5. Re: Parameterized Query not returning result
                  Billy~Verreynne
                  Neha39 wrote:

                  I need a parametrized query to return all the customers for the given locality.
                  That is not a parameterised query. It is a standard query using standerd bind variables.

                  Parameterised queries are queries that use values indirectly via name-space (Oracle context).
                  The locality name is being provided as a parameter.
                  Incorrect. The correct terminology is bind variable. Not parameter.
                  The problem with me is that the query is not returning even a single row when it should return atleast fifty!!. Locality is not unique and there are many consumers with the same value for LOCALITY. The query that I am using is given below:

                  SELECT ROWID, GEOM FROM NOT_MY_CONSUMERS WHERE 's'='s' and LOCALITY=:var1;
                  The query will return rows if the predicate for that query is true. No rows returned when it is not. So the LOCALITY=:var1 is false - and not true as you think it should be.

                  What is the data type for LOCALITY ?

                  How is +:var1+ bound - what is the data type?

                  What is the +:var1+ bind value? Is it in the correct format and case?
                  • 6. Re: Parameterized Query not returning result
                    Devid
                    Do you have rights to provide the system on TV.

                    or just try to another one as like :

                    SELECT nm FROM ops_br_mast WHERE 's'='s' and to_char(nm) =to_char(:var1);

                    otherwise, i wish you are going to wrong any other point. not on this query is false.
                    • 7. Re: Parameterized Query not returning result
                      Billy~Verreynne
                      Neha39 wrote:

                      But the problem is still unsolved. I removed ROWID and tried using both SHAKARPUR as well as 'SHAKARPUR' for parameter values.
                      Then your data is not formatted as you think it is.

                      Run the following SQL and paste the output (using \
                       ) tags here.
                      select LOCALITY, dump(LOCALITY) from NOT_MY_CONSUMERS where rownum < 11
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
                      • 8. Re: Parameterized Query not returning result
                        832745
                        Thanks for your reply. The output is this:
                        SHAKARPUR                                                                                                Typ=96 Len=100: 83,72,65,75,65,82,80,85,82,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32
                        SHAKARPUR                                                                                                Typ=96 Len=100: 83,72,65,75,65,82,80,85,82,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32
                        SHAKARPUR                                                                                                Typ=96 Len=100: 83,72,65,75,65,82,80,85,82,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32
                        SHAKARPUR                                                                                                Typ=96 Len=100: 83,72,65,75,65,82,80,85,82,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32
                        SHAKARPUR                                                                                                Typ=96 Len=100: 83,72,65,75,65,82,80,85,82,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32
                        SHAKARPUR                                                                                                Typ=96 Len=100: 83,72,65,75,65,82,80,85,82,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32
                        SHAKARPUR                                                                                                Typ=96 Len=100: 83,72,65,75,65,82,80,85,82,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32
                        SHAKARPUR                                                                                                Typ=96 Len=100: 83,72,65,75,65,82,80,85,82,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32
                        SHAKARPUR                                                                                                Typ=96 Len=100: 83,72,65,75,65,82,80,85,82,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32
                        SHAKARPUR                                                                                                Typ=96 Len=100: 83,72,65,75,65,82,80,85,82,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32
                        • 9. Re: Parameterized Query not returning result
                          832745
                          Hi Billy,

                          The datatype of LOCALITY is CHAR(100 byte).

                          The value that I am providing to :var1 is SHAKARPUR. I am using SQL Developer. How can I determine the type of the value that I am using?

                          Any help is appreciated.

                          Thanks Neha.
                          • 10. Re: Parameterized Query not returning result
                            Devid
                            As your column datatype please use this one:

                            SELECT ROWID, GEOM FROM NOT_MY_CONSUMERS WHERE 's'='s' and trim(LOCALITY)=:var1;
                            • 11. Re: Parameterized Query not returning result
                              Billy~Verreynne
                              Neha39 wrote:

                              The datatype of LOCALITY is CHAR(100 byte).
                              That is the problem. CHAR is fixed size. So you need to use value "SHAKARPUR" followed by spaces to make up a 100 bytes in total.

                              Not a good idea to use CHAR. Use VARCHAR2 instead. It does not pad string values with spaces.

                              I suggest you modify your CHAR columns to VARCHAR2 columns. More flexible. Easier used and indexed. Less space consumed.
                              • 12. Re: Parameterized Query not returning result
                                Billy~Verreynne
                                Viveka Nand wrote:
                                As your column datatype please use this one:

                                SELECT ROWID, GEOM FROM NOT_MY_CONSUMERS WHERE 's'='s' and trim(LOCALITY)=:var1;
                                Keep in mind this renders any index on LOCALITY useless. The alternative is to use RPAD() on var1 and pad it with spaces so that it is a 100 bytes in size - and allow the CBO to use an index on LOCALITY if available.
                                • 13. Re: Parameterized Query not returning result
                                  Frank Kulash
                                  Hi, Neha,
                                  Neha39 wrote:
                                  Hi Billy,

                                  The datatype of LOCALITY is CHAR(100 byte).
                                  That means all values have spaces added toi the end to make them exactly 100 bytes long. Assuming your character set uses 1 byte per character, what appears as 'SHAKARPUR' is acutally those 9 characters, followed by 91 spaces.
                                  The value that I am providing to :var1 is SHAKARPUR. I am using SQL Developer. How can I determine the type of the value that I am using?
                                  'SHAKARPUR' is a VHARCHAR2 literal, 9 characters long. It is not equal to any string that is 100 characters long.

                                  Don't use the CHAR datatype. Use VARCHAR2 instead.
                                  If you must use CHAR, then remove the trailing spaces when comparing it to VARCHAR2s:
                                  WHERE   RTRIM (locality')  = 'SHAKARPUR'
                                  or append spaces to the end of the VARCHAR2.
                                  • 14. Re: Parameterized Query not returning result
                                    832745
                                    Dear Frank,

                                    thanks for your detailed reply. It solved my problem.

                                    Regards
                                    Neha
                                    1 2 Previous Next