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

x
?
Solved

Oracle variable problem in procedure

Posted on 2010-09-23
26
Medium Priority
?
1,327 Views
Last Modified: 2012-05-10
Hi.

I am trying to update a history log table whenever a certifcate is printed from within our application.  Due to mutating table issues, I have had to create a 2 stage process.  Firstly, I have created a trigger on the table which writes a result_key (report id) to a temporary table.  At the end of the trigger I call a procedure.

This is all very new to me and I've kind of been 'thrown in at the deep end' with this.

My problem that I have is on the "cursor get_dets" section, if I hard code a result_key instead of using the variable, then the procedure works ok (where result_key = '123456').  Leaving the result key as a variable (where result_key = result_key_), then it doesn't find the record.

I have attached the 2 bits of code, which hopefully somebody will be able to help me with.

Thanks in advance,
Mandy


Trigger to add result key to temporary table:

CREATE OR REPLACE TRIGGER IFSAPP.CIL_RESULT_KEY
after INSERT ON IFSAPP.ARCHIVE_TAB FOR EACH ROW
DECLARE
result_key_     number(20);
      
begin
-- If a new certificate is printed, enter the report id into a temporary table
if :new.report_id = 'C_ORDER_CERTIFICATE_REP' then
        result_key_ := :new.result_key;  
    dbms_output.put_line('**inserting into table** '||result_key_);
    insert into  cil_cert_result_key
        (result_key,
        print_date,
        user_id)
    values
        (:new.result_key,
        sysdate,
        FND_SESSION_API.GET_FND_USER);
        dbms_output.put_line('+++++++Calling print cert procedure++++++++++ '||result_key_);
        ifsapp.cil_print_cert(result_key_); --Call procedure for updating the history table
end if;       
end;
/


Procedure:

CREATE OR REPLACE procedure IFSAPP.CIL_PRINT_CERT(result_key__ in  number) is
begin
DECLARE

objid_ varchar2(2000);
objversion_ varchar2(2000);
attr_ varchar2(2000);
info_ varchar2(2000);
order_no_ varchar2(20);
line_no_ varchar2(20);
rel_no_ varchar2(20);
certificate_no_ varchar2(20);
stopit  exception;
gotit  boolean;
gotkey  boolean;
result_key_  number(20) ;


cursor get_key is   --find the report id number from the temporary table
    select result_key from  ifsapp.cil_cert_result_key
    where trunc(result_key) = trunc(result_key__);
    
cursor get_dets is  --find the order details from the archive parameter table
    select order_no, line_no, rel_no, certificate_no from (
    select result_key,
        max(decode(parameter_name,'ORDER_NO',parameter_value)) order_no,
        sum(decode(parameter_name,'LINE_NO',parameter_value)) line_no,
        sum(decode(parameter_name,'REL_NO',parameter_value)) rel_no,
        max(decode(parameter_name,'CERTIFICATE_NO',parameter_value)) certificate_no
    from archive_parameter 
    where result_key = result_key_
    group by result_key
    );  

