sql parser(change parsing method)

Question

I have one select:

select * from table1 where col1=10;

and another select:

select * from table1 where col1=11;

Sql parser parses them as different sqls.. I want to make SQL PARSER parse the statement once and just change the parameter in the where clause..Can I do that? Do have any idea ? please share it with me.. thanks a lot.

P.S: I am selecting:

select * from v$sql
where parsing_user_id=55 (my user id)

and see that new row is inserted when i run first query and another row is inserted when i run second query.If i run first or second query one more time now new row is inserted(means that sql parser doesn't parse it once more)

Alex Poole · Accepted Answer · 2010-10-15 10:51:20Z

7

You need to use bind variables in some form. If you're doing this in SQL*Plus, for example, you could do:

var myval number;

exec :myval := 10;
select * from table1 where col1 = :myval;

exec :myval := 11;
select * from table1 where col1 = :myval;

If you're calling from somewhere else there are slightly different mechanisms so you might need to be a bit more specific.

answered Oct 15, 2010 at 10:51

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

kupa Over a year ago

Thank you very good answer,but I am using PL/SQL Developer..how can it be there?

Alex Poole Over a year ago

Hmm, don't use that but I thought it was the same; have you tried it?

kupa Over a year ago

yaa I've tried it works :) inserting just one row for this queries..but it should be done from PL/SQL developer

kupa Over a year ago

no it will not work because it will be more complicated syntax.You must create function and give it parameter an so on (one more thing you cannot write select in function like that you should assign result of the querie to another variable and return it) You know may be i've got the answer..all i need is to shake my head and write this function based on bind variables. Thank you very much for you answer.

Dave Costa Over a year ago

If you are putting the select into a function, simply use a function parameter for the lookup value. PL/SQL automatically uses bind variables as placeholders for variables used in SQL statements.

|

Collectives™ on Stack Overflow

sql parser(change parsing method)

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related