| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Execute Immediate vs. SQL*Plus syntax for CREATE OR REPLACE VIEW
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_service, domain,building,floor,floortile1,floortile2,racku,officename,
field,location,pdu1,pdu2,pdu3,circuit1,circuit2,circuit3, region,district,av,functionclass,class,usage,os,osversion, os_family,os_role,servicepack,mbss,ram,cpuqty,cpuvendor, cpuname,cpuspeed,hdqty1,hdsize1,hdqty2,hdsize2,diskspace,diskinuse,raid,tapedrive,fy03_sc,fy04_sc,backupmethod, backupschedule,backup_host,tapechanger1,tapechanger2, backupmonitor,offsite_vendor,offsite_acctno,offsite_freq, backup_comments,supportteam,normalized,active,missioncritical, sqlversion,oracleversion,iisversion,remotecontrol,monitoredby, wug_console,wug_map,wug_end,updated_from_ue,ue_conformance, ue_name,ue_console,ue_contact1,ue_contact2,epo,startdate, 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,appscomments, appsdetail,
s.hostname,s.serialno,s.bios,v.vendorname,m.model, m.end_of_service,s.domain,o.building,s.FLOOR,s.floortile1, 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.district, 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.vendorname, s.cpu_name,s.processorspeed,s.hdqty,s.hdsize,s.hdqty2,s.hdsize2, s.diskspace,s.diskinuse,s.raid,t.tapedrivemodel,NVL(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.remotecontrol, 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.leasesubid, s.acquisition_date,s.maint_contract_no,w.description, s.warrantyexpiration,s.warranty_updated,p3.firstname || 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_from_dom, s.updated_from_sms,s.updated_from_wmi,s.ups,ifc.description, s.ban,s.kvm,s.comments,s.apps,s.appscomments,s.appsdetail, NVL (c.description, 'TBD') company, NVL (b.description, 'TBD') bu, p9.firstname || ' ' || p9.lastname bu_contact1, s.ID
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
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(+)
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
--- > CREATE OR REPLACE VIEW askit.tss_serverextractbu 21a22 > company,bu,bu_contact1, 113a115,116 > NVL (c.description, 'TBD') company, NVL ( > p9.firstname || ' ' || p9.lastname bu_cont 205c208,212 < tss_interfaces ifc --- > tss_interfaces ifc, > tss_businessunit b, > tss_company c, > tss_server_bu sbu, > tss_people p9 237c244,248 < ORDER BY s.hostname,s.active --- > 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 Any ideas? Thanks!Received on Tue Apr 06 2004 - 12:19:58 CDT
 |
 |