create table usning bind variables in EXECUTE IMMEDIATE. [message #444441] |
Mon, 22 February 2010 00:44 |
harshalonline22
Messages: 74 Registered: March 2007 Location: Pune
|
Member |
|
|
Hi ,
I am trying to create table using bind variable in EXECUTE IMMEDIATE.. I want to know whether oracle allows to create table using bind variable in EXECUTE IMMEDIATE..
Following is the example :
Declare
test_tab varchar2(10) := 'tab_test';
sql_stm varchar2(100);
Begin
sql_stm := 'create table ' || :a || ' (col1 NUMBER)';
dbms_output.put_line(sql_stm);
EXECUTE IMMEDIATE sql_stm
using test_tab;
Exception
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm || ' ' || sqlcode);
End;
After running above block it is giving error :
ORA-01008: not all variables bound.
Kindly suggest.
Thanks and Regards,
Harshal N.
|
|
|
|
Re: create table usning bind variables in EXECUTE IMMEDIATE. [message #444445 is a reply to message #444442] |
Mon, 22 February 2010 01:09 |
harshalonline22
Messages: 74 Registered: March 2007 Location: Pune
|
Member |
|
|
Thanks Michel for quick reply.
So, It means that you can not create table using bind variables in
EXECUTE IMMEDIATE.
Can you please explain your second point ?
Also can we use following example as an alternative solution to create table using Bind variable?
Create PROCEDURE create_tab(tab_name VARCHAR2) AS
sql_stm varchar2(100);
Begin
sql_stm := 'create table ' || test_tab || ' (col1 NUMBER)';
EXECUTE IMMEDIATE sql_stm;
Exception
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm || ' ' || sqlcode);
End;
Thanks & Regards,
Harshal N.
|
|
|
Re: create table usning bind variables in EXECUTE IMMEDIATE. [message #444447 is a reply to message #444445] |
Mon, 22 February 2010 01:14 |
|
Michel Cadot
Messages: 68704 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:So, It means that you can not create table using bind variables in EXECUTE IMMEDIATE.
Yes.
Quote:Can you please explain your second point ?
sql := 'my sql statement with bind variable :v';
sql := 'my sql statement without bind variable but its value '||:v;
Quote:Also can we use following example as an alternative solution to create table using Bind variable?
Syntaxically, yes.
But if you talk about security, not: search for "sql injection".
And REMOVE the WHEN OTHERS clause, unless you can explain why it is useful in your code (and I will explain you why it hurts).
Regards
Michel
[Updated on: Mon, 22 February 2010 01:14] Report message to a moderator
|
|
|
|
Re: create table usning bind variables in EXECUTE IMMEDIATE. [message #444476 is a reply to message #444470] |
Mon, 22 February 2010 03:39 |
cookiemonster
Messages: 13951 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
harshalonline22 wrote on Mon, 22 February 2010 09:11When OTHERS catches all the errors thats why i used it in my code.
But i would like to know why and in what circumstances it "Hurts"?
Thanks & Regards,
Harshal N.
1) Unless you're running this in sqlplus with serveroutput on you won't see any error.
2) Oracle automatically rolls back a transaction when it encounters an error - this doesn't happen if you catch it. Doesn't matter in this case but more generally it will.
3) You lose the line number the error occurs at.
You should only ever catch errors you can do something about.
If you remove your error handler you'll get to see the full details of all errors in all circumstances - which should be what you want.
|
|
|
|
Re: create table usning bind variables in EXECUTE IMMEDIATE. [message #446393 is a reply to message #444482] |
Mon, 08 March 2010 01:49 |
harshalonline22
Messages: 74 Registered: March 2007 Location: Pune
|
Member |
|
|
Hi Michel,
Please find my reply :
" Michel Cadot replied on 22/02/2010 "
1) Unless you're running this in sqlplus with serveroutput on you won't see any error.
-- I can insert the rows in temp table which will insert the
values for sqlerrm & sqlcode.
Also if the code is to be fired from front end application (Say for e.g. forms & reports 6i) then you can write the code to display the error message directy on the front end which will not require to set serveroutput on.
2) Oracle automatically rolls back a transaction when it encounters an error - this doesn't happen if you catch it. Doesn't matter in this case but more generally it will.
-- I can explicitly mention ROLLBACK in exception block.
3) You lose the line number the error occurs at.
-- you can handle this also explicitly by declaring some variables for line no and display the value for that variable.
In conclusion, we need to think from front end level also. Also, if you hanlde the error and display the correct message then frontend user can be able to usnderstand the error message rather than getting confused thinking about Oracle Error message.
Best Regards,
Harshal N.
|
|
|
Re: create table usning bind variables in EXECUTE IMMEDIATE. [message #446398 is a reply to message #446393] |
Mon, 08 March 2010 02:04 |
|
Michel Cadot
Messages: 68704 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Note that the message you are quoting is NOT from me but from cookiemonster.
Quote:1) Unless you're running this in sqlplus with serveroutput on you won't see any error.
-- I can insert the rows in temp table which will insert the
values for sqlerrm & sqlcode.
Also if the code is to be fired from front end application (Say for e.g. forms & reports 6i) then you can write the code to display the error message directy on the front end which will not require to set serveroutput on.
Why not just let the exception raises until the end or the one that does something really useful with it?
Why do you want to add code to read buffer or table when there is nothing to do letting the exception mechanism working itself?
In addition, doing so you hide from where the exception comes from.
Quote:2) Oracle automatically rolls back a transaction when it encounters an error - this doesn't happen if you catch it. Doesn't matter in this case but more generally it will.
-- I can explicitly mention ROLLBACK in exception block.
You can bad program as you want but this is the caller that knows if it wants to rollback the transaction or not, not the procedure itself. Why the procedure does decide it rollbacks the modifications that were made before the procedure was called?
By the way, Oracle does not rollback the transaction but the statement that is in error.
Quote:3) You lose the line number the error occurs at.
-- you can handle this also explicitly by declaring some variables for line no and display the value for that variable.
Once again why do you want to add code to hide what Oracle gives you to then add more code to retrieve the same information?
It is plain silly.
Quote:In conclusion, we need to think from front end level also. Also, if you hanlde the error and display the correct message then frontend user can be able to usnderstand the error message rather than getting confused thinking about Oracle Error message.
So let the front end trap the exception as it wants and display it as it wants; why the procedure has to choose for it?
Regards
Michel
[Updated on: Mon, 08 March 2010 02:04] Report message to a moderator
|
|
|
Re: create table usning bind variables in EXECUTE IMMEDIATE. [message #446410 is a reply to message #446398] |
Mon, 08 March 2010 03:40 |
harshalonline22
Messages: 74 Registered: March 2007 Location: Pune
|
Member |
|
|
1)
You are saying that i am hiding from where the exception comes from.
Consider following example :
Begin
code1 ;
code2 ;
code3 ;
End;
Do you mean that, if there is any error at code2 section then it will show the error message at this point and also you will come to know about the error line no.??
Do you refer this as a good programming, if this block is to be called from front end?
Also, if business requirment is asking you to provide application which also supports tracking of the error for audit purpose then it is required to read the buffer or table.
2)
It looks like contradiction,
In your 1st reply ,
Oracle automatically rolls back a transaction when it encounters an error - this doesn't happen if you catch it. Doesn't matter in this case but more generally it will.
Then now in recent reply ,
By the way, Oracle does not rollback the transaction but the statement that is in error.
It is purely depends on your business logic that the transaction is required to ROLLBACK in case there is any error occurred. Accordingly Programmer will write the code to handle such case which i dont think so is bad programming.
3)
Once agian i will say that if you think from front end prospective then you need to write code explicitly even if oracle gives you the same.
Pls consider following,
Begin
some code;
select ..
where col1 = var1;
some code;
End;
No exception handled here and the error occurs at select in where condition not satisfying the match criteria.
So, at front end error will be displayed with line no and oracle error message Which i think is Bad or Silly programming.
Better to write exception and handle it with proper message.
I didnt mention that procedure needs to decide, but through procedure only front will display correct message.
Best Regards,
Harshal N.
|
|
|
Re: create table usning bind variables in EXECUTE IMMEDIATE. [message #446412 is a reply to message #446410] |
Mon, 08 March 2010 03:51 |
|
Michel Cadot
Messages: 68704 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:Do you mean that, if there is any error at code2 section then it will show the error message at this point and also you will come to know about the error line no.??
Yes, just try it.
Quote:Do you refer this as a good programming, if this block is to be called from front end?
Then the front-end application traps it and handles it as it wants, the procedure does not have to decide how it should.
This is good programming.
Quote:Also, if business requirment is asking you to provide application which also supports tracking of the error for audit purpose then it is required to read the buffer or table.
If audit is required, this a rare case of catching "when others", then you log into a table or file and MUST reraise the exception, not gobble it.
Quote:In your 1st reply ,
Oracle automatically rolls back a transaction when it encounters an error - this doesn't happen if you catch it. Doesn't matter in this case but more generally it will.
Then now in recent reply ,
By the way, Oracle does not rollback the transaction but the statement that is in error.
There is no contradiction, as I said you quoted cookiemonster not me. There is just a mistake in cookiemonster's post.
Quote:It is purely depends on your business logic that the transaction is required to ROLLBACK in case there is any error occurred.
Yes but who owns the business logic? Not the procedure but its caller (and maybe the caller of the caller...). The procedure can't know it other modifications have been made and it can't decide to roll back these modifications made outside it.
The proper way to program in this case, is to put a savepoint at the beginning of the procedure and then to roll back to the save point not the whole transaction.
Quote:So, at front end error will be displayed with line no and oracle error message Which i think is Bad or Silly programming.
The front end displays what is needed (for the end user), it does not need to display all the information contained in the esception, it does not need to display Oracle error but a useful message for end user, anyway this is the front-end that decides what should be displayed not the procedure (and the message may be different with different front-end applications using the same procedure).
Regards
Michel
|
|
|