?
Solved

Error 1703

Posted on 2005-04-05
5
Medium Priority
?
2,214 Views
Last Modified: 2012-08-14
Hi, i am doing hald way import the new database and i have this error. I admit i have done some database changes (alter) but basically it's all about the adding new table field. But then, dont know what is this error.. please guide.

. . importing table                   "UAXLUNMOVM"          0 rows imported
. . importing table                  "UAXLYTBONUS"          0 rows imported
. . importing table                     "VATLCTRL"          0 rows imported
IMP-00017: following statement failed with ORACLE error 1730:
 "CREATE FORCE VIEW "VL"."VWTSNBAPPLBRANCH"                              ("PR"
 "OPOSALNO","AGENTCODE1","APPLBRNCD","BRAN","NBBRN","JLFLAG","SBBRAN","CAB",""
 "ABR","ACI","ATE","REGI","ACCTBRN","OPERATORID","TRANSDATE") AS "
 "select a.proposalno, a.agentcode1, a.applbrncd,"
 "        c.* from vl.nbxlpropd a, vl.pdtlbranch c where  a.applbrncd = c.nbbrn"

 "        "
IMP-00003: ORACLE error 1730 encountered
ORA-01730: invalid number of column names specified
IMP-00017: following statement failed with ORACLE error 1730:
 "CREATE FORCE VIEW "VL"."VWAGTBRANCH"                              ("POLICYN"
 "O","AGENTCODE1","BRAN","NBBRN","JLFLAG","SBBRAN","CAB","ABR","ACI","ATE","R"
 "EGI","ACCTBRN","OPERATORID","TRANSDATE") AS "
 "select a.policyno, d.agentcode, c.* from"
 "        vl.cwmlpolmd a, vl.agmlagent b, vl.pdtlbranch c, vl.vwcwmlpolma_first
d "
 "where a.policyno = d.policyno and d.agentcode = b.agentcode and b.smcd = c."
 "bran "
IMP-00003: ORACLE error 1730 encountered
ORA-01730: invalid number of column names specified
IMP-00017: following statement failed with ORACLE error 1730:
 "CREATE FORCE VIEW "VL"."VWTSAPPLBRANCH"                              ("POLI"
 "CYNO","AGENTCODE1","APPLBRNCD","BRAN","NBBRN","JLFLAG","SBBRAN","CAB","ABR""
 ","ACI","ATE","REGI","ACCTBRN","OPERATORID","TRANSDATE") AS "
 "select a.policyno, b.agentcode, a.applbrncd,"
 "        c.* from vl.cwmlpolmd a, vl.vwcwmlpolma_first b, vl.pdtlbranch c where
 a"
 ".policyno=b.policyno and a.applbrncd = c.nbbrn"
IMP-00003: ORACLE error 1730 encountered
ORA-01730: invalid number of column names specified
About to enable constraints...
Import terminated successfully with warnings.
0
Comment
Question by:chainreaction
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 48

Expert Comment

by:schwertner
ID: 13704790
Look first if the view is OK on the source instance.
It seems that there is an error caused by the new collumn(s) you added to the table.
0
 
LVL 6

Expert Comment

by:hans_vd
ID: 13704889
Never use "select *"

After adding the new field to the table, the number of columns in the table (pdtlbranch) + the three other fields in the select is not longer equal to the number of columns in your view definition.

Best solution: replace "select ...  c.* " by "select... , c.field1, c.field2, ..."
0
 
LVL 5

Expert Comment

by:helpneed
ID: 13705063
hi

The number of column names specified in a CREATE VIEW statement did not correspond to the number of columns listed in the SELECT clause. If column names are specified in a CREATE VIEW statement, exactly one name must be specified for each column or expression in the SELECT clause.

Specify one view column name for each column in the SELECT clause
regards
0
 
LVL 7

Expert Comment

by:Mehul Shah
ID: 13705170
Yes as hans_vd suggested the problem is with the below statement

CREATE FORCE VIEW "VL"."VWTSNBAPPLBRANCH"                              ("PR"
 "OPOSALNO","AGENTCODE1","APPLBRNCD","BRAN","NBBRN","JLFLAG","SBBRAN","CAB",""
 "ABR","ACI","ATE","REGI","ACCTBRN","OPERATORID","TRANSDATE") AS "
 "select a.proposalno, a.agentcode1, a.applbrncd,"
 "        c.* from vl.nbxlpropd a, vl.pdtlbranch c where  a.applbrncd = c.nbbrn"

In the view query you have mentioned column titles before the actual query and you are using c.* in your view query. If you have added any additional column in vl.pdtlbranch  table than c.* will include that column where as in the view defination the respective titile does not exists and so the view creations fails with ORA-1730 as in the column definations you have provided 15 names and your view query is fetching more than 15 columns.


Hope the helps
0
 
LVL 48

Accepted Solution

by:
schwertner earned 1940 total points
ID: 13705292
Add here

"CREATE FORCE VIEW "VL"."VWTSNBAPPLBRANCH"                              ("PR"
"OPOSALNO","AGENTCODE1","APPLBRNCD","BRAN","NBBRN","JLFLAG","SBBRAN","CAB",""
"ABR","ACI","ATE","REGI","ACCTBRN","OPERATORID","TRANSDATE")

all new columns you have added to the table vl.pdtlbranch
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question