Solved

Oracle variable problem in procedure

Posted on 2010-09-23
26
1,242 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 20

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
 

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 20

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 20

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 20

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 20

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 20

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

 

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 20

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 20

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 20

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 20

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 20

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 20

Accepted Solution

by:
flow01 earned 500 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

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

Suggested Solutions

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

757 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

22 Experts available now in Live!

Get 1:1 Help Now