return value from stored procedure..

[Guest_imported]

I need a value( true or false - or a single row ) returned by a stored procedure.. but i don't know why i can't return the value.. it keeps returning an error message( compilation errors )... It looks a very simple procedure.. and I want to execute this procedure in Active Server Pages... please, help me...

-------------------------------------------------------
CREATE OR REPLACE PROCEDURE SP_BADWORD
( WORD IN VARCHAR, BADWORD_MODE OUT VARCHAR )
IS
BEGIN
SELECT BADWORD INTO BADWORD_MODE FROM SY_BADWORDS WHERE BADWORD = WORD;

END SP_BADWORD;
-------------------------------------------------------

 

[karluk]

Can you be more explicit about the error? You can use the command "show errors procedure sp_badword" to see the compilation errors in SQL*Plus.

 

[Guest_imported]

oh.. it doesn't return an error... but i don't know how to execute( returning true or false - or a single row ) it in ASP...

 

[carp]

To use DBMS_OUTPUT.PUT_LINE and see results, you need to
SET SERVEROUTPUT ON

However, looking at this as well as your other post, I think a function might work better for you:

CREATE OR REPLACE FUNCTION badword_exists (p_badword VARCHAR2) RETURN VARCHAR2 AS
v_badword VARCHAR2(100);
BEGIN
SELECT badword INTO v_badword
FROM sy_badwords
WHERE badword = p_badword;
RETURN('TRUE');
EXCEPTION
WHEN no_data_found THEN RETURN('FALSE');
WHEN too_many_rows THEN RETURN('TRUE');
END;

Now you can run a SQL query such as

SELECT badword_exists('foo') FROM dual;

and it will return either 'TRUE' or 'FALSE'. You can then embed calls to this function from SQL*Plus sessions, other procedures, or (I would assume) ASP.