SQL & PL/SQL :: Change Datatype From Object

Dec 8, 2010

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?

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: Change Column Datatype From CLOB To Varchar2

Jun 22, 2012

I 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.

View 4 Replies View Related

Server Administration :: Change Datatype To HEX Notation / Base 64

Jul 10, 2013

I am currently in the process of migrating our database from US7ASCII to AL32UTF8 using DMU.

I am stuck at a point where I have encrypted data that, when the conversion happens, will be destroyed. Oracle suggests to move the data which is currently stored in VARCHAR2 datatype, to a "character set safe way" like hex notation or base64 before converting.

View 7 Replies View Related

SQL & PL/SQL :: Change Datatype Of Column In Table Online Using DBMS REDEFINITION?

Aug 8, 2011

Is it possible to change the datatype of a column in a table online, using DBMS_REDEFINITION?

If Yes, then which of the options will be used with DBMS_REDEFINITION package?

View 1 Replies View Related

Application Express :: ORA-01439 / Column To Be Modified Must Be Empty To Change Datatype

Sep 4, 2012

I need to modify the column type that already has data in it, i need it change varchar to number then The datas entered have $ and , (e.g $3,200). I need the $ and , removed with column type set to numbers.

When i currently do this i get the following error:

The TABLE operation was not successful for the following reason:

ORA-01439: column to be modified must be empty to change datatype

I've tried unloading the data and loading the data using the spreadsheet, cut and past and csv file but both give me more errors, so i just want that one column modified.

View 2 Replies View Related

PL/SQL :: Conversion Of BLOB Datatype To CLOB Datatype

Aug 17, 2012

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 ?

View 10 Replies View Related

PL/SQL :: Error In Modifying Object Value In Triggers Defined On Object Tables

Sep 26, 2012

While practicing with Triggers, the following error was encountered. An Object Type and an object Table are created.

create or replace
type typPerson is object
(id number,
firstname varchar2(30),
lastname varchar2(30)
[code].........

I executed the below insert statement, and I got the following error.

SQL> insert into person_obj_tab values (10,'Object1','From Trigger');
insert into person_obj_tab values (10,'Object1','From Trigger')
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 4153
Session ID: 136 Serial number: 305
[code]......

SQL> insert into person_obj_tab values (10,'Object','Original');

1 row created.Question:

1) Trigger of version 1 did not report any error during compilation, but during DML execution, hangs for sometime and gives the above error.
2) Whether direct assignment of Objects of greater size is possible inside triggers built on object Tables?
3) Suppose an object contain multiple attributes (say more than 20), then how to assign them inside a trigger?

View 1 Replies View Related

TimesTen In-Memory :: OracleDataReader - Object Reference Not Set To Instance Of Object

Jan 25, 2013

I have a problem with executing oracleCommand.ExecuteReader() method. Whatever I try it always returns null and it won't create OracleData reader object. I'm using ODAC 1120320_x64 for .net 4.0 and timesten112241.win64. Don't sure what to do. Debugger is showing strange thing in OracleConnection object : ConnectionState = Closed, but output of ttStatus shows connection to TimesTen data store and ExecuteNonQuery() command works just fine with or without (in or out) parameters. But when I try to execute some query with multile output such as select *, I can't get any result.

I also have a strange problem with connection.Open() When I execute Open() i throws AccessViolationException that can be handled with [Handle ProcessCorruptedStateExceptions] attribute, but connection is established after that and my application works fine until I try to instance OracleDataReader object.

Here is the code:
OracleCommand select = null;
OracleDataReader reader = null;

