I am using Oracle 8i. I have code that creates a long (but less than
32k) string that contains a CREATE OR REPLACE VIEW statement. If I
take the content of that string and execute it directly in SQL*Plus
the view is successfully created. If I "EXECUTE IMMEDIATE" within a
procedure, I get an error (ORA-01730: invalid number of column names
specified). I tried adding "AUTHID CURRENT_USER" but it made no
difference. I have another similar procedure that also does a CREATE
OR REPLACE VIEW and it works, so I don't think this is a permissions
issue.

Are there any syntax restrictions on EXECUTE IMMEDIATE within a
procedure that do not also apply to statements run directly?

The command string that is generated is as follows:

CREATE OR REPLACE VIEW askit.tss_serverextractbu(
hostname,serialno,bios,vendorname,model,end_of_ser vice,
domain,building,floor,floortile1,floortile2,racku, officename,
field,location,pdu1,pdu2,pdu3,circuit1,circuit2,ci rcuit3,
region,district,av,functionclass,class,usage,os,os version,
os_family,os_role,servicepack,mbss,ram,cpuqty,cpuv endor,
cpuname,cpuspeed,hdqty1,hdsize1,hdqty2,hdsize2,dis kspace,
diskinuse,raid,tapedrive,fy03_sc,fy04_sc,backupmet hod,
backupschedule,backup_host,tapechanger1,tapechange r2,
backupmonitor,offsite_vendor,offsite_acctno,offsit e_freq,
backup_comments,supportteam,normalized,active,miss ioncritical,
sqlversion,oracleversion,iisversion,remotecontrol, monitoredby,
wug_console,wug_map,wug_end,updated_from_ue,ue_con formance,
ue_name,ue_console,ue_contact1,ue_contact2,epo,sta rtdate,
enddate,leasestart,leaseend,leasesubid,acquisition _date,
maint_contract_no,warrantytype,warrantyexpiration,
warranty_updated,ists_manager,support1,support2,
rebootcoordinator1,rebootcoordinator2,updated_from _cim,
updated_from_dom,updated_from_sms,updated_from_wmi ,ups,
extdisk_interface,ban,kvm,comments,apps,appscommen ts,
appsdetail,
company,bu,bu_contact1,
id
,SVC_AD_DNM
,SVC_AD_GC
,SVC_AD_IM
,SVC_AD_Other
,SVC_AD_PDC
,SVC_AD_RIDM
,SVC_AD_SM
,SVC_BackupTarget
,SVC_Citrix
,SVC_DHCP
,SVC_DNS
,SVC_DomainAuth
,SVC_FAX
,SVC_FTP
,SVC_Firewall
,SVC_HTTP
,SVC_LDAP
,SVC_MediaServer
,SVC_NetMon
,SVC_NotesDB
,SVC_NotesHub
,SVC_NotesMail
,SVC_PatchMgmt
,SVC_Printers
,SVC_Printers_Exec
,SVC_RAS
,SVC_SMTP
,SVC_SecMon
,SVC_Shares
,SVC_Shares_Exec
,SVC_SoftDist
,SVC_SysMon
,SVC_TaskSched
,SVC_TermServices
,SVC_TreeAuth
,SVC_Virus
,SVC_Voice
,SVC_WINS
,FC_AD
,FC_App
,FC_BDC
,FC_Citrix
,FC_Core
,FC_DB
,FC_Emedia
,FC_Exchange
,FC_ExecFP
,FC_FAX
,FC_FP
,FC_MPAR
,FC_NDS
,FC_Notes
,FC_NotesAp
,FC_PDC
,FC_SMTP
,FC_Security
,FC_TBD
,FC_TS
,FC_Web
) AS SELECT
s.hostname,s.serialno,s.bios,v.vendorname,m.model,
m.end_of_service,s.domain,o.building,s.FLOOR,s.flo ortile1,
s.floortile2,s.racku,o.officename,DECODE (s.officeid,84,0,85,
0,123,0,360,0,1) FIELD,s.LOCATION,s.pdu1,s.pdu2,s.pdu3,
s.circuit1,s.circuit2,s.circuit3,r.region,d.distri ct,
av.description av,fc.description functionclass,s.CLASS,
u.description USAGE,os.os,s.osversion,os.os_family,s.servertype,
s.servicepack,s.mbss,s.ram,s.processorqty,v1.vendo rname,
s.cpu_name,s.processorspeed,s.hdqty,s.hdsize,s.hdq ty2,s.hdsize2,
s.diskspace,s.diskinuse,s.raid,t.tapedrivemodel,NV L
(s.service_category,0) fy03_sc,NVL (s.nextfy_service_category,0)
fy04_sc,bm.description backupmethod,bs.description backupschedule,
s.backup_host,p5.firstname || p5.lastname tapechanger1,
p6.firstname || p6.lastname tapechanger2,st1.description
backupmonitor,v2.vendorname offsite_vendor,offsite.acct_no,
ofreq.description,s.backup_comments,st.description supportteam,NVL
(s.normalized,0),NVL (s.active,0),NVL (s.missioncritical,0),
s.sqlversion,s.oracleversion,s.iisversion,s.remote control,
s.monitoredby,s.wug_console,s.wug_map,s.wug_end,
s.updated_from_ue,NVL (s.ue_conformance,'TBD'),ue.NAME,
ues.hostname ue_console,p7.firstname || p7.lastname
ue_contact1,p8.firstname || p8.lastname ue_contact2,s.epo,
s.startdate,s.enddate,s.leasestart,s.leaseend,s.le asesubid,
s.acquisition_date,s.maint_contract_no,w.descripti on,
s.warrantyexpiration,s.warranty_updated,p3.firstna me ||
p3.lastname ists_manager,p.firstname || p.lastname support1,
p1.firstname || p1.lastname support2,p2.firstname ||
p2.lastname rebootcoordinator1,p4.firstname || p4.lastname
rebootcoordinator2,s.updated_from_cim,s.updated_fr om_dom,
s.updated_from_sms,s.updated_from_wmi,s.ups,ifc.de scription,
s.ban,s.kvm,s.comments,s.apps,s.appscomments,s.app sdetail,
NVL (c.description, 'TBD') company, NVL (b.description, 'TBD') bu,
p9.firstname || ' ' || p9.lastname bu_contact1,
s.ID
,1541 SVC_AD_DNM
,1542 SVC_AD_GC
,1543 SVC_AD_IM
,1544 SVC_AD_Other
,1545 SVC_AD_PDC
,1546 SVC_AD_RIDM
,1547 SVC_AD_SM
,566 SVC_BackupTarget
,1550 SVC_Citrix
,1470 SVC_DHCP
,1474 SVC_DNS
,1062 SVC_DomainAuth
,1552 SVC_FAX
,1555 SVC_FTP
,1402 SVC_Firewall
,1342 SVC_HTTP
,420 SVC_LDAP
,1548 SVC_MediaServer
,1522 SVC_NetMon
,582 SVC_NotesDB
,1559 SVC_NotesHub
,563 SVC_NotesMail
,1561 SVC_PatchMgmt
,1562 SVC_Printers
,1563 SVC_Printers_Exec
,1564 SVC_RAS
,581 SVC_SMTP
,1623 SVC_SecMon
,564 SVC_Shares
,1554 SVC_Shares_Exec
,642 SVC_SoftDist
,1482 SVC_SysMon
,565 SVC_TaskSched
,1567 SVC_TermServices
,1184 SVC_TreeAuth
,1568 SVC_Virus
,1569 SVC_Voice
,1472 SVC_WINS
,(SELECT count(sfc.functionclass_id) from tss_server_fc sfc where
sfc.device_id=s.device_id and sfc.functionclass_id=96) FC_AD
,(SELECT count(sfc.functionclass_id) from tss_server_fc sfc where
sfc.device_id=s.device_id and sfc.functionclass_id=2) FC_App
,(SELECT count(sfc.functionclass_id) from tss_server_fc sfc where
sfc.device_id=s.device_id and sfc.functionclass_id=12) FC_BDC
,(SELECT count(sfc.functionclass_id) from tss_server_fc sfc where
sfc.device_id=s.device_id and sfc.functionclass_id=3) FC_Citrix
,(SELECT count(sfc.functionclass_id) from tss_server_fc sfc where
sfc.device_id=s.device_id and sfc.functionclass_id=5) FC_Core
,(SELECT count(sfc.functionclass_id) from tss_server_fc sfc where
sfc.device_id=s.device_id and sfc.functionclass_id=4) FC_DB
,(SELECT count(sfc.functionclass_id) from tss_server_fc sfc where
sfc.device_id=s.device_id and sfc.functionclass_id=14) FC_Emedia
,(SELECT count(sfc.functionclass_id) from tss_server_fc sfc where
sfc.device_id=s.device_id and sfc.functionclass_id=36) FC_Exchange
,(SELECT count(sfc.functionclass_id) from tss_server_fc sfc where
sfc.device_id=s.device_id and sfc.functionclass_id=56) FC_ExecFP
,(SELECT count(sfc.functionclass_id) from tss_server_fc sfc where
sfc.device_id=s.device_id and sfc.functionclass_id=13) FC_FAX
,(SELECT count(sfc.functionclass_id) from tss_server_fc sfc where
sfc.device_id=s.device_id and sfc.functionclass_id=1) FC_FP
,(SELECT count(sfc.functionclass_id) from tss_server_fc sfc where
sfc.device_id=s.device_id and sfc.functionclass_id=15) FC_MPAR
,(SELECT count(sfc.functionclass_id) from tss_server_fc sfc where
sfc.device_id=s.device_id and sfc.functionclass_id=10) FC_NDS
,(SELECT count(sfc.functionclass_id) from tss_server_fc sfc where
sfc.device_id=s.device_id and sfc.functionclass_id=8) FC_Notes
,(SELECT count(sfc.functionclass_id) from tss_server_fc sfc where
sfc.device_id=s.device_id and sfc.functionclass_id=116) FC_NotesAp
,(SELECT count(sfc.functionclass_id) from tss_server_fc sfc where
sfc.device_id=s.device_id and sfc.functionclass_id=6) FC_PDC
,(SELECT count(sfc.functionclass_id) from tss_server_fc sfc where
sfc.device_id=s.device_id and sfc.functionclass_id=76) FC_SMTP
,(SELECT count(sfc.functionclass_id) from tss_server_fc sfc where
sfc.device_id=s.device_id and sfc.functionclass_id=11) FC_Security
,(SELECT count(sfc.functionclass_id) from tss_server_fc sfc where
sfc.device_id=s.device_id and sfc.functionclass_id=0) FC_TBD
,(SELECT count(sfc.functionclass_id) from tss_server_fc sfc where
sfc.device_id=s.device_id and sfc.functionclass_id=16) FC_TS
,(SELECT count(sfc.functionclass_id) from tss_server_fc sfc where
sfc.device_id=s.device_id and sfc.functionclass_id=7) FC_Web
FROM tss_people p1,
tss_people p3,
tss_people p,
tss_people p2,
tss_people p4,
tss_people p5,
tss_people p6,
tss_people p7,
tss_people p8,
tss_regions r,
tss_usage u,
tss_functionclass fc,
tss_district d,
tss_antivirus av,
tss_os os,
tss_supportteam st,
tss_supportteam st1,
tss_vendors v,
tss_vendors v1,
tss_vendors v2,
tss_models m,
tss_backupschedules bs,
tss_backupmethods bm,
tss_servers s,
tss_offices o,
tss_tapedrives t,
tss_warrantytype w,
tss_ue_consoles ue,
tss_servers ues,
tss_offsite offsite,
tss_offsitefreq ofreq,
tss_interfaces ifc,
tss_businessunit b,
tss_company c,
tss_server_bu sbu,
tss_people p9
WHERE NVL (s.functionclass,0) = fc.ID(+)
AND NVL (s.USAGE,0) = u.ID(+)
AND NVL (s.antivirus,'TBD') = av.description(+)
AND NVL (s.os_id,0) = os.ID(+)
AND NVL (s.backupmethod,0) = bm.ID(+)
AND NVL (s.backupschedule,0) = bs.ID(+)
AND NVL (s.supportteam,0) = st.ID(+)
AND NVL (s.tssmanagement,0) = p3.ID(+)
AND NVL (s.support1,0) = p.ID(+)
AND NVL (s.support2,0) = p1.ID(+)
AND NVL (s.rebootcoordinator1,0) = p2.ID(+)
AND NVL (s.rebootcoordinator2,0) = p4.ID(+)
AND NVL (s.tapechanger1,0) = p5.ID(+)
AND NVL (s.tapechanger2,0) = p6.ID(+)
AND NVL (s.backup_monitor,0) = st1.ID(+)
AND NVL (s.modelid,0) = m.ID(+)
AND NVL (m.mfg,0) = v.ID(+)
AND NVL (s.officeid,0) = o.ID(+)
AND NVL (o.district,0) = d.ID(+)
AND NVL (d.region,0) = r.ID(+)
AND NVL (s.tapedrive,0) = t.ID(+)
AND NVL (s.warrantytype,0) = w.ID(+)
AND NVL (s.ue_console_id,0) = ue.ID(+)
AND NVL (ue.server_id,0) = ues.ID(+)
AND NVL (ue.contact1_id,0) = p7.ID(+)
AND NVL (ue.contact2_id,0) = p8.ID(+)
AND NVL (s.cpu_vendor,0) = v1.ID(+)
AND NVL (s.offsite_id,0) = offsite.ID(+)
AND NVL (offsite.vendor_id,0) = v2.ID(+)
AND NVL (s.offsite_pickup_freq,0) = ofreq.ID(+)
AND NVL (s.extdisk_interface_id,0) = ifc.ID(+)
AND s.ID = sbu.server_id(+)
AND sbu.bu_id = b.ID(+)
AND b.company = c.ID(+)
AND NVL (sbu.bucontact1, 0) = p9.ID(+)
ORDER BY s.hostname,s.active,company,bu

