problem to test my procedure

Results 1 to 2 of 2

Thread: problem to test my procedure

  1. #1
    Join Date
    Dec 1969

    Default problem to test my procedure

    Hi all, <BR><BR>I created a procedure which I will use in my ASP later as the following: <BR><BR>CREATE OR REPLACE PROCEDURE ASP_RefData_FindProduct( i_ProductName IN VARCHAR2, <BR>i_ProductExist OUT VARCHAR2) <BR>IS <BR><BR>V_NAME S_PROD_INT.NAME%TYPE; <BR>CURSOR curProduct IS <BR>SELECT <BR>NAME <BR>FROM <BR>S_PROD_INT <BR>WHERE <BR>NAME = i_ProductName; <BR><BR>BEGIN <BR>OPEN curProduct; <BR>LOOP <BR>FETCH curProduct INTO V_NAME; <BR>if curProduct%NOTFOUND then <BR>i_ProductExist := &#039;NO&#039;; <BR>ELSE <BR>i_ProductExist := &#039;YES&#039;; <BR>end if; <BR>END LOOP; <BR><BR>DBMS_OUTPUT.PUT_LINE(&#039;PRODUCT EXIST: &#039;&#124&#124 i_ProductExist); <BR>CLOSE curProduct; <BR><BR>EXCEPTION <BR>when others then <BR>CLOSE curProduct; <BR>raise_application_error(-20000, &#039;Unknown Exception Raised: &#039;&#124&#124sqlcode&#124&#124&#039; &#039;&#124&#124sqlerrm); <BR>END ASP_RefData_FindProduct; <BR><BR>It was successfully compiled in SQL plus. However, when I tried to execute as the following: <BR><BR>SET SERVEROUTPUT ON <BR>VARIABLE PRODExist VARCHAR2 <BR>EXECUTE ASP_RefData_FindProduct(&#039;JustOne&#039;, :PRODExist) <BR><BR>It gave me the following error message: <BR><BR>ERROR at line 1: <BR>ORA-20000: Unknown Exception Raised: -6502 ORA-06502: PL/SQL: numeric or value <BR>error: character string buffer too small <BR>ORA-06512: at "SIEBEL.ASP_REFDATA_FINDPRODUCT", line 31 <BR>ORA-06512: at line 1 <BR><BR>What is wrong? I am expecting to see "PRODUCT EXIST: Yes". ANy suggestion will be appreciated. <BR>By the way, is there anyway I can get value of i_ProductExist without fetching the cursor? <BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: problem to test my procedure

    One problem could be that you does not specify any size for the parameters. All Oracle documents says that you must specify a size for the varchar datatypes.<BR><BR><BR>if exists (SELECT NAME FROM S_PROD_INT <BR> WHERE NAME = i_ProductName )then<BR>i_ProductExist := &#039;NO&#039;; <BR>ELSE <BR>i_ProductExist := &#039;YES&#039;; <BR>end if; <BR><BR>Might work, as a alternative solution for your second problem

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts