Solved

ORA-

Posted on 2011-09-02
21
393 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Create table from select - oracle 6 68
pivot rows to columns 1 44
Checking for column width 8 39
Need to Understand Resolution to Oracle Error ORA-00600 2 42
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

749 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