Home » SQL & PL/SQL » Client Tools » Providing bind variables as values in an insert statement inside PL/SQL block not working ? (Oracle 10g, SqlDeveloper 3.0.04)
Providing bind variables as values in an insert statement inside PL/SQL block not working ? [message #520612] Tue, 23 August 2011 12:30 Go to next message
daudiam
Messages: 51
Registered: June 2011
Member
I executed the following PL/SQL block in SqlDeveloper :
VARIABLE max_dept_no NUMBER
DECLARE
  v_dept_name VARCHAR2(30) := '&p_dept_name';
  v_max       NUMBER(4,0);
BEGIN
  SELECT MAX(department_id) INTO v_max FROM departments;
  :max_dept_no := v_max + 20;
  INSERT  INTO departments  VALUES    (:max_dept_no,v_dept_name, NULL,NULL) ;
END;
/


And it gave the error : Quote:
Error report:
ORA-01400: cannot insert NULL into ("HR"."DEPARTMENTS"."DEPARTMENT_ID")
ORA-06512: at line 7
01400. 00000 - "cannot insert NULL into (%s)"


The same code when executed in iSqlPlus gave no error. I can't understand why.
Re: Providing bind variables as values in an insert statement inside PL/SQL block not working ? [message #520613 is a reply to message #520612] Tue, 23 August 2011 12:47 Go to previous messageGo to next message
BlackSwan
Messages: 22489
Registered: January 2009
Senior Member
>The same code when executed in iSqlPlus gave no error. I can't understand why.
different schema and/or different DB
Re: Providing bind variables as values in an insert statement inside PL/SQL block not working ? [message #520614 is a reply to message #520613] Tue, 23 August 2011 12:53 Go to previous messageGo to next message
daudiam
Messages: 51
Registered: June 2011
Member
Nope. Both utilize the same DB and the same HR schema. Is there anything wrong with the code ?
Re: Providing bind variables as values in an insert statement inside PL/SQL block not working ? [message #520617 is a reply to message #520614] Tue, 23 August 2011 13:17 Go to previous messageGo to next message
Michel Cadot
Messages: 58525
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Between you and Oracle, I tend to trust Oracle and not you.
So investigate what you did wrong.
For instance, in both case execute:
describe "HR"."DEPARTMENTS"

Regards
Michel
Re: Providing bind variables as values in an insert statement inside PL/SQL block not working ? [message #520618 is a reply to message #520617] Tue, 23 August 2011 13:24 Go to previous messageGo to next message
BlackSwan
Messages: 22489
Registered: January 2009
Senior Member
>The same code when executed in iSqlPlus gave no error. I can't understand why.
Oracle does not know or care about "flavor" of client connecting to the DB.
Different results occur when something is different.
FWIW, isqlplus is obsoleted & no longer supported.
Believe Oracle error message, as they are correct more often the (newbie) users.
Re: Providing bind variables as values in an insert statement inside PL/SQL block not working ? [message #520621 is a reply to message #520618] Tue, 23 August 2011 13:35 Go to previous messageGo to next message
daudiam
Messages: 51
Registered: June 2011
Member
Ok, even then, is there anything wrong with my code ? If its correct, it should execute in SqlDeveloper too. But why the error message ?

In the case of SqlDeveloper, when I executed :
describe "HR"."DEPARTMENTS"

Name            Null     Type         
--------------- -------- ------------ 
DEPARTMENT_ID   NOT NULL NUMBER(4)    
DEPARTMENT_NAME NOT NULL VARCHAR2(30) 
MANAGER_ID               NUMBER(6)    
LOCATION_ID              NUMBER(4)    
Re: Providing bind variables as values in an insert statement inside PL/SQL block not working ? [message #520622 is a reply to message #520621] Tue, 23 August 2011 13:39 Go to previous messageGo to next message
Michel Cadot
Messages: 58525
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I said "For instance, in both cases execute"

Quote:
But why the error message ?

Because you are not in the same database!

Regards
Michel

[Updated on: Tue, 23 August 2011 13:39]

Report message to a moderator

Re: Providing bind variables as values in an insert statement inside PL/SQL block not working ? [message #520624 is a reply to message #520622] Tue, 23 August 2011 13:45 Go to previous messageGo to next message
John Watson
Messages: 4376
Registered: January 2010
Location: Global Village
Senior Member
I get the same effect as OP. In SQL*Plus:
orcl> VARIABLE max_dept_no NUMBER
orcl> DECLARE
  2    v_dept_name VARCHAR2(30) := '&p_dept_name';
  3    v_max       NUMBER(4,0);
  4  BEGIN
  5    SELECT MAX(department_id) INTO v_max FROM departments;
  6    :max_dept_no := v_max + 20;
  7    INSERT  INTO departments  VALUES    (:max_dept_no,v_dept_name, NULL,NULL) ;
  8  END;
  9  /
Enter value for p_dept_name: new
old   2:   v_dept_name VARCHAR2(30) := '&p_dept_name';
new   2:   v_dept_name VARCHAR2(30) := 'new';

PL/SQL procedure successfully completed.

orcl>
And running it in SQLDeveloper, this is the output:
Error starting at line 2 in command:
DECLARE
  v_dept_name VARCHAR2(30) := '&p_dept_name';
  v_max       NUMBER(4,0);
BEGIN
  SELECT MAX(department_id) INTO v_max FROM departments;
  :max_dept_no := v_max + 20;
  INSERT  INTO departments  VALUES    (:max_dept_no,v_dept_name, NULL,NULL) ;
END;
Error report:
ORA-01400: cannot insert NULL into ("HR"."DEPARTMENTS"."DEPARTMENT_ID")
ORA-06512: at line 7
01400. 00000 -  "cannot insert NULL into (%s)"
*Cause:    
*Action:

This is database 11.2.0.2, SQL Developer 1.5.5
I reckon we don't understand how to use SQL Developer.
Re: Providing bind variables as values in an insert statement inside PL/SQL block not working ? [message #520672 is a reply to message #520624] Tue, 23 August 2011 21:28 Go to previous messageGo to next message
daudiam
Messages: 51
Registered: June 2011
Member
Here is the output of describe "HR"."DEPARTMENTS" on iSqlPlus :

Name Null? Type

DEPARTMENT_ID NOT NULL NUMBER(4)
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
MANAGER_ID NUMBER(6)
LOCATION_ID NUMBER(4)

Looks the same as sqldeveloper's output to me.

Running select * from v$version

In isqlplus : Quote:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production


In SqlDeveloper : Quote:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
"CORE 10.2.0.1.0 Production"
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production


The databases are the same. So now why the error is SqlDeveloper ?

John Watson is also getting the same result as me.
Re: Providing bind variables as values in an insert statement inside PL/SQL block not working ? [message #520685 is a reply to message #520672] Wed, 24 August 2011 01:08 Go to previous messageGo to next message
Michel Cadot
Messages: 58525
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because there is a bug somewhere.

Regards
Michel
Re: Providing bind variables as values in an insert statement inside PL/SQL block not working ? [message #520740 is a reply to message #520685] Wed, 24 August 2011 07:18 Go to previous messageGo to next message
daudiam
Messages: 51
Registered: June 2011
Member
Sorry, I cannot find the bug. And why is the bug not showing up when I ( and Watson, as he mentioned above ) run the code in iSqlPlus ?
Re: Providing bind variables as values in an insert statement inside PL/SQL block not working ? [message #520741 is a reply to message #520740] Wed, 24 August 2011 07:24 Go to previous messageGo to next message
cookiemonster
Messages: 10846
Registered: September 2008
Location: Rainy Manchester
Senior Member
Michel means there is a bug in SQLDeveloper. I suugest you contact oracle support about it.
Re: Providing bind variables as values in an insert statement inside PL/SQL block not working ? [message #520742 is a reply to message #520740] Wed, 24 August 2011 07:29 Go to previous messageGo to next message
John Watson
Messages: 4376
Registered: January 2010
Location: Global Village
Senior Member
Instrad of complaining, why don;t you download and install the latest SQL Developer, and see if the bug is fixed? Or are you on th latest version already?
Re: Providing bind variables as values in an insert statement inside PL/SQL block not working ? [message #520743 is a reply to message #520742] Wed, 24 August 2011 07:40 Go to previous message
daudiam
Messages: 51
Registered: June 2011
Member
I am using the latest version. Knowing its an oracle bug is comforting. Thanks anyway.
Previous Topic: SQL Developer cuts CLOB contents
Next Topic: How to input line to sql script
Goto Forum:
  


Current Time: Thu Jul 24 06:25:50 CDT 2014

Total time taken to generate the page: 0.18015 seconds