SQL & PL/SQL :: How To Describe Number Length In VIEW

Sep 30, 2011

in the case below, when i created VIEW to add a new Department for some reason, its length is un-identified but i want the length as describe in table, how i can get ?

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production With the Partitioning, OLAP and Data Mining options

devtest@ Test.DB> desc dept
Name Null? Type
----------------------------------------------------------------- -------- ------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)

devtest@ Test.DB> l
1 select deptno, dname, loc from dept
2 union all
3 select 99 deptno, 'MY DEPTT' dname, 'MY LOCATION' LOC
4* from dual
devtest@ Test.DB> create view my_dept as
2 select deptno, dname, loc from dept
3 union all
4 select 99 deptno, 'MY DEPTT' dname, 'MY LOCATION' LOC
5 from dual;
[code]....

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: Materialized View - Zero Length Not Allowed?

Dec 8, 2010

I am trying to create one Materialized view from a Select Statement and I am getting the following error

SQL Error: ORA-01723: zero-length columns are not allowed

I have checked the table and found nothing like varchar2(0) or char(0) , but the column in my select statment may get a Null value. Will it result in error like above ?

Normal View : I have created this because I think a materialized view would not be created if we have a Sub query.

CREATE OR REPLACE VIEW mx_test
AS
SELECT t."EXTN_SERVICE_REQUEST_NO",
t."EXTN_SERVICE_TYPE",

[Code]....

Materialized View :

CREATE materialized VIEW mx_ashok
AS
SELECT * FROM mx_test

Error :

Error starting at line 1 in command:
CREATE materialized VIEW mx_ashok
AS
SELECT * FROM mx_test
Error at Command Line:3 Column:16
Error report:
SQL Error: ORA-01723: zero-length columns are not allowed
01723. 00000 - "zero-length columns are not allowed"
*Cause:
*Action:

Do I need to use NVLs for all columns which may result in NULL value ?

View 9 Replies View Related

Number Field Coming As Blank But Length 1

Aug 12, 2013

column with datatype Number contains length 1.Its not null.Tried to trim then also length is 1 ....How to find what is the character there..?

View 1 Replies View Related

Server Utilities :: Field In Datafile Exceeds Maximum Length For Number Field?

Apr 23, 2010

Even though i am using COL1 CHAR(500) NULLIF COL1=BLANKS, then also i am getting same error for those columns.

View 13 Replies View Related

SQL & PL/SQL :: Constraints In Describe

Nov 1, 2013

I want to display columns along with their constraints using describe command

Is it possible?

SQL> desc dept;
Name Null? Type
----------------------------------------- -------- -------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)

View 1 Replies View Related

Precompilers, OCI & OCCI :: Describe STP In Package

Nov 26, 2009

I am trying to describe an STP in a package, but it gives me an error.

e.g. In package ABC suppose there is an STP XYZ, I am trying to describe ABC.XYZ function but it gives me an error code 4043 and error message object XYZ.ABC does not exist.

View 2 Replies View Related

SQL & PL/SQL :: Describe Table Structure Of A Schema?

Apr 21, 2010

i would like to view all table statructure of a schema in a file. i need to take print out of it for one analysys.

i did like this

exp user/pwd file=dumpfile.dmp full=y rows=n log=logfilename.log

imp user/pwd file=dumpfile.dmp full=y indexfile=indexfile.sql logfile=logfilename.log

but

its look like this i could not format

