Output parameter and dynamic sql

[Robert Scheer]

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

[Romeo Olympia]

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

[Romeo Olympia]

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

[sybrandb]

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

[Robert Scheer]

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