SQL & PL/SQL :: ORA-00902 Invalid Data Type

Nov 2, 2012

I have created the following type

create or replace type type_sh_fld_rec
(
s_count count,
s_name varchar2(200),
e_date date);
/
create or replace type t_table_s_field as table of type_sh_fld_rec;
/

I have used these types in package pkg_shippers PROCEDURE process_shipments(userid);

when I run the following sql

select * from table(cast(DEV.PKG_SHIPPERS.process_shipments(324) AS T_TABLE_S_FIELD));

I get the following error.

ORA-00902 Invalid datatype.

I have tried to create the types and table from the sql prompt instead of the package spec, still no luck

Note: The sql statement works fine if I run it as schema owner but not as user who has all the privileges.

View 7 Replies


ADVERTISEMENT

SQL & PL/SQL :: PLS-00330 - Invalid Use Of Type Name Or Subtype Name

Aug 8, 2011

I got error invalid use of type name or subtype name in collection.

create or replace PROCEDURE sp_load_dasstage_security
AS
cursor cur_stag is
select Asset_id,ID_ISIN
ID_SEDOL1,
ID_CUSIP,
LONG_ULT_PARENT_COMP_NAME,
ISSUER,
[code]....

View 10 Replies View Related

SQL & PL/SQL :: ORA-28827 - Invalid Cipher Type Passed?

Jan 7, 2011

I keep on getting the Invalid cipher error whenever I do a call to DBMS_CRYPTO.Decrypt The flavor I am calling is the one taking the parameter list below:

(dst in out Blob,src in Blob,typ in pls_integer, key in raw,iv in raw)

So the issue happens on the third parameter: typ. It is a constant defined in the package representing the decryption algorithm I intend to use (AES256 in my case). So the call goes something like that in my pl/sql

dbms_crypto.Decrypt(vdecrypted, vcrypted,dbms_crypto.encrypt_aes256,vkey);

but it won't work. I also tried setting a variable like :

my_algo := dbms_crypto.encrypt_aes256;

and using it in the call but that gave the same result.

View 6 Replies View Related

Client Tools :: ORA-31180 / Type Mismatch In Invalid PL/SQL DOM Handle

Jul 23, 2010

ORA-31180: DOM Type mismatch in invalid PL/SQL DOM handle

I am getting this error while debugging one of the package in PL/SQL developer or in TOAD. Is there any setting which we need to perform at the database level to debug the packages which have the code related to XML DOM. I am asking as the same code works exactly fine at one of the test database and it gives the above error on the other database.

View 10 Replies View Related

Server Administration :: Functions To Convert The Long Type Field Data To Varchar2 Type

Oct 17, 2011

Is there some functions to convert the long type field data to varchar2 type?

View 2 Replies View Related

Store Data In CLOD Data Type - How To Create A Unique Index

May 20, 2013

We have been recommended to store data in CLOD data type.

Sample data: 1:2:2000000:20000:4455:000099:444:099999:....etc it will grow to a large number.

We want to create a Unique index, for functional reason. Is it advised to create a unique index on a CLOB datatype?

View 2 Replies View Related

SQL & PL/SQL :: Extracting Data From LONG RAW Data Type Column

Oct 7, 2011

I have come one requirement where i need to extract data from a LONG RAW data type column.

View 7 Replies View Related

XE :: Cannot Access NLS Data Files Or Invalid Environment Specified

Jul 1, 2013

Trying to connect to XE 11.2 getting the following: 

Unable to connectSQLState=51000[Oracle][ODBC][Ora]ORA12705: Cannot access NLS data files or invalid environment specified. 

I have set the environment variables using the oracle_env.sh shell script. Trying to connect with the instant client running on Windows XP with the ODBC extensions installed. Host is X86_64 CentOS Linux

View 0 Replies View Related

SQL & PL/SQL :: Getting Data From Record Type

Jul 19, 2010

