Solved

ORA-

Posted on 2011-09-02
21
390 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
Comment Utility
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á
Comment Utility
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
Comment Utility
Any solution for passing the values ? on my code?
0
 
LVL 16

Author Comment

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

Expert Comment

by:Helena Marková
Comment Utility
What values ought to be passed ?
0
 
LVL 22

Expert Comment

by:Helena Marková
Comment Utility
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
Comment Utility
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á
Comment Utility
Is there any error or simply the values are not passed ?
0
 
LVL 16

Author Comment

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

Accepted Solution

by:
Helena Marková earned 500 total points
Comment Utility
Please, try to create object type
line_ui_tab_type
in schema.
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
Comment Utility
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á
Comment Utility
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
Comment Utility
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á
Comment Utility
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
Comment Utility
Modified accordingly and will checking again..

Will update you soon..
0
 
LVL 16

Author Comment

by:Swadhin Ray
Comment Utility
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á
Comment Utility
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á
Comment Utility
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
Comment Utility
>> 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
Comment Utility
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
Comment Utility
Thanks for your help ...
0

Featured Post

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.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now