I have a website I am working on a database for, and to insert new data into the table is not a problem, even to output the table isn't an issue.
But a delete query won't work and it doesn't return any errors or echo commands that I put in the steps of the logic.
I'm trying to create a view that includes both custom and delivered tables. The SQL code produces the desired results in SQL Developer. However, when I create and test the view using that code, I get very different and incorrect results. The SQL part of the view is below:
SELECT j.emplid, pt.deptid, pt.y_policy_id, pt.effdt, pt.title, pt.comments, pt.y_policy_covg, pt.doc_url
FROM psoprdefn a
, ps_job j
, ps_y_policy_tbl pt
WHERE substr(a.oprid,1,1) = '0'
AND a.emplid = j.emplid
AND j.job_indicator = 'P'
AND j.effdt = (SELECT MAX(effdt) FROM ps_job
WHERE emplid = j.emplid AND empl_rcd = j.empl_rcd AND effdt <= sysdate)
[code]....
When I delete a table in Toad it takes only a one or two second until I receive success message.
But with SQL*Plus on UNIX I canceled the operation after 5 minutes:
$ sqlplus tmo323/IXQMISX8ttr@PROD
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Nov 28 11:54:22 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> delete table_5
2 ;
^Cdelete table_5
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
exact the same SQL statement.
The following script doesnt work.
DELETE
FROM PSPROJECTITEM I
WHERE I.PROJECTNAME = 'TEMP1'
and (I.OBJECTTYPE between 79 and 84
or (I.OBJECTTYPE = 58
and I.OBJECTID1 = 104
[code]...
It gives the following error:
Error at Command Line:12 Column:6
Error report:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"
*Cause:
*Action:
If i inserted the values in table it gets inserting very few rows only.I dont know y it is?
View 15 Replies View RelatedI have a large 10G database, with records being inserted almost constantly. In order to "UPDATE" records, I need to be able to DELETE the old record and INSERT the new record (even while records are being inserted into the table). It appears that DELETES won't work while records are being inserted (1.5 million records per day). Is there a way around this (which I'm assuming at this point is a contention issue)? The INSERT uses the NOPARALLEL hint; no hints on the DELETE.
View 7 Replies View RelatedI am trying to write a trigger that will do an insert/delete/update into a audit table when a change has occurred on the primary table. The change will be recorded in the audit table by a incemental sequence number and the updated data.
there will be an extra column in the audit table
how to get a simplified version of this trigger.
the primary table will look like so
Id_num varchar (20)
code Integer
desc varchar(20)
sequence_num Integer
audit table
Id_num varchar(20)
code Integer
desc varchar(20)
timestamp date
sequence_num Integer
We have a requirement to archive and purge the tables dynamically based on the control table input. For that we have to design a control table to gather the necessary information and passed to generate the queries.
I have designed the table as below.But in this case I am not able to handle the parent and child relation ship.
Suppose one table needs to be archived and purged and that table is parent table and it is having 2 child tables, so first required data will be inserted into target table and delete from source parent and child tables. so before deleting from parent we have to delete data from all 2 child tables.
Suppose one table needs to be purged and that table is parent table and it is having 5 child tables, so before deleting from parent we have to delete data from all 5 child tables.
To handle this scenario how can I design my control table.
For archive and purge the query like this.
INSERT INTO towner_name.ttable_name
(SELECT * FROM sowner_name.stable_name WHERE condition_column<=(sysdate-30));
DELETE FROM sowner_name.stable_name WHERE condition_column<=(sysdate-30);
for purge the quey is like this.
DELETE FROM sowner_name.stable_name WHERE condition_column<=(sysdate-30);
This is my control table and I have 300 tables list to archive and purge.
CID SOWNER_NAME STABLE_NAME TOWNER_NAME TTABLE_NAME CONDITION_COLUMN PERIOD UNIT TYPE
1 wedb_au OFFER_HEADER wedb_au OFFER_HEADER LAST_DATE 30 D A
1 wedb_sa OFFER_CUSTOMER wedb_sa OFFER_CUSTOMER LAST_DATE 60 D A
1 wedb_au OFFER_SERVICE LAST_DATE 1 Y P
1 wedb_us OFFER_CUSTOMER LAST_DATE 90 D P
1 wedb_cn OFFER_CARDS UPDATE_DT 2 Y P
2 wedb_au ORDER_HEAD wedb_au ORDER_HEAD LAST_DATE 120 D A
2 wedb_us ORDER_CUSTOMER wedb_us ORDER_CUSTOMER LAST_DATE 150 D A
2 wedb_sa ORDER_HEAD wedb_sa ORDER_HEAD CREATION_DT 1 Y A
3 wedb_us DELIVERY_HEAD wedb_us DELIVERY_HEAD UPDATE_DT 50 D A
3 wedb_au DELIVERY_CARDS wedb_au DELIVERY_CARDS UPDATE_DT 200 D A
3 wedb_au DELIVERY_SERVICE wedb_au DELIVERY_SERVICE LAST_DT 100 D A
WHERE TYPE=P means insert and delete
TYPE=A means only delete
wedb_au.OFFER_HEADER is Parent Table.
child tables for wedb_au.OFFER_HEADER are wedb_au.OFFER_SERVICE,wedb_au.OFFER_BODY,wedb_au.OFFER_EMAIL,OFFER_TAX.
wedb_au.OFFER_SERVICE is child table and parent for this table is wedb_au.OFFER_HEADER
wedb_sa.OFFER_CUSTOMER Stand alone table no relationship
wedb_us.OFFER_CUSTOMER Stand alone table no relationship
[code].......
I have 4 tables , Certain users need to (Select ,insert,delete) certain rows only based on nApplication id
All the tables have Application Id , Based on this application id user will (Select ,insert,delete)
I tried row level security DBMS_RLS.add_policy but it hangs every time when i query the data or when i try to drop the policy. Is there is any method to resolve this issue.
If only Row level security will solve this issue , give me some example
How to get all the name of tables that a user can select, insert, update or delete?
View 2 Replies View RelatedFor triggers, is it possible to do separate actions on insert , update and delete. For example, if insert is the case, do select; if update is the case, do select from another table and so on?
View 1 Replies View RelatedI have multirecord block and I want to disable Inserting/Updating/Deleting more then one record at a time.
View 32 Replies View Relatedi have one table ot_ins_item where user will enter the details of item, grade,item qty , later on user will go and update the same table the details of different grades received for the same item in different columns with qty breakup in 3 different fields it_qty_01 , it_qty_02,it_qty_03 respectively with different grades , what i need is i want is whenever he updates this table with different grades based on data entered in 3 different fields , a procedure or trigger should delete the initial record saved and insert three different rows based on newly updated values , it may be 3 or it may be 2 sometime depending upon input values that many records should be inserted same time controlling the qty's entered in breakup not exceeding the main qty.
CREATE TABLE ot_ins_item (it_ins_no NUMBER,it_no NUMBER,it_grade VARCHAR2(12),
it_code VARCHAR2(12),it_qty NUMBER,it_flex_01 VARCHAR2(12),
it_01_qty NUMBER,it_flex_02 VARCHAR2(12),it_02_qty NUMBER,it_flex_03 VARCHAR2(12),
it_03_qty NUMBER);
create sequence s_it_no start with 1 ;
INSERT INTO OT_INS_ITEM VALUES (1,s_it_no.NEXTVAL,'A','ITEM1',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO OT_INS_ITEM VALUES (1,s_it_no.NEXTVAL,'B','1TEM2',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO OT_INS_ITEM VALUES (1,s_it_no.nextval,'C','ITEM3',NULL,NULL,NULL,NULL,NULL,NULL);
SELECT * FROM OT_INS_ITEM;
[code]....
I have to write a procedure that accepts schema name, table name and column value as parameters....I knew that i need to use metadata to do that deleting manually.
View 9 Replies View Related[CODE]
DECLARE
D1 DATE:='&D1';
D2 DATE;
BEGIN
WHILE D1<=D2 LOOP
DBMS_OUTPUT.PUT_LINE(TO_CHAR(D1,'DAY DD-MON-YYYY'));
D1:=D1+1;
END LOOP;
END;
Here I want to display all the seven days. One more question is here I'm d1<=d2 but d2 is null. So how does <= works here a null. A null is always null. What will be there in d2 how does the loop works with this comparision.
I have the query below that works absolutely fine in Oracle 10g but is not running in Oracle 11g. Couple of things I notices about this query.
1. If I remove the alias 'FACILITY_W_CODE' in the ORDER BY clause, query works fine in 11g. If I replace 'FACILITY_W_CODE' with the actual fields - 'F.SERVICE_AREA || F.DISTRICT_OFFICE || F.DO_GROUP || F.DO_SSC' even then it works fine.
2. If I remove the GROUP BY clause in the inside query but keep the alias 'FACILITY_W_CODE' in the outside ORDER BY clause, the query works fine.
I am not sure where the issue is, GROUP BY or the ALIAS. Also, the alias issue seems to be only where the alias is used for multiple fields.
SELECT F.SERVICE_AREA || F.DISTRICT_OFFICE || F.DO_GROUP || F.DO_SSC FACILITY_W_CODE, PF.FAC_TYPE FACILITY_TYPE, PF.FAC_IDENT LOCATION_IDENTIFIER, P.DISTRICT_CODE USER_W_CODE, P.EMAIL_ADDRESS, PF.CERTIFICATION_AUTHORITY, PF.CALLBACK_TECHNICIAN, PF.CALLBACK_ENVIRONMENTAL
FROM PEOPLE P
JOIN (
SELECT I_PF.ID, I_PF.FAC_TYPE, I_PF.FAC_IDENT,
[code]....
How oracle retrieves data from a table.like let say a table product as
create table product(id number,name varchar2(10))
table product dont have any index on it
now if this table conatain 500 rows and we have two query
1. select * from product where id=26
2. select * from product
does both query take same time or first query will execute in less time since it has where condition
I am fighting with some code to work in IE7. It is currently working in Firefox.
A little bit more on the context of the situation is that I am entering the code as a personalization on an Oracle Self Service Page. I am entering in as a raw text personalization.
[Code]
<html>
<head>
<style>
</style>
</head>
<body lang=EN-US style='tab-interval:.5in'>
[code].....
I tested the SQL in 11G and get the ORA-00979 (not group by error) , the same SQL is working in 10G.
553322.1 - ORA-00979 IN 11.1.0.6 BUT NOT IN 9I OR 10G
814423.1 - ORA-00979 AFTER UPGRADE TO 11G
Do we have simple soluation for it if I don't have Oracle support account?
My teacher taught the lesson of DML statemnts, he told us how does merge works , but he did not give us any query for that,provide query for Merge and if possible then explain it too , I am using Oracle 10g Sql Plus.
View 3 Replies View RelatedI want to know how Remap_datafile paramater in IMPDP works in Oracle 10g. give me the whole scenario for two databases. One in Linux Platform and other in Windows platform. I want to import the tablesapce data from Linux to Windows Server database.
View 5 Replies View Relatedhow oracle background process works and in what sequence when we file an insert or update command?
View 4 Replies View Relateddeclare
blk_name varchar2(60);
form_name varchar2(60);
[Code].....
this is my coding on save button. this is working fine but only once. when i enter the data second time and try to save, it didn't do anything. what should i do?
I have a two node RAC on 11.2.0.3. We are using SCAN Listener here. On my node 1, i can connect to the database using tns alias: The alias looks something like:
Prod =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = x1200.prod.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cusms)
(UR = A)
)
)
sqlplus system/actdbasystem@cusms ----- works sucesfully on the prod server. Even the same prod alias copied to second tnsnames.ora in the cluster and run, it works sucesful too. But the issue is, when we copy the same alias onto any other servers and try to connect give's the following error message:
SQL*Plus: Release 11.2.0.3.0 Production on Fri Jul 27 12:37:55 2012
Copyright © 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-12519: TNS:no appropriate service handler found
[code]...
why is it my connection using sqlplus works locally and not from other servers?
How can I do this ?
is there an rpad that works for multiple chars?
select RPAD('test',10, 'X') from dual;
I want to be able to do something like
select RPAD('test',6, '%20') from dual;
which should return test%20%20
when i am trying to excute below query.. it says ORA-00942: table or view does not exist.
create or replace force view v2 as select * from employee;
I have a problem spooling flat files in utf 8. I have 3 different sql that needs to be spooled regulary in utf8 format.the first two containing special chars is spooled without any problem, but the third that is containg only numbers and pipe-s is spooled in ansi format.
i have set nls_lang=.utf8 in my shell script, but that only worked at the first two sql the last doesnt work.
I have also tried to convert file using iconv command, but the output is still in ansi (only works if i put a special char in the ansi file then it converts to utf8 without problem) if i open the file in notepad and save as utf8 then it works, but i cannnot reach the same from unix script.
I have a table called Student and a column as name now if i write a Query
select * from student where name < 'BRIAN D'
How does the comparison will be done.
know whether Oracle 9i works in Windows 2012? As we are planning to upgrade.
View 1 Replies View RelatedI have a query similar to the one below which is embedded in a PRO*C program. The query works fine when run in the PRO*C program against a 8i database but fails with an "ORA-02015: cannot select FOR UPDATE from remote table" error when run against a 10g database. The PRO*C program is executing the sql using "EXEC SQL".
QUERY: Select last_name, first_name from Member
where ....
FOR UPDATE OF LAST_NAME;
The other thing to note is this SQL query works fine via sqlplus in a 10g environment.
ADDITIONAL DETAILS: The above query is selecting data from a base table via a user view
VIEW: select * from
otherschema.member@connection_identifier;
This view was created in this manner to allow the user account access to the underlaying table without creating explicit permissions.