Home » SQL & PL/SQL » SQL & PL/SQL » error_message : ORA-06508: PL/SQL: could not find program unit being called (Oracle 10g)
error_message : ORA-06508: PL/SQL: could not find program unit being called [message #429077] Mon, 02 November 2009 01:08 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
I am calling a procedure named 'restore_tabledata' into another parent procedure named data_seg.

CREATE OR REPLACE PROCEDURE restore_tabledata
IS
   error_message   VARCHAR2 (250);
BEGIN
   DBMS_OUTPUT.put_line ('In Procedure restore_table_data');

  
   EXECUTE IMMEDIATE 'TRUNCATE TABLE smp_product_plan ';

     -- not able to run this insert part
   INSERT INTO smp_product_plan
      SELECT *
        FROM bck_smp_product_plan;
---------------------------------
   

   DBMS_OUTPUT.put_line ('Tables Restored successfully');
   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      error_message := SQLERRM;
      DBMS_OUTPUT.put_line ('error_message : ' || SQLERRM);

      INSERT INTO shalina_upload_error_log
                  (erp_upload_date, error_id, error_msg, error_log_date
                  )
           VALUES (SYSDATE, 30, error_message, SYSDATE
                  );
END restore_tabledata;
/


When I call this procedure, it gives the error :-
error_message : ORA-06508: PL/SQL: could not find program unit being called


If I comment the insert part of the procedure then it is called successfully.

I am not able to run the insert part from restore_tabledata procedure when I call it in data_seg.

Adding to this, I am also calling a procedure named 'BACKUP' in the parent procedure 'data_seg' where I am creating the table 'bck_smp_product_plan'.

EXECUTE IMMEDIATE 'CREATE TABLE bck_smp_good_return_note AS SELECT * FROM smp_good_return_note';


It seems that the calling procedure 'restore_tabledata' doesn't have permission to execure the insert operation, do I need to give any rights...
I tried giving rights as :
GRANT insert ON  shalina_qc.bck_smp_product_plan to shalina_qc



But its giving the same error :-
error_message : ORA-06508: PL/SQL: could not find program unit being called



Please help me on this...I am stuck since long time on this ...

Regards,
Mahi
Re: error_message : ORA-06508: PL/SQL: could not find program unit being called [message #429079 is a reply to message #429077] Mon, 02 November 2009 01:15 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Get rid of your exception handler, so you can actually SEE what is going on.
Now you lost your stacktrace, so you cannot even be sure it is this procedure that raises the error. (Might very well be a trigger on the table you are inserting into)

[Updated on: Mon, 02 November 2009 01:16]

Report message to a moderator

Re: error_message : ORA-06508: PL/SQL: could not find program unit being called [message #429083 is a reply to message #429079] Mon, 02 November 2009 01:23 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi Frank,
I tried this many times and found that when I comment the insert part of the table which I created using execute immediate in another procedure.....it works fine...only when I uncomment the insert part, it gives the error....

I also tried commenting the exception handler...I got the below error:-

ORA-04068: existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure "SHALINA_DUMMY.RESTORE_TABLE_DATA"
ORA-06508: PL/SQL: could not find program unit being called: "SHALINA_DUMMY.RESTORE_TABLE_DATA"
ORA-06512: at "SHALINA_DUMMY.SHALINA_DATA_SEGREGATION", line 605
ORA-06512: at line 5


Line 605 is where I am calling the procedure.

There are no triggers on any table.

I have given you only a part of the procedure....

Regards,
Mahi

[Updated on: Mon, 02 November 2009 01:24]

Report message to a moderator

Re: error_message : ORA-06508: PL/SQL: could not find program unit being called [message #429084 is a reply to message #429077] Mon, 02 November 2009 01:24 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, use SQL*Plus and copy and paste your session.

Regards
Michel
Re: error_message : ORA-06508: PL/SQL: could not find program unit being called [message #429085 is a reply to message #429084] Mon, 02 November 2009 01:27 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Error message at sqlPlus

declare
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure
"SHALINA_DUMMY.RESTORE_TABLE_DATA"
ORA-06508: PL/SQL: could not find program unit being called:
"SHALINA_DUMMY.RESTORE_TABLE_DATA"
ORA-06512: at "SHALINA_DUMMY.SHALINA_DATA_SEGREGATION", line 605
ORA-06512: at line 5


SQL> show user
USER is "SHALINA_DUMMY"


Did I misunderstood what you said?
Re: error_message : ORA-06508: PL/SQL: could not find program unit being called [message #429086 is a reply to message #429083] Mon, 02 November 2009 01:27 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Is your procedure valid?
Most probably not, because you try to select from a table you are creating at runtime (BAAAAD idea)

You should not create & drop your backup table, just the data.
Re: error_message : ORA-06508: PL/SQL: could not find program unit being called [message #429087 is a reply to message #429086] Mon, 02 November 2009 01:32 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
I am creating and dropping just the backup table because if in futue some datatype or columns changes.
I create a table similar to production table and take its backup in the bck_prodtable backup. And drop it next time before taking fresh backup.

And my procedure is valid. It runs if I just comment the insert part else it gives error. But its compiled.

Is it somewhere related to permission to insert on a table which is created on runtime....how can I fix this...can I give some permission after I create on runtime...?

[Updated on: Mon, 02 November 2009 01:34]

Report message to a moderator

Re: error_message : ORA-06508: PL/SQL: could not find program unit being called [message #429088 is a reply to message #429086] Mon, 02 November 2009 01:34 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
SQL> CREATE OR REPLACE PROCEDURE restore_tabledata
  2  IS
  3     error_message   VARCHAR2 (250);
  4  BEGIN
  5     DBMS_OUTPUT.put_line ('In Procedure restore_table_data');
  6
  7
  8     EXECUTE IMMEDIATE 'TRUNCATE TABLE emp1 ';
  9
 10       -- not able to run this insert part
 11     INSERT INTO emp1
 12        SELECT *
 13          FROM emp;
 14  ---------------------------------
 15
 16
 17     DBMS_OUTPUT.put_line ('Tables Restored successfully');
 18     COMMIT;
 19  EXCEPTION
 20     WHEN OTHERS
 21     THEN
 22        ROLLBACK;
 23        error_message := SQLERRM;
 24        DBMS_OUTPUT.put_line ('error_message : ' || SQLERRM);
 25
 26        INSERT INTO upload_error_log
 27                    (erp_upload_date, error_id, error_msg, error_log_date
 28                    )
 29             VALUES (SYSDATE, 30, error_message, SYSDATE
 30                    );
 31  END restore_tabledata;
 32  /

Procedure created.

SQL> select count(*) from emp;

  COUNT(*)
----------
        14

SQL> select count(*) from emp1;

  COUNT(*)
----------
        14

SQL> truncate table emp1;

Table truncated.

SQL> select count(*) from emp1;

  COUNT(*)
----------
         0

SQL> exec restore_tabledata;
In Procedure restore_table_data
Tables Restored successfully

PL/SQL procedure successfully completed.

SQL> select count(*) from emp1;

  COUNT(*)
----------
        14


SQL>create or replace procedure data_seg
  2  is
  3   error_message   VARCHAR2 (250);
  4  BEGIN
  5     DBMS_OUTPUT.put_line ('In Procedure restore_table_data');
  6  restore_tabledata();
  7  DBMS_OUTPUT.put_line ('Executed successfully');
  8  EXCEPTION
  9     WHEN OTHERS
 10     THEN
 11        ROLLBACK;
 12        error_message := SQLERRM;
 13        DBMS_OUTPUT.put_line ('error_message : ' || SQLERRM);
 14        INSERT INTO upload_error_log
 15                    (erp_upload_date, error_id, error_msg, error_log_date
 16                    )
 17             VALUES (SYSDATE, 30, error_message, SYSDATE
 18                    );
 19* END data_seg;
SQL> /

Procedure created.

SQL> truncate table emp1;

Table truncated.

SQL> select * from emp1;

no rows selected

SQL> exec data_seg;
In Procedure restore_table_data
In Procedure restore_table_data
Tables Restored successfully
Executed successfully

PL/SQL procedure successfully completed.

SQL> select * from emp1;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-DEC-80        800
        20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300
        30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7566 JONES      MANAGER         7839 02-APR-81       2975
        20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400
        30

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7782 CLARK      MANAGER         7839 09-JUN-81       2450
        10

      7788 SCOTT      ANALYST         7566 19-APR-87       3000
        20

      7839 KING       PRESIDENT            17-NOV-81       5000
        10


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0
        30

      7876 ADAMS      CLERK           7788 23-MAY-87       1100
        20

      7900 JAMES      CLERK           7698 03-DEC-81        950
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7902 FORD       ANALYST         7566 03-DEC-81       3000
        20

      7934 MILLER     CLERK           7782 23-JAN-82       1300
        10


14 rows selected.

SQL>
No such Error messages.

Sriram
Re: error_message : ORA-06508: PL/SQL: could not find program unit being called [message #429089 is a reply to message #429088] Mon, 02 November 2009 01:36 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
In your example, the table emp1 is not created on runtime.
Re: error_message : ORA-06508: PL/SQL: could not find program unit being called [message #429090 is a reply to message #429089] Mon, 02 November 2009 01:49 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
SQL> drop table emp1;

Table dropped.

SQL> create or replace procedure data_seg
  2  is
  3   error_message   VARCHAR2 (250);
  4  BEGIN
  5     DBMS_OUTPUT.put_line ('In Procedure restore_table_data');
  6     EXECUTE IMMEDIATE 'CREATE TABLE emp1 AS SELECT * FROM emp where 1=2';
  7  restore_tabledata;
  8  DBMS_OUTPUT.put_line ('Executed successfully');
  9  EXCEPTION
 10     WHEN OTHERS
 11     THEN
 12        ROLLBACK;
 13        error_message := SQLERRM;
 14        DBMS_OUTPUT.put_line ('error_message : ' || SQLERRM);
 15
 16        INSERT INTO upload_error_log
 17                    (erp_upload_date, error_id, error_msg, error_log_date
 18                    )
 19             VALUES (SYSDATE, 30, error_message, SYSDATE
 20                    );
 21  END data_seg;
 22  /

Warning: Procedure created with compilation errors.

SQL> sho err
Errors for PROCEDURE DATA_SEG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
7/1      PL/SQL: Statement ignored
7/1      PLS-00905: object SCOTT.RESTORE_TABLEDATA is invalid
SQL> desc restore_tabledata;
ERROR:
ORA-24372: invalid object for describe
Re: error_message : ORA-06508: PL/SQL: could not find program unit being called [message #429091 is a reply to message #429087] Mon, 02 November 2009 01:51 Go to previous messageGo to next message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
virmahi wrote on Mon, 02 November 2009 08:32
I am creating and dropping just the backup table because if in futue some datatype or columns changes.
I create a table similar to production table and take its backup in the bck_prodtable backup. And drop it next time before taking fresh backup.

And my procedure is valid. It runs if I just comment the insert part else it gives error. But its compiled.

It is valid until any of the tables involved is dropped. So when you drop BCK_ table, the procedure becomes invalid. That's why the exception is raised - it may be valid when called if BCK_ table was created again, but the only way Oracle is able to determine it is to re-compile it.
virmahi wrote on Mon, 02 November 2009 08:32
Is it somewhere related to permission to insert on a table which is created on runtime....how can I fix this...can I give some permission after I create on runtime...?

No permission. You have to run all SQL accessing backup table dynamically (EXECUTE IMMEDIATE, DBMS_SQL), or, better do not DROP backup table. You may truncate its content (even faster than dropping the table, without need to re-create it), as demonstrated by ramoradba.
Re: error_message : ORA-06508: PL/SQL: could not find program unit being called [message #429092 is a reply to message #429085] Mon, 02 November 2009 01:54 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Did I misunderstood what you said?

I meant the WHOLE session.

Regards
Michel
Re: error_message : ORA-06508: PL/SQL: could not find program unit being called [message #429100 is a reply to message #429092] Mon, 02 November 2009 02:36 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
I am using Oracle 9i and had to take backup of 6 tables in bck_tablenames. So I thought dropping and re-creating would be better idea. Now I am only truncating and inserting data into the backup tables.

The problem is only when the columns or datatypes of any table changes then same would be required to bedone on backup tables as well....so maintenace would be an issue for client.

And I have to do this in backend only thru a procedure. I have put the main procedure in a schedular which would run every night...so if any error occur then the data need to be restored in tables from backup tables.

Re: error_message : ORA-06508: PL/SQL: could not find program unit being called [message #429107 is a reply to message #429100] Mon, 02 November 2009 03:00 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
virmahi wrote on Mon, 02 November 2009 09:36
I am using Oracle 9i and had to take backup of 6 tables in bck_tablenames. So I thought dropping and re-creating would be better idea. Now I am only truncating and inserting data into the backup tables.

The problem is only when the columns or datatypes of any table changes then same would be required to bedone on backup tables as well....so maintenace would be an issue for client.

And I have to do this in backend only thru a procedure. I have put the main procedure in a schedular which would run every night...so if any error occur then the data need to be restored in tables from backup tables.


How would a change in datatype on the backup tables differ from one in the main tables? This is no more a maintenance issue for the client than a change in your mains.
Consider the backup tables as a part of your application.
Re: error_message : ORA-06508: PL/SQL: could not find program unit being called [message #429108 is a reply to message #429100] Mon, 02 November 2009 03:01 Go to previous messageGo to next message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
virmahi wrote on Mon, 02 November 2009 09:36
The problem is only when the columns or datatypes of any table changes then same would be required to bedone on backup tables as well....so maintenace would be an issue for client.

Any change in any table will be issue not only for these scripts, but for entire application. Is it so hard to apply the same changes to "backup" tables as well?
virmahi wrote on Mon, 02 November 2009 09:36
And I have to do this in backend only thru a procedure. I have put the main procedure in a schedular which would run every night...so if any error occur then the data need to be restored in tables from backup tables.

Good luck when "restoring" changed tables from "backup" ones with old structure.
Re: error_message : ORA-06508: PL/SQL: could not find program unit being called [message #429183 is a reply to message #429077] Mon, 02 November 2009 08:05 Go to previous message
joy_division
Messages: 4615
Registered: February 2005
Location: East Coast USA
Senior Member
virmahi wrote on Mon, 02 November 2009 02:08

GRANT insert ON  shalina_qc.bck_smp_product_plan to shalina_qc



What is the purpose of this? A schema does not need grants on it's own objects.
Previous Topic: view to function
Next Topic: How to know the dependencies
Goto Forum:
  


Current Time: Sun Sep 25 14:31:49 CDT 2016

Total time taken to generate the page: 0.13342 seconds