Home » SQL & PL/SQL » SQL & PL/SQL » How to use prepare statment?
How to use prepare statment? [message #252984] Sat, 21 July 2007 02:52 Go to next message
JOHNDARREN01
Messages: 7
Registered: July 2007
Location: Philippines
Junior Member
I have created a simple stored procedure that uses a PREPARE STATEMENT:
-----------------------------------------
create or replace procedure mytst2
is
CON_VAR1 VARCHAR2(100);
begin
CON_VAR1:='SELECT * FROM tbl_batch';
EXEC SQL PREPARE SQL_STMNT FROM :CON_VAR1;
EXEC IMMEDIATE SQL_STMNT;
end;
/
-------------------------------------------
I am having an error message "PLS-00103: Encountered the symbol "SQL" when expecting one of the following: = . ( @ % ;".

Could anyone point out the error in my stored procedure?



Re: How to use prepare statment? [message #252988 is a reply to message #252984] Sat, 21 July 2007 03:07 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Please RT...M about PL/SQL - Native Dynamic SQL.

Re: How to use prepare statment? [message #252992 is a reply to message #252984] Sat, 21 July 2007 03:37 Go to previous messageGo to next message
JOHNDARREN01
Messages: 7
Registered: July 2007
Location: Philippines
Junior Member
I already read a lot of material about Prepare Statement on the net. I have found several topic about the native dynamic sql.

I have followed the syntax but could not make it work. The Stored Procudure above is fairly simple but could not run it as it is suppose to run.

Could anyone please point out what i'm doing wrong?
Re: How to use prepare statment? [message #253005 is a reply to message #252984] Sat, 21 July 2007 07:40 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
What is your proc is supposed to do?
Re: How to use prepare statment? [message #253022 is a reply to message #252992] Sat, 21 July 2007 10:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your procedure is a mix of PL/SQL and Pro*C and can't work in either environment.
Choose one and then use its syntax.

Regards
Michel


Re: How to use prepare statment? [message #253038 is a reply to message #252984] Sat, 21 July 2007 17:28 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Syntax of EXECUTE IMMEDIATE plus examples

"EXEC" and "SQL" are not PL/SQL keywords. Where did you read about them and what are they supposed to do?
Re: How to use prepare statment? [message #253044 is a reply to message #252984] Sat, 21 July 2007 22:26 Go to previous messageGo to next message
JOHNDARREN01
Messages: 7
Registered: July 2007
Location: Philippines
Junior Member
Thanks for the reply. I reread some of the documents and I had misunderstood a lot of the things written there.

so the correct syntax would be EXECUTE IMMEDIATE CON_VAR1;

the statement EXEC SQL PREPARE SQL_STMNT FROM :CON_VAR1; is no longer included.

The stored procedure is just a test procedure to see if the PREPARE STATEMENT is working. I'm converting MYSQL programs to PL/SQL. Apparently the EXECUTE IMMEDIATE command will do the trick for PL/SQL.
Re: How to use prepare statment? [message #253045 is a reply to message #252984] Sat, 21 July 2007 22:37 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
It appears to me that you are "testing" or validating SQL syntax.
If so, I suggest a simpler approach by just using SQL*Plus.
IMO, you should NOT be using EXECUTE IMMEDIATE to invoke DML; just invoke the vanilla SQL statement.
Re: How to use prepare statment? [message #253065 is a reply to message #252984] Sun, 22 July 2007 02:00 Go to previous message
JOHNDARREN01
Messages: 7
Registered: July 2007
Location: Philippines
Junior Member
thanks for all of the advise. I appreciate it very much....
Previous Topic: Login Problem
Next Topic: LAST_INSERT_ID() ?
Goto Forum:
  


Current Time: Wed Dec 07 14:27:15 CST 2016

Total time taken to generate the page: 0.07219 seconds