Home » SQL & PL/SQL » SQL & PL/SQL » Pl/Sql Error (Oracle 10g Rel2 )
Pl/Sql Error [message #391008] Tue, 10 March 2009 11:50 Go to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Hi All,

When I run the below code , I'm getting the error as

DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4



***********************************************************
* Script: INSERT_DIRECTORY_ADMIN.txt
* Created Date: 02/26/2009
* Purpose: Insert NEW ADMIN USERS to run Directory reporting
***********************************************************/

/* Insert NEW role Type */

INSERT INTO role_type
(role_id, role_type)
VALUES
(283, 'ROLE_FRI_DIRECTORY_ADMIN');
commit;

/* Insert NEW admin users, its user_role and user_property */

DECLARE
p_user_id NUMBER;
TYPE membernumberList IS TABLE OF VARCHAR2(11);
membernumbers membernumberList := membernumberList('prodsupport','ownerwebteam');
TYPE passwordList IS TABLE OF VARCHAR2(11);
passwords passwordList := passwordList('margate01','orlando01');
BEGIN


FOR i IN membernumbers.FIRST .. membernumbers.LAST
LOOP
DBMS_OUTPUT.PUT_LINE('looping');

SELECT HIBERNATE_SEQUENCE.NEXTVAL
INTO p_user_id
FROM DUAL;

INSERT INTO USERS
(USER_ID,
USER_NAME,
PASSWORD,
EMAIL,
SECURITY_QUESTION,
SECURITY_ANSWER,
DISABLED_FLAG,
DELETED_FLAG,
LOCKOUT_FLAG,
CREATED_TIME)
VALUES
(p_user_id,
membernumbers(i),
passwords(i),
'abc@xyx.com',
'Please do not use the forgot password function on this account',
passwords(i),
0,
0,
0,
SYSDATE);

INSERT INTO USER_ROLE
(USER_ROLE_ID,
ROLE_ID,
USER_ID)
VALUES
(HIBERNATE_SEQUENCE.NEXTVAL,
283,
p_user_id);

INSERT INTO user_properties
(user_properties_id,
property_id,
user_id,
VALUE)
VALUES
(Hibernate_sequence.NEXTVAL,
215,
p_user_id,
membernumbers(i));

END LOOP;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('inserting admin failed');
END;

commit;

All this is a single script which we need to execute. Tried increasing the lenght of the varible also, but it was of no help.
Can somebody help on this please. It's urgent.

Regards,
Raj
Re: Pl/Sql Error [message #391009 is a reply to message #391008] Tue, 10 March 2009 11:54 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.

>Can somebody help on this please. It's urgent.
Please explain why it is URGENT for me to solve this problem for you.


DECLARE 
  p_user_id      NUMBER; 
   TYPE membernumberlist IS TABLE OF VARCHAR2(11); 
   membernumbers  MEMBERNUMBERLIST := Membernumberlist('prodsupport','ownerwebteam'); 
   TYPE passwordlist IS TABLE OF VARCHAR2(11); 
   passwords      PASSWORDLIST := Passwordlist('margate01','orlando01'); 
BEGIN 
  FOR i IN membernumbers.FIRST.. membernumbers.LAST LOOP 
    dbms_output.Put_line('looping'); 
     
    SELECT hibernate_sequence.nextval 
    INTO   p_user_id 
    FROM   dual; 
     
    INSERT INTO users 
               (user_id, 
                user_name, 
                PASSWORD, 
                email, 
                security_question, 
                security_answer, 
                disabled_flag, 
                deleted_flag, 
                lockout_flag, 
                created_time) 
    VALUES     (p_user_id, 
                Membernumbers(i), 
                Passwords(i), 
                'abc@xyx.com', 
                'Please do not use the forgot password function on this account', 
                Passwords(i), 
                0, 
                0, 
                0, 
                SYSDATE); 
     
    INSERT INTO user_role 
               (user_role_id, 
                role_id, 
                user_id) 
    VALUES     (hibernate_sequence.nextval, 
                283, 
                p_user_id); 
     
    INSERT INTO user_properties 
               (user_properties_id, 
                property_id, 
                user_id, 
                VALUE) 
    VALUES     (hibernate_sequence.nextval, 
                215, 
                p_user_id, 
                Membernumbers(i)); 
  END LOOP; 
EXCEPTION 
  WHEN OTHERS THEN 
    dbms_output.Put_line('inserting admin failed'); 
END;
Re: Pl/Sql Error [message #391010 is a reply to message #391008] Tue, 10 March 2009 12:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The only urgent thing is to read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

One advice: your WHEN OTHERS is just stupid.

Regards
Michel
Re: Pl/Sql Error [message #391012 is a reply to message #391008] Tue, 10 March 2009 12:06 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
Why after Messages: 211 do you refuse to comply with Posting Guidelines???????????????????
Re: Pl/Sql Error [message #391016 is a reply to message #391009] Tue, 10 March 2009 12:25 Go to previous messageGo to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Hi ,

I'm extremly Sorry for my previous mail for not posting as per the rules. I've attached the complete script which has the entire DDL and DML's in that . Can you spare some time into that and figure out what where's wrong in the code.

Everything seems to be OK , except the PL/SQL part . I tried to debug it but didn't work.

Any help me on this please.

Regards,
Raj
  • Attachment: test.txt
    (Size: 4.80KB, Downloaded 115 times)
Re: Pl/Sql Error [message #391017 is a reply to message #391008] Tue, 10 March 2009 12:28 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>I tried to debug it but didn't work.
Above is not a valid Oracle error message.

Unless & until you comply with Posting Guidelines, answer is not possible.
Re: Pl/Sql Error [message #391018 is a reply to message #391008] Tue, 10 March 2009 12:29 Go to previous messageGo to next message
cookiemonster
Messages: 12410
Registered: September 2008
Location: Rainy Manchester
Senior Member
Which line is line 4?
Re: Pl/Sql Error [message #391019 is a reply to message #391008] Tue, 10 March 2009 12:39 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
Posting Guidelines state
Practice

Show us what you did (if you tried it yourself) and how Oracle responded (COPY & PASTE your SQL*Plus session), including errors and/or why the result is not what you want.
Do not describe, explain or report - show us!
Re: Pl/Sql Error [message #391020 is a reply to message #391018] Tue, 10 March 2009 12:49 Go to previous messageGo to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4

membernumbers MEMBERNUMBERLIST := Membernumberlist('prodsupport','ownerwebteam');

The above is the 4'th line.

I tried to increase the length of charecters but could'nt succeed.

Kindly help me please.
Re: Pl/Sql Error [message #391021 is a reply to message #391008] Tue, 10 March 2009 12:58 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
You refuse to show us what we request.
We don't know what you are doing.

>membernumbers MEMBERNUMBERLIST := Membernumberlist('prodsupport','ownerwebteam');

above line is part of something that you have not provided.

Why do you expect help debugging code we do not have & can not see?

Enjoy your bug. I refuse to waste more time on this.
Re: Pl/Sql Error [message #391022 is a reply to message #391008] Tue, 10 March 2009 12:58 Go to previous messageGo to next message
cookiemonster
Messages: 12410
Registered: September 2008
Location: Rainy Manchester
Senior Member
So what's the length of MEMBERNUMBERLIST and what's the length of the values you're assigning to it?
Re: Pl/Sql Error [message #391023 is a reply to message #391020] Tue, 10 March 2009 12:58 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

I tried to increase the length of charecters but could'nt succeed.



"couldn't succeed" isn't an Oracle error either. And HOW did you try. It is absolutely impossible to help you if you don't post the required information.
Re: Pl/Sql Error [message #391025 is a reply to message #391021] Tue, 10 March 2009 13:03 Go to previous messageGo to next message
cookiemonster
Messages: 12410
Registered: September 2008
Location: Rainy Manchester
Senior Member
BlackSwan wrote on Tue, 10 March 2009 17:58

>membernumbers MEMBERNUMBERLIST := Membernumberlist('prodsupport','ownerwebteam');

above line is part of something that you have not provided.




So you're saying that's not in the code you reformatted in your earlier post?
Looks the same to me.
Looks like line 4 as well.
Re: Pl/Sql Error [message #391027 is a reply to message #391020] Tue, 10 March 2009 13:08 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
rsreddy28 wrote on Tue, 10 March 2009 13:49
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4

membernumbers MEMBERNUMBERLIST := Membernumberlist('prodsupport','ownerwebteam');

The above is the 4'th line.

I tried to increase the length of charecters but could'nt succeed.

Kindly help me please.



Aside from the fact that you make no effort to make a proper post, this is quite obvious. What do you not understand?
ORA-06502: PL/SQL: numeric or value error: character string buffer too small


Do you even know what it means when a character string buffer is too small?
Re: Pl/Sql Error [message #391030 is a reply to message #391027] Tue, 10 March 2009 13:16 Go to previous messageGo to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Hi All,

I've attached the complete script which contains the entire code. I tried to increase the length of p_user_id NUMBER to more than user_id column in users table . The lenght of the column user_id in users table is 19 and I tried giving 25 and 30 also . But still it gives me the same error.


DECLARE
p_user_id NUMBER(30);
TYPE membernumberList IS TABLE OF VARCHAR2(11);
membernumbers membernumberList := membernumberList('prodsupport','ownerwebteam');
TYPE passwordList IS TABLE OF VARCHAR2(11);
passwords passwordList := passwordList('margate01','orlando01');

I'm getting the error at the line in bold.

------------

DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4


Please help me out .
  • Attachment: test.txt
    (Size: 4.80KB, Downloaded 70 times)
Re: Pl/Sql Error [message #391031 is a reply to message #391030] Tue, 10 March 2009 13:24 Go to previous messageGo to next message
cookiemonster
Messages: 12410
Registered: September 2008
Location: Rainy Manchester
Senior Member
rsreddy28 wrote on Tue, 10 March 2009 18:16
Hi All,

I've attached the complete script which contains the entire code. I tried to increase the length of p_user_id NUMBER to more than user_id column in users table . The lenght of the column user_id in users table is 19 and I tried giving 25 and 30 also . But still it gives me the same error.




?????

I'm not surprised. Where does p_user_id come into the problem line?
Re: Pl/Sql Error [message #391032 is a reply to message #391031] Tue, 10 March 2009 13:29 Go to previous messageGo to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Like that was the script which we were told to execute on a schema. You mean to say that the code is wrong ?
Re: Pl/Sql Error [message #391033 is a reply to message #391031] Tue, 10 March 2009 13:30 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
*Mind Boggles*

Sledgehammer Hint 1: How long is the string "ownerwebteam"?

Sledgehammer Hint 2: How long is the "TABLE OF VARCHAR2" type you try to hammer it into?
Previous Topic: killing session with status snipped
Next Topic: Problems in DDE, TEXT_IO, OLE2
Goto Forum:
  


Current Time: Wed Dec 07 04:59:32 CST 2016

Total time taken to generate the page: 0.05647 seconds