PL/SQL ORA-06512

Very simple proc, but can't execute it!!!  Getting the dreaded ORA-06512 error:

PL/SQL: numeric or value error: character string buffer too small

PROC:

CREATE OR REPLACE PROCEDURE jjblogp.pgetblogdescription(
  inBLOG_ID IN NUMBER,
  outBLOG_DESCN_TXT out varchar2
)

AS

BEGIN

SELECT BLOG_DESCN_TXT into outBLOG_DESCN_TXT
FROM jjblog.blog
WHERE BLOG_ID = inBLOG_ID;

END;
/

What am I doing wrong???
LVL 1
tomkynAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rafranciscoCommented:
Try specifying the length of outBLOG_DESCN_TXT output parameter:

CREATE OR REPLACE PROCEDURE jjblogp.pgetblogdescription(
  inBLOG_ID IN NUMBER,
  outBLOG_DESCN_TXT out varchar2(1000)
)
tomkynAuthor Commented:
PLSqueal doesn't seem to like it...

SQL> CREATE OR REPLACE PROCEDURE jjblogp.pgetblogdescription(
  2    inBLOG_ID IN NUMBER,
  3    outBLOG_DESCN_TXT in out varchar2(4096)
  4  )
  5  
  6  AS
  7  
  8  BEGIN
  9  
 10  SELECT BLOG_DESCN_TXT into outBLOG_DESCN_TXT
 11  FROM jjblog.blog
 12  WHERE BLOG_ID = inBLOG_ID;
 13  
 14  END;
 15  /

Warning: Procedure created with compilation errors.

SQL>
SQL> show errors
Errors for PROCEDURE JJBLOGP.PGETBLOGDESCRIPTION:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/36     PLS-00103: Encountered the symbol "(" when expecting one of the
         following:
         := . ) , @ % default character
         The symbol ":=" was substituted for "(" to continue.
GiggsipCommented:
could you desc jjblog.blog and paste it in here?
or just tell us, what is the field BLOG_DESCN_TXT datatype?
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

rafranciscoCommented:
Try this one:

CREATE OR REPLACE PROCEDURE jjblogp.pgetblogdescription(
  inBLOG_ID IN NUMBER,
  outBLOG_DESCN_TXT out jjblog.blog.BLOG_DESCN_TXT%type
)
tomkynAuthor Commented:
rafrancisco:  your suggestion compiles but execution produces the following (same) error.

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "JJBLOGP.PGETBLOGDESCRIPTION", line 13
ORA-06512: at line 1

giggsip:
1      BLOG_ID      NUMBER (12)      NOT NULL      
2      BLOG_NM      VARCHAR2 (255)      NOT NULL      
3      BLOG_DESCN_TXT      VARCHAR2 (4000)      NOT NULL      
4      BLOG_STRT_DT      DATE (7)      NOT NULL      
5      BLOG_END_DT      DATE (7)      NULL      
6      PUBL_ACS_IND      CHAR (1)      NOT NULL      
7      BLOG_ENAB_IND      CHAR (1)      NOT NULL      
8      EMP_ONLY_IND      CHAR (1)      NOT NULL      
9      ROW_ADD_TMS      DATE (7)      NOT NULL      
10      ROW_ADD_UID_NO      VARCHAR2 (30)      NOT NULL      
11      ROW_ADD_PGM_NO      VARCHAR2 (30)      NOT NULL      
12      ROW_UPDT_TMS      DATE (7)      NOT NULL      
13      ROW_UPDT_UID_NO      VARCHAR2 (30)      NOT NULL      
14      ROW_UPDT_PGM_NO      VARCHAR2 (30)      NOT NULL      
       
rafranciscoCommented:
How about this:

CREATE OR REPLACE PROCEDURE jjblogp.pgetblogdescription(
  inBLOG_ID IN jjblog.blog.BLOG_ID%type,
  outBLOG_DESCN_TXT out jjblog.blog.BLOG_DESCN_TXT%type
)
tomkynAuthor Commented:
rafrancisco:

