i am trying to calculate the avg size of a row, in which i have one column which has clob datatype,
for rest of the datatypes i am using (avg(vsize(col_name,varchar2(10)))), i want a simple function that can calculate the clob datatype.
I would need to convert the column datatype from BLOB to CLOB. currently in the table, the BLOB column has the data. the requirement is to convert this column from BLOB to CLOB datatype.
How to convert from BLOB datatype to CLOB datatype ?
how can i update CLOB values in Oracle? I am passing string values to Clob datatype from .Net Eg '13223311','12122112','122125552'
View 6 Replies View Related<ORACLE VERSION : 11.2.0.2.0> i have created a table with CLOB as datatype for one of the columns, I am trying to store a string ( I am not sure about the length of the string) , when i am querying on my table for the CLOB column,instead of the actual string "(HUGECLOB)" is coming. How to get the actual string in case the problem is with the SIZE.
View 1 Replies View RelatedI have to change the datatype of a column from CLOB to varchar2, without changing the order of the columns. The table has no data.
I could find any other way other than dropping the CLOB columns and then adding new columns with varchar2 datatype. But this changes the order of the columns in the table.
How could we concat a clob with a char datatype.
declare
a clob;
b varchar2;
c clob;
begin
c:=a||b;
end;
When execute above code in form runtime, I get error ora-32767.
"ORA-29287: invalid maximum line size Cause: An invalid maximum line size value was specified.
Action: Correct the maximum line size to be in the range [1, 32767]."
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
In my table there is column with number datatype of size col1 number(15,3) and my data in column is like
001
002
003
and i am changing its size to number(10,3) by alter table command but it not allowing. why?????? as my data in that column still satisfy the the changes.
and even when i modify the char column to varchar2 column by alter table command and changing the size of that column,it is not allowing me to change it why?
I want to decrease the size of testid column of number datatype in my "test" named table from size 20 to 15 and the data of maximum size is of 10 digits. but oracle throws an error "ORA-01440: column to be modified must be empty to decrease precision or scale". i cant understand why it is happening?
what is the reason behind it even though new size is maximum than the maximum size of existing data. but when i decrease the size of "varchar2" then oracle does not through any error.
when trying to calculate the occupied space for a table, I'm using DBA_SEGMENTS, which works fine as long as the table does not have a BLOB column.
As far as I can tell, the size of the BLOB data is stored with the SEGMENT_TYPE = 'LOBSEGMENT', but I cannot find a view that tells me which DBA_SEGMENT row belongs to the BLOB column in the table I'm checking.
To give you an example:
select sum(BYTES)
from DBA_SEGMENTS
where owner = user
and segment_name = 'MY_TABLE'
group by SEGMENT_NAME
returns 262144
running:
SELECT sum(length(blob_column))
FROM my_table
returns 821333
There are entries in DBA_SEGMENTS for my user with the type LOBSEGMENT, but I cannot find a way to map the correct DBA_SEGMENTS row to the table I am checking.
I'm using Oracle 10.2.0.3.0 on Redhat
I need to resize my datafile as i have allocated more space and need to reduce ( i.e.data load completed now). my tablespace is having 11.74 gb free space now. it has 3 datafile.
TABLESPACE TOTAL USED FREE PCT_FREE LARGEST FRAGMENTS
------------------------ ---------- ---------- ---------- ---------- ---------- ----------
CFC_DATA 150528 138780.6 11747.4 7.80412946 1251 992
TABLESPACE_NAME FILE_ID FILE_NAME Size(MB)
------------------ ---------- ------------------------------------------------------- ----------
CFC_DATA 71 +DATA/dedw/datafile/cfc_data.4074.731085435 65535.9688
CFC_DATA 334 +DATA/dedw/datafile/cfc_data.4473.757566557 20480
CFC_DATA 1710 +DATA/dedw/datafile/cfc_data.2012.728095695 64512I used below script to find out HWM in order to resize the datafile.
db_block_size is 16KB.
[code]....
in TOAD, we have an option, that is "Minimum size" button against each datafile.. Need the SQL which is running behind when we press this button from TOAD ?
I have read following statement from a link [URL]...
Oracle Database XE can be installed on any size host machine with any number of CPUs (one database per machine), but XE will store up to 4GB of user data, use up to 1GB of memory, and use one CPU on the host machine.
calculation of this 4GB size. how can we calculate this size?
by simply going to DBF file and seeing their size?
or
by exporting dump and seeing the size of that dump?
I'm trying to get size of CLOB column but not getting any output.
SQL> desc TABLE_STEP_INST234
Name Null? Type
----------------------------------------- -------- ----------------------------
NUM_PENDING_PREREQS NOT NULL NUMBER(10)
OBJID NOT NULL VARCHAR2(31)
OUTFLOW_BITS NUMBER(19)
PARAMS CLOB
PARENT2PROC_INST NOT NULL VARCHAR2(31)
ROOT2PROC_INST NOT NULL VARCHAR2(31)
START_TIME DATE
STATUS NOT NULL NUMBER(2)
[code]...
We are planning to export the table data to a file pipedelimited. How do i estimate the size of the FlatFile based on the table size? or avg rowlength
View 3 Replies View RelatedI am using oracle 8.1.5 database and my temp01.dbf file size is increased upto 19.8 GB now i want reduce its size .
View 13 Replies View Relatedonly one table A(autoid, number)
how SELECT statment result: B(autoid, number, numberB)
numberB is sum of all the number have autoid > its autoid.
i was using sql server there is time keyword but in oracle which keyword i should take instead of time keyword?
View 7 Replies View RelatedTrying to accomplish:
I am trying to calculate pay with a package which consists of four functions for calculations and a procedure that calls the functions to calculate net pay.
DML DDL and package
I the DML and DDL and the package as an attachment.
Problem
Errors below
32/9 PLS-00103: Encountered the symbol "E" when expecting one of the following:
, ; for group having intersect minus order start union where connect The symbol "having" was substituted for "E" to continue.
32/54 PLS-00103: Encountered the symbol ")" when expecting one of the following:
LINE/COL ERROR
-------- ----------------------------------------------------------------
. ( * @ % & - + ; / at for mod remainder rem <an exponent (**)> and or group having intersect minus order start union where connect || multiset
33/9 PLS-00103: Encountered the symbol "INTO" when expecting one of the following:
. ( ) , * @ % & = - + < / > at in is mod remainder not rem <an exponent (**)> <> or != or ~= >= <= <> and or like like2 like4 likec between || member submultiset
How to calculate exact age for example my date of birth is 10-04-1972 and today current date is 21-10-2011 so i want to calculate age how many years, how many months and how many days.
View 27 Replies View RelatedI need a query to find out the working days. I have attached the sample script to create table and data. Here is the description of the tables.
Emp1 : To record the employe's information and weekly rest day.
Attendance :- To record daily attendance.
Leave_appovd : To record the approved leaves.
Holiday : To record the holidays.
W = Attendance
R = Weekly Rest
L = Leave
H = Holiday
A = Absent
Required output is
Emp No.In Date Out Date Type
736912/1/201212/1/2012W
736912/2/201212/2/2012R
736912/3/201212/3/2012W
736912/4/201212/4/2012W
736912/5/201212/5/2012W
736912/6/201212/6/2012L
736912/7/201212/7/2012H
736912/8/201212/8/2012H
736912/9/201212/9/2012R
736912/10/201212/10/2012A
736912/11/201212/11/2012W
736912/12/201212/12/2012W
736912/13/201212/13/2012W
736912/14/201212/14/2012W
736912/15/201212/15/2012L
736912/16/201212/16/2012L
736912/17/201212/17/2012L
736912/18/201212/18/2012W
736912/19/201212/19/2012W
736912/20/201212/20/2012W
736912/21/201212/21/2012W
736912/22/201212/22/2012W
736912/23/201212/23/2012R
736912/24/201212/24/2012A
736912/25/201212/25/2012A
736912/26/201212/26/2012A
736912/27/201212/27/2012W
736912/28/201212/28/2012W
736912/29/201212/29/2012W
736912/30/201212/30/2012R
736912/31/201212/31/2012W
778212/1/201212/1/2012W
778212/2/201212/2/2012W
778212/3/201212/3/2012W
778212/4/201212/4/2012W
778212/5/201212/5/2012W
778212/6/201212/6/2012W
Separate Query will be run for the following output.
Wroking Days
EmpnoAttend WeeklyRestsLeavesHolidays TotalAbsentsG. Total
7369 17 4 4 2 27 4 31
7782 6 0 0 0 6 25 31
If any body work on weekly rest or holiday, it will be considered as weekly rest and holiday. There working on these days will be treated separately.
We have 2 tables.
Example :
Table Name1: Sales_orders - Columns(Cust_Id,Customer,Quantity,Unit_Price,........etc)
Table name2: Sales_taxes - Columns(Central_sales_Tax,Service_Tax,ValueAdd_Tax,Entry_Tax,Professional_Tax)
My requirement is;
I what create new table/view which contains all value of both table and new column called Total Amount.
Total amount=SUM(Quantity*Unit_Price+Central_sales_Tax+Service_Tax+ValueAdd_Tax+Entry_Tax+Professional_Tax)
I created a table of Number(20,4) column. I inserted an amount value 999999999999999.5555 but this value is rounded off to 1000000000000000.0000 automatically in Oracle. How to avoid this? I tried for less number of digits and I am getting the exact value. Is there any way to get the exact value without changing the datatype?
View 1 Replies View RelatedI am using Anchored datatype wheere the table_name which lies in another schema and the current scheam has select insert update delete access on the the said table of the schema.
Example in current schema LL_TAR
I have defined a varaible in a script/procedure
V_TAG LL_TMR.TAG_DETAILS.TAG_VAL%type where TAG_VALUE column lies in a table TAG_DETAILS which lies
in LL_TMR.
During compilation it gives me an error PLS-00904 saying insufficient privilege. This issue and still use anchored datatype.Or anchored datatype of tables which lie in different schema on the same database server is not allowed? Cause i had read that on some websites that it is allowed for a table which resides on a different schema on a database.
I have created an Object Type and this Type is mapped to a column datatype in a table.This Table has values inserted.
create or replace
type column_type as object (
col_name varchar2(30),
col_comment varchar2(4000)
);
[code]....
ORA-02303: cannot drop or replace a type with type or table dependents...how to resolve this issue without having to delete any column from the table?
I have a table in my database with a column called theme_night_date that i want to store just a date and no time.
View 2 Replies View RelatedCan we use long data type in where clause of the query ?
View 2 Replies View RelatedI have a table which have almost 90000 rows.
1. The datatype of some columns is NVARCHAR2. I want to change it to VARCHAR2 and NUMBER for some.
Query that I want to run:
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES', false);
result: E;;45;45 ;45 ;45 ;S 45
I am not sure but may be I need to set long size before running above query. But when I try to set long size gives below error. "The output from DBMS_METADATA.GET_DDL is a LONG datatype. When using SQL*Plus, your output may be truncated by default. Issue the following SQL*Plus command before issuing the
DBMS_METADATA.GET_DDL statement to ensure that your output is not truncated:"SQL> SET LONG 9999error: Unhandled SET statement: "SET LONG 9999"
I've this problem:
create table t1 ( x long );
Table created.
create table t2 ( x long );
Table created.
insert into t1 values ( 'test long type' );
1 row created.
insert into t2 select * from t1;
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype
How can I issue this error, I need use also dblink with long type.
I do not want to use the copy command.
I know that you can solve this problem with a stored procedure or anonymous block.
on this query. I need to get a particular value from a column which is a BLOB datatype. Here is the sample
data ID TESTDATA1 Best Buy
00001234 12222 30 00 2
Lowes 00001234 12222 100 00 3
Walmart 00001234 12222 129 00 4
abc 00001234 12222 5000 00 5
Toshiba 00001234 12222 21 00 6
abcdefghij 00001234 12222 49 00
Where '00001234' is the Invoice , '12222' is Netamount field and highlighted in red is the $ amount for that invoice.
The only data i need query to return 12222 (netamount)= $amount. I tried using substr select substr((TESTDATA),19,26)test from TABLEA; But this gives me the only the first row but not other amount which have different positions.
Here is the desired output
OutputID TESTDATA 1
12222 30 2 12222 100 3
12222 129 4 12222 5000 5
12222 21 6 12222 49