Home » SQL & PL/SQL » SQL & PL/SQL » bind variables to package procedure call stored in table
bind variables to package procedure call stored in table [message #184073] Tue, 25 July 2006 03:46 Go to next message
niklasb
Messages: 2
Registered: July 2006
Location: sweden
Junior Member
Hi,

I have a table where I store dynamic SQL statements that will be called using bind variables.

I want to use a call to a package procdure doing some DML, e.g. updating a table, therefore I save the string:
'Begin
mypackage.myproc(:theBindVar);
End;'
into the table.

When I try to use the procedure call via the code:

declare
 vStmt varchar2(300);
 vBindVar NUMBER := 5;
begin
 select the_stmt into vStmt from table_with_stmts where id = '24';
 execute immediate vStmt
   using vBindVar; 
 commit;
end;


I get the following error:

ORA-06550: line 1, column 7:
PLS-00103: Encountered the symbol "" when expecting one of the following:

begin declare exit for goto if loop mod null pragma raise
return select update while <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall
<a single-quoted SQL string>
The symbol "" was ignored.

Is there a way to do what I want without "binding" the variable by myself, e.g. using replace functionality?

br niklas
Re: bind variables to package procedure call stored in table [message #184078 is a reply to message #184073] Tue, 25 July 2006 04:03 Go to previous message
niklasb
Messages: 2
Registered: July 2006
Location: sweden
Junior Member
OK, I reply to my own message if someone is interested in the solution:

The error was that I used Toad to insert the stored procedure call and got some carrige returns into the statement. The appropriate value in the database column should be:
'Begin mypackage.myproc(:theBindVar); End;'

Previous Topic: Combining Collections
Next Topic: Primary Key with a Condition (Constraints)
Goto Forum:
  


Current Time: Fri Dec 02 20:40:35 CST 2016

Total time taken to generate the page: 0.05022 seconds