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


ADVERTISEMENT

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

JDeveloper, Java & XML :: Date Time Omits Time Part

Jun 4, 2012

When I try to extract the date tag value from XML data, the time stored in 20120602153021 format i.e., YYYYMMDD24HHMISS format. The following statement extracts only date as 02-JUN-12 however do not extract the time part.

If I try the same in SQLplus with to_date it works however fails in PL/SQL.

XML data:
<?xml version="1.0"?>
<RECORD>
<REGTIMESTAMP>20120601130010</REGTIMESTAMP>
</RECORD>

PL/SQL Extract:

CURSOR c_xml_record
IS
SELECT extract(value(d), '//ACTIVATIONTS/text()').getStringVal() AS REGTIMESTAMP,
FROM t_xml_data x,
[code].......

View 3 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 :: 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 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

How To Set Interval Time 4hrs In Dbms_jobs But Starting Time 3:00 Am

Jul 25, 2013

how to set interval time every 4hrs in dbms_jobs but starting time 3.00am.

i am set trunc(sysdate)+4/24. but it will take starting at 12.00,4.00,.....in this way..

View 2 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

ORA-00997 / Illegal Use Of LONG Datatype

Aug 13, 2013

We also face the same in oracle 9i version. we tried to re-org some tables for performance issue.But our tables have long and long raw datatype. then we approached the traditional way i.e 1. Export tables.2.Truncate tables.3.import tables.(use ignore=y).4.check the index validation.5.gather stats. Above was successfully done in our production environment.but some application downtime is required. 

View 1 Replies View Related

PL/SQL :: Datatype Name For Multiple Rows Return

Jun 7, 2013

If I return multiple rows says ex:100 rows then what will be data type for that return values.

View 1 Replies View Related

Raw Datatype Changing The Case Of The Data?

Jul 31, 2013

I have a table users which contains the column password and the datatype for this is raw(64) ,which will be the hashed one coming from the application. When Inserting the application is inserting with the lower case but while storing the table data is storing int the  uppercase .Is it the expected behavior of this datatype.   I am using Oracle 11g  and attaching the ddl and dml pertaining to the column 

CREATE TABLE users_new(USER_ID NUMBER(10), PASSWORD RAW(64) );  insert into USERS_NEWvalues(1,'ba660ed7b382f2c57754c4cd0d5289cc60ee455244847268d88b555168b28b23156f5a016b2764966a15674bec4eb0fb2220be736bfc21282be957910b9e80cf'); 

But after selecting from the database I am getting as below BA660ED7B382F2C57754C4CD0D5289CC60EE455244847268D88B555168B28B23156F5A016B2764966A15674BEC4EB0FB2220BE736BFC21282BE957910B9E80CF

View 4 Replies View Related

PL/SQL :: Apply LPAD In NUMBER Datatype?

Jun 27, 2013

Can we apply LPAD in NUMBER datatype?

View 11 Replies View Related

PL/SQL :: Creating Table With Boolean As Datatype

Dec 1, 2012

I'm trying to create a table 'a' with column name as 'flag' and datatype as 'boolean' ie. create table a(flag boolean);

is it possible in sql or pl/sql in oracle?

If its possible to create a table and also let me now inserting row and what boolean should return.

View 1 Replies View Related

Datatype Of Column Of A Table In Oracle Or SQL

Aug 6, 2013

a) What if i want to find out the  Datatype of a specific column in the Table. 
b) How do i find the Column Datatypes?

View 3 Replies View Related







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