Home » SQL & PL/SQL » SQL & PL/SQL » Creating Procedures
Creating Procedures [message #185955] Fri, 04 August 2006 08:58 Go to next message
ravisingh
Messages: 1
Registered: August 2006
Location: Mumbai
Junior Member
hi

well had created on table in which i used a column name as NUMBER .

while creating the table i used the NUMBER filed in double qoutes as "NUMBER" i was able to create the table and insert the data Succcessfully.

Well i was wrting a procedure in which used the table containg the NUMBER field.

a simple SQL statement.

Select tt into t1 from test;

i was not able to create the procedure ..

when i drop that that column("NUMBER") i was able to create the procedure
Well it is rquirement for you Product to keep the column name as
"NUMBER"

PLease help ....

Thanks
Re: Creating Procedures [message #185967 is a reply to message #185955] Fri, 04 August 2006 09:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
NUMBER is a reserved word in oracle - it's a basic data type.

As you have found, you can create columns in tables using reserved words, by enclosing them in ", but if you do this, then everywhere that you use this column, you must enclose it in quotes.

PL/SQL is deeply unhappy with this though:
SQL> CREATE TABLE number_test (col_1  VARCHAR2(10), "NUMBER" NUMBER);

Table created.

SQL> 
SQL> INSERT INTO number_test VALUES ('A',1);

1 row created.

SQL> CREATE OR REPLACE PROCEDURE number_test_proc AS
  2    v_vc  VARCHAR2(10);
  3  BEGIN
  4    SELECT COUNT(*)
  5    INTO   v_vc
  6    FROM   NUMBER_TEST
  7    WHERE ROWNUM = 1;
  8  END;
  9  /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE NUMBER_TEST_PROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3      PL/SQL: SQL Statement ignored
6/10     PL/SQL: ORA-06552: PL/SQL: Compilation unit analysis terminated
         ORA-06553: PLS-320: the declaration of the type of this
         expression is incomplete or malformed


You can work round this by creating a view on that table, in which all the columns are renamed to valid Oracle names:
SQL> create or replace view v_number_Test (col_1, number_col) as select col_1,"NUMBER" from number_test;

View created.

SQL> select * from v_number_test;

COL_1      NUMBER_COL
---------- ----------
A                   1

SQL> CREATE OR REPLACE PROCEDURE number_test_proc AS
  2    v_vc  VARCHAR2(10);
  3  BEGIN
  4    SELECT COUNT(*)
  5    INTO   v_vc
  6    FROM   v_NUMBER_TEST
  7    WHERE ROWNUM = 1;
  8  END;
  9  /

Procedure created.


In my opinion, your first approach should be to explain to whoever it is who believes that the column must be called 'NUMBER' that this is a very poor idea, will cause a significant number of problems during development and testing, and will come to be regarded as one of the more stupid decisions he's ever made.

Failing that, create the table with that column, and then create a view on it as I did, so that nothing in the system needs to reference the column.

If anyone can find a way of getting pl/sql to use the table I created directly, I'll count that as my learning experience for the day.
Re: Creating Procedures [message #223516 is a reply to message #185955] Fri, 09 March 2007 04:43 Go to previous messageGo to next message
ddkdhar
Messages: 68
Registered: February 2007
Member

thank you JRowbottom
even i have same doubt.now my doubt cleared
Re: Creating Procedures [message #223678 is a reply to message #223516] Sat, 10 March 2007 04:24 Go to previous message
parlovi
Messages: 6
Registered: February 2007
Location: BANGALORE
Junior Member
But as mentioned above usage of RESERVE words is really not good.
First thing is to convince them to not to user reserved words ,
if your Users or Clients wants on Front End some fieds header to be NUMBER and get populated from database table ,
you have plenty of ways to do that, if such is the scenario I believe no need to use NUMBER as column name.

Thx
P
Previous Topic: TABLE ACCESS FULL
Next Topic: Granting ownership on any object to other user
Goto Forum:
  


Current Time: Sun Dec 11 00:06:04 CST 2016

Total time taken to generate the page: 0.05673 seconds