SQL & PL/SQL :: Unexpected Characters In Table Column
Mar 22, 2012
I have a table called temp which has nearly 20 columns when i insert values in some columns then null is inserted successfully in remaining column except one column,this column takes string of unexpected characters(like-"inverted question mark") which are not exist on my keyboard, how it is possible. it is happening only in this column and this is only one column of "nvarchar" type , is this because of it(datatype)?
View 4 Replies
ADVERTISEMENT
Nov 27, 2012
The following query gives me 28,800 as sum(sal)
SELECT SUM(salary)
FROM employees
WHERE department_id =60
O/P is : 28800But when i use the above query in anonymous block it gives me 684400
DECLARE
v_sum_sal NUMBER;
[code]....
The above output statements gives me 684400 as output.. But the expected is 28800
View 6 Replies
View Related
Jul 23, 2013
create table test
(
name varchar2(50),
descd varchar2(50)
)
insert into test values ('kethlin','da,dad!tyerx');
insert into test values ('tauwatson','#$dfegr');
insert into test values ('jennybrown','fsa!!trtw$ fda');
insert into test values ('tauwatson','#$dfegr ,try');
how do I get the first three characters and last three characters from name field and remove all the junk characters from descd field?
so my o/p be like;
Quote:('ketlin','dadadtyerx')
('tauson','dfegr')
('jenown','fsatrtw fda')
('tauson','dfegr try')
View 6 Replies
View Related
Jul 15, 2010
I have this table column of varchar2 data type. I need to find out if it contains anything other than numbers or alphabets(basically to see if it contains any special characters in it).
I just need to fetch only those rows that has special characters. How do I get this?
CREATE TABLE CHECK_SPL_CHARAC
(ADDRESS VARCHAR2(100));
INSERT INTO CHECK_SPL_CHARAC VALUES ('ABC%ABC');
INSERT INTO CHECK_SPL_CHARACVALUES ('ABC_ABC');
INSERT INTO CHECK_SPL_CHARAC VALUES ('ABC?ABC');
INSERT INTO CHECK_SPL_CHARAC VALUES ('ABC*ABC');
INSERT INTO CHECK_SPL_CHARAC VALUES ('1234');
INSERT INTO CHECK_SPL_CHARAC VALUES ('ABC');
INSERT INTO CHECK_SPL_CHARAC VALUES ('AB C');
INSERT INTO CHECK_SPL_CHARAC VALUES ('12 34');
COMMIT;
I need to get the first 4 rows alone in the sql output. I do not wan to get the last 2 rows with space.
View 8 Replies
View Related
Jul 13, 2011
I am trying to find difference between two dates .I am getting 73045 as the difference. I want 12 as difference. find the below lines of
request_date:=TO_DATE('1-jul-2011','dd-mon-yyyy');
Today_date:=TO_DATE(sysdate,'dd-mon-yyyy');
dbms_output.put_line('>> difference is ' ||( Today_date-requestdate) || ' days' );
Output:-difference is 73045 days
( if tried '2-jul-2011' as request_date, i am getting 73046.
View 5 Replies
View Related
Aug 3, 2010
I've come across an fatal error which terminates my database instance. It's basically cropped up twice over a weekend, and the instance needs to be restarted as a result.There doesn't appear to be any entry in the alert log that ties with the time of the failure, so I only have the trace file entries to go on...
CODE*** 2010-07-30 22:15:31.238
kgsksysstop: from KGSKWT_INTSTATECHNG6 retry FALSE mode
kgsksysstop: Failed
kgsksysstop: from KGSKWT_INTSTATECHNG6 retry FALSE mode
kgsksysstop: Failed
kgsksysstop: from KGSKWT_INTSTATECHNG6 retry FALSE mode
kgsksysstop: Failed
kgskrecoveryrecalc: Failed kgsksysstop for 6
kgsksysstop: from KGSKWT_INTSTATECHNG6 retry FALSE mode
kgsksysstop: Succeeded
[code]....
I had a look on my old metalink account to see if there was anything relating to that ORA-56710 error code, but the one article I foun d seemed to be related to RAC instances (which I'm sure that this instance isn't).
I'm using Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production.
View 2 Replies
View Related
Aug 18, 2011
An Unexpected error window like popup is raised when starting Oracle SQL developer.Here I have attached the screenshot of the error.What does the error mean. How to resolve.Below is the error message shown if I click the REPORT button
FeedbackAn unexpected error has occured provide some information that would allow the developer to reproduce the problem
oracle.dbtools.raptor.utils.AbstractFolderedXml:Aug 16, 2011 8:36:56 PM oracle.dbtools.raptor.utils.AbstractFolderedXml newNode
FINE: Unknown Node:1:null................
View 3 Replies
View Related
Sep 17, 2013
solving the error below?
[oracle@localhost Desktop]$ netca
Oracle Net Services Configuration:
# An unexpected error has been detected by HotSpot Virtual Machine:
# SIGSEGV (0xb) at pc=0xa23ae762, pid=7422, tid=3075933888
# Java VM: Java HotSpot™ Server VM (1.5.0_17-b02 mixed mode)
# Problematic frame:
# C [libclntsh.so.11.1+0x429762] snlinGetAddrInfo+0x1b2
An error report file with more information is saved as hs_err_pid7422.log
#If you would like to submit a bug report, {URL}...
/u01/app/oracle/product/11.2.0/db_1/bin/netca: line 178: 7422 Aborted (core dumped) $JRE $JRE_OPTIONS -classpath $CLASSPATH oracle.net.ca.NetCA $*
View 1 Replies
View Related
Nov 3, 2013
I am trying to export a Schema from 11.2.0.2 and getting the below error on export ORA-39127: unexpected error from call to export_string
:=SYS.DBMS_RMGR_GROUP_EXPORT.GRANT_EXP(12425,1,...) ORA-06502: PL/SQL: numeric or value error: NULL index table key value ORA-06512: at "SYS.DBMS_RMGR_GROUP_EXPORT", line 154 ORA-06512: at line 1 ORA-06512: at "SYS.DBMS_METADATA", line 7049 ORA-39127: unexpected error from call to export_string :=SYS.DBMS_RMGR_GROUP_EXPORT.GRANT_EXP(12424,1,...) ORA-06502: PL/SQL: numeric or value error: NULL index table key value ORA-06512: at "SYS.DBMS_RMGR_GROUP_EXPORT", line 154 ORA-06512: at line 1 ORA-06512: at "SYS.DBMS_METADATA", line 7049 ORA-39127: unexpected error from call to export_string :=SYS.DBMS_RMGR_GROUP_EXPORT.GRANT_EXP(12423,1,...) ORA-06502: PL/SQL: numeric or value error: NULL index table key value ORA-06512: at "SYS.DBMS_RMGR_GROUP_EXPORT", line 154 ORA-06512: at line 1 ORA-06512: at "SYS.DBMS_METADATA", line 7049 ORA-39127: unexpected error from call to export_string := SYS.DBMS_SCHED_JOB_EXPORT.GRANT_EXP(2003412,1,...) ORA-01031: insufficient privileges ORA-06512: at "SYS.DBMS_SCHED_MAIN_EXPORT", line 2296 ORA-06512: at "SYS.DBMS_SCHED_JOB_EXPORT", line 52 ORA-06512: at line 1 ORA-06512: at "SYS.DBMS_METADATA", line 7049.
I have searched the forum and found similar errors, but I am struggling to understand the cause and solution.Forums seem to indicate it may be related to bug 4358907, Re: Datapump export/import object grants given by another user but not sure it is relevant as I can see a ORA-01031: insufficient privileges in this log.
View 2 Replies
View Related
Sep 27, 2011
I am having difficulty importing Spanish text files into my Oracle 11g XE database. Below is my external table declaration code. The external file is attached.
CREATE OR REPLACE DIRECTORY xtern_data_dir AS 'C:/.../';
CREATE TABLE ET_SLBLWD0_SPNSH_DESC (
LBL_WARN VARCHAR(4),
LBL_TXTSNS NUMBER(2),
LBL_DESCS VARCHAR2(55),
[Code]...
All 406 rows seem to import fine, but do not display the Spanish accent characters correctly. Note the strange characters:
SQL> SELECT count(*) FROM ET_SLBLWD0_SPNSH_DESC;
COUNT(*)
----------
406
SQL> select * from et_slblwd0_spnsh_desc;
LBL_ LBL_TXTSNS LBL_DESCS L L L L
---- ---------- ------------------------------------------------------- - - - -
0001 1 Puede causar somnolencia. El alcohol puede intensificar
0001 2 este efecto. Tenga cuidado cuando conduzca veh¿los
0001 3 automotores u opere maquinaria peligrosa.
0002 1 Importante: Acabe todo este medicamento a menos que
0002 2 quien le escriba la receta le indique lo contrario.
[Code]...
I tried modifying my external table declaration by adding CHARACTERSET WE8ISO8859P1 and CHARACTERSET WE8MSWIN1252, but that just seems to cause the system to throw out ALL the rows containing Spanish characters:
COUNT(*) ---------- 197
SQL> select * from et_slblwd0_spnsh_desc;
LBL_ LBL_TXTSNS LBL_DESCS L L L L
---- ---------- ------------------------------------------------------- - - - -
0001 1 Puede causar somnolencia. El alcohol puede intensificar
0001 3 automotores u opere maquinaria peligrosa.
0002 1 Importante: Acabe todo este medicamento a menos que
0002 2 quien le escriba la receta le indique lo contrario.
0005 1 Debe tomar este medicamento con mucha agua.
0006 1 Puede descolorar la orina o las heces.
[Code]...
The log file specifies the following error on the bad rows:
error processing column LBL_DESCS in row xxx for datafile ...SLBLWD0_CRLF.TXT ORA-12899: value too large for column LBL_DESCS (actual: 55, maximum: 55)
What can I do to import these Spanish characters correctly?
View 3 Replies
View Related
Sep 24, 2013
In the Employee table i want to display having 4 characters of Emplo_name ,that name's 3rd character must be 'R' How to get it .
View 9 Replies
View Related
Nov 23, 2011
I have an oracle code that is using Oracle External table to load a file.The issue: the column within the files have NA within them which needs to be skipped. Mentioned the code below -
create table lric_arc_fac1_xt
(
cost_category varchar2(30),
component varchar2(30),
fac number(38,18),
lric number(38,18),
marginal number(38,18),
sacs number(38,18),
dlric number(38,18),
dsac number(38,18),
[code].....
View 6 Replies
View Related
May 1, 2011
I have the following bit of code that reads data from the an Oracle table (Note: This is running on Jdk 1.4.2)
ResultSet message = messageStatement.executeQuery(getMsgSql);
String messageData = message.getString("MESSAGE_DATA");
The data in the MESSAGE_DATA column contains text but also control characters that separate data elements in the message (i.e (char)31, (char)29) and (char)28) . What i am finding is that for some reason message.getString() is sometimes truncating the message. I can read the majority of messages but some of them are truncated. Am i supposed to be reading the data using a different method? If so how?
I have tried to use sqlplus to look at the data in the database and it is all there it is just truncated by the message.getString() method. I saw this when i tried to output the result i.e. System.out.println(message.getString()).
I ran an sql query using the length() function in Oracle and length("MESSAGE_DATA") returns 2032 whereas in java message.getString(1).length() returns 2000.
View 5 Replies
View Related
May 15, 2011
i have two questions.
(1) how can i fill some value in a table column based on some existing column value automatically without user intervention. my actual problem is i have 'expiry date' column and 'status'. the 'status' column should get filled automatically based on the current system date. ex: if expiry date is '25-Apr-2011' and current date is '14-May-2011', then status should be filled as 'EXPIRED'
(2)hOw can i build 'select' query in a report (report 6i) so that it will show me list of items 'EXPIRED' or 'NOT EXPIRED' or both expired and not expired separately in a single report based on user choice. 'EXPIRED' & 'NOT EXPIRED' can be taken from the above question no. 1.
View 3 Replies
View Related
May 11, 2012
I am trying to update currency column of one table using the currency column of other table using the following sql code.
update ODS.SO_ITEM OSI
set CURRENCY__CODE=(select currency__code from sa_sales.SO_ITEM SSI where SSI.ID=OSI.ID)
This update is taking taking a lot of time and is never ending.
should i create index on source table (SA_SALES.SO_ITEM) or on target table (ODS.SO_ITEM) ?
View 7 Replies
View Related
Jun 16, 2010
How to add column 'col_new' in below table after the column 'col1'?
Table name: ofq1
table structure.
columnname datatype
------- -------
COL1VARCHAR2(20 BYTE)
COL2NUMBER
COL3DATE
COL4DATE
View 8 Replies
View Related
Feb 22, 2011
if a table contains two columns and both are part of the primary key of that table (Kind of obvoius).
should i opt for a index organized tbale in this case ?Or should i opt for another running sequential colum which would serve as a primary key of this table and define the actual two columns of the system as unique keys.
there is a drawback if a most of the tables of a database contain composite primary keys?
View 3 Replies
View Related
Jul 18, 2012
I am trying to join column names from a table with data from a different table. I think i should be able to pass the parameter to a 'select list' in a query. Look at my sample data below. And the data in sales table can grow till 15 rows and similarly corresponding columns in saleshist.
CREATE TABLE SALESHIST
(
PRODUCT VARCHAR2(30 BYTE),
Q1 VARCHAR2(30),
Q2 VARCHAR2(30),
Q3 VARCHAR2(30),
Q4 VARCHAR2(30)
)
[code]......
View 6 Replies
View Related
Sep 28, 2012
I have a requirement as to find all the tables and columns containing keyword "SELECT" in my database. I followed forum
Re: How to find the column name and table name with a value
But when i run the below query on 11.0.2g Oracle Database
select table_name,
column_name,
:search_string search_string,
result
from (select column_name,
[Code]...
I get
ORA-29913: error in executing contains callout ORA-20000: Oracle Text error: 29913. 00000 - "error in executing %s callout"
*Cause: The execution of the specified callout caused an error.
*Action: Examine the error messages take appropriate action.
View 5 Replies
View Related
Aug 17, 2011
I have two table country and state.Columns are given below
SQL> DESC Country
Name Null? Type
----------------- -------- --------------
PRIMARY_KEY NOT NULL NUMBER
DISPLAY_ORDER NOT NULL NUMBER
NAME NOT NULL VARCHAR2(50)
TREE_POSITION NOT NULL VARCHAR2(250)
PARENT_ID NUMBER
SQL> desc state
Name Null? Type
----------------- -------- -------------------------
PRIMARY_KEY NOT NULL NUMBER
DISPLAY_ORDER NOT NULL NUMBER
NAME NOT NULL VARCHAR2(50 CHAR)
VALUE NOT NULL VARCHAR2(50 CHAR)
TREE_POSITION NOT NULL VARCHAR2(250 CHAR)
PARENT_ID NUMBER
Now I have to insert the values in the state table taking from the country table.
EXAMPLE :
INSERT INTO STATE (PRIMARY_KEY, DISPLAY_ORDER, NAME, VALUE, PARENT_ID, TREE_POSITION) VALUES (251, 1, 'Alaska', 'AK', 233, 'STAT_USUS_AKAK');
but here the tree position is static it can be anything at the client side so I want to make it dynamic. So to make dynamic what I have used is mentioned below:
INSERT INTO STATE
(PRIMARY_KEY,DISPLAY_ORDER,NAME, VALUE,
PARENT_ID,
TREE_POSITION)
VALUES
[code].....
Now I want that Quote: select tree_position from L_STATE_ITEM where name ='United States')||'_ALAL' which i have used in the state table to put value in the tree_position, I want to make the other half part after the concatenation operator "_ALAL" also dynamic.
So I was just wondering If I can pic that value from the "VALUE COLUMN" of the same table.
View 3 Replies
View Related
Dec 29, 2010
How can I add new column at the specified location in a oracle table? I am having a flat file from which data is inserted to the table..But initially I don't need that column so I m not having this column in the table but if further i need that column at the particular location then how can I do this..?
View 1 Replies
View Related
Oct 22, 2010
I have a table ITEM as under;
ARTICLE VRTCODENAMESTATUSNEW_NAME
4KABAN.NM.12.21121143SU19I
4KABAN.NM.12.21564101SU23I
4KABAN.NM.24,5.22491015SR08I
4KABAN.NM.24,5.23181038SR22I
4KABAN.NM.24,5.23491015SR08I
4KABAN.NM.24,5.24121161SR26I
how to update the filed from NAME to NEW_NAME, I need following output.
ARTICLE VRTCODENAMESTATUSNEW_NAME
4KABAN.NM.12.21121143SU19ISU19
4KABAN.NM.12.21564101SU23ISU23
4KABAN.NM.24,5.22491015SR08ISR08
4KABAN.NM.24,5.23181038SR22ISR22
4KABAN.NM.24,5.23491015SR08ISR08
4KABAN.NM.24,5.24121161SR26ISR26
View 2 Replies
View Related
Nov 5, 2012
I have some tables. TABLE1 contains columns called query_id and a column called List_name TABLE2 holds a list of data and the TABLE_NAME = value in TABLE1 held under LIST_NAME TABLE3 contains column with Query_ID and other info.
Is it possible to grab the entry in TABLE1 under LIST_NAME as a TABLE_NAME in a data source?
View 1 Replies
View Related
Dec 3, 2008
I'm having a table which has a column which has values given inside square brackets.
[[[123412]]] ,[[[[werer34]]]],'[[qw2_w3rt]]
Now, the requirement is to get the values which are inside the innermost square brackets.Such as in
First case : 123412
Second one: werer34
Third : qw2_w3rt
Only the values inside are needed. Is there any way to achieve it using Pl-Sql or just using Sql ?
View 3 Replies
View Related
May 17, 2012
i'v a problem regarding dual table,i logged to oracle as sys as sysdba and i add a column to dual table and then want to delete that added column but oracle gives "ORA-12988: cannot drop column from table owned by SYS"
i'm not able to drop any user oracle gives "ORA-00604: error occurred at recursive SQL level 1" "ORA-01031: insufficient privileges"
another error:: SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level oracle gives error when i want to switch user all problems starts when i add that column to dual table
View 1 Replies
View Related
Sep 2, 2009
I want to find out all the tables that contains a known column name?
I know a column name called 'receiving_number', but don't what what table it's in.
View 2 Replies
View Related
Oct 29, 2010
why the dual table has only column?
View 3 Replies
View Related
Aug 30, 2010
How to set default value of particular column of a table ?
View 5 Replies
View Related
Nov 9, 2011
To find out column in which table it is located.
View 1 Replies
View Related
Jul 22, 2010
I have one table which has 90 columns all has varchar2 datatype except one Column[primary key (Number)]. In this Table we have 1000 records, I want to fetch those records from Table which has value in all 90 columns means there is no null value in any column.
I know simple method Like this :-
column_name1 IS NOT NULL AND Column_name2 IS NOT NULL.
Like this we can write IS NOT NULL condition for all column.Is there any other way to write this Query because it makes Query very longer and it is very tedious job to write this Condition for all Columns.
View 3 Replies
View Related
