[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3151
  • Last Modified:

PL/SQL error ....

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
anil_26
Asked:
anil_26
  • 4
  • 3
  • 2
  • +3
1 Solution
 
vanmeerendonkCommented:
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
 
GrinbergCommented:
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
 
vanmeerendonkCommented:
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
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.

 
GrinbergCommented:
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
 
vanmeerendonkCommented:
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
 
GrinbergCommented:
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
 
RMZCommented:
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
 
GrinbergCommented:
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
 
ser6398Commented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
ser6398Commented:
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
 
anil_26Author Commented:
Thanks for your comments.. I will try them and get back to you.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 4
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now