SQL & PL/SQL :: Insert Query Having Strings As A Column
Dec 13, 2012
tyring to insert an insery query having string as a column where i am supposed to insert a single quote casuing the problem.
insert into abc(x,y) values (1,'select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh='self' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij')
getting missing comma with the above query.when i tried to give as
insert into abc(x,y) values (1,'select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh=''self'' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij')
insert is happening but saving as "select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh=''self'' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij"
how to avoid this and get the select query to store as
select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh=''self'' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij
I'm trying to do a pivot query in oracle to get the years from a column and make a separate column for each. I found an example of the code to use on the internet and i changed it for my own tables but i'm getting errors. Namely a "FROM keyword not where expected" error at the beginning of the 'avg(...' statements.
I have copied the code used in
select stud_id, 2006, 2007, 2008, 2009 from ( select stud_id, avg(case when year=2006 then ((present/poss)*100) else null end) 2006, avg(case when year=2007 then ((present/poss)*100) else null end) 2007, avg(case when year=2008 then ((present/poss)*100) else null end) 2008, avg(case when year=2009 then ((present/poss)*100) else null end) 2009 from attendance.vw_all_attendance_perc group by stud_id );
We are using Oracle 11g with Apex 3.2 on AIX. We are reporting data from customer satisfaction surveys. I'm using the following sql to create my report
<code>select * from( select month,'Overall Satisfaction' as q_group, 1 as srt,Overall Satisfaction,site, case when count(*) < 31 then '*' else round((sum(ttos)/count(*))*100,0)||'/'|round((sum(bfos)/count(*))*100,0)||'/'||count(*) end ospct from v_XXX_report a,(select distinct month_dt month from v_XXX_report) b where Overall_Satisfaction is not null and year_dt = 2012
[Code]....
The problem is that site is not allways present and sometimes I have other variables in addition to site. This creates a situation where the month columns will not allways appear at the same column number. For example, When I run this query as is then the "JAN" column is Col3 (first column is a break, col2 is not shown). When I run this query without site then "JAN" is the second column. I would like to create column links for the "JAN" - "DEC" columns but not for any other columns.
Another issue - in the column link creation screen I can create up to 3 variables that I can pass to the next page. Since my query is a pivot I'm uncertain how to pass the column heading or the row value (for col2)
ie Overall Satisfaction JAN FEB MAR APR MAY ... Overall Satisfaction 12/12/200 12/12/210 12/12/220 12/12/230 12/12/240... Recommend 12/12/200 12/12/210 12/12/220 12/12/230 12/12/240... etc.
So if I clicked on the values at Recommend:FEB how can I get "Recommend" and "FEB" into variables that I can use on the next page? I've tried #column_name#, #month#, #q_name# and #APEX_APPLICATION.G_F10# but no luck.
I have a table with a column of type blob. Now i want to create a procedure which will insert into that table. But I don't like to create a directory. How can i solve this.
I want to insert a row like this:
insert into table x(image) values('d:photo est.jpg');
INSERT ALL INTO test_abc VALUES (1,12345,34567,87654) INTO test_abc VALUES (2,17345,37567,87754) INTO test_abc VALUES (3,12745,34767,87674) INTO test_abc VALUES (4,17045,30567,80754) INTO test_abc VALUES (5,12740,34067,87604) SELECT * FROM dual;
CREATE TABLE test_b (id NUMBER, col VARCHAR2(10), coltype_id NUMBER);
What I need to do is to convert the columns col1, clo2, col3 in test_abs into rows in test_b. but I need to do this so that the column name is what determines the value of colytype_id.So in test_b values will look like:
I want to store a pdf file into a database column of BLOB type. The pdf file on the client system not on the database server. Is there any way i can achieve this?
i have a problem when i try to insert a large character string of nearly 1 lac characters (code of html) in a clob column of my test table, then i get an error "ORA-01704: string literal too long" , i didnot understand that why clob column is not storing this data.
I have encountered some problems in SQL I want to create a table with a bunch of prepared data. For ease of use, I choose to generate a SQL file which contains all the sql clauses used to create the table and insert the data. So all the data can only be inserted to a table using sql clause.
My questions: 1) If data of a column is large (for example, 1 M text), how to insert it using SQL, is there a piecewise method. 2) And how can I insert BLOB data using SQL clause.
What I what is to enclose all the operations in a single SQL file, and when the table is needed, just execute this SQL file.
I am trying to insert an image files into a blob column using the following code.
CREATE TABLE MY_IMAGE_TABLE ( ID NUMBER, NAME VARCHAR2(20), IMAGE BLOB); CREATE OR REPLACE DIRECTORY MY_FILES AS '\dppdb-dev est'; GRANT ALL ON DIRECTORY MY_FILES TO PUBLIC;
[code]....
However, I am getting the this error
ORA-22288: file or LOB operation FILEOPEN failed
I've done some tracing on toad and it seems that the file am trying to insert exists yet am unable to open it.
I have a table A on dev with definition as TAble A(address,name) and the same table on Prod is defined as Table A(name,address).
my question is Ihave one package in that am trying to insert into this table as follows:
INSERT INTO A SELECT b.name name, a.address address,
[Code]....
so the query works on Prod but fails on Dev because column order is different.
I have 2 solutions:
1. I can mention column names in insert line and modify the query but tomorro some body changes again the definition of table A I need to change the query, so do I have solution in oracle sql that can handle the column order without specifying the column names in insert line.
so tomorrow On prod column order and on Dev column order is different though my sql should successfully execute.
I have requirement wherein i need to compare two strings (with multiple words) and it should return the %(percentage) of comparison. e.g. "oracle infotech" and "infotech oracle" are 100% match
I have to fetch a string which is between to constant strings in a column.
Ex: Test Column "The Student Record 10101 is deleted" "The Student Record 10102 is deleted" "The Student Record 10103 is deleted" 3 rows.
In this i need to fetch only ID from each row.
create table testtable ( TestCol varchar2(4000));
INSERT INTO TESTTABLE VALUES ('The Student Record 10101 is deleted'); INSERT INTO TESTTABLE VALUES ('The Student Record 10102 is deleted'); INSERT INTO TESTTABLE VALUES ('The Student Record 10103 is deleted');
PROCEDURE COLUMN_SPLIT (p_def IN VARCHAR2, p_sch OUT VARCHAR2, p_table OUT VARCHAR2, p_column OUT VARCHAR2) IS BEGIN NULL; END; END;
I want to split p_def by dots, check for 3 elements, and return them in p_sch, p_table and p_column for example p_sch will be like hello.howare.you.I want to split it to hellohowareyouI have very limited knowledge with pl/sql.
i used sql loader to import data from csv file to my db.but every time the columns places are changed.o i need dynamic way to insert data into correct column in the table.
in csv file contains column name and i insert this data to temp table, after that i want to read data over column name.also i read the column names from (All_Tab_Columns) to make combination of column name between temp table and All_Tab_Columns table to insert data to right place...
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
select * from (select pa_request_id ,max(status) status ,max(approved_amount) approved_amount ,min(level_id) level_id ,max(req_amount) req_amount from target_aggregation_attendee group by pa_request_id) where status = 'Approved')
My Main Query:
select TARGET_EMPLOYEE.FIRST_NAME||' ' ||TARGET_EMPLOYEE.LAST_NAME as Requestername, TARGET_EMPLOYEE.GE_ID as requesterGEID, TARGET_ATTENDEE.FIRST_NAME||' ' ||TARGET_ATTENDEE.LAST_NAME as AttendeeName, TARGET_ATTENDEE.ATTENDEE_TYPE_FLAG as Attendeetyflg, TARGET_ATTENDEE.US_GO_ATTENDEE_FLAG as usgoflg, TARGET_ATTENDEE.COMP_GOVT_AGENCY_DEPT as Atcomp,
The db field is a string-type field that hold strings such as:
'1234' '753' 'textstring' '345'
Obviously, if you sort it, it'll be stored as a string such as: '1234' '345' '753' 'textstring'
My client wants it so the numbers sort as integers, followed by string-like strings (sorted alphabetically), so it's like. '345' '753' '1234' 'textstring'
Is there a quick and dirty SQL-only way to doing this in Oracle?
There could be anything after the 2nd ~ in string 2 is there a easy way of trimming string2 to the first 14 Characters? Or do I have to find the 2nd instance of ~ and then remove everything after (and including) that?
In the code segment below (hope it appears right) I can understand the use of single quotes in the first two examples but in the third example below I had to use double quotes around the word - Today's - and I not sure I understand why?! I'm aware of the rules ...If you want a single quote to appear in the middle of a string add another single quote to it.If you want a single quote to appear at the beginning or end of a string add 2 single quotes to it.If you want a single quote to appear on its own add 3 single quotes to it.
SQL> select 'This isn''t' from dual; 'THISISN'' ---------- This isn't SQL> select to_number('34@456#789', '999G999D999', 'nls_numeric_characters=''#@'' ') from dual; TO_NUMBER('34@456#789','999G999D999','NLS_NUMERIC_CHARACTERS=''#@''') --------------------------------------------------------------------- 34456.789 SQL> select to_char(sysdate, 'fm"Today''s" ddth Month YYYY') from dual; TO_CHAR(SYSDATE,'FM"TODAY''S"DDTHMONTHYYYY') ------------------------------------------------------ Today's 16th August 2013
extract the value of the strings for REQUEST_GROUP_CODE and REQUEST_GROUP_APPL_SHORT_NAME. As you can see I have to deal with whitespace differences, case differences etc. I need the values between the quotes for each one. get ACCOUNTING and AR for the first example. I am using db version 11g r1.
WITH TEST AS (SELECT 'REQUEST_GROUP_CODE="ACCOUNTING" REQUEST_GROUP_APPL_SHORT_NAME="AR" TITLE="AR:AR_SRS_TITLE_ACC_RPTS"' str FROM dual UNION ALL SELECT 'REQUEST_GROUP_CODE = "PRINT_CONSINV"REQUEST_GROUP_APPL_SHORT_NAME = "AR"TITLE =
that I need to insert into Child using seq_parent but I want to insert the same sequence for each group of rRef. I dont know how to do that using SQL not PL/SQL.
When i run a script that does a select from a single table (table has 33521868 records)the query is executed in about .094 seconds. I use the exact same query to insert into a temporary table and the query takes 10 minutes and more.
What should I be doing to speed up this process. Also tried using hints and it does not speed up the insert.