cursor get_rows     --find the certficate, based on the order details from the archive parameter table
--(order_no_ in varchar2, line_no_ in varchar2, rel_no_ in varchar2,             certificate_no_ in varchar2) 
is 
        select * from IFSAPP.C_ORDER_CERTIFICATE
        where order_no = order_no_
        and line_no = line_no_
        and rel_no = rel_no_
        and certificate_no = certificate_no_;

    begin
    dbms_output.put_line('Result_key__ = '||result_key__);
    dbms_output.put_line('Result_key_ = '||result_key_);
        open get_key;   --get report id
        fetch get_key into result_key_;
        dbms_output.put_line('fetch Result_key__ = '||result_key__);
        dbms_output.put_line('fetch Result_key_ = '||result_key_);
        gotkey := get_key%found;
        close get_key;
        
        open get_dets;  --get order details
        fetch get_dets into order_no_,line_no_,rel_no_,certificate_no_;
        dbms_output.put_line('GET_DETS: Order_no: '||order_no_||'result_key: '||result_key_);
        gotit := get_dets%found;
        close get_dets; 
            if not (gotit) then
                dbms_output.put_line( 'Unable to find record ');
           end if;

        dbms_output.put_line('fetch get_dets Result_key__ = '||result_key__);
        dbms_output.put_line('fetch get_Dets Result_key_ = '||result_key_);
            for row_ in get_rows loop
            --(order_no_,line_no_,rel_no_,certificate_no_) loop
            dbms_output.put_line('**get_rows order_no**'|| row_.certificate_no);
            info_ := null;
            client_sys.clear_attr(attr_);
            client_sys.add_to_attr('ORDER_NO',row_.ORDER_NO,attr_);
            CLIENT_SYS.ADD_TO_ATTR('LINE_NO',row_.LINE_NO,attr_);
            client_sys.add_to_attr('REL_NO',row_.REL_NO,attr_);
            client_sys.add_to_attr('LINE_ITEM_NO',row_.LINE_ITEM_NO,attr_);
            client_sys.add_to_attr('CERTIFICATE_NO',row_.CERTIFICATE_NO,attr_);
            client_sys.add_to_attr('ROWSTATE',row_.STATE,attr_);
            client_sys.add_to_attr('DATE_ENTERED',SYSDATE,attr_);
            client_sys.add_to_attr('USERID',FND_SESSION_API.GET_FND_USER,attr_);
            client_sys.add_to_attr('MESSAGE_TEXT','Printed',attr_);
            ifsapp.C_ORDER_CERTIFICATE_HIST_API.new__(info_,objid_,objversion_,attr_,'DO'); --add a new record to the history log table recording when certificate is printed.
            end loop;
    end;
end;
/

Open in new window

0
Comment
Question by:HelpdeskEU
  • 13
  • 12
26 Comments
 
LVL 1

Expert Comment

by:sunny25
ID: 33743746
Change the following code
At line no:-52 cursor get_dets(result number),
At line no:-60 where result_key = result  and
At line no:-83 open get_dets(result_key_),
Made it a parameterized cursor and then called it using open get_dets(result_key_).
It should work. Try...
0
 

Author Comment

by:HelpdeskEU
ID: 33743872
Hi, and thanks for your response.  

Unfortunately, I am still getting the same problem where the order details are not being retrieved from the get_dets cursor.

Thanks
Mandy
0
 
LVL 21

Expert Comment

by:flow01
ID: 33745461

for what reason do you use trunc
    select result_key from  ifsapp.cil_cert_result_key
    where trunc(result_key) = trunc(result_key__);
in the first cursor
and not in the second cursor ?

 from archive_parameter
    where result_key = result_key_

does
dbms_output.put_line('GET_DETS: Order_no: '||order_no_||'result_key: '||result_key_);
still show your key ?

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!

 

Author Comment

by:HelpdeskEU
ID: 33745616
I decided to trunc it just incase there were any extra characters on the end of the result key, and I just haven't removed it yet - it made no difference.

Yes, the result key is displayed in the dbms_output output.
0
 
LVL 21

Expert Comment

by:flow01
ID: 33746647
Extra characters ? You defined result_key as number(20)

Has result_key in archive_parameter the same definition
what happens if you change

where result_key = result_key_
into
where result_key = to_char(result_key_)
or
where to_char(result_key) = to_char(result_key_)
0
 

Author Comment

by:HelpdeskEU
ID: 33752040
Hi there,

Result_key in archive_parameter is also defined as a number field.

I have tried both of your suggestions, but still it will not find the order details.

Thanks aagin.
0
 
LVL 21

Expert Comment

by:flow01
ID: 33757517
weird ! another attempt

add
v_rsl  number(20) ;

--extent the select  with input variable  result_key_
cursor get_dets is  --find the order details from the archive parameter table
    select result_key_,  order_no, .....

--change the where back to its fixed value to get a row
where result_key ='123456'

-- get the variable back form the cursor
fetch get_dets into v_rsl , order_no_,line_no_,rel_no_,certificate_no_;
-- show the result
 dbms_output.put_line('fetch v_rsl = '|| v_rsl);

-- do you get a row and what is the value of v_rsl ?
0
 

Author Comment

by:HelpdeskEU
ID: 33759772
Hi there.  Row was retrieved successfully.  This is the output from the changes:

      <no timestamp>      fetch v_rsl = 2849645
      <no timestamp>      GET_DETS: Order_no: 51600012result_key: 2849645

Thanks
0
 
LVL 21

Expert Comment

by:flow01
ID: 33761064
Next 4 changes  (all changes to the last one used)

1) what is the result if you select  result_key instead of result_key_ (should be 123456)

