Error Encountered on SQL Task when submitting with src="script.sql" and the script is a CREATE PROCEDURE statement
1 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   Ryan_Docena
Posted On:   Thursday, November 4, 2004 10:07 PM

Error Message: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: .................... No problem is encountered for simple SQL: INSERT then another SQL e.g. SELECT. For CREATE PROCEDURE, it seems to terminate the whole SQL upon encountering the first semicolon (;) even with specifying the 'keepformat' attribute. The error also happens even without the 'src' attribute and the SQL for 'Create Procedure ...' is inline. Here's my <?xml version="1.0" ?> Database Scripts Build File driver="${jdbc_   More>>

Error Message:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ....................


No problem is encountered for simple SQL: INSERT then another SQL e.g. SELECT.


For CREATE PROCEDURE, it seems to terminate the whole SQL upon encountering the first semicolon (;) even with specifying the 'keepformat' attribute.


The error also happens even without the 'src' attribute and the SQL for 'Create Procedure ...' is inline.


Here's my


			
<?xml version="1.0" ?>


Database Scripts Build File










driver="${jdbc_driver}"
url="${jdbc_url}"
userid="${db_user}"
password="${db_pass}"
classpath="${class_path}"
src="CrtProc.sql"
keepformat="true"
print="yes"
output="RunSQL.txt"
>





Here's my CrtProc.sql


			
CREATE OR REPLACE PROCEDURE ant_test_pr (
i_ref_cd IN ant_test.ref_cd%TYPE
)
IS
v_rec ant_test%ROWTYPE;
BEGIN

SELECT *
INTO v_rec
FROM ant_test
WHERE ref_cd = i_ref_cd;

DBMS_OUTPUT.PUT_LINE(v_rec.ref_cd);
DBMS_OUTPUT.PUT_LINE(v_rec.ref_desc);

END;
   <<Less

Re: Error Encountered on SQL Task when submitting with src="script.sql" and the script is a CREATE PROCEDURE statement

Posted By:   Philip_Howell  
Posted On:   Monday, November 15, 2004 01:02 AM

The semicolons ';' in stored procedures are confusing it. You need to set the delimiter property for the ant sql task and on a newline at the end of the procedure add the forward slash '/' character.

e.g.


driver="${jdbc_driver}"
url="${jdbc_url}"
userid="${db_user}"
password="${db_pass}"
classpath="${class_path}"
src="CrtProc.sql"
keepformat="true"
print="yes"
output="RunSQL.txt"
delimiter="/">

...





-- Stored Procedure --


CREATE OR REPLACE PROCEDURE ant_test_pr (
i_ref_cd IN ant_test.ref_cd%TYPE
)
IS
v_rec ant_test%ROWTYPE;
BEGIN

SELECT *
INTO v_rec
FROM ant_test
WHERE ref_cd = i_ref_cd;

DBMS_OUTPUT.PUT_LINE(v_rec.ref_cd);
DBMS_OUTPUT.PUT_LINE(v_rec.ref_desc);

END;
/
About | Sitemap | Contact