Solved

PL/SQL error ....

Posted on 2001-09-05
12
3,128 Views
Last Modified: 2012-05-04
I am getting following errors in a procedure :-

SQL> show error
Errors for PROCEDURE TEST1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PLS-00801: internal error [1407]
0/0      PLS-00801: internal error [1407]
12/9     PL/SQL: SQL Statement ignored
20/9     PL/SQL: SQL Statement ignored
SQL>

The code is as following :-
create or replace procedure test1 as
dtac_val VARCHAR(20);
counter NUMBER;
d_tr_val VARCHAR(10);
d_tmc_val VARCHAR(20);
d_tb1_val VARCHAR(10);
langcur_val NUMBER;
prof_d_inact NUMBER;
curr_date DATE;
rows NUMBER(5);

CURSOR dtac is
        select d_tac,d_tb1,d_tr,d_tmc,language from account;

BEGIN
        select count(*) into rows from account;

        if dtac%ISOPEN then
        close dtac;
        end if;
        open dtac;

for counter in 1..rows loop
        fetch dtac into dtac_val,d_tb1_val,d_tr_val,d_tmc_val,langcur_val;
        if (dtac_val != null) then
                update account set tac_f=1;
        end if;

        if (dtac_val is not null) then
                update account set tb1_f=1;
        end if;

        select d_inact into prof_d_inact from accountp;
        update account set d_tr = dtac_val + to_date(prof_d_inact);


        select sysdate into curr_date from sys.dual;
        if curr_date > to_date(d_tr_val) then
                update account set a_cursta = 'DEACT';
        elsif
                (curr_date > to_date(d_tmc_val)) and (curr_date <= to_date(
d_tr_val))
                then
                update account set a_cursta = 'INACT';
        elsif
                (dtac_val is NULL) then
                update account set a_cursta = 'VALIDE';
        end if;

update account set langcur = langcur_val + 1;

end loop;
end;


Thanks
0
Comment
Question by:anil_26
  • 4
  • 3
  • 2
  • +3
12 Comments
 
LVL 2

Expert Comment

by:vanmeerendonk
ID: 6456855
hard to tell without a describe of the table;
the internal error is nothing to be concerned, i think.

select d_inact into prof_d_inact from accountp;

don't you mean account ??
0
 

Expert Comment

by:Grinberg
ID: 6456918
Your proc has been translated without any errors against my Oracle 8.1.6. May be, this error depends on Oracle version.

One of possible reasons - using reserved word ROWS for variable name. ROWS is reserved for SQL, not for PL/SQL, but try to change it. May be, it helps.
0
 
LVL 2

Expert Comment

by:vanmeerendonk
ID: 6456959
another idea:
ORA-1407 => cannot update to null.
is there a not-null column that you are updating with a non-initalized variable? Try initializing it in the declaration.
0
 

Expert Comment

by:Grinberg
ID: 6456981
Sorry, I believe that [1407] in internal error doesn't refer to ORA-1407. Last one is runtime error and can't arrive during translation.
0
 
LVL 2

Expert Comment

by:vanmeerendonk
ID: 6456993
yep, i know, but from 8.0 "suddenly" internal errors appear when compiling code. I never had them in 7.3.
But I aggree, it was a long shot, but did it work???
0
 

Expert Comment

by:Grinberg
ID: 6457013
Sorry, I believe that [1407] in internal error doesn't refer to ORA-1407. Last one is runtime error and can't arrive during translation.
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.

 
LVL 2

Expert Comment

by:RMZ
ID: 6457047
hi
possible error may be one of
1-pls desc account table and chk for fields d_tac,d_tb1,d_tr,d_tmc,language
2- select d_inact into prof_d_inact from accountp;
may return many record
3-rows may be reserved word pls change it to be rows_v
---rmz----
0
 

Expert Comment