2) what happens if you change the where to a numeric value ?
     where result_key = 123456

3) what happens if you use result_key_ hardcoded
    where result_key = '2849645'

4) what happens if you change the where back to the variable
    where result_key = result_key_
   
Is it possible to execute the query outside this scope ?
    select result_key, count(*)
    from archive_parameter
    where result_key = '123456'
    group by result_key

    select result_key,  count(*)
    from archive_parameter
    where result_key = 123456
    group by result_key

    select result_key,  count(*)
    from archive_parameter
    where result_key = '2849645'
    group by result_key

    select result_key,  count(*)
    from archive_parameter
    where result_key = 2849645
    group by result_key
0
 

Author Comment

by:HelpdeskEU
ID: 33763392
1 = record found
2 = record found
3 = record found
4 = no order details found

By executing either of the select statements with the correct result key (123456 was just an example):
RESULT_KEY,COUNT(*)
2849645,5

By executing the actual select statement with a hardcoded result key:
RESULT_KEY,ORDER_NO,LINE_NO,REL_NO,CERTIFICATE_NO
2849643,51600012,1,1,510
0
 
LVL 21

Expert Comment

by:flow01
ID: 33764813
since
cursor get_dets should always result in 1 record
try using a direct sql instead of the cursor
(i'm just trying alternative coding since it makes no sence,
  my previous questions aimed a finding a sort of parsing problem (false distinction between result_key, result_key_ and result_key__)  or
  maybe a corrupted index )

so

