Object type Casting and %type (merged) [message #316180] |
Thu, 24 April 2008 03:01  |
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 #316212 is a reply to message #316204] |
Thu, 24 April 2008 05:09   |
ThomasG
Messages: 3212 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   |
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: How to use %TYPE in tables? [message #326532 is a reply to message #326531] |
Wed, 11 June 2008 22:44   |
 |
BlackSwan
Messages: 26766 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   |
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 #326571 is a reply to message #326550] |
Thu, 12 June 2008 00:49  |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
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
|
|
|