Oracle variable problem in procedure

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

HelpdeskEUAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
flow01Connect With a Mentor Commented:
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
 
sunny25Commented:
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
 
HelpdeskEUAuthor Commented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
flow01Commented:

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
 
HelpdeskEUAuthor Commented:
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
 
flow01Commented:
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
 
HelpdeskEUAuthor Commented:
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
 
flow01Commented:
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
 
HelpdeskEUAuthor Commented:
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
 
flow01Commented:
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
 
HelpdeskEUAuthor Commented:
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
 
flow01Commented:
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
 
HelpdeskEUAuthor Commented:
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
 
flow01Commented:
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
 
HelpdeskEUAuthor Commented:
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
 
flow01Commented:
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
 
HelpdeskEUAuthor Commented:
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
 
flow01Commented:
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
 
HelpdeskEUAuthor Commented:
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
 
flow01Commented:
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
 
HelpdeskEUAuthor Commented:
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
 
flow01Commented:
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
 
HelpdeskEUAuthor Commented:
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
 
flow01Commented:
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
 
HelpdeskEUAuthor Commented:
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
 
HelpdeskEUAuthor Commented:
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
All Courses

From novice to tech pro — start learning today.