asked on

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.

schwertner

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.

hans_vd

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, ..."

helpneed

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

Mehul Shah

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

ASKER CERTIFIED SOLUTION

schwertner

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial