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.
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.
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.
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
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.
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.
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 >
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
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?
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 )
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 :
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.
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.
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
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.
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?
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]...
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.
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>.
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]....
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.
