471,073 Members | 1,313 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,073 software developers and data experts.

Output parameter and dynamic sql

Hi.

I have a stored procedure on a Oracle 8.1.6 database that generates a
dynamic sql statement. This stored procedure has an output parameter
that needs to return a count from a view. I can generate and run the
sql successfuly, but when I try to return the count I get errors. I am
showing the relevant part of the procedure, since the rest of
concatenations are used to compose the sql.

CREATE OR REPLACE PROCEDURE getScoreEntries (user VARCHAR2, level
NUMBER, ret OUT NUMBER)
AS

sCall VARCHAR2(500);
nTotal NUMBER;

BEGIN
sCall := 'SELECT COUNT(*) INTO nTotal FROM vw_Scores WHERE
UserName=''' || user || '''';
sCall := sCall || --dynamic sql continues here...

EXECUTE IMMEDIATE sCall;
ret := nTotal;

END getTotalEntries;

Can anyone help me with this issue?

Thanks,
Robert Scheer
Jul 19 '05 #1
4 24400
A quick look of your code shows two things that need revising:
(1) You need to use the INTO clause of EXECUTE IMMEDIATE when you want
to get the results of a single-row query. In your case, the "count(*)"
value.
(2) Use bind arguments whenever possible (USING clause). That's the
"where username" condition for you.

So try something like this:

....
sCall := 'SELECT COUNT(*) FROM vw_Scores WHERE UserName = :username';

EXECUTE IMMEDIATE sCall INTO nTotal USING user;
....

Do read the PL/SQL guide for more detailed info. HTH.

rb******@my-deja.com (Robert Scheer) wrote in message news:<cf*************************@posting.google.c om>...
Hi.

I have a stored procedure on a Oracle 8.1.6 database that generates a
dynamic sql statement. This stored procedure has an output parameter
that needs to return a count from a view. I can generate and run the
sql successfuly, but when I try to return the count I get errors. I am
showing the relevant part of the procedure, since the rest of
concatenations are used to compose the sql.

CREATE OR REPLACE PROCEDURE getScoreEntries (user VARCHAR2, level
NUMBER, ret OUT NUMBER)
AS

sCall VARCHAR2(500);
nTotal NUMBER;

BEGIN
sCall := 'SELECT COUNT(*) INTO nTotal FROM vw_Scores WHERE
UserName=''' || user || '''';
sCall := sCall || --dynamic sql continues here...

EXECUTE IMMEDIATE sCall;
ret := nTotal;

END getTotalEntries;

Can anyone help me with this issue?

Thanks,
Robert Scheer

Jul 19 '05 #2
A quick look of your code shows two things that need revising:
(1) You need to use the INTO clause of EXECUTE IMMEDIATE when you want
to get the results of a single-row query. In your case, the "count(*)"
value.
(2) Use bind arguments whenever possible (USING clause). That's the
"where username" condition for you.

So try something like this:

....
sCall := 'SELECT COUNT(*) FROM vw_Scores WHERE UserName = :username';

EXECUTE IMMEDIATE sCall INTO nTotal USING user;
....

Do read the PL/SQL guide for more detailed info. HTH.

rb******@my-deja.com (Robert Scheer) wrote in message news:<cf*************************@posting.google.c om>...
Hi.

I have a stored procedure on a Oracle 8.1.6 database that generates a
dynamic sql statement. This stored procedure has an output parameter
that needs to return a count from a view. I can generate and run the
sql successfuly, but when I try to return the count I get errors. I am
showing the relevant part of the procedure, since the rest of
concatenations are used to compose the sql.

CREATE OR REPLACE PROCEDURE getScoreEntries (user VARCHAR2, level
NUMBER, ret OUT NUMBER)
AS

sCall VARCHAR2(500);
nTotal NUMBER;

BEGIN
sCall := 'SELECT COUNT(*) INTO nTotal FROM vw_Scores WHERE
UserName=''' || user || '''';
sCall := sCall || --dynamic sql continues here...

EXECUTE IMMEDIATE sCall;
ret := nTotal;

END getTotalEntries;

Can anyone help me with this issue?

Thanks,
Robert Scheer

Jul 19 '05 #3
rb******@my-deja.com (Robert Scheer) wrote in message news:<cf*************************@posting.google.c om>...
Hi.

I have a stored procedure on a Oracle 8.1.6 database that generates a
dynamic sql statement. This stored procedure has an output parameter
that needs to return a count from a view. I can generate and run the
sql successfuly, but when I try to return the count I get errors. I am
showing the relevant part of the procedure, since the rest of
concatenations are used to compose the sql.

CREATE OR REPLACE PROCEDURE getScoreEntries (user VARCHAR2, level
NUMBER, ret OUT NUMBER)
AS

sCall VARCHAR2(500);
nTotal NUMBER;

BEGIN
sCall := 'SELECT COUNT(*) INTO nTotal FROM vw_Scores WHERE
UserName=''' || user || '''';
sCall := sCall || --dynamic sql continues here...

EXECUTE IMMEDIATE sCall;
ret := nTotal;

END getTotalEntries;

Can anyone help me with this issue?

Thanks,
Robert Scheer


Don't see an INTO clause after the execute immediate statement.

Other than that: do you think anyone here can guess what your
unspecified errors are?

Sybrand Bakker
Senior Oracle DBA
Jul 19 '05 #4
Hi Romeo,

thanks, it worked!

Robert Scheer
ro******@hotmail.com (Romeo Olympia) wrote in message news:<42**************************@posting.google. com>...
A quick look of your code shows two things that need revising:
(1) You need to use the INTO clause of EXECUTE IMMEDIATE when you want
to get the results of a single-row query. In your case, the "count(*)"
value.
(2) Use bind arguments whenever possible (USING clause). That's the
"where username" condition for you.

So try something like this:

...
sCall := 'SELECT COUNT(*) FROM vw_Scores WHERE UserName = :username';

EXECUTE IMMEDIATE sCall INTO nTotal USING user;
...

Do read the PL/SQL guide for more detailed info. HTH.

rb******@my-deja.com (Robert Scheer) wrote in message news:<cf*************************@posting.google.c om>...
Hi.

I have a stored procedure on a Oracle 8.1.6 database that generates a
dynamic sql statement. This stored procedure has an output parameter
that needs to return a count from a view. I can generate and run the
sql successfuly, but when I try to return the count I get errors. I am
showing the relevant part of the procedure, since the rest of
concatenations are used to compose the sql.

CREATE OR REPLACE PROCEDURE getScoreEntries (user VARCHAR2, level
NUMBER, ret OUT NUMBER)
AS

sCall VARCHAR2(500);
nTotal NUMBER;

BEGIN
sCall := 'SELECT COUNT(*) INTO nTotal FROM vw_Scores WHERE
UserName=''' || user || '''';
sCall := sCall || --dynamic sql continues here...

EXECUTE IMMEDIATE sCall;
ret := nTotal;

END getTotalEntries;

Can anyone help me with this issue?

Thanks,
Robert Scheer

Jul 19 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Bob | last post: by
reply views Thread by Martin | last post: by
2 posts views Thread by Nemisis | last post: by
4 posts views Thread by Robert Scheer | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.