SQL & PL/SQL :: ORA-22370 / Incorrect Usage Of Method AnyData Insert
Mar 26, 2010
Any success using the sys.anydata.ConvertClob member function when trying to insert data into a Anydata column? This function has been listed in Oracle documentation since 9i, but even in 11G I get the error :
ORA-22370: incorrect usage of method AnyData Insert.
Code is pretty straight forward using a CLOB column in a after insert trigger :
Insert into TRACE_DETAIL(OBJ_ID, COLUMN_ID, OLD_VALUE, NEW_VALUE) values (logId, 73, null, sys.anydata.convertClob(:NEW.Req_Data));
View 6 Replies
ADVERTISEMENT
Sep 25, 2013
Would like to ask expert here, how could I insert data into oracle table where the value is 03 ? The case is like that, the column was defined as varchar2(50) data type, and I have a csv file where the value is 03 but when load into oracle table the value become 3 instead of 03.
View 8 Replies
View Related
Feb 27, 2012
How to convert a SYS.ANYDATA object to BLOB?
or any user defined type to BLOB?
View 1 Replies
View Related
Jun 28, 2013
Is their any query to find cpu usage & memory usage of all the queries currently running on DB?
View 5 Replies
View Related
Apr 8, 2011
Consider the statement below:
Update employee e
set e.dept_id = (select d.dept_id
from dept d
[Code].....
The above is not the exact code which I am executing but an exact replica of the logic implied in my code.
Now, when i display the value of 'rows_updated' it returns a value greater than 0,i.e 3 but it should ideally return 0
since there are no records matching for the condition:
(select d.dept_id
from dept d
where e.dept_name = d.dept_name)
So, I executed the statement:
select count(*) from employee e
where emp_id = 1234
and exists
(select 1
from emp_his ee
where e.emp_id = ee.emp_id)
and the result was 3 which is the same value returned by %rowcount.
why this is happening as I am getting incorrect values in %rowcount for the number of rows updated.
View 7 Replies
View Related
Sep 24, 2013
I currently have around 560 rows of bad data where
GL_Encumbered_Date and TO_CHAR(PRD.GL_ENCUMBERED_DATE,'YYYY' display correctly.
The problem lies in
PRD.GL_CANCELLED_DATE which displays correctly (example: 30-APR-10) but TO_CHAR(PRD.GL_CANCELLED_DATE, 'DD-MON-YYYY') for the same row displays as 30-APR-0010.
I have separated out the incorrect year with:
to_char(prd.gl_cancelled_date,'YYYY') where each of the 560 rows display 0010 instead of 2010. I am unsure if arithmetic with dates is allowed within SQL- but am curious if the operation date + number will result in a date.
If this is so, how could one go about taking 0010 as a date and add 2000 to create 2010 for: PRD.GL_CANCELLED_DATE.
View 2 Replies
View Related
Apr 28, 2011
I installed 11.1.0.6. on Linux RedHat 5.5. Then upgraded to 11.1.0.7. However when I issue sqlplus /nolog and do select banner from v$version it shows 11.1.0.6. Although the install and upgrade were successful. I wonder why it does not show 11.1.0.7. I upgraded using the installer and not dbua could this be the reason why?
View 17 Replies
View Related
Mar 3, 2011
When I run a menu (which was working fine yesterday and hasn't been changed in months) I click on an item and it runs the following
"
DECLARE
module_name VARCHAR2(125);
BEGIN
module_name := :GLOBAL.FORMS_PATH || 'covenants';
Open_Form(module_name);
END;
"
The module_name variable should be assigned the value 'covenants', because :GLOBAL.FORMS_PATH is '' (nothing). I stepped thru the code and in the debug system values window there is nothing in :GLOBAL.FORMS_PATH. But instead, it gives me: 'FORM_NAMEcovenants' where 'FORM_NAME' is the form the menu was called from.
View 5 Replies
View Related
Jul 20, 2011
We just upgraded to 11g and have run into incorrect results for some of our LEFT JOINs. If the table, view, subquery, or WITH clause that is being LEFT JOINed to contains any constants, the results are not correct.
For example, a test (nonsensical) view such as the following is created:
create or replace view fyvtst1 as
select spriden_pidm as fyvtst1_pidm,
'Sch' as fyvtst1_test
from spriden
where spriden_last_name like 'Sch%' ;
When I run the following query, I get correct results; that is, only those with "Sch" starting their last name are listed.
select spriden_pidm, spriden_last_name, fyvtst1_pidm, fyvtst1_test
from spriden
join fyvtst1 on fyvtst1_pidm = spriden_pidm ;
However, when I change the JOIN to a LEFT JOIN, the last column contains "Sch" for all rows, instead of NULL:
select spriden_pidm, spriden_last_name, fyvtst1_pidm, fyvtst1_test
from spriden
left join fyvtst1 on fyvtst1_pidm = spriden_pidm ;
We've discovered other quirky things related to this. A WITH clause with similar logic as the above view, when LEFT JOINed to a table will also cause the constant to appear in each row, instead of NULL (and only the value where there is a join). But when additional columns are added to the WITH, it behaves correctly.
This is easy enough to rewrite - but we have WITHs and views containing constants in numerous places, and cannot hope to track down every single one successfully before the incorrect results are used.
Finally, the NO_QUERY_TRANSFORMATION hint will force the query to work correctly. Unfortunately, it has a huge negative performance impact (one query ran for an hour, vs. 1 second in 10g).
View 10 Replies
View Related
Nov 2, 2012
I have the following DDL:
drop table tmp_guid;
CREATE TABLE tmp_guid (
c1 raw(16) not null
,c2 raw(16) not null
);
begin
[code]...
It seems that a combination of a unique index and extended stats are to blame. Removing any one of them causes the query to also produce correct results.Extended stats basically captures the fact that despite being unique, c1 depends on c2.
View 0 Replies
View Related
Jul 2, 2012
My problem is I have 3 tables (TEST_TBL1, TEST_TBL2, TEST_TBL3). TEST_TBL2 and TEST_TBL3 are in remote database and I use database link to join them. The following query returns incorrect result (I seems that it ignore the where clause)
SELECT * FROM TEST_TBL1 JOIN TEST_TBL2@db_remote USING (KEY1) JOIN TEST_TBL3@db_remote USING (KEY2) WHERE KEY1=XXX OR KEY2=YYY;
I am on 11R1 (11.1.0.7)
FOR EXAMPLE:
Local database:
CREATE TABLE TEST_TBL1
(
KEY1 NUMBER(5) NOT NULL,
[Code].....
View 8 Replies
View Related
May 18, 2011
I can't seem to understand why the hour is incorrect. Below query "dte_computation_on_data" is the old function they use to convert date and insert it to the table. Problem is when I revert it to the actual date the hour
is incorrect.
CODE
SELECT -- THIS HERE IS MY TEST TO REVERT TIME AND DATE ON THE FORMULA OF WITH RESPECT TO THEIR FUNCTION
to_char(TO_DATE('19700101', 'YYYYMMDD')+(tb1.dte_computation_on_data/86400),'MM/DD/YYYY') || ' ' ||
to_char(to_date(mod (tb1.dte_computation_on_data,86400) ,'sssss'),'hh24:mi:ss ') revert_test,
systimestamp,tb1.dte_computation_on_data
from
( SELECT -- THIS IS THE FORMULA OF THE OLD FUNCTION THEY USE TO CONVERT DATE TO NUMBER AND INSERTED ON THE ROW
floor((CAST(SYS_EXTRACT_UTC(systimestamp) AS DATE) - TO_DATE('19700101', 'YYYYMMDD')) * 86400) dte_computation_on_data
FROM dual)tb1;
results
---------------------------------------------------------------------------------------
REVERT_TEST SYSTIMESTAMP DTE_COMPUTATION_ON_DATA
05/19/2011 03:46:18 5/19/2011 11:46:18.005171 AM +08:00 1305776778
View 1 Replies
View Related
Jan 17, 2012
I am working on the Reports Builder 10.1.2 at the Database Oracle 11.2.0. I have developed a report , which shows the page number at top right cornet. So I have selected a field and set the Source as "page Number". While I run the report through Reports Builder,it is properly displayed the page number. But while I copied the report to unix and run through "$ORACLE_INSTANCE/config/reports/bin/rwrun.sh", the page number is showing as 65536 . By changing the Source of the field to "Total Page" or "Physical page Number" make no difference.Every times It is showing the page number 65536 in the pdf output of the report.
View 6 Replies
View Related
Sep 16, 2013
I am building a new Oracle Server. I have the following in the listener.ora file.
LISTENERProd =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=ORACLEPRODDB)(PORT=1555))
)
SID_LIST_LISTENERProd=
(SID_LIST=
(SID_DESC=
(SID_NAME=dg4odbc)
(ORACLE_HOME=c:Oracleproduct11.2.0dbhome_1)
[code]....
View 6 Replies
View Related
Jun 20, 2011
nlsb> select file_name,bytes from dba_data_files where tablespace_name='ARIAN_DATA';
FILE_NAME BYTES
------------------------------------------------------------ ----------
/data950/nlsb/nlsb/datafile/o1_mf_arian_da_6wf27lcn_.dbf 5368709120
/data950/nlsb/nlsb/datafile/o1_mf_arian_da_6wf1txnm_.dbf 8589934592
/data950/nlsb/nlsb/datafile/o1_mf_arian_da_6wf1tr6v_.dbf 8589934592
/data950/nlsb/nlsb/datafile/o1_mf_arian_da_6wf1tonc_.dbf 8589934592
[code].....
dbms_metadata is giving me code that will create more datafiles than the original, and furthermore won't run: firstly, because two files are named with a null string, and secondly because it includes RESIZE commands which won't work because they nominate OMF file names. Or is dbms_metadata unreliable?
View 1 Replies
View Related
Feb 4, 2011
I am having issue with configuring listner name on Oracle Server.My default listener is working,But I have stopped the default listener and tried to create another listener is differnt port,but no success,It always says The address of the specifed listener name is incorrect.Below is the listener.ora file
My db name and sid name are etl
LIST1=(DESCRIPTION=(ADDRESS=
(HOST=127.0.0.1)
(PROTOCOL=TCP)
(PORT=1530)
)
and the list1.log file says The address of the specified listener is incorrect.
View 12 Replies
View Related
Jun 3, 2013
The following query is used to generate a flash chart:
select null link
'Available' as "Available",
to_number(max_licenses - consumed_licenses)
from software_detail
where capture_date = trim(GET_CAPTURE_DATE)
and software_product = :p1
and software version = :p2
[code].....
But the 2d doughnut flash chart shows:
Available = 39
Consumed = 1
How is this possible?
Oracle 11.2.0.3 RAC on Windows 2008 R2
APEX 4.2.1.00.0 with Apex Listener 2.0 on Apache Tomcat 6.
View 4 Replies
View Related
Feb 1, 2012
I have the following Union All query. It throws the following error in SQL plus
ERROR at line 27: ORA-01789: query block has incorrect number of result columns
After doing some google for the above error it suggests there are incorrect number of columns in the Union All query.I could not figure out the exact location well SQl Plus says error is on line 27 at the first opening bracket like
(Select distinct c.contact_code
Following is the SQL query
Select
tbl_contact.contact_code,
contact_title
||'.'||contact_name contact_name,
contact_address,
[Code] ......
View 1 Replies
View Related
May 9, 2013
We have a requirement to update one column as given in the below mentioned pl/sql block.The challenge is to update the a large volume of data every day.Generally this kind of one to one update will takes long time (45 min approxmately) We need a solution that will be able to update 10 million records in few minutes.
begin
for i in (select t3.event_id, t1.header_id from
table_1 t1, table_2 t2, table_3 t3
where t1.header_id=t2.header_id and t2.entity_id = t3.entity_id)
loop
update table_1 set event_id = i.event_id
where header_id =i.header_id;
end loop;
commit;
end;
View 5 Replies
View Related
Mar 4, 2011
NOVALIDATE CONSTRAINT METHOD.
Below is the step i gone through:
1) CREATE TABLE mutu (id NUMBER, text VARCHAR2(20));
2) INSERT INTO mutu SELECT rownum , 'mutu' FROM dual CONNECT BY LEVEL <= 10000;
3) Insert Duplicate row.
INSERT INTO mutu VALUES (42, 'DUPLICATE');
4) COMMIT;
5)Create a NON-UNIQUE INDEX's CONSTRAINT as below.
ALTER TABLE mutu ADD CONSTRAINT mutu_pk PRIMARY KEY(id)
USING INDEX(CREATE INDEX mutu_pk ON mutu(id)) ENABLE NOVALIDATE;
6) Insert Duplicate row.
INSERT INTO mutu VALUES (42, 'DUPLICATE');
7) ERROR at line 1:
ORA-00001: unique constraint (SYS.ZIGGY_PK) violated
Why it shows the error. Even though I had created a NON-UNIQUE INDEX's Constraint only.
View 2 Replies
View Related
Jun 11, 2010
I have three table for the stock calculations. The structure are like this
Product_master
product_id number,
product_name varchar2(30),
company_id number(3),
rate_per_unit number(14,4)
Purchase_master
trans_date date
product_id number,
company_id number(3),
quantity number(14,4),
rate_per_unit number(14,4)
Sales_master
trans_date date
product_id number,
company_id number(3),
quantity number(14,4),
rate_per_unit number(14,4)
Purchase_return_master
trans_date date
product_id number,
company_id number(3),
quantity number(14,4),
rate_per_unit number(14,4)
Sales_return_master
trans_date date
product_id number,
company_id number(3),
quantity number(14,4),
rate_per_unit number(14,4)
I need to find out the valuation on particular sales date at FIFO method.
View 22 Replies
View Related
Oct 21, 2010
I have 2 tests Oracle instances (a 10g and a 11g) in which somebody added many users (almost 50k), every user having a TEST table with no rows in its schema.
I have to drop these users without recreating the instances, but DROP USER username CASCADE is taking almost 1 minute for each user. This will take me to almost a month.
View 2 Replies
View Related
Apr 3, 2008
i got a table and it had 5000 rows of data...ive deleted around 2000 to decrease the db size but i have no success. My harddrive is still showing the same size with no increase in mb.
I've looked at shrink etc methods but some are not compatible with 8i.
I take it the db is still reserving that those deleted rows thinking it may be used again which is the reason for no increase in space.
View 1 Replies
View Related
Aug 23, 2011
Question:
How to find out the status of the import job(how much work is done, ETA), i have started the job few days back it still running on the server. i am using import not datapump, because i got the dump which took with export. DB version is 11.2.0.2.0
for datapump i know i can use the following queries, but these are not giving any output l think these are only for datapump
select sid, serial#, sofar, totalwork from v$session_longops;
select sid, serial# from v$session s, dba_datapump_sessions d where s.saddr = d.saddr;
View 1 Replies
View Related
Sep 15, 2010
Which Oracle access method is the fastest way for Oracle to retrieve a single row?
a) Primary key access
b) Access via unique index
c)Table access by ROWID
d)Full table scan
View 2 Replies
View Related
Jun 2, 2011
I have a table :
Product
A
B
C
D
and I was wondering if there is a quick method of populating it with calendar data so it would look like the following:
Product Year Month
A 2008 Jan
A 2008 Feb
A 2008 Mar
A 2008 Apr
A 2008 May
A 2008 Jun
A 2008 Jul
A 2008 Aug
A 2008 Sep
A 2008 Oct
A 2008 Nov
A 2008 Dec
A 2009 Jan
A 2009 Feb
Etc.
This would be done for all products for 4 years.
View 2 Replies
View Related
Feb 11, 2013
I have installed oracle 10g in D drive .
The instalation says it is succeessful, without asking the database name or the password .
I have followed the steps given in the attached file .
However it is not asking the databse name or password ( no configuration set up is appearing for the user to interact with the system ) .
So, after installation I can not give the username and password .
The tnsnames.ora file is given below
# tnsnames.ora Network Configuration File: D:DevSuiteHome_1
etworkadmin nsnames.ora
# Generated by Oracle configuration tools.
[Code]....
The files from where I installed is ds_windowx86 disk1 and disk 2.
View 26 Replies
View Related
May 9, 2012
I have a form of a user feedback with SMS Sending. which I gave to my all clients when ever my any client enter a feedback they press send SMS button so I got SMS if they press the button 2 times I got 2 same sms and if press the button 3 times I got 3 same sms and so on I want to restrict them and allow them to send only 2 sms of that feedback.any method on WHEN-BUTTON-PRESSED Trigger.
View 7 Replies
View Related
Feb 25, 2010
Is it possible to solve an equation with the newton-raphson method with sql?
I have an equation f(x)=1-a/x**2, f(x)'= 2a/x**3
and want to solve x for several a's
The newton-raphson methos uses the derivation as next approximation:
xn2 = xn1 - f(x)/f(x)'
The result should be like this:
create table temp_res (a integer, res number);
insert into temp_res values(1, 1);
insert into temp_res values(2, 1.41421288939285);
insert into temp_res values(3, 1.73205078513617);
insert into temp_res values(4, 1.99999999999617);
insert into temp_res values(5, 2.23606797658102);
insert into temp_res values(6, 2.4494897130809);
insert into temp_res values(7, 2.64575095945042);
insert into temp_res values(8, 2.82842712474403);
View 33 Replies
View Related
Jun 30, 2013
DB: 11.2.0.3 & OS: RHEL5 Easy Connect Naming method enhances the host naming method by allowing for a port and service specification. My question is does Oracle 11g support Host naming method?
View 4 Replies
View Related