select = new OracleCommand(selectStmt, connection);
select.CommandType = CommandType.Text;
try
{
reader = select.ExecuteReader(); // this line throws NullReferenceException
if (reader.HasRows)
{
[code]....

Just to mention, I tried it with different queries (pl/sql, plane sql, stored procedure) and all of them works fine in SQL Developer, but not in app.

View 10 Replies View Related

Find Dependent Object Details For Any Object

Dec 24, 2012

Is there any query to find the dependent object details for any object. Like if mview is built on a table, then i should be able to find the table name with out checking code of the mview. similar way for view and functions or procedures etc...

View 5 Replies View Related

PL/SQL :: How To Access Type Object Variable Declared Inside Another Type Object

Mar 29, 2013

I have an Type-object typeObj1 that consists another Type-object typeObj2. this def has another Type-object typeObj3. how to access variable declared inside typeObj3. I have syntax below for each Type.

CREATE OR REPLACE TYPE typeObj1
AS OBJECT
   (
      SYSTEM_IDENTIFER                    VARCHAR2(50),
      PROCESS_TYPE                          VARCHAR2(50),
      abc                                            typeObj2
     
   )
/

[Code]...

/I have tried to access the type-object in where clause in following way

FROM TABLE(CAST(I_typeObj1 AS typeObj1)) ITTPRC,
......
Where
.......
AND (ADDKEY.ADDTN_INFO_KEY_TYP_NM IN (SELECT ADDTN_INFO_KEY_TYP_NM FROM TABLE(ITTPRC.abc)))

AND (ADTINF.ADDTN_RQST_TYP_VAL_DT  IN (SELECT ADDTN_RQST_VAL_DT FROM TABLE(     ITTPRC.def)) OR ITTPRC.def IS NULL )
AND (ADTINF.ADDTN_RQST_TYP_VAL_NUM  IN (SELECT ADDTN_RQST_VAL_NUM FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL )
AND (ADTINF.ADDTN_RQST_TYP_VALUE  IN (SELECT ADDTN_RQST_VALUE FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL )

In this way i am able to access the variable inside typeObj3. But problem is i am getting error "ORA-01427 single-row subquery returns more than one row" when i pass more that one typeObj2.

I passed the values like this in proc execution.

T_T_A_I_V  :=  typeObj3('asdasd',NULL,NULL),
                       typeObj3('String654',NULL,NULL),
                       typeObj3('abcdef',NULL,NULL));                                    
T_T_A_I_I  :=  typeObj2('CampusCode',T_T_A_I_V),
                          typeObj2('PlanNumber',T_T_A_I_V);

What i have done is removed typeObj3 from typeObj2, variables defined in typeObj3 are added in typeObj2 then i got ride of above error. is it correct

View 4 Replies View Related

PL/SQL :: Which Datatype Should Take Instead Of Time

Oct 4, 2012

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 Related

Get Exact Value Without Changing Datatype?

Nov 23, 2011

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 Related

PLS-00904 And Anchored Datatype

Jul 20, 2011

I 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.

View 3 Replies View Related

SQL & PL/SQL :: Date Datatype Being Stored As DD:MM:YY HH:MI:SS?

Feb 21, 2012

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 Related

SQL & PL/SQL :: Long Datatype In Where Clause

Sep 1, 2010

Can we use long data type in where clause of the query ?

View 2 Replies View Related

SQL & PL/SQL :: Datatype Of Some Columns Is NVARCHAR2

Feb 21, 2010

I 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.

View 5 Replies View Related

PL/SQL :: How To Set The Default Long Datatype

Jul 12, 2013

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"

View 5 Replies View Related

PL/SQL :: Illegal Use Of LONG Datatype

Aug 31, 2012

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.

View 8 Replies View Related

PL/SQL :: Get Particular Value From Column Which Is BLOB Datatype?

Jul 31, 2013

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 

View 11 Replies View Related

SQL Statement With Varchar Datatype Size

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

Updating A Table Column Which Is XML Datatype

Nov 1, 2011

I am updating a table column which is xml datatype and am getting above error.Below is the process what i did. since the xml is too large i split them into small chunks.

DECLARE
conditionXML CLOB;
ls_xml_2 Clob;
ls_xml_3 clob;
ls_xml_4 Clob;
ls_xml_5 Clob;
ls_xml_6 clob;
ls_xml_7 Clob;
[code]...

View 1 Replies View Related

Update Using CLOB Datatype In Oracle

Aug 16, 2010

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

SQL & PL/SQL :: Convert From LONG To BLOB Datatype?

Feb 1, 2012

I have a source data in a column with LONG datatype. I want to insert that data into a separate table in a column which has BLOB datatype.

View 9 Replies View Related

SQL & PL/SQL :: How To Fetch Value Which Column Has Timestamp Datatype

Oct 14, 2012

i am running one query which is here

INSERT INTO shiftsample (Empid, PPDate, Inpunch, outpunch)( SELECT Emp_ID, PDate, In_Punch, Out_Punch FROM ProcessDailyData WHERE PDate = to_char(2012-10-15,'yyyy-MM-dd') AND Emp_ID = '00000001' );

in this query pdate has timestamp datatype and in shift sample ppdate column has date type. so i am not able to insert value from process daily data table.

getting this error.

SQL Error: ORA-01481: invalid number format model
01481. 00000 - "invalid number format model"
*Cause: The user is attempting to either convert a number to a string
via TO_CHAR or a string to a number via TO_NUMBER and has
supplied an invalid number format model parameter.

View 19 Replies View Related

SQL & PL/SQL :: Insert Value In Column Which Has Date Datatype?

Oct 12, 2012

i am having one column name pdate with date datatype

i am updating here value like this

update table1 set pdate='15-10-2012' where id=1;

but showing error: not a valid month.

how to update this.

View 7 Replies View Related

SQL & PL/SQL :: Finding MAX And MIN Value From Varchar Datatype Field

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

SQL & PL/SQL :: Two Columns Can Have Long Datatype In Table?

Apr 27, 2010

I have question related to LONG datatype. Actually from google and get to know that one table can have only one LONG datatype when i searched for reason . i got these resons:-

With 9i (I believe) and later versions, Oracle deprecates using the long datatype in favor of the lob (clob, nclob and blog) datatypes. It is only supported for backward compatibility.

Restriction:- It can not be used in create type as an attribute of the defined type.

It can not be used in where conditions.

There can be no indexes on long columns.

Regular Expression are not possible.

long can not be returned from a stored function.

SQL can not call functions that have an attribute of type long.

And even more restrictions.

So I want to know that is only reason because of that Oracle doesn't allow us to make two Column or is there any strong reason which make it more logical Like storing of data in Row blocks or some thing else.

View 2 Replies View Related

SQL & PL/SQL :: BLOB Datatype - Copy From One Column To Another

Mar 11, 2011

I have two tables with BLOB datatype. I am trying to copy from one column to another column , i am getting errors

CREATE OR REPLACE PROCEDURE update_blob_lob_copy(
v_id in integer,
v_string in varchar
)
as
auditlob lobdemoaudit.theblob%type;
sourcelob lobdemo.THEBLOB%type;
[code]...

View 3 Replies View Related

SQL & PL/SQL :: Passing IN Parameter Of Datatype Varchar2

Feb 16, 2010

I have a stored proc which takes IN parameter of datatype varchar2.When I am trying to run the proc it is throwing error that "input buffer too small".The datatype assigned to IN parameter is of varchar2(200) but actually the length of the parameter passed is around 500 characters.the way to increase the length of Input parameter to 500 characters??

View 16 Replies View Related

SQL & PL/SQL :: Number Datatype Size Can't Decreased

Mar 22, 2010

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?

View 30 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved