Re: procedure and ddl commands?

"dead" <OVOIZBACI-vlasic_at_vodatel.net> wrote in message news:ce5jpt$ha5$1_at_sunce.iskon.hr...
| I will explain why I need that in procedure
| I need to change working server for small but important aplication in
| posible problem period with working server.
| I have same tables on these servers, but don't have indexes and right
| sequences, which gave me next number for application on used tables.
| I tryed with 'execute immediate' but didnt do that in right way
| this is problem
|
| Procedure PRTEST1
| ( )
| IS
| nNewSNo number;
| BEGIN
|
| CREATE INDEX testind1
| ON testtable1
| ( SNo );
|
| SELECT max(SNo)+1 INTO nNewSNo FROM testtable1;
|
| EXECUTE IMMEDIATE 'CREATE CREATE SEQUENCE testsequence
| INCREMENT BY 1
| START WITH nNewSNo
| MINVALUE 1
| MAXVALUE 999999
| CYCLE
| NOORDER
| NOCACHE';
|
| EXCEPTION
| END;
|
|
| where I use in application 'testsequence' for 'SNo' in 'testtable1'
|

you've included the variable name (nNewSNo) in the quoted string literal -- so the name of the variable, not the value in it, will be passed to the database and the database will choke on it

also you still have the create index DDL in your procedure, and your exception handler is incomplete -- both of these will cause the procedure not to compile long before the execute immediate fails (plus additional errors in the procedure specification)

suggestions:

read up on PL/SQL, starting with the oracle docs

post your error messages -- often they will point an experienced developer to an area in the code other than what you assume the problem is (or, additional problems that may be getting in the way of the part of the code you're trying to test)

specific to this piece of code
1) the exception handler either needs to have one or more 'WHEN' clauses to actually handle exceptions, or just eliminate the keyword EXCEPTION (but make sure you look up exception handlers and understand why this is necessary)
2) the create index statement needs to be issued as a string literal via EXECUTE IMMEDATE) -- but in reality, there is no reason to do so, since it is not a dynamic statement, it should just be issued independently of this procedure
3) this doesn't need to be a procedure, an anonymous block will do (if you don't know what that is, look it up in the manual) 4) if this was a procedure, you need to use the CREATE OR REPLACE keywords in front of the procedure keyword, and don't use the parenthesis if the procedure does not have an parameters
5) use string concatenation to get the value of your variable included in the string literal expression that is used by the execute immediate

++ mcs Received on Tue Jul 27 2004 - 10:09:02 CDT