• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2344
  • Last Modified:

Error 1703

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
chainreaction
Asked:
chainreaction
1 Solution
 
schwertnerCommented:
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
 
hans_vdCommented:
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
 
helpneedCommented:
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
 
Mehul ShahIT consultantCommented:
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
 
schwertnerCommented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now