Solved

ORA-

Posted on 2011-09-02
21
391 Views
Last Modified: 2012-05-12
Hello Experts,

I have a plsql block which I am testing to get the output for procedure which add line to my table, the values are been passed with an object type but for testing if the procedure works fine I am using this piece of code which gives  me an error :

Error Code: -6530



 
DECLARE
  l_line_ui_obj_i line_ui_tab_type;
  l_return_status_o VARCHAR2(2000);
  l_quote_type_i    VARCHAR2(5);
  l_add_target_obj_o target_obj_type;
  l_proc_id_io  NUMBER;
  l_cco_user_id VARCHAR2 (200) ;
  l_screen_name_i ui_filter_stg.screen_name%type;
BEGIN
  dbms_output.put_line('Start check for code changes on Add line page');
  l_line_ui_obj_i := line_ui_tab_type ();
  l_line_ui_obj_i.EXTEND;
  l_line_ui_obj_i(l_line_ui_obj_i.LAST).serial_number   := NULL;
  L_LINE_UI_OBJ_I(L_LINE_UI_OBJ_I.last).PRODUCT_NUMBER  := NULL;
  l_line_ui_obj_i(l_line_ui_obj_i.LAST).service_sku     := 'CP-960';
  l_line_ui_obj_i(l_line_ui_obj_i.LAST).quote_header_id := NULL;
  l_quote_type_i                                        := 'Q';
  l_proc_id_io                                          :=192;
  L_CCO_USER_ID                                         :='RM';
  L_SCREEN_NAME_I                                       :='Pricing Item';
  
  LINE_ACTIONS.ADD_LINES 
 (p_proc_id_io => l_proc_id_io,
  p_cco_userid_i => l_cco_user_id,
  p_screen_name_i => l_screen_name_i,
  p_line_ui_tab_i => l_line_ui_obj_i,
  p_return_status_o => l_return_status_o );
  
  dbms_output.put_line('RETURN VALUES AS BELOW');
   dbms_output.put_line('* START ='||l_return_status_o);
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line('Error code = '||SQLCODE);
  dbms_output.put_line('Error message = '||SQLERRM);
END;

Open in new window

0
Comment
Question by:Swadhin Ray
  • 12
  • 9
21 Comments
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 36472013
Need to comment : l_add_target_obj_o target_obj_type;

