SQL & PL/SQL :: Converting Varchar To Clob Within SQL Union?
Feb 14, 2013
Why does oracle not allow the following query
select to_clob(1) from dual
union
select wm_concat(sysdate) from dual;
wm_concat returns a clob. To make both queries in the union have the same type columns i convert the column in the first query to a clob but Oracle gives me an [1]: ORA-00932: inconsistent datatypes: expected - got CLOB error even though both are returning a clob value.
Each of the queries work individually and both return a clob value.
View 4 Replies
ADVERTISEMENT
Feb 6, 2013
converting BLOB data into varchar2 or long .
we have function which convert long data and return it has varchar . But has part of Apps upgrade the Column has been converted into blob column.
How we need the same function to read the data from BLOB and return its as long or varchar2.
Somewhere i am making mistake..
CREATE OR REPLACE function GDS.test_alert_msg(v_rowid rowid) return varchar2 is
vblob blob;
i2 number;
amt number :=32767;
len number;
pos raw(32767);
position INTEGER := 10000;
my_vr raw(32767);
[Code]....
View 2 Replies
View Related
Aug 16, 2012
I want to convert a clob datatype to varchar data type.I have a clob data type in the below format.
-----------------------------------------------------------------------------------------
<HTML><HEAD><TITLE>Tata Communications Limited</TITLE><BODY leftMargin=20 topMargin=10 rightMargin=20 marginheight=10 marginwidth=10><Table border="0" cellpadding="0" cellspacing="0"
-----------------------------------------------------
I want to insert all the values into varchar datatype.provide the sample code how to convert.
View 1 Replies
View Related
May 8, 2013
how to convert existing table with varchar column to a clob
View 1 Replies
View Related
Jun 15, 2012
We are able to insert CLOB into database, Using oracle Text I'm able to search inside clob. [two questions solved ]. The question arise when we need to send this data to application either as file or as text(varchar2).
I'm able to generate file from CLOB using function,unfortunately it resides inside db and developer is not able to access it.
1)There is option to mount application partition inside db and export file over there but it is not viable option according to management.
2)I've Googled the solution to create JAVA API. Which will perform OS command like scp to send file from db to app(or any remote host). For security reason this option is also dropped.
3)I tried dbms_lob.substr but actual text inside clob is too long. File generated from CLOB sized around 5 MB.
So I guess it requires lot of effort if I wanted to convert into varchar2 as out parameter inside function.
So, to give CLOB data to developer as file or as varchar2. I'm not able to get any solution. Is there any other option using database to convert to string/varchar2 from CLOB ? Or do I need to drill down more into third option.
I'm having oracle 10.2.0.5.
View 2 Replies
View Related
Jul 31, 2012
I am more of a C/C++ guy and relatively amateur in oracle. I have to update a table field from "Long" to "CLOB". I have planned to do a simple alter table, and as far as I know there won't be any issues.
Queries:
1. Although I have triple checked, is there any scenario under which there can be any data loss during the data type change? The data is very critical and no data loss can be entertained.
2. Is there any easy way to update all the related views without having to do so manually?
3. Any particular precautions I should take before introducing the change?
View 2 Replies
View Related
Apr 22, 2010
Is it better to use UNION (or) UNION ALL with DISTINCT,Which one will improve performance.
Query1:
SELECT deptno FROM emp
UNION
SELECT deptno FROM dept
Query2:
SELECT DISTINCT * FROM(SELECT deptno FROM emp
UNION ALL
SELECT deptno FROM dept)
I mean to say query1 is better (or) query2 is better,which query improves the performance.
View 1 Replies
View Related
Feb 26, 2012
I have one command compare varchar type with date type by Pl/SQL.
date_entry gave values:" 2012/01/06 22:28:24",
now i want to convert it to date and compare with sysdate.
Select * From qvsystem
Where to_char(to_date(date_entry, 'rrrr-mm-dd hh24:mi:ss'), 'dd-mm-rrrr') = to_char(Sysdate, 'dd-mm-rrrr');
----
note:date_en2012/01/06 22:28:24
after I executed that command It's appear one message .
View 7 Replies
View Related
Aug 30, 2012
display or get values stored in a long column.i tried the below code, but myvar is shown as null. am i missing something here or is there a better apporoach than this ?. There are actually 16 rows returned for this query but with empty values for 'high_value' column which is critical for me. How can i do this long to varchar convertion ?
SET SERVEROUTPUT ON
DECLARE
my_var long;
BEGIN
for x in ( SELECT high_value
FROM all_tab_partitions
WHERE table_name = 'SALES_DISCOUNT'
AND table_owner = 'CED12'
ORDER BY partition_position DESC )
[code]....
View 4 Replies
View Related
Dec 7, 2011
how to create sequence to varchar and how see that sequence
View 2 Replies
View Related
Feb 12, 2011
I've a large table on which I applying aggregate and group by functions to the the results.
Here are two of the columns in my table:
Name ==== Score
John ==== 200*
Zohaib ==== 299
Ali ==== 0*
John ==== 200
Maria ==== 150*
Ali ==== 0
Maria ==== absent
John ==== absent
Here astrick (*) means with distinction....
The "score" column is a varchar column I want to run a query on this table to show the highest score for each student and the output should be like this:
Name ==== Score
Zohaib ==== 299
John ==== 200*
Maria ==== 150*
Ali ==== 0*
Important note:
1. if there is a tie between two highest scores like for a student, incase of john 200 was made twice, but the score with asterick (*) will be the "maximum" becuase it is with distinction so the output should also show the the highest score with asterick.
2. the output should show the the 2nd column (score) in desc order of highest score by each student...again incase of a tie, the one with astrick will come first in the result..
I know with just mere numbers, that is pretty easy but in this case it is a varchar column and also i need the astrick along with the highest score.
I want the simplest and shortest query if possible to achieve this result
I hope I've been able to clearly explain my requirment. I am using 10G.
View 1 Replies
View Related
Aug 31, 2012
What is the exact difference between varchar and varchar2?
As i know only the length is the one difference.Apart from this length,what are all the differences?
View 3 Replies
View Related
Feb 3, 2011
I have a varchar field in a table. When I store the following string, some undefined character is storing.
String to be stored: Test String
String actually stored: Test ¿ string
Please note that here '' is not Hyphen. It is a special character that msword has. when you copy this and paste in a word document you find the difference.
I have listed the character set for CHAR and NCHAR from NLS_DATABASE_PARAMETERS
NLS_CHARACTERSET - WE8ISO8859P1
NLS_NCHAR_CHARACTERSET - AL16UTF16
View 12 Replies
View Related
Mar 22, 2012
I see you can build an address object consisting of street name and number, city and so on, and use this in CREATE TABLE, so all tables use the same fields for addresses. And moreover you can add check functions to prevent street numbers to be negative for instance in the constructor. You can even use inheritance. I like that.
However, all teaching on this subject I found on the Internet starts with structs (points consisting of x and y, persons consisting of name and address, etc.). Then they go to inheritance and show how to build an employee who is just a person plus an employee number. They never start with simple types.
What I would like to do would be rather to start with basic types such as number or varchar2 and inherit from them. An EAN is a VARCHAR2 holding only digits where the last digit is a check sum that must be matched. An item number may be a VARCHAR2 matching 'NNN-AAAA'. A simple string may be a VARCHAR2 where no functional characters such as carriage return, etc. occur.
I would then write my create table statement thus:
CREATE TABLE item (itemno T_ITEMNO, ean T_EAN, itemname T_SIMPLESTRING); and inserts like INSERT INTO item values('123--BCD', '1234567890abc', 'toy') would fail with exceptions like 'item numbers must be formatted NNN-AAAA' or '1234567890abc is not a valid EAN'.
However this seems not possible:
create or replace type T_EAN under varchar2
(
constructor function T_EAN(p_string in varchar2) return self as result
);
fails with "PLS-00580: supertype must be an object type".
Rather than creating completely new types, I would start with extending existing types, but this seems not possible. Is that really impossible or am I using the wrong approach?
View 4 Replies
View Related
Dec 21, 2010
The below table and functions are examples, but issue is like this.
i have one issue while forming below sql , Application will form this query at run time wih required inputs
it is failing because the application will not support the clob datatype.
Table : T_E (similar to EMP table structure)
This table has data similar to emp but bulk data around 10k records.
Query formed
select empno,ename, get_employees(deptno)
from t_e;
This sql query this is failing when function return varchar2 string more than 4000 size.Because in sql query size should not exceed 4000 for varchar2 data type , function return size can be upto 20000
Function which
CREATE OR REPLACE FUNCTION get_employees (p_deptno in emp.deptno%TYPE)
RETURN varchar2
IS
l_text varchar2(32767) := NULL;
BEGIN
FOR cur_rec IN (SELECT ename FROM t_e WHERE deptno = p_deptno) LOOP
l_text := l_text || ',' || cur_rec.ename;
END LOOP;
RETURN LTRIM(l_text, ',');
END;
I can try this with clob but application doesn't support that..so i tried with using clob in above function , after that while returning to application i want to conver the clob to char and return the entire string
select empno,ename, to_char(substring(get_employees(deptno),1,4000)) -- first 4000 characters
, to_char(substring(get_employees(deptno),4000,8000)) -- next 4000 characters
from t_e;
But this is failing how to return the entire string in above sql
View 1 Replies
View Related
Feb 18, 2013
Today we faced one strange issue Varchar doesnot print the zero front of number
eg
SQL> select agent_code from table_name where AGENT_CODE = 0061;
AGENT_CODE
----------
61
61
where in my database agent_code columns as value 0061 and 061 for both it shows same value.
View 9 Replies
View Related
Mar 13, 2013
How to get max and min value from a varchar2 datatype column?
CREATE TABLE TEST
(
WEIGHT VARCHAR2(20)
);
INSERT INTO TEST VALUES('100');
INSERT INTO TEST VALUES('120');
INSERT INTO TEST VALUES('113');
INSERT INTO TEST VALUES('145');
INSERT INTO TEST VALUES('204');
INSERT INTO TEST VALUES('130');
I've to find the max and min weight from this data.
View 2 Replies
View Related
Nov 14, 2011
We have server oracle 11 , running on UNIX . we have table with a lot of columns - varchar2(4000)
My question
when data comes from server to client? what actually comes for column varchar2(4000) ?
for example value of columns 'abc'
does it mean that from server to client will be sent buffer with size 3 char ?
or
it will be sent buffer 4000 characters and one more value shows how big is variable inside buffer ( for this case it will be 3 )
View 5 Replies
View Related
Mar 22, 2010
I need to separate 1 field into 2 fields. The source is varchar2 and is like:
[VOUCHER]
CGJ0000617
CG0001442
CGJ0001444
CMOV0000200
CXAR00000001
CXAR00000002
Result should seperate numeric value from characters so that the result would be:
[VOUCHER_char] [VOUCHER_num]
CGJ 0000617
CG 0001442
CGJ 0001444
CMOV 0000200
CXAR 00000001
CXAR 00000002
I need one or two SQL statement.
View 9 Replies
View Related
Jun 28, 2011
I have procedure that is used by trigger but I define it as Varchar2 it's result show number.
change my PLSQL to use as varchar,
If trigger fired, the procedure make this query
select CB_SMS_FLAG, CB_SMS_DT from TM_SFS_CUST_04 where CUST_NUM = 01071234151;
but I want this query
select CB_SMS_FLAG, CB_SMS_DT from TM_SFS_CUST_04 where CUST_NUM ='01071234151';
because CUST_NUM is VARCHAR2(12)
so it can use index appropriately.
how can I change my PLSQL this sentence,
v_sel_sql1 := 'select CB_SMS_FLAG, CB_SMS_DT from ' || pTBL_NM ||' where CUST_NUM = ' || pCUST_NUM;
Here my code
CREATE OR REPLACE PROCEDURE ORAASFS.P_TM_SFS_CB_SMS_CNT_upd_104(
gKIND VARCHAR2,
pCUST_NUM VARCHAR2,
pSMS_CNT NUMBER,
pSMS_CLC VARCHAR2,
pRD_FLAG VARCHAR2,
[Code]....
View 4 Replies
View Related
Oct 5, 2010
i have a table COLOR having alphanumeric fields i.e Color Description & Color Ref. i want to copy the values from Color Description to Color Ref.
what will the select statement for this?
View 9 Replies
View Related
Feb 13, 2007
know if there's a built-in function to covert an Oracle CURSOR to VARCHAR? Or how about a XMLType to VARCHAR?
View 1 Replies
View Related
Aug 16, 2013
I have a table and data like mentioned below.
create table emp( ename varchar2(20));
insert into emp values ('122');
insert into emp values ('abc');
insert into emp values ('0.2');
insert into emp values ('0-5');
insert into emp values ('25-30');
SQL> Select * from emp;
| ENAME |
-------------------
| 122 |
| abc |
| 0.2 |
| 0-5 |
| 25-30 |
I am running the code
SQL>select regexp_substr(ename , '^[[:digit:]]+.[[:digit:]]+$|^[[:digit:]]+$')
from emp;
AFTER RUNNING I AM GETTING THIS
| REGEXP_SUBSTR(ENAME,'^[[:DIGIT:]]+.[[:DIGIT:]]+$|^[[:DIGIT:]]+$') |
---------------------------------------------------------------------
| 122 |
| (null) |
| 0.2 |
| 0-5 |
| 25-30 |
| (null) |
Why it's not excluding '0-5' and '25-30', how I should write code to exclude this and Is there is any function in oracle to check for numeric in column and print.
View 4 Replies
View Related
Jul 17, 2012
Have table with two columns with datatypes as number and varchar and the values in A column like 1,2,3 AND B column values like a,b,c. Now need to display data in a single column as 1,a,2,b,3,c.
View 4 Replies
View Related
Feb 5, 2011
select numeric values from a varchar column
For Example:
select * from t1 ;
ID
----------
00300
ABCXY
04230
xyzab
i need to fetch only numeric values from column id
My output should be
00300
04230
View 8 Replies
View Related
Jun 11, 2012
So this statement works:
SELECT column_name BULK COLLECT INTO table_column_list FROM all_tab_columns WHERE table_name = conv_tablename AND OWNER IN (SELECT USER FROM DUAL);
However I want to add conditions like " AND column_name <> 'abc' " at the end. So i tried to store this in a varchar and call EXEC on it:
select_column_value_sql_stmt := 'SELECT column_name BULK COLLECT INTO table_column_list FROM user_tab_columns WHERE table_name = '''|| conv_tablename || '''' || inexclude_column;
EXECUTE IMMEDIATE select_column_value_sql_stmt;
No matter how I concatenate the conv_tablename part, i get an error. either invalid identifier or unimplemented feature.
so is there a way to do this at all?
View 3 Replies
View Related
Apr 12, 2011
calculating values of A & B
(both these fields a_std and a_time are coming as varchar from the parent table in a cursor.(basically they are time period and actual arrival time respectively)
i was juggling with the attempt to make varchar to timestamp or date..but caught with Round up /Round down)
Formula ->
A = Round down [A_TIME - A_STD]
B = Round up [A_TIME) - 10 minute + A_STD]
where
A_TIME VARCHAR2(8) N Time (Format" HH:MM AM/PM") eg "3:50 PM"
A_STD VARCHAR2(5) N Standard time (Format" HH:MM") eg "1:00"
Allowed values for A & B after round up/down = multiple of 10 ( 11:00,11:10,11:20 etc.)
View 10 Replies
View Related
Jul 4, 2013
I would like to know if using varchar parameter in sql queries with number column can result in performance degrade.
Ex: Procedure testa ( myparam varchar) is
begin
select col1 into var1 from table where colno = myparam;
end;
Here col no is a number column and myparam is varchar. I feel its better to change the parameter to number.
View 7 Replies
View Related
Aug 16, 2007
i have 2 tables
A B
id a.id
name
type
There are at most 2 entries of a in b. Depending on the value of the type column in B, this determines whether the entry should be male or female. I want to have a select statement that will retrieve 2 rows into one row essentially like below, how is this done:
id male_name female_name
1 paul paula
the column names will appear as such, if its a 0 its a male name if its 1 its a female name, there will generally be 2 entries in B for 1 value of a.
View 2 Replies
View Related
Nov 12, 2012
is there any difference between
- returning from the procedure 2 ref cursors containing result set of 2 queries
- returning from the procedure 1 ref cursor containing result set of that 2 queries as one (with UNION ALL)?
Will 2nd option be faster or similar to 1st?
View 1 Replies
View Related