by:Grinberg
ID: 6457052
Sorry, I believe that [1407] in internal error doesn't refer to ORA-1407. Last one is runtime error and can't arrive during translation.
0
 
LVL 5

Expert Comment

by:ser6398
ID: 6457091
I'm not sure what is causing your error, but I want to make sure you understand something about your code:

update account set tac_f=1;

Will update EVERY row in the account table (not just the current one).  If you only want to update a single row you need to add a WHERE clause to your update statement.  This goes for every update statement your have.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 6457114
This isn't a solution, but I have some performance suggestions/questions.

First of all, it appears that you have omitted the "where" clause from the update statements you posted here.  I hope you code has a "where" clause for each update!

Doing at least two, and possibly up to four separate updates of every record is a very inefficient way to get the job done.  It is better to use PL\SQL variables to hold the values you want assigned for each column, then do just one update at the end, setting all of the columns at the same time.

The "select sysdate ... from dual" adds an unnecessary performance penalty.  If you need to know the exact second, then select it either in the main query, or in the "select d_inact..." statement.  Otherwise, it may be sufficient just to get the time once at the start of the job, and use that value for each record.

The implicit cursors like: "select d_inact into prof_d_inact from accountp;" are easy to code, but they come with a performance penalty and they can raise exceptions (too_many_rows, or: no_data_found) that you may need to handle.  Explicit cursors take just a little bit more coding time, but execute faster and avoid the possible exceptions.  Your "dtac" cursor is a good example of an explicit cursor.

0
 
LVL 5

Accepted Solution

by:
ser6398 earned 50 total points
ID: 6457252
You may want to modify your code to look more like the example I have below (I don't know your exact table structure, so I had to leave some parts out, but it should be enough to give you the general idea).

create or replace procedure test1 as
dtac_val VARCHAR(20);
counter NUMBER;
d_tr_val VARCHAR(10);
d_tmc_val VARCHAR(20);
d_tb1_val VARCHAR(10);
langcur_val NUMBER;
prof_d_inact NUMBER;
curr_date DATE;
rows NUMBER(5);

CURSOR c_dtac is
       select d_tac,d_tb1,d_tr,d_tmc,language from account;

CURSOR c_get_inact is
       select d_inact from accountp WHERE ????;

r_get_inact    c_get_inact%ROWTYPE;

BEGIN
       

FOR r_dtac IN c_dtac
LOOP

       OPEN c_get_inact;
       FETCH c_get_inact INTO r_get_inact;
       IF (c_get_inact%FOUND)
       THEN
         prof_d_inact := r_get_inact.d_inact;
       ELSE
         prof_d_inact := NULL;
       END IF;
       CLOSE c_get_inact;

   
       if (r_dtac.dtac IS NOT null)
       then
               update account
                 set tac_f=1,
                     tb1_f=1,
                     d_tr = r_dtac.dtac + to_date(prof_d_inact),
                     langcur = langcur + 1
                 WHERE CURRENT OF c_dtac;
       else
              update account
                 set langcur = langcur + 1
                 WHERE CURRENT OF c_dtac;

       end if;
                   


       select sysdate into curr_date from sys.dual;
       if curr_date > to_date(r_dtac.d_tr) then
               update account
                   set a_cursta = 'DEACT'
                   WHERE CURRENT of c_dtac;
       elsif
               (curr_date > to_date(r_dtac.d_tmc)) and (curr_date <= to_date(
r_dtac.d_tr))
               then
               update account
                 set a_cursta = 'INACT'
                 WHERE CURRENT of c_dtac;
       elsif
               (r_dtac.dtac is NULL) then
               update account
                 set a_cursta = 'VALIDE'
                 WHERE CURRENT of c_dtac;
       end if;



end loop;
end;
0
 

Author Comment

by:anil_26
ID: 6469581
Thanks for your comments.. I will try them and get back to you.
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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
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.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
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

708 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

15 Experts available now in Live!

Get 1:1 Help Now