The one that works is almost identical -- the new one just adds fields
from some additional tables. Here's a diff:

C:\TempFiles>diff 1.txt 2.txt
1c1
< CREATE OR REPLACE VIEW askit.tss_serverextract
---
< tss_interfaces ifc
---
< ORDER BY s.hostname,s.active
---
Any ideas?

Thanks!

Mark Freeman wrote:

Just one ... stop performing DDL in a proc.

There is no justification for doing this so just don't. Build the
view and then leave it alone.

Sorry but it is the only idea I have.
--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

In article <df323d2.0404060919.3f14f5db (AT) posting (DOT) google.com>,
mark_freeman (AT) asc (DOT) aon.com says...

Still, it's not a good idea to be doing this in a proc.

-- Tom

Daniel Morgan <damorgan@x.washington.edu> wrote

There is actually a need for this. The view includes different
columns depending on the contents of tables whose content occasionally
changes. The procedure will run nightly to ensure that the view is in
sync with the data.

I broke down the code that generated the CREATE OR REPLACE VIEW
statement into modules, made them as consistent with each other as
possible, simplified the use of variables and cursors, and now the
generated code works fine.

I never did figure out why the original statement would execute
directly but not via the EXECUTE IMMEDIATE, but I'm not going to worry
about it now.

Thanks for the reply.

- Mark

tojo <Tojo (AT) hotmail (DOT) com> wrote

I addressed the need for this in another message in this thread.

I will definitely look into the alternate syntax you suggested. I
hate having to list the columns twice, and would much rather specify
them and their aliases in one place. I thought that listing them in
two places like that was a requirement.

Thanks for the reply.

- Mark