Home » SQL & PL/SQL » SQL & PL/SQL » create table usning bind variables in EXECUTE IMMEDIATE.
create table usning bind variables in EXECUTE IMMEDIATE. [message #444441] Mon, 22 February 2010 00:44 Go to next message
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 #444442 is a reply to message #444441] Mon, 22 February 2010 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Object name cannot be bind.
2/ You do not use bind variable, you concatenate the value of the variable inside the string, and so the error.

Regards
Michel
Re: create table usning bind variables in EXECUTE IMMEDIATE. [message #444445 is a reply to message #444442] Mon, 22 February 2010 01:09 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
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 #444470 is a reply to message #444447] Mon, 22 February 2010 03:11 Go to previous messageGo to next message
harshalonline22
Messages: 74
Registered: March 2007
Location: Pune
Member
When 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.
Re: create table usning bind variables in EXECUTE IMMEDIATE. [message #444476 is a reply to message #444470] Mon, 22 February 2010 03:39 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
harshalonline22 wrote on Mon, 22 February 2010 09:11
When 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 #444482 is a reply to message #444470] Mon, 22 February 2010 03:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
harshalonline22 wrote on Mon, 22 February 2010 10:11
When 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.

If you remove it you will have the error.
In addtion, with your code:
- you do not have the error unless you set serveroutput on. So if someone didn't set it thinks the block executed successfully
- even if set serveroutput is on, you hide where this error occurs.

In conclusion, a very bad way to program.

Regards
Michel

Re: create table usning bind variables in EXECUTE IMMEDIATE. [message #446393 is a reply to message #444482] Mon, 08 March 2010 01:49 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
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 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 64152
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

Previous Topic: Query to fill data from top records
Next Topic: cursor with parameter inside function
Goto Forum:
  


Current Time: Sat Dec 10 20:35:46 CST 2016

Total time taken to generate the page: 0.13074 seconds