i have table contains a column of var char type i want to insert a value
'1 mmTHICK GI SHEET 4' X 8' X 1MM THICH' in to the coulmn but m getting error
I tried set scan off but its not worrking for the below query.
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
my query is
Insert into Inventory select
'N1280000015',
'1 mmTHICK GI SHEET 4''' X 8''' X 1MM THICH',
[code]....
INSERT INTO LKP_ASSET_LOCATION (LOCATION) VALUES ('AMERICA'S CUP VILLAGE')
View 2 Replies View RelatedI am loading .csv file into Oracle using sql loader file has strings, and numberics, Strings are surrounded by double quotes(") and field terminated by comma(,)
load data
BADFILE '/var/opt/app/bad/filename'
DISCARDFILE '/var/opt/app/discard/filename'
append into table source_file
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
Some time String fields may have double quotes in it, at that time it is rejecting the records. how to handle those records to load into table.
I want to remove more than one space from a string by excluding double quotes.
For example:
I/P: Item .getChildByType(" Agreement").getParent( ) .hasChildByType("Agreement ")
O/P : Item.getChildByType(" Agreement").getParent().hasChildByType("Agreement ")
Oracle version 11.2.0
OS Linux
I have a table with no primary key constraints with some roles containing null value/duplicates. I then decided to alter the table to add composite primary key constraints on four columns (a, b, c, and d). I did this by using the same script that was used to create the original table but this time adding the not null constraints.
I then took and export of the original table. I now want to import the data to the newly created table but I am now getting the error: ORA-01400: cannot insert NULL into (string).
I will like to perform the import without NULL. Is there a parameter in impdp that I can use? I tried DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS but it didn't work.
Beside options using impdp is there a way to do an insert statement like this insert into table a (select * from table) excluding NULL;?
Basically, I need to load the data into the newly created table without NULL.
I have to convert string 1234567 as 123,456,7 .
note 1234567 is a string.
when i insert string text in hebrew i see ????????? how to fix it ?
work on Oracle 11.2 on Oracle Developer using my C# program
I'm looking for a way to insert strings larger than 40.000 characters in a CLOB-field without geting the "ORA-01461: can bind a LONG value only for insert into a LONG column".
Something like this:
insert into MyClobTable(ID,Data) values ('101','A string containing more than 40000 characters...')
The problem is that a Java-application concatinates the string from a MSSQL-DB so I don't store the string in my oracle-DB. As far as I'm aware this means I can't chop my string in pieces and use declare to put the pieces in variables, right?
Below is an example I found but I don't think I can apply it on my case, correct?
SQL> CREATE TABLE myClob
2 (id NUMBER PRIMARY KEY,
3 clob_data CLOB);
Table created.
SQL>
SQL> INSERT INTO myClob VALUES (101,null);
1 row created.
SQL>
SQL> declare
2 clob_pointer CLOB;
3 v_buf VARCHAR2(1000);
4 Amount BINARY_INTEGER :=1000;
[Code]...
PL/SQL procedure successfully completed.
SQL>
SQL> drop table myClob;
Table dropped.
SQL>
i try to insert Concatenation string to my table,i need that all traps that has 12 length will be insert the new trapnum like this:
for example: 26001005CC45 = 260001005CC0045 ....... 08060027RF05 = 080600027RF0005 ......... and so....
update trap set TrapNum = (
select trim(both from to_char(substr(TrapNum,1,4),'0000'))||
trim(both from to_char(substr(TrapNum,5,1),'00'))||
trim(both from to_char(substr(TrapNum,6,3),'000'))||
substr(TrapNum,9,2)||
trim(both from to_char(substr(TrapNum,11,2),'0000')) from Trap)
where length(Trapnum)=12
but i got error ORA-01427
i have three tables ot_cut_head,ot_cut_det and om_mc_master based on which fourth table ot_cut_opr and fifth table ot_cut_mc must get populated , Conditions are as follows
first one is based on job_no in ot_cut_head the selection criteria will be filtered,if the job number is like '%M' then type MISC will be chosen ,if job number is '%G' then GRAT TYPE will be picked from om_mc_master (Machine Master) and operations and machines based on this will be filtered.
Second all the cd_ps_desc will be taken from ot_cut_det and will be compared with om_mc_master to get their corresponding operation codes and machine codes , there can be 2 operations or 1 operation.
Finally if the match is found record will be inserted into ot_cut_opr and ot_cut_mc ,based on the criterias and what i want is the search criteria to be more flexible and if there are 2 operations 2 rows will be inserted and if one opeation is defined in om_mc_master ,then only one record will be inserted.
We have to make sure that if based on operation number stage will be populated ,if its first operation then stage will be 1 and if its second operation the stage will be 2.like previous operation also depends on them , the second operation will have the previous operation as first operation and so on.
CREATE TABLE om_mc_master ( mc_type VARCHAR2(12),mc_prof VARCHAR2(30),mc_prep_cd1 VARCHAR2(30),mc_mach_cd1 VARCHAR2
(30),mc_prep_cd2 VARCHAR2(30),mc_mach_cd2 VARCHAR2(30));
INSERT INTO OM_MC_MASTER VALUES ('MISC','TEE SCH','IR','HO','RE','HO');
insert into om_mc_master values('MISC','Vertical Brace','R','HM','I','HO');
insert into om_mc_master values('MISC','Pipe','IR','HO',NULL,NULL);
INSERT INTO OM_MC_MASTER VALUES ('GRAT','PL','RE','HO',NULL,NULL);
SQL> SELECT * FROM OM_MC_MASTER;
[code]....
I'm trying to insert records of one table into another using the insert into table with select logic.
I'm trying to convert a two character value using CASE statement:
CASE REC_TYPE
WHEN '00' THEN to_number('0')
ELSE to_number('1')
END "REC_TYPE"
The target field is defined as number(1,0) and the source field is varchar2(2).
I keep getting an ORA-01861 literal does not match format string error.
create table test_g(x date);
insert into test_g values (to_date('01-NOV-2001','DD-MON-YYYY'));
insert into test_g values (to_date('02-NOV-2011','DD-MON-YYYY'));
insert into test_g values (to_date('03-DEC-2012','DD-MON-YYYY'));
insert into test_g values (to_date('12-DEC-2012','DD-MON-YYYY'));
insert into test_g values (to_date('31-DEC-2012','DD-MON-YYYY'));
[code].....
I wrote below procedure
create or replace procedure p_testq(p_in_date in date) is
v_comp date;
v_strg varchar2(200);
i number:=1;
type t_trc is ref cursor;
trc t_trc;
v_sql varchar2(2000);
-- record to which data goes into
type t_prec is record(x date);
prec t_prec;
-- plsql table to store data
type t_frec is table of t_prec index by binary_integer;
frec t_frec;
-- flow of data, is from v_sql --> plsql record --> plsql table
begin
dbms_output.put_line(' month of paramter '|| P_IN_DATE ||' is '||to_char(P_IN_DATE,'MON'));
select min(x)
into v_comp
from test_g
where x <= P_IN_DATE
[code].....
how do I store v_strg values so that the dates are included in single quotes
begin
p_testq('12-DEC-2012');
end;
v_strg: 06-Nov-2012,09-Nov-2012
I want values to be '06-Nov-2012',''09-Nov-2012'
I have a query which does a double right outer join, and I need it to be rewritten using the newer syntax.
The previous query had the following clause:
WHERE
MOD_INFO.MODIFICATION_ID (+)= MOD.ID
AND MOD.PEPTIDE_ID (+)= PEPTIDE.ID
Or you can think of it generically as:
T1.T2_FK (+)= T2.ID AND
T2.T3_FK (+)= T3.ID
How would this be written using the newer syntax?
I am reading Section 4-8 (page 42/216) in the Oracle Database 2-day Developer Guide from here:
[Code]....
It reads:
Suppose that you want to select every employee whose last name has a double vowel(two adjacent occurrences of the same vowel).
Example 4.9 shows how you can do this.
The regular expression ([AEIOU]) represents any vowel. The metacharacter 1 represents the first (and in this case, only) regular expression. The third function parameter, 'i', specifies that the match is case-insensitive.
Example 4.9 Selecting All Employees Whose Last Names Have Double Vowels
SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE REGEXP_LIKE(LAST_NAME, '([AEIOU])1', 'i');
Result is similar to:
FIRST_NAME LAST_NAME
-------------------- -------------------------
Harrison Bloom
Lex De Haan
Kevin Feeney
Ki Gee
Nancy Greenberg
Danielle Greene
Alexander Khoo
David Lee
8 rows selected.
I don't understand how [AEIOU])1 would find a double vowel(two adjacent occurrences of the same vowel).
i'm trying to read a text file into an oracel table. I'm having issues with this statement FIELDS TERMINATED BY ' ' fields are separated by 2 spaces. How ever Oracle is not recognizing ' ' or " " as double space. It is treating as one space thus shifting all coulmns.
How can I represent double space to Oracle ?
I have a issue on running the query with quote operator . When I am executing the SQL query I am getting error "Quoted String not properly Ended".
select q'[Oracle's world ]'
from dual
But The following query works.
select q'[It's Oracle's world ]'
from dual
The description field in the item table has the single quote used as the symbol for feet. I have the same issue pulling from a last name field in other tables. (Like O'Connor)
select descrip into v_result
from c_ship_hist
where shipment_dtl_id = :SDID;
exception when others then null;
The error I get is "Missing right quote". How do I code around this issue without having to change the data?
I have a simple update statement. Sometimes the data in this statement has single quotes in it (like shown below).
Update table1 set account = 'CD'S NOT MINE' WHERE NUMBER = '0027201'
When I run this SQL, I get SQL Error: ORA-01756: quoted string not properly terminated
01756. 00000 - "quoted string not properly terminated"
Is there an escape charecter that I can use?
which is the better option to use when the quote needs to be concatenated to a varchar2 value i.e.
In order to insert 'test' into a column exactly as yet see I used:
chr(39)||'test'||chr(39)
However, the DBA (OCP) says that is not a good way to do it and should be changed to:
''''||'test'||''''
declare
v_a varchar2(2000) := 'abcd';
v_e varchar2(2000) := '6666';
v_d varchar2(2000) := 'example';
v_final varchar2(4000);
begin
v_final := '"v_a"'||'''|'''||'"v_e"'||'''|'''||'"v_d"';
-- v_final := '"v_a"';
dbms_output.put_line('v_final: '||v_final);
end;
/
above gives me :
v_final: "v_a"'|'"v_e"'|'"v_d"
so it is printing the variable names, But I want to see values, like this: "abcd"|"6666"|"example"
I have table say Messages. In which there is a column msg_text varchar2(900).My requirement is to fetch the very last character of the msg_text for a single row identified by its msg_code(primary key).
The problem is, whenever msg_text contain second last character as single quote( ' ), it doesn't give me the last character i.e. after the single quote.For example if msg_text is "Congratulations, you opted for 'A'." and if its message_code is 10 then query
SQL> SELECT SUBSTR(msg_text,LENGTH(msg_text),LENGTH(msg_text)) AS LAST_CHAR
FROM messages
WHERE msg_code = 10;
returns nothing.
Whereas if msg_text is "Are you sure to continue?" and if its message_code is 20 then query
SQL> SELECT SUBSTR(msg_text,LENGTH(msg_text),LENGTH(msg_text)) AS LAST_CHAR
FROM messages
WHERE msg_code = 20;
returns character '?'.
I need to write a procedure to be able to reuse it to clean up subcategory sequence. Here is a problem: two tables: Category and subcategory:
create table Category
(category_id number,
Name varchar2(20))
/
create table Sub_Category
(sub_category_id number,
category_id number,
sequence number)
/
In the ideal world each subcategory of a single category would have unique sequence so if there are 3 subcategory for the same category then each of them would have 1,2,3 in sequence, if there are 5 subcategories then 1,2,3,4,5 for each of them etc.
Problem I'm facing is that some of the subcategories sequences for the same category has the same values . For instance for 4 subcategories of the same category, each of them has 1 (1,1,1,1) in a sequence.
So ideal world is :
Insert into Category values (123 ,'Category1');
Insert into Category values (234 ,'Category2');
Insert into Category values (345 ,'Category3');
Insert into Category values (456 ,'Category4');
Insert into Category values (567 ,'Category5');
Insert into Sub_Category values (1,123,1);
Insert into Sub_Category values (2,123,2);
Insert into Sub_Category values (3,123,3);
But I've also bad rows like this:
Insert into Sub_Category values (4,234,1);
Insert into Sub_Category values (5,234,1);
Insert into Sub_Category values (6,234,1);
Insert into Sub_Category values (7,345,1);
Insert into Sub_Category values (8,345,1);
[code].....
Fix for this and my goal is to select all such cases where subcases have mixed up sequences as above and give them randomly numbers starting from 1. So if there are 3 subcategories like for CATEGORY 2 then just apply random number to the sequence of the subCATEGORIES like 1,2,3. For CATEGORY 3 : 1,2,3 to 7.
I was thinking to write two procedures one selecting all the categories and passing category ID to the other procedure that would actually update sequence, like this:
CREATE OR REPLACE PROCEDURE SCHEMA.SELECT_CATEGORY
IS
CURSOR c1
IS
select category_ID from category where ...;
BEGIN
FOR a IN c1 LOOP
UPDATE_SUBCATEGORY(a.Category_id);
COMMIT;
END LOOP;
END;
/
And the actual procedure updating subcategory:
CREATE OR REPLACE PROCEDURE SCHEMA.UPDATE_SUBCATEGORY
BEGIN
............
END;
/
write PROCEDURE SCHEMA.SELECT_CATEGORY cursor to not miss any of the categories ID having mixed up subcategory. There can be any of the doubled sequences like doubled 1 value (this is majority) but there can be any other doubles (or at least I need to make sure that there aren't any other doubles 2 values or 3 values in sequence etc.)
And how to write SCHEMA.UPDATE_SUBCATEGORY to loop through rows of subcategory and update sequence with values starting from 1 ?
I have oracle 10g database running on Sun Solaris. My database character set is AL32UTF8. Our user wants to store a double byte character and an image, I want to know what the column type should be using for these.
View 2 Replies View RelatedI support old oracle code and we currently print using the set report object property.
I recently was requested to modify all of our printing to two-sided printing from the current one page per sheet.
I am trying to figure out if there is a parameter I can set, i.e.REPORT_OTHER or something else that would allow me to do this.
create or replace procedure ab(a in varchar2, b in varchar2)
is
test varcha2(8);
begin
if (a is not null) then
for i in(select c
from t
where c between ||'''||a||'''|| and ||'''||b||'''||)
loop
test:=i.c
end loop;
end if;
end;
I want both parameter input values to be enclosed in quotes so that it considers both parameter values as char.Receiving ora 00936 missing expression error.
This package is generating excel file which contains cursor result.In excel data is populated like below.Column name is Zip_code .My concern is how to remove that single quote from excel file.
eg:
Zip_
'01234
'12567
'23432
'00234
create or replace
PACKAGE BODY PKG_MONTH_END_AUTOMATION AS
PROCEDURE PROC_ZIP_CODE_MONTHEND (directoryOrPath IN VARCHAR2 default 'LOC_PHASE1_WHOUSE_SALES_ADMIN')
[code]...
-- main body
BEGIN
-- Generating Zip Files
SELECT last_day(add_months(sysdate,-1))
INTO v_last_date
[code]...
Any way to replace the Double Quotes used to enclose column names with an alternative character. This is the SQL I have now that Works!
select (case when CUST is null then "/BIC/Z_SUPPLNT" else NM1 end) CMPNTSUPSRCE
from TBL1, TBL2
where "/BIC/Z_MAJVEND"=CUST(+) and Material = '1ABCD456'
order by Material
But would like to do something along these lines below but keep getting error "ORA-00936: missing expression".
select (case when CUST is null then chr(34)||/BIC/Z_SUPPLNT||chr(34) else NM1 end) CMPNTSUPSRCE
from TBL1, TBL2
where chr(34)||/BIC/Z_MAJVEND||chr(34)=CUST(+) and Material = '1ABCD456'
order by Material
I wish to replace the string "Benutzername&Kontakt" with nothing.
SELECT REPLACE (role_owner, 'Benutzername&Kontakt', '')
FROM JC_NAME_62
WHERE jc_name LIKE 'SAP_R4_BIN5\_%' ESCAPE ''
But my Toad prompts me always to fill in the variable. I don't wish to do that, I wish to escape the &
I am having a similar problem like above ONLY in UNIX box where my datafile is delimited by "|". The last field is ITM_CMNT declared as VARCHAR2(60) in Oracle. When I have exactly 60bytes in the last field it rejects the record saying actual 61 and max allowed is 60. If i reduce it to < 60bytes then it is stored as a value enclosed with double quotes. The enclosing double quote is on the next line.
"PROC,RAM,FLPY,HD,ACT MTX CLR DSP,D/PCMCIA,TRKBAL,LIT ION BA"
Expected: the one below is exactly 60bytes.
PROC,RAM,FLPY,HD,ACT MTX CLR DSP,D/PCMCIA,TRKBAL,LIT ION BAT
LOAD DATA
INFILE *
INTO TABLE TMPTLI_LAWSON_ITM_MST
TRUNCATE
FIELDS TERMINATED BY "|"
(ITM_NO, HAZ_MAT_CD, ITM_SHRT_DS, ITM_SON "TRIM(:ITM_SON)", ADDED_DT DATE "YYYY-MM-DD",
AVL_CD , ITM_CST_AMT, ITM_SLL_AMT,
EXCHG_PRC_AMT , ITM_UOM "TRIM(:ITM_UOM)",
PCK_QTY INTEGER EXTERNAL, SPC_HNDL_CD "TRIM(:SPC_HNDL_CD)", EFF_DT DATE "YYYY-MM-DD",
ITM_CMNT "TRIM(:ITM_CMNT)")
I want to replace double space with single space and also remove junk characters from the data. How can I do that?
CREATE TABLE test07013
(
NAME VARCHAR2(50)
);
INSERT INTO VALUES ('WARREN,'); -- REMOVE ","
INSERT INTO VALUES ('CLARK H'); -- REPLACE "DOUBLE SPACE" WITH "SINGLE SPACE"
INSERT INTO VALUES ('BRYAN A.'); -- REMOVE "."
INSERT INTO VALUES ('CARTER JR. ROBERT'); -- REMOVE "."," AND REPLACE "DOUBLE SPACE" WITH "SINGLE SPACE"