I have one requirement. We have a Package which consists of 2 Stored Procedures which has a RecordType output Parameter.

FUNCTION GET_NFE_INFO(O_status_code IN OUT NUMBER,
O_error_message IN OUT VARCHAR2,
O_message IN OUT "OBJ_FM_NFE_DOCHDR_REC",
I_fiscal_doc_id IN FM_FISCAL_DOC_HEADER.FISCAL_DOC_ID%TYPE)
return BOOLEAN is

I am working on a one Stored Procedure which would display the output from the above function as,

set serveroutput on size 9000;

DECLARE

L_stg_status FM_FISCAL_DOC_HEADER.STATUS%TYPE := 'NFE_P';
L_fiscal_doc_id FM_FISCAL_DOC_HEADER.FISCAL_DOC_ID%TYPE := 12325;
L_dummy VARCHAR2(1) := NULL;
L_status_code NUMBER(1) := 0;
L_error_message VARCHAR2(255) := NULL;
L_message "OBJ_FM_NFE_DOCHDR_REC" := NULL;

[Code]..

Here for each and every data value i am Printing it using DBMS_OUTPUT, L_message record has more than 100 columns. Is there a easy way of handling it instead of using DBMS_OUTPUT ?

View 20 Replies View Related

SQL & PL/SQL :: What Should Be The Data Type For The Column

May 9, 2012

I have to create a table which should store data at Week level. The table have the following columns

Product id, Loc id, Business group id, FISCAL WEEK , Revenue,

Fiscal week column will have data as '2011-W01', '2011-W47' etc.

What should be the data type for fiscal week column. Based on this table i have to create a calculated column which should fetch trailing 12 weeks average for each row.

View 5 Replies View Related

User Defined Data Type

Jun 19, 2012

I am trying to create & use a new data type but keep getting a ora-0902 invalid data type error running on 10g express. The create type and select statements execute fine and select confirms the ssn_t is a type. The create table statement fails with the invalid data type error.

Here is an example.

CREATE TYPE ssn_t AS OBJECT ssn_type CHAR(11));
SELECT object_name, object_type
FROM user_objects WHERE object_type = 'TYPE';
CREATE TABLE Z (A CHAR(4), B SSN_T);

View 14 Replies View Related

SQL & PL/SQL :: How To Get Date From The Timestamp Data Type

Oct 13, 2011

How can i get just date from the timestamp data type.

Suppose i have a column timestamp with has data like "2011-05-16 16:19:22.579764-07" when i select from table i just want the date like 2011-05-16.

View 6 Replies View Related

SQL & PL/SQL :: How To Determine The Data Type Of A Variable

Sep 15, 2010

Code to determine the data type of a variable in oracle 10g.

View 5 Replies View Related

SQL & PL/SQL :: How To Insert Varying Type Of Data

Jul 14, 2010

I want to insert varying type of data from one table to another like i want to insert name from one table and salary from another,sysdate too.How should i consider doing it?

View 3 Replies View Related

Server Administration :: RAW Data-type?

Apr 3, 2013

RAW datatype in oracle,any problems we will face using this datatype,Reason is we have column which stores session id in RAW datatype using sys_guid, drawbacks in using this datatype.

View 5 Replies View Related

PL/SQL :: Retrieve XML Data In Clob Type?

Dec 5, 2012

I have a table created as below

create table tab123( x clob);

Inserted a row as below