same old error
rafranciscoCommented:
Try posting a pointer question in the Oracle Topic Area so that the Oracle experts can look into your question.  Sorry if I wasn't able to help much.
DrSQLCommented:
tomkyn,
    Oracle's varchar2 datatype has a limit of 4000 characters.  If you're brining back more than that you might want to use a clob (character large object):

CREATE OR REPLACE PROCEDURE jjblogp.pgetblogdescription(
  inBLOG_ID IN NUMBER,
  outBLOG_DESCN_TXT out clob
)

AS

BEGIN

SELECT BLOG_DESCN_TXT into outBLOG_DESCN_TXT
FROM jjblog.blog
WHERE BLOG_ID = inBLOG_ID;

END;
/

And, have you considered making this a function?  Not that it is the issue, but it sure seems more appropriate for the functionality:

CREATE OR REPLACE FUNCTION jjblogp.pgetblogdescription(inBLOG_ID IN NUMBER) RETURN CLOB IS
  outBLOG_DESCN_TXT CLOB

AS

BEGIN

SELECT BLOG_DESCN_TXT into outBLOG_DESCN_TXT
FROM jjblog.blog
WHERE BLOG_ID = inBLOG_ID;

return outBLOG_DESCN_TXT;

END;
/

Good luck!
DrSQL
GiggsipCommented:
Although I don't see why this problem occurs, as the varchar2 datatype should be able to handle 4000 chars (that is the maximum), how about you try changing the variable into a BLOB?
it might be a bit less comfortable but hopefully will work.
DrSQLCommented:
tomkyn,
   I just read the table description.  I know that pl/sql handles strings longer than 2000 characters differently.  The fact that you're selecting into a parameter is not considered good form, so it may be the issue.  Try selecting into a local variable and then making an assignment, or using the function idea I gave you before (but with varchar2(4000) instead of clob).

Good luck!
DrSQL
GiggsipCommented:
of course I meant CLOB, and ofcourse DrSQL beat me to it... :)
tomkynAuthor Commented:
drsql:

can you show an example?  I'm wet behing my ears in PLSqueal.  This has to be a varchar, because it has to be searchable.  Also, at any time during testing I have not returned more than 32 chars.
DrSQLCommented:
tomkyn,
    I just noticed that you have an "AS" right before the begin - normally packages use AS and store procs/funcs use "IS" (I actually had BOTH - incorrectly - in my last post).  That could be a problem.  If that isn't it, then it might be helpful to see an example of your testing.  How are you passing a variable to the procedure?

  Here's your procedure defined as a function:

CREATE OR REPLACE FUNCTION jjblogp.pgetblogdescription(inBLOG_ID IN NUMBER) RETURN varchar2 IS
  outBLOG_DESCN_TXT varchar2(4000);
BEGIN

SELECT BLOG_DESCN_TXT into outBLOG_DESCN_TXT
FROM jjblog.blog
WHERE BLOG_ID = inBLOG_ID;

return outBLOG_DESCN_TXT;

END;
/

Good luck!
DrSQL

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sidcapCommented:
tomkyn, your problem resides in how you are calling that procedure, there is no problem with the procedure itself.

Are are you testing the procedure ? from within PL/SQL?, let me give you an anonymous block for you to test your original procedure once again:

declare
resp varchar2(4000);
begin
 jjblogp.pgetblogdescription(1,resp)   -- 1 or any valid id
end;
/

this shoud run fine.

Hope this helps, SIDCAP


David VanZandtOracle Database Administrator IIICommented:
There are a number of postings about this error, but my money's on determining if you have execute privileges on the HR package.  And yes, the abcd stuff indicates a wrapped object.
srilathag09Commented:
Am getting the same error which is ORA-06512
why its populates
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.