Ok finally managed to get a solution from an almagmation from several sources.. I'll post here just to show people how we achieved it.
********************
** PL/ SQL Source
********************
PROCEDURE InitClob( Clobname OUT CLOB)IS
BEGIN
dbms_lob.CreateTemporary( Clobname, FALSE);
END;
PROCEDURE FreeClob( Clobname IN OUT CLOB)IS
BEGIN
dbms_lob.FreeTemporary(Clobname);
END;
-- Function and procedure implementations
function testSQL( sqltext IN OUT clob, errdesc OUT varchar2, errpos OUT number ) RETURN VARCHAR2 is
acursor integer;
asqltext dbms_sql.varchar2s;
csqltext clob;
pos INTEGER;
amt BINARY_INTEGER;
buf varchar2(256);
begin
--init vars
errdesc := '';
errpos := 0;
csqltext := sqltext;
--create table
buf := '';
pos := 1;
amt := 256;
Begin
loop
dbms_lob.read( csqltext,amt,pos, buf);
pos := pos+amt;
asqltext(asqltext.count+1):=buf;
end loop;
exception
when no_data_found then null;
end;
--dbms_lob.FreeTemporary( csqltext);
Begin
--create a cursor, parse the sql and see what happens
acursor := dbms_sql.open_cursor;
dbms_sql.parse(acursor,asqltext,1,asqltext.count,false,dbms_sql.V7);
dbms_sql.close_cursor(acursor);
return 'T';
exception
when others then
errpos :=dbms_sql.last_error_position;
errdesc := sqlerrm;
dbms_sql.close_cursor(acursor);
return 'F';
end;
end;
end checkSQL;
**************
DELPHI SOURCE
**************
procedure TForm1.Button1Click(Sender: TObject);
var
bSQL : TLOBLocator;
sqlString : string;
begin
OracleSession1.LogOn;
with OracleQuery1 do
begin
bSQL := TLOBLocator.create( OracleSession1, otCLOB);
SetComplexVariable('SQLTEXT', bSQL);
execute;
sqlString := 'Select * from currency2';
bSQL.write( sqlString[1], length( sqlString));
end;
with OracleQuery2 do
begin
SetComplexVariable('SQLTEXT', bSQL);
execute;
showmessage(getVariable('RESULT' ));
showmessage(getVariable('ERRDESC'));
end;
with OracleQuery3 do
begin
//bSQL := TLOBLocator.create( OracleSession1, otCLOB);
SetComplexVariable('SQLTEXT', bSQL);
execute;
bSQL.free;
end;
end;
And thats all there is to it.. There are 3 Oracle Querys on the form .. first one initialises the CLOB, second one calls the syntax checker, third one frees the CLOB. Any questions/comments.
Dave.
[This message has been edited by Cybrey (edited 18 October 2002).]
