Hello
I'm trying to create oracle/mssql stored procedures by using de TZSQLprocessor. mssql works fine, but oracle 91 gives som problems:
The stored procedure:
create or replace procedure PRAEMIDAT.AA001_DropUserObjects (v_DropType in varchar2 default 'P')
as
type EmpCurTyp is ref cursor;
c_UserObjects EmpCurTyp;
v_ObjectType varchar2(15);
v_ObjectNaam varchar2(128);
v_ObjectDropString varchar2(200);
v_ObjectMessageString varchar2(200);
v_cn integer;
v_rp integer;
v_chk integer;
begin
v_chk := 0;
if upper(v_DropType) = 'A'
then
open c_UserObjects for
select
OBJECT_TYPE,
OBJECT_NAME
from
USER_OBJECTS
where
OBJECT_TYPE not in ('INDEX','TRIGGER')
order by
OBJECT_TYPE,
OBJECT_NAME;
else
open c_UserObjects for
select
OBJECT_TYPE,
OBJECT_NAME
from
USER_OBJECTS
where
OBJECT_TYPE in ('FUNCTION','PROCEDURE','VIEW')
order by
OBJECT_TYPE,
OBJECT_NAME;
end if;
--
loop
fetch c_UserObjects into v_ObjectType, v_ObjectNaam;
exit when c_UserObjects%NOTFOUND;
if v_ObjectNaam <> 'AA001_DROPUSEROBJECTS'
then
if v_chk = 0
then
v_chk := 1;
dbms_output.enable(200000);
end if;
-- dropstring opbouwen
v_ObjectDropString := 'DROP ' || v_ObjectType || ' ' || v_ObjectNaam;
-- toevoegen cascade constraints voor tabellen
if v_ObjectType = 'TABLE'
then
v_ObjectDropString := v_ObjectDropString || ' CASCADE CONSTRAINTS';
end if;
-- dropprocess
v_cn := dbms_sql.open_cursor;
dbms_sql.parse(v_cn, v_ObjectDropString,dbms_sql.native);
v_rp := dbms_sql.execute(v_cn);
dbms_sql.close_cursor(v_cn);
-- controle op foutmeldingen
if v_rp = 0
then
v_ObjectMessageString := v_ObjectType || ': ' || v_ObjectNaam || ', verwijderd!';
else
v_ObjectMessageString := 'FOUT -> ' || v_ObjectType || ' ' || v_ObjectNaam || ' NIET geslaagd';
end if;
-- uitvoer van de melding
dbms_output.put_line(v_ObjectMessageString);
end if;
end loop;
close c_UserObjects;
end;
/
The setting used in delph 2006:
with dbSQLProcessor do
begin
DelimiterType := dtSetTerm;
Delimiter := '/';
//CleanupStatements := True;
LoadFromFile(FScriptFile);
Parse;
for iStatementCount := 0 to StatementCount - 1 do
begin
showmessage(Statements[istatementcount]);
end;
Execute;
end;
The error i get is
ERROR : SQL Error: OCI_SUCCESS_WITH_INFO
when looking at this error oracle says
line # = 1 column #= 1 PLS-00103: Symbol "" found ....
with CleanupStatements := true the same error appears only at a different position line #= 1 column = 1521 ...
In my opinion it has something to do with te command ending.
Does anybody have any suggestions?
Teun
TZSQLProcessor - Creating stored procedure oracl 9i
Moderators: gto, cipto_kh, EgonHugeist