Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ORA-

Posted on 2011-09-02
21
Medium Priority
?
400 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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 2000 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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to recover a database from a user managed backup
Suggested Courses

916 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