Home » SQL & PL/SQL » SQL & PL/SQL » Create View in Oracle9.2.0.7.0 - Is this a bug?
Create View in Oracle9.2.0.7.0 - Is this a bug? [message #204340] Mon, 20 November 2006 03:49 Go to next message
Spark_Hong
Messages: 9
Registered: November 2006
Junior Member
Hi,

I installed the Oracle9.2.0.1.0 on Windows XP and then upgraded it to 9.2.0.7.0 via patch p4163445_92070_winnt.

I tested the following SQLs:

CREATE TABLE SPARK_TEST_1
(
NAME_1 VARCHAR2(64),
ID_1 VARCHAR2(64)
);

CREATE TABLE SPARK_TEST_2
(
NAME_2 VARCHAR2(64),
ID_2 VARCHAR2(64)
);

CREATE OR REPLACE VIEW SPARK_TEST_V
AS SELECT T1.*
, T2.ID_2 FROM SPARK_TEST_1 T1, SPARK_TEST_2 T2;

(Note that this SQL will separate into three rows, and the "," is in the first of third row)

Then I execute "Select * from SPARK_TEST_V", there will an error to indicate the "Expression Missing". I got the detailed view script below:

CREATE OR REPLACE FORCE VIEW SPARK_PO70.SPARK_TEST_V
(NAME_1, ID_1, ID_2)
AS
select t1."NAME_1",t1."ID_1",
,t2.id_2 from spark_test_1 t1,spark_test_2 t2;

Note that the T1.* will be spread to detailed columns but add a "," at the end of the row, that's why the error occurred.

I tested the same thing before upgrade to 9.2.0.7.0, that is in Oracle9.2.0.1.0, the result is OK, the detailed view script is:

CREATE OR REPLACE FORCE VIEW SPARK_PO70.SPARK_TEST_V
(NAME_1, ID_1, ID_2)
AS
select t1.*
,t2.id_2 from spark_test_1 t1,spark_test_2 t2;

Any one who can tell me what's the root cause?
Is this the bug of Oracle9.2.0.7.0?
Has any one met this problem before?

I'll appreciate if you give me some advice!

Thanks,
Spark
Re: Create View in Oracle9.2.0.7.0 - Is this a bug? [message #204350 is a reply to message #204340] Mon, 20 November 2006 04:38 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm on 9.2.0.7 Patch 7 on Win 2000 and I don't get your results. After running your first 3 commands, DBMS_METADATA gives me this as the SQL for the view:
CREATE OR REPLACE FORCE VIEW "JOHN_TEST"."SPARK_TEST_V" ("NAME_1", "ID_1", "ID_2") AS 
  SELECT T1."NAME_1",T1."ID_1"
, T2.ID_2 FROM SPARK_TEST_1 T1, SPARK_TEST_2 T2;

What are you using to get the text for the view from the database?

What happens if you reformat the CREATE VIEW as
CREATE OR REPLACE VIEW SPARK_TEST_V
AS 
SELECT T1.*, T2.ID_2 
FROM SPARK_TEST_1 T1
   , SPARK_TEST_2 T2;
Re: Create View in Oracle9.2.0.7.0 - Is this a bug? [message #204453 is a reply to message #204350] Mon, 20 November 2006 18:27 Go to previous messageGo to next message
Spark_Hong
Messages: 9
Registered: November 2006
Junior Member
I'm using the TOAD7.6 to get script of the view.

If I run the script as your format, there is no error. The issue only exists when the "," is at the beginning of the row.

By the way, My OS is Windows XP Pro., does something with this issue?
Re: Create View in Oracle9.2.0.7.0 - Is this a bug? [message #204510 is a reply to message #204453] Tue, 21 November 2006 01:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you've got a test case that reproduces the problem, I'd raise this as a TAR with Oracle. From what you say, it does sound like a bug.
Re: Create View in Oracle9.2.0.7.0 - Is this a bug? [message #204542 is a reply to message #204510] Tue, 21 November 2006 03:33 Go to previous messageGo to next message
Spark_Hong
Messages: 9
Registered: November 2006
Junior Member
Thanks, but how can I prepare for the test case?

What's other additional information should be added base on my first description?
Re: Create View in Oracle9.2.0.7.0 - Is this a bug? [message #204547 is a reply to message #204542] Tue, 21 November 2006 03:51 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Just create a script that contains all the SQL you need to reproduce the problem
Re: Create View in Oracle9.2.0.7.0 - Is this a bug? [message #204722 is a reply to message #204547] Tue, 21 November 2006 20:04 Go to previous messageGo to next message
Spark_Hong
Messages: 9
Registered: November 2006
Junior Member
Test Case:

OS: Windows2000 Advanced Server or Windows XP Pro.
Oracle: Oracle9.2.0.1.0 upgrade to Oracle9.2.0.7.0

1.Create user with some privilege.
2.Connect to DB with this user.
3.Create a sql file with the following contents:
---------------------------------------------------
DROP TABLE SPARK_TEST_1
/

CREATE TABLE SPARK_TEST_1
(
NAME_1 VARCHAR2(64),
ID_1 VARCHAR2(64)
)
/

DROP TABLE SPARK_TEST_2
/

CREATE TABLE SPARK_TEST_2
(
NAME_2 VARCHAR2(64),
ID_2 VARCHAR2(64)
)
/

DROP VIEW SPARK_TEST_V
/

CREATE OR REPLACE VIEW SPARK_TEST_V
AS SELECT T1.*
, T2.ID_2 FROM SPARK_TEST_1 T1, SPARK_TEST_2 T2
/

SELECT * FROM SPARK_TEST_V
/
---------------------------------------------------
4.Run this sql script.

Excepted Result: View is created and no row is selected.
For example, I run it on Oracle9.2.0.1.0 and get the result:
---------------------------------------------------
SQL> @ora_issue.sql
DROP TABLE SPARK_TEST_1
*
ERROR at line 1:
ORA-00942: table or view does not exist



Table created.

DROP TABLE SPARK_TEST_2
*
ERROR at line 1:
ORA-00942: table or view does not exist



Table created.

DROP VIEW SPARK_TEST_V
*
ERROR at line 1:
ORA-00942: table or view does not exist



View created.


no rows selected
--------------------------------------------------

Unexpected Result: View is created but error in select, raise "ORA-00936: missing expression"
For example, I run it on Oracle9.2.0.7.0 and get the result:
---------------------------------------------------
SQL> @ora_issue.sql

Table dropped.


Table created.


Table dropped.


Table created.


View dropped.


View created.

SELECT * FROM SPARK_TEST_V
*
ERROR at line 1:
ORA-00936: missing expression
-------------------------------------------------------

Thanks.
Re: Create View in Oracle9.2.0.7.0 - Is this a bug? [message #204745 is a reply to message #204547] Tue, 21 November 2006 23:09 Go to previous message
Spark_Hong
Messages: 9
Registered: November 2006
Junior Member
Hi JRowbottom,

I have looked up the Bug Database of Oracle and it is really the bug and fixed in Oracle9.2.0.8.0.

The Bug ID is : 4192148, for anyone who will run into this issue!

Thank you very much!

Regards,
Spark
Previous Topic: Oracle Keywords
Next Topic: TRIM
Goto Forum:
  


Current Time: Sun Dec 04 02:36:44 CST 2016

Total time taken to generate the page: 0.07273 seconds