SQL & PL/SQL :: ALTER TABLE Causing Procedure To Go INVALID?
Aug 3, 2010
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"
I altered existing table EVENT_SUB - added 3 columns. After that, I noticed all the procedures which had mention of this table name went in INVALID status, even if its simple SELECT, ALTER OR INSERT as shown below..
SELECT * FROM EVENT_SUB
OR
INSERT INTO EVENT_SUB...
OR
ALTER TABLE EVENT_SUB
WHERE....
So I had to recompile all the procedures associated with it. Is there any other ways to achieve this, like a line of code to add in the procedure itself, right after this DDL statements.
Sometimes i use this:
select object_name, object_type from all_objects where owner='TOYCOM' and status='INVALID'
Then, I would simply recompile the invalid objects.
For indexes, i do...
alter index <name> rebuild;
BTW, I did try to preview message, and then click on Create Topic, it gave me error..again.
"A system error has occurred.
View 3 Replies
ADVERTISEMENT
May 12, 2011
I ran the following PL/SQL Code & I am getting the following Error:
Drop table MODIFIEDTABLE
/
Commit
/
create table MODIFIEDTABLE(TABNAMES varchar2(100))
/
DECLARE
[code].....
RESULT:
Table dropped.
Commit complete.
Table created.
DECLARE
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option
ORA-06512: at line 12
View 2 Replies
View Related
Aug 25, 2013
I got this error when try to connect to the database using discover administrator or user edition
ora - 02248 : invalid option for ALTER SESSION
i can connect to the database with sqlplus with no problem.
View 4 Replies
View Related
Dec 31, 2005
When I try to compile a procedure with this command:
alter sequence myschema.seqmessages increment by 100;
The error says "encountered symbol "ALTER" when expecting...
Is there another way to alter a sequence from a procedure? In this case, I am altering a sequence in another schema that has granted the alter and select privileges for the sequence.
View 7 Replies
View Related
Jun 19, 2013
What is the difference between alter session and alter system?
View 2 Replies
View Related
Feb 2, 2011
I have this stored procedure as part of package.
PROCEDURE XCOM_X060_UPDATEOHBFAILURE( in_CALL_ID IN NUMBER,
in_SPLY_REORD_NO IN CHAR,
in_OHB_QTY_CARTONS IN NUMBER,
in_OHB_QTY_UNITS IN NUMBER,
in_SPLY_TOT_OHB_QTY IN NUMBER,
in_OHB_INPUT_CTNS_MIN IN NUMBER,
in_OHB_INPUT_CTNS_MAX IN NUMBER,
in_UNITS_PER_CARTON IN NUMBER,
in_OHB_INPUT_UNIT_CONSTANT IN NUMBER,
in_TOTAL_CARTONS IN NUMBER,
out_ALLOW_OHB_INPUT_FLAG OUT CHAR,
out_ERR_CODE OUT NUMBER,
out_ERR_MESSAGE OUT VARCHAR2)
When the stored procedure is executed it is throwing following exception OTHERS EXCEPTION in XCOM_X060_UPDATEOHBFAILURE - SQL -1001 SQLERRM: ORA-01001: invalid cursor
Here is the execution script
DECLARE
IN_CALL_ID NUMBER;
IN_SPLY_REORD_NO VARCHAR2(32767);
IN_OHB_QTY_CARTONS NUMBER;
IN_OHB_QTY_UNITS NUMBER;
IN_SPLY_TOT_OHB_QTY NUMBER;
IN_OHB_INPUT_CTNS_MIN NUMBER;
[code]...
There is no cursor used in the procedure. It just inserts records in the table for given input values.
View 10 Replies
View Related
May 12, 2010
I want run a procedure from a easy sql select.I have write this code to run this procedure, but response with a oracle error:
select [procedure_name](:data1, :data2) as test from dual
the oracle error is this:
ORA-00904: "PROCEDURENAME": invalid identifier
View 12 Replies
View Related
Jul 26, 2011
if a user have alter table gant but could not alter .. what additional grant it need
SQL> alter table HRS_PERS_FIELDS_INC modify(PER0000252 NUMBER(19,3));
alter table HRS_PERS_FIELDS_INC modify(PER0000252 NUMBER(19,3))
*
ERROR at line 1:
ORA-00942: table or view does not exist
View 11 Replies
View Related
Jul 22, 2010
I created a DDL trigger which manipulates columns of a table after an "alter table" statement.
For us this was all the time
"alter table XXX add NEW_COL varchar2(20)"
or
"alter table XXX modify NEW_COL varchar2(20)".
Unfortunatly we have now a requirement which statements "alter table XXX drop NEW_COL varchar2(20)".
My trigger works fine - you see no problem directly after statement execution but the table is then in an invalid status.
The connection will be destroyed after you tried to see the data of this table.
Also the export (creating a dump) will not work.
The problem I see is that I the database will do a "modify" on a column which has internal the status "dropped".
How I can get the information what kind of alter table statement will be executed?
Or is there any chance to select only columns from USER_TAB_COLUMNS without the "dropped" flag?
create or replace trigger TRIGGER
after alter
on SCHEMA
declare
vCharSet NLS_DATABASE_PARAMETERS.VALUE%TYPE;
begin
-- Select all columns of a table
select COLUMN_NAME, DATA_TYPE, DATA_LENGTH
from USER_TAB_COLUMNS
where TABLE_NAME = vTABLE_NAME; ...
-- Loop for all columns from our select
...
vSTATEMENT := 'alter table ' || vTABLE_NAME || ' modify ' || VOBJECTREC.COLUMN_NAME || ' ' || VOBJECTREC.DATA_TYPE || '(' || vNEW_DATA_LENGTH || ')';
--For example:
-- alter table XXX modify NEW_COL varchar2(20)
execute immediate vSTATEMENT;
exception
...
end;
/
View 11 Replies
View Related
Feb 4, 2012
Here is the code I am using:
CREATE OR REPLACE
PROCEDURE CUBE_VIEW (VAR_DT IN VARCHAR2 DEFAULT '')
AUTHID CURRENT_USER
AS
START_DATE NUMBER;
END_DATE NUMBER;
VAR DATE;
BEGIN
IF VAR_DT IS NULL THEN
[code]....
The code complies successfully but when I am executing the code, here is the error I get. The code works successfully when I manually insert the values (as done in the commented out statement) but when use it from variable, it gives me error.
Connecting to the database LocalEnvironment.
ORA-00904: "END_DATE": invalid identifier
ORA-06512: at "TESTING.CUBE_VIEW", line 18
ORA-06512: at line 6
START DATE IS 90301
END DATE IS 111201
Process exited.
Disconnecting from the database LocalEnvironment.
View 3 Replies
View Related
May 4, 2011
I have a table with usernames and passwords. The passwords are stored in plaintext. I would like to issue an ALTER command on the password field to store a hash instead, and then repopulate those fields with an encrypted version of the plaintext passwords that were there before.
I would prefer to do this in a procedure, as I am going to perform it in a test environment first, then eventually in the production environment.
View 2 Replies
View Related
Apr 14, 2012
I have a table PR in that some data is there for instance, My Mr_NO was Char(11) I modify MR_No to Char(13)My Table Structure now is:
MR_No Char(13),
Mr_Date Date
My Previous data is MR_NO=APN00209085
I want to add two 00 after alter the table I want my result data like APN0000209085.I am updating through this command
update PR set Substr(MR_No,4,2)='00'
ERROR at line 1: ORA-00927: missing equal sign
Result I want is APN0000209085
View 13 Replies
View Related
Mar 23, 2011
I need to Modify the column(MSGID) data type from RAW to BLOB for a Queue Table, I'm getting the following error.
BANNER
---------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE11.2.0.2.0Production
YUV >
YUV > DESC PDA_REPORT_MESSAGE_QTAB
Name Null?Type
----------------------------------------------------- -------- ------------------------------------
Q_NAME VARCHAR2(30)
MSGID NOT NULL RAW(16)
YUV >
YUV >
YUV > ALTER TABLE PDA_REPORT_MESSAGE_QTAB MODIFY (MSGID BLOB);
ALTER TABLE PDA_REPORT_MESSAGE_QTAB MODIFY (MSGID BLOB)
*
ERROR at line 1:
ORA-22858: invalid alteration of datatype
YUV >
YUV >
YUV >
YUV >
YUV > ALTER TABLE PDA_REPORT_MESSAGE_QTAB ADD (MSGID_NEW BLOB);
ALTER TABLE PDA_REPORT_MESSAGE_QTAB ADD (MSGID_NEW BLOB)
*
ERROR at line 1:
ORA-24005: Inappropriate utilities used to perform DDL on AQ table PDADBA.PDA_REPORT_MESSAGE_QTAB
YUV >
View 10 Replies
View Related
Jun 11, 2009
I want to alter a very large table.
ALTER TABLE MYTABLE ADD
(
ENTRY_TSTMP DATE DEFAULT SYSDATE NOT NULL
)
My table is very large and I am getting an error saying I am out of undo space.
The dba says the undo space is as big as the table.
View 1 Replies
View Related
Nov 10, 2010
This is regarding a problem we are facing during report(.xls) creation which is done using a procedure triggered by a job run.
The report file(.xls) file is not getting created when the job(using dbms_scheduler) calls.
The procedure uses utl_file to create an .xls file
We have a folder on the path /oracle/tata_aig_life/websales/dnld . This folder( dnld ) has the all the priviliges drwxrwxrwx.
We have a job scheduler as the attachment which in turn triggers a procedure(check the code attachment for the scheduler and the procedure).
In the procedure we first remove the file(.xls) created in the path and then recreate a new .xls file.
The reason for first removing the file and recreating is that the existing file is not getting updated with a new file when the job is run.
We capture the exceptions in a table. In the table the following exception is logged :ORA-29283: invalid file operation
The job is triggered and the files are created on the path mentioned in the procedure on the UAT Environment..
Also the files are created when we manually run the same procedure in the path.
The Oracle version is Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
The OS flavour is SunOS otlpsr5cora01 5.10 Generic_127111-11 sun4u sparc SUNW,Sun-Fire-V490 -------
View 1 Replies
View Related
Nov 30, 2011
how to play around with NDS dynamic sql and I'm trying to add a column on the fly.Basically the procedure is trying to take a table name, column name, and eventually a data type and adds it to a table.
It works fine without the bind variable for the column name, accepting the table name on the fly.As soon as it tries to use the column name I get an ORA-00904 invalid identifier exception.
Here is the procedure I'm using
CODEcreate or replace
procedure test(tbl_name varchar2, col_name varchar2) IS
qry varchar2(500);
begin
[code]....
Here is how I'm executing it.
CODEexecute test(tbl_name => 'BB_SHOPPER', col_name => 'MEMEBER');
View 3 Replies
View Related
Sep 1, 2012
SQL> ALTER SESSION SET CURRENT_SCHEMA = CLA_T3;
Session altered.
SQL> select sys_context('USERENV','SESSION_USER') current_user,
2 sys_context('USERENV','SESSION_SCHEMA') current_schema
3 from dual
4 ;
CURRENT_USER
--------------------------------------------------------------------------------
CURRENT_SCHEMA
--------------------------------------------------------------------------------
CSR_ETL
CLA_T3
SQL> set linesize 300;
SQL> /
CURRENT_USER
----------------------------------------------------------------------------------------------------
CURRENT_SCHEMA
----------------------------------------------------------------------------------------------------
CSR_ETL
CLA_T3
SQL> create table cla_t3.test (r number, b char(2));
create table cla_t3.test (r number, b char(2))
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> create table test (r number, b char(2));
create table test (r number, b char(2))
*
ERROR at line 1:
ORA-01031: insufficient privileges
After Setting current schema to 'CLA_T3', I am unable to create table in cla_t3 schema.
View 5 Replies
View Related
Oct 18, 2012
alter index test_idx1 shrink space;
I've heard that this statement causes a table lock but cant find any information on this.if it is so, is it a write lock or also a read lock of the table?
View 5 Replies
View Related
Jan 3, 2011
CREATE OR REPLACE PROCEDURE test
IS
CURSOR cusers IS SELECT user_name, user_date FROM users;
uname users.user_name%TYPE;
udate users.user_date%TYPE;
BEGIN
OPEN cusers;
[code].....
When I try to execute this procedure I get following error:
ORA-00900: invalid SQL statement
Compilation of procedure is successful.
SQL code for creating the USERS table is here:
CREATE TABLE "USERS"
("USER_ID" NUMBER(10,0) NOT NULL ENABLE,
"USER_NAME" VARCHAR2(50) NOT NULL ENABLE,
"USER_EMAIL" VARCHAR2(50) NOT NULL ENABLE,
"USER_PASS" VARCHAR2(50) NOT NULL ENABLE,
"USER_DATE" DATE NOT NULL ENABLE,
CONSTRAINT "USERS_PK" PRIMARY KEY ("USER_ID") ENABLE
)
View 3 Replies
View Related
Oct 18, 2012
The Oracle DB in question is 11.2.0.1, x64, Server 2008.I also have a SQL Server 2005 database that runs a third party product, "PaperVision", which we use to manage documents of various kinds. This SQL Server server is also Win 2008, x64.Now, on the server that runs SQL Server, I have a simple view which is defined as such :
select DOCID,
DOCINDEX1,
DOCINDEX2,
DOCINDEX3,
DOCINDEX4,
DOCINDEX5,
DOCINDEX6,
DOCINDEX7,
DOCINDEX8,
[code]....
This view works great from the SQL Server side. I also created a database link from Oracle to the SQL Server machine, and it also works great.It is defined as such :
CREATE PUBLIC DATABASE LINK PVE_SQLSERVER
CONNECT TO EVP_PVE_USER
IDENTIFIED BY <PWD>
USING 'PVE_SQLSERVER';
Where EVP_PVE_USER is a user created on the SQL Server machine with rights to select from this view.I know it works because I get results with a sql command like :
select * from VW_PVE_DOCS_1_1@PVE_SQLSERVER;
I also created a view on the Oracle server that refines this information. It is defined as such :
CREATE OR REPLACE FORCE VIEW EVPDBA.VW_PVE_CONTRACTS_INALERT
(
DOCID,
EFFECTIVE_DATE,
EXPIRATION_TYPE,
ALERT_PERIOD_START,
ALERT_PERIOD_END,
ACRONYM,
[code]....
This view also works fine, i.e., I can select * from it from the sql command line.Now, the problem comes in when I need to run a procedure that processes this view every night and/or week.I have stripped everything out of this procedure that is not relevant, and it is defined as such for this forum :
CREATE OR REPLACE PROCEDURE EVPDBA.TESTME
is
tnum number := 0;
begin
select count(*) into tnum from VW_PVE_CONTRACTS_INALERT;
end;
/
If I execute this procedure from the sql command line, all is well.When I run it from a scheduler job, I get
ORA-01010: invalid OCI operation
ORA-02063: preceding line from PVE_SQLSERVER
ORA-06512: at "EVPDBA.TESTME", line 5
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_ISCHED", line 185
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 1
I am aware that DBMS_SCHEDULER performs a commit when scheduling a job, however, this is not scheduled from a trigger.I scoured the forums and have found a few things that seemed relevant, but not much. One had to do with the version of the JDBC driver between two Oracle databases, but I wonder if the age difference between Oracle 11 and SQL Server 2005 (Express) might be an issue. The fact that all command line select statements and running the procedure work fine implies to me that there is an additional issue raised due to the scheduler.
The other posts I found talked about performing a commit just before any select that ultimately pulls across a db link. I did this, and still no luck.One other useful fact - the job appeared to run succesfully at 5am, yet trying again at 8am threw the error, so it may be sporadic. (Although during regular daytime hours it is a very repeatable error).
I am looking into reformatting things to use the older DBMS_JOB, however, I really like the log history of job details and other functionality available with SCHEDULER.
View 5 Replies
View Related
Mar 11, 2011
We have some tables in our database in which for loading data we have the setup in place to do Exchange partition after data load into staging. Today we did changes to column length to one pair of main and staging table. Post that Exchange partition stop working.
View 1 Replies
View Related
Aug 11, 2010
can you use alter table command to disable/enable constraints in a form if you can how, if you cant why
View 4 Replies
View Related
Sep 20, 2011
i need a trigger with alter commands to alter the table structure,it will be captured in a separate meta data table(META)
CREATE OR REPLACE TRIGGER meta_alter AFTER Alter ON SCHEMA
BEGIN
update meta set column_name=:new where table_name=ora_dict_obj_name column_name=:old;
END;
/
Meta table contains Table name and column name..i attached the table data in atext file
View 39 Replies
View Related
Oct 25, 2013
I am using, Release 11.2.0.3.0 of oracle. In our database we observe, high 'Application' wait followed by 'other' and 'User I/O'. After investigating through the wait class 'Other', i found that 90% of the wait is due to wait event 'Enq: WF Contention ' that to excatly 5 PM to 5.30 PM daily. Then i found from the dba_hist_active_sess_history, that the sessions experiencing this waits are oracle internal(M004),
Now i can see two of the oracle jobs has been scheduled at this particular time.
1)dbms_autotask_prvt.age
2)dbms_scheduler.auto_purge
And these were having frequency daily.So, i am suspecting these were experiencing the waits. Now my question is, can i decrease the frequency(may be weekly) of these jobs and it will not put any negative impact on my DB? Or should change the schedule time to some less peak hour or whether these are 'not required' and can be disabled?
View 3 Replies
View Related
Nov 5, 2012
I am using oracle 10.2.0.3 and i am receiving very slow response time for the below query and sometimes resulting in a deadlock throwing ora-60 error.
DELETE FROM GBC_CORE.SPI_ELEMENT_ID TRGT WHERE (TRGT.URI) NOT IN (
SELECT DISTINCT FROMTOURI.URI FROM ( SELECT SERVICEACCESSNAME AS URI,
SUBSTR( SERVICEACCESSNAME,1,INSTR( SERVICEACCESSNAME ,'_')-1) AS
FROM_URI, SUBSTR( SERVICEACCESSNAME,INSTR( SERVICEACCESSNAME
,'_')+1,LENGTH(SERVICEACCESSNAME)) AS TO_URI FROM
TRPT.V_TRPT_SPI_VIEW@DBLNK_FCE_TRPT ) FROMTOURI,
[code]...
View 1 Replies
View Related
Feb 28, 2011
I have to find out the cause of a lock where a particular session with some serial no is causing a TX level lock for certain duration.
View 17 Replies
View Related
Dec 4, 2012
i'm executing a load test using the below statement:
INSERT INTO BPMBI.PPROCINSTANCE SELECT * FROM BPMBI.PPROCINSTANCE_BKP WHERE ROWNUM < 501;
COMMIT;
EXIT;
I've been using the same for a few days without any error while now wehn i try to use the same i get below error:
SQL> insert into pprocinstance select * from pprocinstance_bkp where rownum <= 500;
insert into pprocinstance select * from pprocinstance_bkp where rownum <= 500
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected.
I've been trying to check what the issue is and when i execute the rownum < 140 works fine while rownum < 150 is showing the same error.
View 6 Replies
View Related
Sep 20, 2013
how to write procedure to load the data into a table using xml as input parameter to a procedure and xml file is as shown below which is input to me.
xml version="1.0"?><DiseaseCodes><Entity><dcode>0</dcode><ddesc>(I87)Other disorders of veins - postphlebitic syndrome</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity><Entity><dcode>0</dcode><ddesc>(J04)Acute laryngitis and tracheitis</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity><Entity><dcode>0</dcode><ddesc>(J17*)Pneumonia in other diseases - whooping cough</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity></DiseaseCodes>.
View 3 Replies
View Related
Mar 6, 2010
I have created one procedure based on one table item master which has a field called item stock or non stock based on this i will fetch data from one of two tables .If its a stock item data will be retrieved from wip_main_acnt table and if its non stock it will pick from ns_main_acnt.my procedure is working fine but all i need is i just want to put an exception that if data is not found in one of the table based on the item selected.I am confused which one to be used whether no_data_found or notfound%.
CREATE OR REPLACE PROCEDURE dflt_pr_acnt (
l_item_code IN VARCHAR2,
l_main_acnt_code OUT VARCHAR2
)
[code]....
View 8 Replies
View Related
Jun 19, 2012
Can i alter the table to create partition on non partition table, i have tried and could not create it. Do we have some other means to do it as this is the live table and cannot drop them else will lose the data.
View 1 Replies
View Related
