Link to home
Start Free TrialLog in
Avatar of Swadhin Ray
Swadhin RayFlag for United States of America

asked on

ORA-

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

Avatar of Swadhin Ray
Swadhin Ray
Flag of United States of America image

ASKER

Need to comment : l_add_target_obj_o target_obj_type;

As we are not using this in our block.
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 ();
...
Any solution for passing the values ? on my code?
can you give me the example for how to pass the values based on my code?
What values ought to be passed ?
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.
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...
Is there any error or simply the values are not passed ?
The error is same as I mentioned earlier ... 6350
ASKER CERTIFIED SOLUTION
Avatar of Helena Marková
Helena Marková
Flag of Slovakia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

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 ?
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 ?
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'
...
Modified accordingly and will checking again..

Will update you soon..
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
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);
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;
>> 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

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;
..
...
...
Thanks for your help ...