As we are not using this in our block.
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 36472092
ACCESS_INTO_NULL 06530 -6530 A program attempts to assign values to the attributes of an uninitialized object
(http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/errors.htm#sthref1966)

In spite of there is no usage of
l_add_target_obj_o target_obj_type;
in your testing piece of code I will try add this:
BEGIN
  dbms_output.put_line('Start check for code changes on Add line page');
l_add_target_obj_o:= target_obj_type();
  l_line_ui_obj_i := line_ui_tab_type ();
...
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 36472200
Any solution for passing the values ? on my code?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 16

Author Comment

by:Swadhin Ray
ID: 36472222
can you give me the example for how to pass the values based on my code?
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 36472242
What values ought to be passed ?
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 36472264
Maybe I don't understand the question:

when you run this:
DECLARE
  l_line_ui_obj_i line_ui_tab_type;
  l_return_status_o VARCHAR2(2000);
  l_quote_type_i    VARCHAR2(5);
--  l_add_target_obj_o target_obj_type;
  l_proc_id_io  NUMBER;
  l_cco_user_id VARCHAR2 (200) ;
  l_screen_name_i ui_filter_stg.screen_name%type;
BEGIN
  dbms_output.put_line('Start check for code changes on Add line page');
  l_line_ui_obj_i := line_ui_tab_type ();
  l_line_ui_obj_i.EXTEND;
  l_line_ui_obj_i(l_line_ui_obj_i.LAST).serial_number   := NULL;
  L_LINE_UI_OBJ_I(L_LINE_UI_OBJ_I.last).PRODUCT_NUMBER  := NULL;
  l_line_ui_obj_i(l_line_ui_obj_i.LAST).service_sku     := 'CP-960';
  l_line_ui_obj_i(l_line_ui_obj_i.LAST).quote_header_id := NULL;
  l_quote_type_i                                        := 'Q';
  l_proc_id_io                                          :=192;
  L_CCO_USER_ID                                         :='RM';
  L_SCREEN_NAME_I                                       :='Pricing Item';
 
  LINE_ACTIONS.ADD_LINES
 (p_proc_id_io => l_proc_id_io,
  p_cco_userid_i => l_cco_user_id,
  p_screen_name_i => l_screen_name_i,
  p_line_ui_tab_i => l_line_ui_obj_i,
  p_return_status_o => l_return_status_o );
 
  dbms_output.put_line('RETURN VALUES AS BELOW');
   dbms_output.put_line('* START ='||l_return_status_o);
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line('Error code = '||SQLCODE);
  dbms_output.put_line('Error message = '||SQLERRM);
END;
is there still the same error ?

If yes then
create object type
line_ui_tab_type
in schema.
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 36472283
The problem is with add line :

p_line_ui_tab_i => l_line_ui_obj_i,

The service SKU is not been passed to the procedure ...

For which I want to check if the service SKU is been passed or not...
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 36472328
Is there any error or simply the values are not passed ?
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 36472423
The error is same as I mentioned earlier ... 6350
0
 
LVL 22

Accepted Solution

by:
Helena Marková earned 500 total points
ID: 36472505
Please, try to create object type
line_ui_tab_type
in schema.
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 36472624
From the below code I am able to execute my block successfully but I really doubt if my  "l_line_ui_obj_i " is getting passed ..

as I am not able to get the output for the below line:

DBMS_OUTPUT.PUT_LINE('Serial number taken for extend');  

now what ever I have passed in the tab type I want them to show in the DBMS output for verification if the values are been passed or not .
declare
l_line_ui_obj_i           line_ui_tab_type;
l_return_status_o        varchar2(2000);
l_quote_type_i           varchar2(5);
l_proc_id_io             number;  
l_cco_user_id            varchar2 (200)  ;
l_screen_name_i           ui_filter_stg.screen_name%type;
begin  
dbms_output.put_line('Start check for code changes on Add line page');  
L_LINE_UI_OBJ_I  :=  LINE_UI_TAB_TYPE();
FOR i IN  1 .. L_LINE_UI_OBJ_I.count
LOOP
L_LINE_UI_OBJ_I.extend();
DBMS_OUTPUT.PUT_LINE('Start of extend'); 
L_LINE_UI_OBJ_I(L_LINE_UI_OBJ_I.last).SERIAL_NUMBER := null;
DBMS_OUTPUT.PUT_LINE('Serial number taken for extend');  - not able to get as output ..  l_line_ui_obj_i(l_line_ui_obj_i.LAST).product_number := null;
l_line_ui_obj_i(l_line_ui_obj_i.LAST).service_sku := 'CP-60';
L_LINE_UI_OBJ_I(L_LINE_UI_OBJ_I.last).QUOTE_HEADER_ID := null;
end loop ;
DBMS_OUTPUT.PUT_LINE('end of extend');
L_QUOTE_TYPE_I := 'Q'; 
l_proc_id_io :=24806192;
l_cco_user_id :='RM';
l_screen_name_i:='Pricing Item';
line_actions.add_lines 
(    p_proc_id_io        =>              l_proc_id_io,
    p_cco_userid_i      =>              l_cco_user_id, 
   p_screen_name_i     =>              l_screen_name_i, 
   p_line_ui_tab_i     =>              l_line_ui_obj_i, 
   p_return_status_o   =>              l_return_status_o );  
  dbms_output.put_line('RETURN VALUES AS BELOW'); 
 dbms_output.put_line('* START ='||l_return_status_o);
exception
when others then
dbms_output.put_line('Error code = '||SQLCODE);
dbms_output.put_line('Error message = '||SQLERRM);
end;

Open in new window

0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 36472667
What is output of the code ?

'Start check for code changes on Add line page'
'Start of extend'
???

What is the value of L_LINE_UI_OBJ_I.count ?
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 36472785
I have added another line after :

DBMS_OUTPUT.PUT_LINE('Start of extend');
DBMS_OUTPUT.PUT_LINE('The count is '||L_LINE_UI_OBJ_I.count );


when I run  my block it getting hang ..

Can you tell me where I should add the DBMS for getting the count ?
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 36472833
The code is wrong, it hangs because the LOOP does not end. I have removed LOOP.

declare
l_line_ui_obj_i           line_ui_tab_type;
l_return_status_o        varchar2(2000);
l_quote_type_i           varchar2(5);
l_proc_id_io             number;  
l_cco_user_id            varchar2 (200)  ;
l_screen_name_i           ui_filter_stg.screen_name%type;

n_count PLS_INTEGER;
begin  
dbms_output.put_line('Start check for code changes on Add line page');  
L_LINE_UI_OBJ_I  :=  LINE_UI_TAB_TYPE();

dbms_output.put_line('1');

L_LINE_UI_OBJ_I.extend();

n_count:=L_LINE_UI_OBJ_I.count;
dbms_output.put_line(n_count);

DBMS_OUTPUT.PUT_LINE('Start of extend');
L_LINE_UI_OBJ_I(L_LINE_UI_OBJ_I.last).SERIAL_NUMBER := null;
DBMS_OUTPUT.PUT_LINE('Serial number taken for extend');  - not able to get as output ..  l_line_ui_obj_i(l_line_ui_obj_i.LAST).product_number := null;
l_line_ui_obj_i(l_line_ui_obj_i.LAST).service_sku := 'CP-60';
L_LINE_UI_OBJ_I(L_LINE_UI_OBJ_I.last).QUOTE_HEADER_ID := null;

DBMS_OUTPUT.PUT_LINE('end of extend');

L_QUOTE_TYPE_I := 'Q';
l_proc_id_io :=24806192;
l_cco_user_id :='RM';
l_screen_name_i:='Pricing Item';

line_actions.add_lines
(    p_proc_id_io        =>              l_proc_id_io,
    p_cco_userid_i      =>              l_cco_user_id,
   p_screen_name_i     =>              l_screen_name_i,
   p_line_ui_tab_i     =>              l_line_ui_obj_i,
   p_return_status_o   =>              l_return_status_o );  

  dbms_output.put_line('RETURN VALUES AS BELOW');
 dbms_output.put_line('* START ='||l_return_status_o);
exception
when others then
dbms_output.put_line('Error code = '||SQLCODE);
dbms_output.put_line('Error message = '||SQLERRM);
end;

The output ought to be like this:
'Start check for code changes on Add line page'
'1'
<<value of n_count>>
'Start of extend'
...
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 36472976
Modified accordingly and will checking again..

Will update you soon..
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 36472991
Now I am getting the below as output :

Start check for code changes on Add line page
1
1
Start of extend
Error code = -6530
Error message = ORA-06530: Reference to uninitialized composite
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 36473071
Is there SERIAL_NUMBER in object type ? Can it be NULL ?

You can try this for initilalize - there is no need for using EXTEND
l_line_ui_obj_i := line_ui_tab_type (NULL,NULL, 'CP-960', NULL);
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 36473109
Also try this: (n_count instead of L_LINE_UI_OBJ_I.last)
dbms_output.put_line('Start check for code changes on Add line page');  
L_LINE_UI_OBJ_I  :=  LINE_UI_TAB_TYPE();

dbms_output.put_line('1');

L_LINE_UI_OBJ_I.extend();

n_count:=L_LINE_UI_OBJ_I.count;
dbms_output.put_line(n_count);

DBMS_OUTPUT.PUT_LINE('Start of extend');
L_LINE_UI_OBJ_I(n_count).SERIAL_NUMBER := null;
DBMS_OUTPUT.PUT_LINE('Serial number taken for extend');  - not able to get as output ..  l_line_ui_obj_i(l_line_ui_obj_i.LAST).product_number := null;
l_line_ui_obj_i(n_count).service_sku := 'CP-60';
L_LINE_UI_OBJ_I(n_count).QUOTE_HEADER_ID := null;
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 36473247
>> Is there SERIAL_NUMBER in object type ? Can it be NULL ?
yes it can be


>>You can try this for initilalize - there is no need for using EXTEND
>>l_line_ui_obj_i := line_ui_tab_type (NULL,NULL, 'CP-960', NULL);
cannot use this as the tab type has more number of arguments for which I am using extend...


Still getting the same error after changing it to n_count .... for the existing extend functionality

0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 36473438
My issue is solved now ..

The tabt ype was referring to another object type for which I have used as like below :

...
..
..

dbms_output.put_line('Start check for code changes on Add line page');    
L_LINE_UI_OBJ_I  := LINE_UI_TAB_TYPE();
DBMS_OUTPUT.PUT_LINE('Start object tab type');    
L_LINE_UI_OBJ_I.extend();
L_LINE_UI_OBJ_I(1):=LINE_UI_OBJ_TYPE.getobject;
L_LINE_UI_OBJ_I(1).proc_id :=24806192;
l_line_ui_obj_i(1).serial_number := null;
l_line_ui_obj_i(1).product_number := null;
..
...
...
0
 
LVL 16

Author Closing Comment

by:Swadhin Ray
ID: 36473448
Thanks for your help ...
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
oracle 11g 23 76
constraint check 2 40
ODBC in excel 2016 in Windows 10 via VBA 16 61
PL SQL Developer 7 32
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question