oracle - Procedure with looping and execute immediate -



oracle - Procedure with looping and execute immediate -

recently have started preparing datamarts regular reporting process, , tried create utilize of procedures parameters.

so read this guideline , tried replicate confused error in compiling.

here code:

create or replace procedure mig_rate @rep_date date create or replace table mig_temp1( report_date date, portfolio string, bucket integer, q integer); begin j in 1..7 loop t in 0..32 loop -- execute immediate 'bla-bla-bla insert statement mig_temp1 end loop; end loop; end

this results in pls-00103 saying @ symbol unexpected.

what's wrong syntax, how should set parameters procedure??

the main thing wrong you're trying utilize sql server syntax in oracle database.

firstly, oracle object names have start letter (unless utilize quoted identifiers, please don't) and... well, here relevant parts:

nonquoted identifiers must begin alphabetic character database character set. quoted identifiers can begin character.

nonquoted identifiers can contain alphanumeric characters database character set , underscore (_), dollar sign ($), , pound sign (#). database links can contain periods (.) , "at" signs (@). oracle discourages using $ , # in nonquoted identifiers.

quoted identifiers can contain characters , punctuations marks spaces. however, neither quoted nor nonquoted identifiers can contain double quotation marks or null character (\0).

... can't utilize @ @ in procedure name, , don't need that's sql server thing (i think, don't utilize that!).

edit: see, i'm unfamiliar sql server misread doing. procedure name ok, that's not how declare parameters; should start:

create or replace procedure mig_rate(rep_date date) ...

secondly, can't create (declare?) table in oracle procedure. looks want temporary table duration of procedure, that's not how oracle works; can create global temporary table outside procedure it's permanent database object - info temporary.

you can utilize pl/sql collection instead depends want it. if it's staging area before inserting normal table might able single sql command, perform better.

i'm not sure why you'd want utilize dynamic sql (execute immediate) here...

please refer pl/sql language reference , oracle-specific sites rather sql server-specific sites larn do; there important differences in how work.

oracle pls-00103

Comments

Post a Comment

Popular posts from this blog

web services - java.lang.NoClassDefFoundError: Could not initialize class net.sf.cglib.proxy.Enhancer -

web services - java.lang.NoClassDefFoundError: Could not initialize class net.sf.cglib.proxy.Enhancer - when trying request rest service @ time of response getting exception.. feb 21, 2013 2:34:49 pm com.sun.jersey.spi.container.containerresponse mapmappablecontainerexception severe: exception contained within mappablecontainerexception not mapped response, re-throwing http container java.lang.noclassdeffounderror: not initialize class net.sf.cglib.proxy.enhancer @ org.hibernate.proxy.pojo.cglib.cgliblazyinitializer.getproxyfactory(cgliblazyinitializer.java:117) @ org.hibernate.proxy.pojo.cglib.cglibproxyfactory.postinstantiate(cglibproxyfactory.java:43) @ org.hibernate.tuple.entity.pojoentitytuplizer.buildproxyfactory(pojoentitytuplizer.java:162) @ org.hibernate.tuple.entity.abstractentitytuplizer.<init>(abstractentitytuplizer.java:135) @ org.hibernate.tuple.entity.pojoentitytuplizer.<init>(pojoentitytuplizer.java:55) @ org.hibernate.tuple.e...
Read more

Accessing MATLAB's unicode strings from C -

Accessing MATLAB's unicode strings from C - how can access underlying unicode info of matlab strings through matlab engine or mex c interfaces? here's example. let's set unicode characters in utf-8 encoded file test.txt, read as fid=fopen('test.txt','r','l','utf-8'); s=fscanf(fid, '%s') in matlab. now if first feature('defaultcharacterset', 'utf-8') , c engevalstring(ep, "s") , output text file utf-8. proves matlab stores unicode internally. if mxarraytostring(enggetvariable(ep, "s")) , unicode2native(s, 'latin-1') give me in matlab: non-latin-1 characters replaced character code 26. need getting access underlying unicode info c string in unicode format (utf-8, utf-16, etc.), , preserving non-latin-1 characters. is possible? my platform os x, matlab r2012b. addendum: documentation explicitly states "[mxarraytostring()] supports multibyte encoded characters...
Read more

javascript - mongodb won't find my schema method in nested container -

javascript - mongodb won't find my schema method in nested container - i trying access method of schema stored within mixed container. here situation : have cases model can many different things, have schema each of these things stored in "casecontent" mixed property. var caseschema = mongoose.schema({ casecontent : {}, object : {type:string, default : "null"}, collision : {type : boolean, default : false} }); the casecontent property filled model of 1 of schemas, 1 exemple : var treeschema = new mongoose.schema({ applecount : {type : number, default : 3} }); treeschema.methods.dostuff = function (data) { console.log('hey, listen'); homecoming true; }; then, want utilize method of schema original container : caseschema.methods.dostuff = function (data) { if (this.casecontent.dostuff !== undefined) { this.casecontent.dostuff(); console.log('it worked'); } else { ...
Read more