begin
select order_no, line_no, rel_no, certificate_no
 into order_no_,line_no_,rel_no_,certificate_no_
 from (
    select result_key,
        max(decode(parameter_name,'ORDER_NO',parameter_value)) order_no,
        sum(decode(parameter_name,'LINE_NO',parameter_value)) line_no,
        sum(decode(parameter_name,'REL_NO',parameter_value)) rel_no,
        max(decode(parameter_name,'CERTIFICATE_NO',parameter_value)) certificate_no
    from archive_parameter
    where result_key = result_key_
    group by result_key;
    dbms_output.put_line('GET_DETS: Order_no: '||order_no_||'result_key: '||result_key_);
exception when no_data_found then
                dbms_output.put_line( 'Unable to find record ');
end;

instead of
        open get_dets;  --get order details
        fetch get_dets into order_no_,line_no_,rel_no_,certificate_no_;
        dbms_output.put_line('GET_DETS: Order_no: '||order_no_||'result_key: '||result_key_);
        gotit := get_dets%found;
        close get_dets;
            if not (gotit) then
                dbms_output.put_line( 'Unable to find record ');
           end if;


0
 

Author Comment

by:HelpdeskEU
ID: 33767925
Hi.

Unfortunately, the order is still not being found using the variable.  If I hard code the result key, then the order is found ok.

Thanks, Mandy
0
 
LVL 21

Expert Comment

by:flow01
ID: 33771572
lets make the same query  dynamic  using a hardcoded key by substituting the pl/sql-block from the last post by the next  pl/sql-blok
please tell me if you are on oracle 10g : then we can avoid the escaping ''  and use the quote-statement
(i could check the syntax except for the sentence with the execute immediate)

declare
  v_statement varchar2(4000);
begin
  v_statement :=
'select order_no, line_no, rel_no, certificate_no                                              ' ||
' from (                                                                                       ' ||
'    select result_key,                                                                        ' ||
'        max(decode(parameter_name,''ORDER_NO'',parameter_value)) order_no,                      ' ||
'        sum(decode(parameter_name,''LINE_NO'',parameter_value)) line_no,                        ' ||
'        sum(decode(parameter_name,''REL_NO'',parameter_value)) rel_no,                          ' ||
'        max(decode(parameter_name,''CERTIFICATE_NO'',parameter_value)) certificate_no           ' ||
'    from archive_parameter                                                                    ' ||
'    where result_key = ''' || result_key_  || '''                                             ' ||
'    group by result_key;                                                                      ' ||
'    dbms_output.put_line(''GET_DETS: Order_no: ''||order_no_||''result_key: ''||result_key_);     ' ||
'exception when no_data_found then                                                             ' ||
'                dbms_output.put_line( ''Unable to find record'');                              ' ||
'end;                                                                                          ';
-- check resulting hard_coded where
   dbms_output.put_line(substr(v_statement, instr(v_statement,'where'), 200));
-- and get the results
   execute immediate v_statement into order_no, line_no, rel_no,  certificate_no;
end;

what does the resulting where look like
and
what is the result
0
 

Author Comment

by:HelpdeskEU
ID: 33772569
On printing the certificate, I get the following error:

Current User:            ******
Current Database:      ******
Level:                  Informational
Code:                  ORA-00911
Location:            frmInfoService
Text:                  invalid character
ORA-06512: at "CIL_PRINT_CERT", line 102
ORA-06512: at "CIL_RESULT_KEY", line 20
ORA-04088: error during execution of trigger 'CIL_RESULT_KEY'
ORA-06512: at line 12
Error Text:
BEGIN  
Archive_API.New_Client_Report(  
:i_hWndFrame.cFrmInfoService.__nPrintJobId,  
:i_hWndFrame.cFrmInfoService.__lsAttr[0],  
:i_hWndFrame.cFrmInfoService.__lsAttr[1],  
:i_hWndFrame.cFrmInfoService.__lsAttr[2],  
:i_hWndFrame.cFrmInfoService.__lsAttr[3] );
commit;  
EXCEPTION  
WHEN OTHERS THEN  
rollback;  
raise;  
END;

After clearing the error message, the following message is the result of the where statement:

      <no timestamp>      where result_key = '2849675'                                                 group by result_key;                                                                          dbms_output.put_line('GET_DET

Maybe I have missed something?  We are using Oracle 10g.  

Thanks for your patience with this :-)
0
 
LVL 21

Expert Comment

by:flow01
ID: 33772940
The result of the where statement is what i expected (I showed only part of it because of the 255 bytes limit of dbms_output.put_line).
Is 2849675 the correct result_key value ?
And does printing the certificate mean that you are getting now the desired result ?

What error message do you get when you execute
select result_key,  count(*)
    from archive_parameter
    where result_key = result_key_
    group by result_key

If you don't get an error message there is either a column result_key_  in the archive_parameter table or there exists a function result_key_ .




0
 

Author Comment

by:HelpdeskEU
ID: 33773793
No, when I said about printing the certificate, it is the printing process that then calls the trigger on the report archive table.  If an entry is placed in the history log for the certificate then the process has been successful, which unfortunately isn't the case.

As there was an error during the trigger, the result key hasn't actually been written to either the report archive table (report_archive) or the temporary table (cil_cert_result_key) that I am storing the result key in, but it is the one that is used in all the variables that are output in the put_line statements.

The error message that I get is :

ORA-00904: "RESULT_KEY_": invalid identifier

For info, this is the structure of the table:

SQL> desc archive_parameter;
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------
 RESULT_KEY                                NOT NULL NUMBER
 PARAMETER_NAME                            NOT NULL VARCHAR2(30)
 PARAMETER_VALUE                                    VARCHAR2(2000)
 OBJID                                              ROWID
 OBJVERSION                                         VARCHAR2(14)


0
 
LVL 21

Expert Comment

by:flow01
ID: 33774273
I took a too great part into the v_statement : it should be just the select statement without a closing ;

declare
  v_statement varchar2(4000);
begin
  v_statement :=
'select order_no, line_no, rel_no, certificate_no                                              ' ||
' from (                                                                                       ' ||
'    select result_key,                                                                        ' ||
'        max(decode(parameter_name,''ORDER_NO'',parameter_value)) order_no,                      ' ||
'        sum(decode(parameter_name,''LINE_NO'',parameter_value)) line_no,                        ' ||
'        sum(decode(parameter_name,''REL_NO'',parameter_value)) rel_no,                          ' ||
'        max(decode(parameter_name,''CERTIFICATE_NO'',parameter_value)) certificate_no           ' ||
'    from archive_parameter                                                                    ' ||
'    where result_key = ''' || result_key_  || '''                                             ' ||
'    group by result_key                                                                      ';
-- check resulting hard_coded where
   dbms_output.put_line(substr(v_statement, instr(v_statement,'where'), 200));
-- and get the results
   execute immediate v_statement into order_no, line_no, rel_no,  certificate_no;
  dbms_output.put_line('GET_DETS: Order_no: '||order_no_||'result_key: '||result_key_);  
exception when no_data_found then                                                            
                dbms_output.put_line( 'Unable to find record');                            
end;


do you now get the GET_DETS output ?
0
 

Author Comment

by:HelpdeskEU
ID: 33776686
The following is now produced:

      <no timestamp>      where result_key = '2849676'                                                 group by result_key  

I commented out the '   dbms_output.put_line(substr(v_statement, instr(v_statement,'where'), 200));
' line but still no output from the GET_DETS statement.                                                
0
 
LVL 21

Expert Comment

by:flow01
ID: 33777265
change uncommented to dbms_output
to  
 dbms_output.put_line(substr(v_statement, 1,200));
 dbms_output.put_line(substr(v_statement, 201,200));
 dbms_output.put_line(substr(v_statement, 401,200));
 dbms_output.put_line(substr(v_statement, 601,200));
--- to able to control the statement

after the
when no_data_found then                                                            
                dbms_output.put_line( 'Unable to find record');  
add
when others then
 dbms_output.put_line(substr(sqlerrm, 1,200));
 dbms_output.put_line(substr(sqlerrm, 201,200));
 dbms_output.put_line(substr(sqlerrm, 401,200));
 dbms_output.put_line(substr(sqlerrm, 601,200));
 raise;        
-- to get more information about the syntax error

and check if the v_statement is a valid sql-statement  and/or get more error information

                 
0
 

Author Comment

by:HelpdeskEU
ID: 33777432
Hi there

I made the changes and corrected the v_statement as it was missing a right-parenthesis at the end of the group by statement, so the statement now runs correctly and retrieves the correct data according to the result_key.

However, it still drops out to the no_data_found exception and the variable order_no_ is not set.
0
 
LVL 21

Expert Comment

by:flow01
ID: 33779099
quote 'so the statement now runs correctly and retrieves the correct data according to the result_key'
how did you verify that ?
(did you run the resulting query and was this with the same user executing the procedure and
is the owner of the procedure IFSAPP.CIL_PRINT_CERT
the same as the user executing it
0
 

Author Comment

by:HelpdeskEU
ID: 33779909
Yes, I ran the resulting query which produced one record.  The user was IFSAPP, which is the same user executing the procedure and the owner of the procedure.
0
 
LVL 21

Expert Comment

by:flow01
ID: 33781784
Results suggest the archive_parameter table really doesn't contain records when the trigger executes and does contain records after execution.
Is the inserting of archive_parameters part of the same transaction where  ARCHIVE_TAB is inserted  or are the archive_parameters already there ?
0
 

Author Comment

by:HelpdeskEU
ID: 33782810
We are using an application called IFS as our ERP system, and it is possible to create our own customised triggers and packages so, to be honest, I am not completely sure on the stages of the IFS update of records.

However, looking at the debug window, it might appear that you are correct and the archive_parameters aren't actually wirtten to the table until after the procedure runs, see below log from the debug window (the <no timestamp> entries are the dbms_output.put_line statements from my custom procedure):

      <no timestamp>      Unable to find record
      <no timestamp>      fetch get_dets Result_key__ = 2849684
      <no timestamp>      fetch get_Dets Order_no_ =
Server Method      00.32 (+0.04)      ARCHIVE_PARAMETER_API.New_Entry_Parameter__
Server Method      00.33 (+0.01)      ARCHIVE_DISTRIBUTION_API.New_Entry_Distribution__

Is there going to be anyway around this, that you know of?
0
 
LVL 21

Accepted Solution

by:
flow01 earned 2000 total points
ID: 33783185
either find out what record is inserted last in the transaction or
consider using a background jobs
    for example starting a non-repeating job for each  cil_cert_result_key
    or extend the cil_cert_result_key (with a "done"-column) and start a repeating job that creates certificates for each cil_cert_result_key that  isn't "done".
    Depending on the repeat interval there wil be a delay in the certificates.
    In both ways you wil have to change cil_cert_result_key from temporary table to persistent.
    But you can also keep track of "done" also by deleting the corresponding record.

We use a different background job -system so I have no experience with dbms_scheduler
0
 

Author Comment

by:HelpdeskEU
ID: 33783267
I did wonder whether I would have to have it as a background job, but really wanted to persist with the trigger - a lot more dynamic and the user is able to see instantly the record appearing in the history log.  Thank you so much for your help and perserverance with this.  

Thanks
Mandy
0

Featured Post

Hire Technology Freelancers with Gigs

Work with 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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

773 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