REM CREATE TABLE "SYSTEM"."DEF$_AQCALL" ("Q_NAME" VARCHAR2(30), "MSGID"
REM RAW(16), "CORRID" VARCHAR2(128), "PRIORITY" NUMBER, "STATE" NUMBER,
REM "DELAY" TIMESTAMP (6), "EXPIRATION" NUMBER, "TIME_MANAGER_INFO"
REM TIMESTAMP (6), "LOCAL_ORDER_NO" NUMBER, "CHAIN_NO" NUMBER, "CSCN"
REM NUMBER, "DSCN" NUMBER, "ENQ_TIME" TIMESTAMP (6), "ENQ_UID" NUMBER,

[Code] ..........

View 3 Replies View Related

Client Tools :: Script To Describe Tables?

Jun 1, 2010

how to create a script that will describe only the structure of 150 tables of a database.have it just like this, but I cannot produce the name of the table:

spool desctab.sql
select 'desc '||owner||'.'||table_name||'' from all_tables where owner='SKIMA'
order by table_name;
spool off

.. the output of the spool file is something like this:

Name Null? Type
----------------------------------------- -------- -------------
OFC_CODE VARCHAR2(3)
OFFICE VARCHAR2(35)
DESCRIPTION VARCHAR2(50)
REMARKS VARCHAR2(200)
OFFICER VARCHAR2(35)

View 4 Replies View Related

SQL & PL/SQL :: Describe Results / Define Output And Fetch Rows Of A Query

Jun 2, 2010

There are several stages for sql processing in 10g2 database concept document.The following stages are necessary for each type of statement processing:

■ Stage 1: Create a Cursor
■ Stage 2: Parse the Statement
■ Stage 5: Bind Any Variables
■ Stage 7: Run the Statement
■ Stage 9: Close the Cursor
Optionally, you can include another stage:
■ Stage 6: Parallelize the Statement

Queries (SELECTs) require several additional stages, as shown in Figure 241:

■ Stage 3: Describe Results of a Query
■ Stage 4: Define Output of a Query
■ Stage 8: Fetch Rows of a Query

Stage 3: Describe Results of a Query The describe stage is necessary only if the characteristics of a query's result are not known; for example, when a query is entered interactively by a user. In this case, the describe stage determines the characteristics (datatypes, lengths, and names) of a query's result.

Stage 4: Define Output of a Query In the define stage for queries, you specify the location, size, and datatype of variables defined to receive each fetched value. These variables are called define variables. Oracle performs datatype conversion if necessary.

I still don't understand what's Stage 3: Describe Results of a Query and Stage 4: Define Output of a Query.

View 2 Replies View Related

View With Variable Number Of Columns

Oct 21, 2011

I've created a stored procedure which creates itself a view (a MV to be honest); the instructions to create this m.view are dinamically built insinde my procedure, so each time i run it, based on the different input parameters, i've got a different result (my output m.view can have three colums the first time, or ten the next time) how can I read my output view to put the data into file? I've tried with "select * bulk collect into my_array from my_ output_ view"...after declaring "my_array" as a varying array with the max number of colums I could ever have...but nothing: if the array dimension doesn't match the number of columns that i've on my view, i.e. i receive "ora-00947 not enough values" error.

Is there a method to dimension dinamucally the array to store my data? Or should I change the code to fetch some other way the data i need to put to a file?

View 2 Replies View Related

SQL & PL/SQL :: How To Find Number Of Rows Of View

Oct 22, 2010

is there a way to find out how many rows a View has? Something similar to NUM_ROWS with regular tables perhaps? Or do I have to use Count(*)?

View 4 Replies View Related

PL/SQL :: How To View The Number Of Locks Defined For A Database

Aug 21, 2012

How to view the number of locks defined for a database? I am using Oracle 10g.

How to calculate the number of locks for a given sql select statement?

say for example,

select a.eno,b.ename,c.salary,d.dob,e.address
from a,b,c,d,e
where...
...
..

Assume all of them are row level locking.

I want to calculate the number of locks . How many locks will be held while running the above query?

View 16 Replies View Related

Force View Number Data Type Precision?

Aug 18, 2011

I am working on an application that pulls data from an Oracle view into Microsoft Excel (Oracle 11g, MS Excel 2003). It is an automated pivot table created through vba. The problem I'm having is that the decimal points from number columns are being truncated - they don't make it to the pivot table.

When I use the pivot table wizard and set the external data source using a SQL string (select * from view), the precision is not lost. When I create the pivot table automatically in vba, the precision is lost. The pivot table settings, regardless of how the data is brought in, shows the format of the number column as general. This tells me that Excel is trying to figure out what the data type is, and can't (not smart enough).

When looking at the description of the view, the data type is NUMBER (no precision). The table that it is pulling from has the precision set (NUMBER(11,3)). I have tried using the following, but it doesn't work:

select to_number(field_name, '99999999.999') field_name from view

View 7 Replies View Related

Creating A View To Show Employee Names / Age And Total Number Of Projects Assigned

Apr 14, 2009

I have following tables:

EMPLOYEE (E-Number, Name, Department, age)
ASSIGNMENT (E-Number, P-Number )
PROJECT( P-Number, Project, Manager)

Create a view to show employee names, age and total number of projects they are assigned to

View 2 Replies View Related

SQL & PL/SQL :: TRIM To A Specified Length?

Aug 9, 2013

I have a field "Email". The length of it is restricted to 30. But i mayget more than 30 characters. So how to trim the email address so that its max length is 30 characters.

View 5 Replies View Related

SQL & PL/SQL :: Max Length For A Column

Jan 8, 2013

I want to find the max length for a column in oracle, without querying each of the columns. Are these stats stored somewhere?

I have several fields defined as varchar2(4000). Not all of them use up 4000. Instead of querying each one ..max(column name) i want to explore if there is a way, i can find the max size stored somewhere? dba_tab_cols provides size of the field. is there any table that provides max used so far?

View 1 Replies View Related

SQL & PL/SQL :: Get MAX Length Of Column Value Along With Field Value

Oct 4, 2012

create table test_schema(
col1 varchar2(50)
)
insert all
into test_schema values ('this_is_a_test')
into test_schema values ('this_is_a_test_test')
into test_schema values ('this_is_a_test_test_xxxx')
into test_schema values ('this_is_a_test_test_aaaaaaaa')
select * from dual;

I want to get the length of the col1 value with maximum length of characters also with that field value.

i.e o/p is

this_is_a_test_test_aaaaaaaa length 28

how can I do it??

View 3 Replies View Related

SQL & PL/SQL :: Maximum Length Of Table Name

Jun 10, 2013

I want to create a table with a length greater than 30.I Thought there was a way to override the max length for for a table name in Oracle 11.2.0.2.I cant find a documentation that states how to get it done.

View 1 Replies View Related

SQL & PL/SQL :: Datapump Field Length

May 21, 2012

I have a problem to read in data from dmp file to target table.

I've created a dmp file using:

CREATE TABLE table_name
ORGANIZATION EXTERNAL
(TYPE oracle_datapump
DEFAULT DIRECTORY directory_name
LOCATION ('file_name.dmp')
) AS SELECT col1, col2, col3 from source_table;

[Code]...

The file created via db_link from target db to remote db and then the data are insterted into target db table target_table.

desc target_table
col1 varchar2(20)
col2 number
col3 number

There are no values longer than 20 characters in source table, but when I insert into target_table(col1, col2, col3) as select col1, col2 col3 from source_table; I get ORA-12899: value too large for column "target_table"."col1" (actual: 25, maximum: 20).

I gues it has something to do with how the oracle datapump stored the data in dmp file. When I select col1 from source_table where length(col1) > 20; I get two values which clearly are not longer than 20 characters.

Selected values are:

748473358           
693197674           

where the bug is hidden or any "normal" workaround?

View 17 Replies View Related

Maximum Length Of Table Name?

Jan 31, 2013

I think the maximum length of table and column name in oracle 11g is 30 characters.I want to increase the limit as i want to import a mysql database that is having bigger table names.Can i preset the table name and column name length??

View 2 Replies View Related

XML DB :: Set Field Length At Runtime

Jul 17, 2012

can I control at length of field at tuntime?i want to set the number of the size (length) at runtime.

(no by use "HORIZONTAL ELASTICITY"= variable/expand because I need a specific length)

View 1 Replies View Related

Server Administration :: How To Change Existing Materialized View To Normal View

Jan 17, 2013

can we change the existing materialized view to normal view? if yes how?

View 2 Replies View Related

Column Length Versus Size

Oct 6, 2011

if one of the columns is given as

ABC varchar2(10)

the size of the data in bytes that this column going to hold.

View 5 Replies View Related

ORA-24373 / Invalid Length Specified For Statement

Aug 20, 2008

We are getting an error in our web application that is using Oracle.DataAccess.dll v2.111.6.20. When a couple users are using the site everything is fine, but when the load goes up we start getting the error ORA-24373: invalid length specified for statement. We are unable to duplicate this error in Visual Studio and don't know where to turn. We use stored procedure and the .dll to access the database for everything. Also, when this error occurs, it occurs indefenitely for all OracleCommand objects until the web server is rebooted. Also, when I attempt to remote debug with SQL Developer, the process doesn't even make it to the database!

View 3 Replies View Related

Select Fields Longer Than Length X?

Dec 8, 2011

I'm trying to select only codes from a column that are above a certain length. how would this be achieved? I've tried char_length(fieldname) > x in the where clause but i'm getting the error ORA-00904: "char_length" invalid identifier.

View 2 Replies View Related

SQL & PL/SQL :: Table Column Name - Maximum Length

Apr 8, 2011

What is the Maximum Charter length can be given as a column name in a table?

View 3 Replies View Related

How To Get Type / Length And Flags By Query

Feb 19, 2007

how to get the type, length, flags in oracle by query..??

View 1 Replies View Related

PL/SQL :: Finding Length Of CLOB Column

Jul 11, 2012

I had a varchar2 variable which was storing some data and I could use the LENGTH function to get the length of the data. However, If I change it to CLOB. What is the best way to get the length?

v_clob_size:= (DBMS_LOB.getlength(v_clob)) / 1024 / 1024;
DBMS_OUTPUT.put_line('CLOB Size ' || v_clob_size);

another one is
LENGTHB(TO_CHAR(SUBSTR(<clob-column>,1,4000))) But this seems restricted to first 4000 characters only.

View 3 Replies View Related

SQL & PL/SQL :: Sum Of Length Of Column Data Of All Tables

Jul 24, 2012

i want to add a column data of datatype number of all the tables at a time in a database..

i am trying with the all_tab_columns but i can get only the column info whether it is number or varchar2 or any other data type but i am unable to retrieve the column name and the sum of length of the data present in all the rows in that column...

View 39 Replies View Related

SQL & PL/SQL :: Fetch All Three Address Line With Max Length Of 40 In Each Columns

Jun 9, 2010

there are 3 columns as Address1(max data length 80) , Address2(max data length 80), Address3 (max data length 80)..I need to fetch all the three address line with max length of 40 in each columns after concatenate address1,address2,address3

E.g Field data_length
Address1 49
Address2 32
Address3 33

now I need to concate all the char and split in 40 char per field i.e. 49+32+33 = 114

Field data_length
Address1 40
Address2 40
Address3 24

View 15 Replies View Related







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