insert into t values ('"<ProcessBatchRequest xmlns:inp1="http://services.abc.com/" ServiceVersionNumber="" xmlns="http://services.abc.com/GlobalBatchServiceV1.0">
<inp1:BatchDetail>
<inp1:ApplicationID>test.123</inp1:ApplicationID>
<inp1:ApplicationBatchID>test.123</inp1:ApplicationBatchID>
<inp1:MessageTimeStamp>2012-11-28T11:05:32.000-05:00</inp1:MessageTimeStamp>

[code]....

Now i want to retrieve the value present under a given xpath.

View 7 Replies View Related

XML Type Column Data XML Query?

Mar 16, 2013

I have extracted in the following XML document some Mpeg7 visual descriptors from an image and I saved it in an XMLType column. I would like to use XMLQuery to extract the data from the Value node. how write such a query. I could not get the proper Xpath to any node.

<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<Mpeg7 xmlns="http://www.mpeg7.org/2001/MPEG-7_Schema" xmlns:xsi="http://www.w3.org/2000/10/XMLSchema-instance">
<DescriptionUnit xsi:type="DescriptorCollectionType">

[code]...

View 1 Replies View Related

Server Utilities :: Cannot Access NLS Data Files Or Invalid Environment Specified

May 15, 2010

I exported oracle database from one server in a dmp file. then i imported it in another oracle database server. when i saw the imported data the columns which were storing german data is in rubbish characters.

then i remember that the database from where i exported is having nls language as german. i executed this statement to set the nls on the new server

alter system set nls_lang=german SCOPE=SPFILE

but now my database is not getting started always giving me error - cannot access NLS data files or invalid environment specified.

i also set the path NLS_LANG=german in the solaris environment.

View 1 Replies View Related

Data Guard :: ORA-17627 / ORA-01017 Invalid Username / Password

Aug 21, 2013

I have a strange behavior with a Data Guard environment on Linux and Oracle 11.2.0.2. Since a couple of days, I have the following error in the alertlog of the standby database: 

Errors in file /u00/app/oracle/diag/rdbms/SID_site2/SID/trace/SID_pr0f_22728.trc:ORA-17627: ORA-01017:
invalid username/password; logon deniedORA-17629: Cannot connect to the remote database server First I restarted the Apply on the standby without any changes.

So I tried to connect to the primary database from the standby server with Oracle .Net with success. In the other way, it's also working.I also tried to copy the password file from the primary server to the standby server and I restarted both the transport and the apply.The error is still happening on the standby but the most surprising is that the Data Guard is still fully synchronized. Why I get that error and what is the function of the process pr0f?

View 13 Replies View Related

Data Type Consistency CASE / Decode?

Apr 6, 2012

Data Type Consistency CASE and Decode

CASE expects data type consistency, DECODE not expecting.

Obviously both functions handling data types are different let it be

SQL> select decode(2,1,1, 2,'2', ' three' )"RESULT" from dual;

RESULT
---
2
SQL> select case 2
2 when 1 then 1
3 when 2 then 2
4 else 3
5 end "RESULT" from dual;

RESULT
2
SQL> select case 2
2 when 1 then 1
3 when '2' then 2
4 else 3
5 end "RESULT" from dual;

ERROR at line 3:
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

know cause of error here????I mean " every time case exp checking data type consistency"

my thought is

I am trying to get same output but different methods. Yes, clearly states this is one of the Oracle bug!

I want to know how oracle handles here ???? i mean 3rd query.Purely i am testing this function with dual(dummy) table...
obviously, no possibilities for different data type.next one i am not sure about Oracle compares int variables to int variables, char variables to char variables,

I think so .... that's why oracle throws error. am i right ??ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

see this query

SQL> select case 2
2 when 1 then 1
3 when '2' then 2
4 else 3
5 end "RESULT" from dual;

// clearly i am stating what's error on 3rd line ?????Even if I refer reference books some concepts are blind to understand.

View 2 Replies View Related

Insert Or Select Date Data Type Value In AM Or PM?

Oct 26, 2006

How do i insert or select date data type value in am or pm Eg 10:30:00 am or 10:35:00 pm

View 3 Replies View Related

How To Convert Long Data Type To CLOB

Nov 15, 2007

i am trying insert data from one DB to other DB table. one field data type is LONG in first DB table, Same field data type in other DB is CLOB.

i used TO_LOB function to convert from LONG to CLOB data type.

My problem is, i used this TO_LOB function, i got illegal operation of LONG Data type.

View 3 Replies View Related

Updating Long Data Type Column

Aug 31, 2010

I have a task to update one of the rows in a table (having only 2 columns, number and long) which is long data type. We are on Oracle 10g. Not sure how to use update for a long data type column.

I have tried using dbms_metadata_util.long2varchar, but still not getting what I want.

View 2 Replies View Related

Client Tools :: Different Type Of Data Export

Feb 24, 2010

I need a free/open source tool for exporting different kind of data sets to oracle.

View 5 Replies View Related

SQL & PL/SQL :: Create Object Then Use As Data Type Of New Table

Nov 11, 2012

I have existing table which needs to be copy to new table with object using the cursor. The exist table has a rank column which has some duplicate rank, which need to be remove and provide a series of numbers, like 1,2,3,4,5,...

create type UNIVERSITY as object (
U_RANK number(2),
U_SCHOOL varchar2(150),
U_COUNTRY varchar2(150),
U_SCORE number(3)
)

[code].....

DBMS_OUTPUT:
------------
Warnings: --->
W (1): Warning: execution completed with warning
<---
0 record(s) affected
[Executed: 11/11/2012 9:12:19 PM] [Execution: 31ms]

View 4 Replies View Related

SQL & PL/SQL :: Convert VARCHAR2 To Date Data Type

Jun 13, 2012

I have problem to convert simply varchar to date data type.

For example: 2012-05-28 22:36:08 and I would like to get format 28.5.2012 22:36:08

However I try to do it I get always some errors.

select '2012-05-28 22:36:08',
to_date( '2012-05-28 22:36:08', 'dd.mm.yyyy HH24:MI:SS') ,
to_char('2012-05-28 22:36:08', 'dd.mm.yyyy HH24:MI:SS')
from dual

View 7 Replies View Related

SQL & PL/SQL :: Convert Clob To Varchar Data Type

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

SQL & PL/SQL :: Getting ORA-00932 Inconsistent Data Type Error

Jan 26, 2011

I am getting this error on a varchar2(30) data column that I am trying to convert to number. I am creating a new view against some tables that house converted legacy data.The table is defined with a line_type column and a line_value columns. The line_type will contain either 'AMT', 'DATE' or 'STR'. The line_value column is defined as varchar2(30). Based on the line_type, that will determine how I format the line_value data. I have created the select for my view to translate this using a case statement:

CASE line_type
when 'AMT' then round(to_number(line_7_value),2) when 'DATE' then to_date(line_7_value, 'YYYY-MM-DD')
ELSE
line_7_value
END

I can't figure out what I am doing incorrectly. It does not like the "When 'AMT'" line, gives me an error of 'inconsistent data type, expected NUMBER, got CHAR". The data in line_7_value is defined as varchar2(30), I attempting to use To_number to convert it to a number, then round the number. I don't know why it says it is 'expecting number'.

View 4 Replies View Related

SQL & PL/SQL :: Table With VARCAHAR2 Data Type Column

Jul 28, 2010

I have a table with VARCAHAR2 data type column, I am unable to update that column with the following data.

UPDATE we set sname='(([Emp].Job.Catageory
IN 'SALES,COMPUTER,SCIENCE,TELESERVICE')
AND ([Employee].IDtype IN 'ABC'))'

I am getting the error "ORA-00933: SQL command not properly ended"

View 9 Replies View Related

SQL & PL/SQL :: Convert Binary To Oracle Data Type?

Jun 28, 2011

I have a data synch procedure where I am extracting data from an Oracle table and inserting it into a SQL Server table. The Oracle column is VARCHAR2 and the receiving SQL Server column is Binary. I am receiving a conversion error. I have tried HEXTORAW in the Oracle procedure but it does not work. What data type can I use.

View 5 Replies View Related







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