Prev: Oracle SQL Date Calculations?
Next: Sysdate Value
From: ajay on 18 Jan 2006 00:27
hi i am writing a new stored procedure. the error i am gettin g is i
have a variable whereclause
in this i have an and clause as

whereclause := ' AND usr.act_key IN (SELECT DISTINCT act2.act_key
FROM' ||
' act act2, aad, usg,
ugp, usr usr5' ||
' WHERE usr5.act_key =
aad.act_key' ||
' and aad.ugp_key =
usg.ugp_key' ||
' and ugp.ugp_key =
usg.ugp_key' ||
' and usg.usr_key = '
||
' intuserkey_in
)';


in am using the same in my execute immediate statement
but not getting the desired result

EXECUTE IMMEDIATE 'SELECT count(*)'
||' FROM usr,'
|| ' act,'
|| ' oiu,'
|| ' obi,'
|| ' obj,'
|| ' ugp,'
|| ' usg,'
|| ' aad,'
|| ' oug'
||' WHERE usr.act_key = act.act_key'
||' AND usr.usr_key = oiu.usr_key'
||' AND oiu.obi_key = obi.obi_key'
||' AND obi.obj_key = obj.obj_key'
||' AND usg.usr_key = usr.usr_key'
||' AND usg.ugp_key = ugp.ugp_key'
||' AND aad.act_key = act.act_key'
||' AND aad.ugp_key = ugp.ugp_key'
||' AND obj.obj_key = oug.obj_key'
||' AND oug.ugp_key = ugp.ugp_key'
|| whereclause
INTO inttotalrows_out;

the problem is whereclause variable is not getting appended to
statement

can any body help

From: Ashish on 18 Jan 2006 02:53
Can u try to have another variable, append the 'whereclause' to that
variable, and then Execute the query?
<example>
l_query := 'SELECT count(*)'
||' FROM usr,'
|| ' act,'
|| ' oiu,'
|| ' obi,'
|| ' obj,'
|| ' ugp,'
|| ' usg,'
|| ' aad,'
|| ' oug'
||' WHERE usr.act_key = act.act_key'
||' AND usr.usr_key = oiu.usr_key'
||' AND oiu.obi_key = obi.obi_key'
||' AND obi.obj_key = obj.obj_key'
||' AND usg.usr_key = usr.usr_key'
||' AND usg.ugp_key = ugp.ugp_key'
||' AND aad.act_key = act.act_key'
||' AND aad.ugp_key = ugp.ugp_key'
||' AND obj.obj_key = oug.obj_key'
||' AND oug.ugp_key = ugp.ugp_key'
|| whereclause
INTO inttotalrows_out' ;

EXECUTE IMMEDIATE l_query .....

</example>

Ashish

From: Laurenz Albe on 18 Jan 2006 03:31
ajay <ajaym259(a)yahoo.com> wrote:
> hi i am writing a new stored procedure. the error i am gettin g is i
> have a variable whereclause
> in this i have an and clause as
>
[...]
>
> in am using the same in my execute immediate statement
> but not getting the desired result
>
[...]
>
> the problem is whereclause variable is not getting appended to
> statement

What made you think that? Please post some evidence.

If you have an error message, post it.

If the result you get from the query is not what you expect it to be,
describe the difference.

Yours,
Laurenz Albe
From: sybrandb on 18 Jan 2006 04:08
Please explain why you think you need *dynamic* sql in for an
apparently static statement with no parameters at all.
You don't want to build an unscalable application, do you.
Get rid of the execute immediate and it will work.

--
Sybrand Bakker
Senior Oracle DBA

From: DA Morgan on 18 Jan 2006 12:50
ajay wrote:
> hi i am writing a new stored procedure. the error i am gettin g is i
> have a variable whereclause
> in this i have an and clause as

I see nothing variable in your WHERE clause. Unless your code sample is
incorrect go with Sybrand's advice: This is purely static.
--
Daniel A. Morgan
http://www.psoug.org
damorgan(a)x.washington.edu
(replace x with u to respond)
 |  Next  |  Last
Pages: 1 2 3
Prev: Oracle SQL Date Calculations?
Next: Sysdate Value