Home » SQL & PL/SQL » SQL & PL/SQL » Object type Casting and %type (merged)
Object type Casting and %type (merged) [message #316180] Thu, 24 April 2008 03:01 Go to next message
rangan.s
Messages: 75
Registered: February 2008
Location: chennai
Member
We are facing problem in typecasting object. can you help on that

i used the following method. iam getting error pls help

In SQL Server

CREATE TYPE t_emp_desig FROM varchar(180)
go
CREATE TABLE [emp]
(
[id] [int],
emp_name varchar(255),
emp_desc t_emp_desig
)
GO
INSERT INTO emp VALUES(1,'xyz','supervisor')
go
SELECT * FROM emp


In Oracle

CREATE OR REPLACE TYPE t_emp_desig AS OBJECT (name_type varchar2(180))

CREATE TABLE emp(id number, name varchar2(255), desig EMP_DESIG)
/

INSERT INTO EMP VALUES(1,'XYZ','SUPERVISOR')


ERROR : ORA-00932: inconsistent datatypes: expected PACE_MASTERDBO.EMP_DESIG got CHAR




But
INSERT INTO EMP VALUES(1,'XYZ',EMP_DESIG('SUPERVISOR'))
/
Is working fine.

How to make it as like sql server without typecasting?
Re: Object Type Casting Problem? [message #316204 is a reply to message #316180] Thu, 24 April 2008 04:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> CREATE OR REPLACE TYPE t_emp_desig AS OBJECT (name_type varchar2(180))
  2  /

Type created.

SQL> CREATE TABLE t (id number, name varchar2(255), desig t_EMP_DESIG)
  2  /

Table created.

SQL> INSERT INTO t VALUES(1,'XYZ',t_EMP_DESIG('SUPERVISOR'));

1 row created.

If you use an object type you have to generate an instance of this type.

Also next time format your post as I did.

Regards
Michel
Re: Object Type Casting Problem? [message #316212 is a reply to message #316204] Thu, 24 April 2008 05:09 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Also, this might not be rally necessary in Oracle.

I have seen it used in SQL Server, but as far as I was able to figure out it was because they wanted to keep the column types in sync with the variable types in the T-SQL Packages.

The way to do that in Oracle is to just define the tables with the data types you want and then use the table column type in the PL/SQL variable definition, like :

SQL> CREATE TABLE test_table (test_col VARCHAR2(180));

Table created.

SQL>
SQL>
SQL> DECLARE
  2      test_variable test_table.test_col%TYPE;
  3  BEGIN
  4     test_variable := 'YO';
  5     Dbms_Output.put_line(test_variable);
  6  END;
  7  /
YO

PL/SQL procedure successfully completed.
Re: Object Type Casting Problem? [message #326379 is a reply to message #316212] Wed, 11 June 2008 05:07 Go to previous messageGo to next message
rangan.s
Messages: 75
Registered: February 2008
Location: chennai
Member
I am facing problem while creating this user-defined datatype while create table. I am trying to use as below.

SQL> CREATE TABLE test_table (test_col VARCHAR2(180));

SQL> CREATE TABLE main_table(COL1 test_table.test_col%type)

ORA-00911: invalid character
Re: Object Type Casting Problem? [message #326385 is a reply to message #326379] Wed, 11 June 2008 05:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This can't be done.
You have to create a specific permanent type as I mentioned above.

Regards
Michel
icon5.gif  How to use %TYPE in tables? [message #326531 is a reply to message #316180] Wed, 11 June 2008 22:37 Go to previous messageGo to next message
venki5star
Messages: 4
Registered: March 2008
Location: Chennai
Junior Member
Hi All,
I am new to the Oracle world since working in SQL Server. I have an issue with user defined data. I am working in a project which works on SQL Server and Oracle. In SQL Server, I am using user data types for some of the table columns. When I tried to replicate the same in Oracle, I am stopped due to an issue.

In SQL Server while inserting the field which is using the user defined data type does an automatic casting and inserts the record. Whereas in Oracle, I need to cast it. Since the Application development is over and all the INSERTs & UPDATEs are handled inside for SQL Server assuming Oracle also has the same syntax, I can't change the INSERTs and UPDATEs with TYPE casting.

I heard from a friend that I can using another table's column as data type like

SQL> CREATE TABLE test_table (test_col VARCHAR2(180));

Table created

SQL> DECLARE
  2      test_variable test_table.test_col%TYPE;
  3  BEGIN
  4     test_variable := 'YO';
  5     Dbms_Output.put_line(test_variable);
  6  END;
  7  /
YO

PL/SQL procedure successfully completed.


The above code working with the simple print. But when I tried to create table, I getting error on "%" symbol as "ORA-00911: invalid character". I tried like,

SQL> CREATE TABLE test_table (test_col VARCHAR2(180));

Table created
 

SQL> create table test_1(col1  test_table.test_col%type);

ORA-00911: invalid character


Is there a solution for this or I have to use Casting all the way?

Thanks in Advance

Venkatesh Mookkan
Re: How to use %TYPE in tables? [message #326532 is a reply to message #326531] Wed, 11 June 2008 22:44 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
PL/SQL<>SQL<>SQL*Plus
All 3 of the above are separate, distinct, & DIFFERENT from each other.
What is valid in 1, may not be valid in either of the others.

When all else fails you should Read The Fine Reference Manuals for each which can be found at http://tahiti.oracle.com

>I have an issue with user defined data
Why do you require "user defined data"?
In 15 years with Oracle, I've always used Oracle defined datatypes.

>How to use %TYPE in tables?
You don't.

[Updated on: Wed, 11 June 2008 22:45] by Moderator

Report message to a moderator

Re: How to use %TYPE in tables? [message #326536 is a reply to message #326532] Wed, 11 June 2008 23:11 Go to previous messageGo to next message
venki5star
Messages: 4
Registered: March 2008
Location: Chennai
Junior Member
Thanks for replying Anacedent.

>I have an issue with user defined data
Why do you require "user defined data"?
In 15 years with Oracle, I've always used Oracle defined datatypes.

Sorry, its my bad. Its not "user defined data", its "user defined data types".

I could explain you in SQL Server syntax (that is what I know)

I am having my own data types like
id_type - VARCHAR (180)
name_type - VARCHAR (180)

My tables uses these data types as

id - id_type (sample field)
name - name_type (sample field)

If I need to change the datatype or increase the size of the field, I can change it in my id_type or name_type itself and this would affect all the places where I used my user defined data types. I have a 'n' number of tables, changing all the tables will not be perfect and time consuming.

Hope this would make you understand my problem. How to acheive this?
Re: How to use %TYPE in tables? [message #326541 is a reply to message #326531] Wed, 11 June 2008 23:20 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>How to acheive this?
As Far As I Know, Oracle contains no automated mechanism, but I honestly question how practical it is.

It is trivial to write a search & replace capability.
Besides if the application has already been released,
a completely different solution is required to ALTER existing objects;
as opposed to CREATE new object(s) with new, correct size.
Re: How to use %TYPE in tables? [message #326545 is a reply to message #326531] Wed, 11 June 2008 23:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Same answer to the same question with the same IP (have you many IDs?):
http://www.orafaq.com/forum/mv/msg/101337/326379/102589/#msg_326379

Regards
Michel
Re: How to use %TYPE in tables? [message #326548 is a reply to message #326541] Wed, 11 June 2008 23:30 Go to previous messageGo to next message
venki5star
Messages: 4
Registered: March 2008
Location: Chennai
Junior Member
Thanks Ana.

Since you are in Oracle 15 years, you would know very much about Oracle. I will take your suggestion. The problem with me is we are using functions which returns as TABLE with these columns. I am having 100s of functions like this. I have adding casting of each functions now. Confused




Venkatesh Mookkan
Re: How to use %TYPE in tables? [message #326550 is a reply to message #326545] Wed, 11 June 2008 23:32 Go to previous messageGo to next message
venki5star
Messages: 4
Registered: March 2008
Location: Chennai
Junior Member
Its not my ID Michel.

He is my colleage. Both are assigned the same job to research. I don't know that he raised question here.

[Updated on: Wed, 11 June 2008 23:34]

Report message to a moderator

Re: How to use %TYPE in tables? [message #326571 is a reply to message #326550] Thu, 12 June 2008 00:49 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I don't know that he raised question here.

This is the reason of Search field and button: to find the answer to same question.

Quote:
Both are assigned the same job to research

And you don't talk each other?

So now you have answers.

Regards
Michel
Previous Topic: Where clause and a plus sign
Next Topic: Generating sequence numbers for Duplicate records
Goto Forum:
  


Current Time: Fri Dec 09 21:22:45 CST 2016

Total time taken to generate the page: 0.25678 seconds