Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2010
    Posts
    14

    Unanswered: select into out parameter

    Hello all,

    this is probably a simple error but i can't see what i do wrong

    Code:
    CREATE OR REPLACE
    PROCEDURE TRACK_INSERT
    ( album_year_ IN NUMBER
    , tag_album_year_id_ OUT NUMBER
    ) AS
    BEGIN
      insert into tags(name) values(album_year_);
      select id into tag_album_year_id_ from tags where name = album_year_;
    END TRACK_INSERT;
    Code:
    ORA-01722: invalid number
    ORA-06512: at "SYSTEM.TRACK_INSERT", line 18
    ORA-06512: at line 26
    Process exited.
    Disconnecting from the database oracle.
    The error explains that i'm doing an invalid cast to a number. but the TAGS.ID has the datatype NUMBER(11,0).

    The insert works so the id is around 32000, seems to be a valid number to me

    What's wrong?

    Thanks in advance,
    Chielus

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Show us the CREATE statement for the TAGS table.

    How do you know it's the SELECT that's raising the error, and not the insert?

    Btw: you should not create objects in the "SYSTEM" schema.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This is not the whole procedure as error stack says that error happened in line 18, while procedure you posted contains just a few lines.

    So, what's in line 18?

  4. #4
    Join Date
    Mar 2010
    Posts
    14
    Quote Originally Posted by Littlefoot View Post
    This is not the whole procedure as error stack says that error happened in line 18, while procedure you posted contains just a few lines.

    So, what's in line 18?
    you're right, i merely deleted some parameters. It's just the beginning of a procedure so the deleted parameters aren't used yet

    Code:
    CREATE OR REPLACE
    PROCEDURE TRACK_INSERT
    ( track_title_ IN VARCHAR2
    , track_license_ IN VARCHAR2
    , ogg_url_ IN VARCHAR2
    , buy_url_ IN VARCHAR2
    , artist_name_ IN VARCHAR2
    , artist_url_ IN VARCHAR2
    , album_name_ IN VARCHAR2
    , album_cover_ IN VARCHAR2
    , album_year_ IN NUMBER
    , track_id_ OUT NUMBER
    , artist_id_ OUT NUMBER
    , album_id_ OUT NUMBER
    , tag_album_year_id_ OUT NUMBER
    ) AS
    BEGIN
      insert into tags(name) values(album_year_);
      select id into tag_album_year_id_ from tags where name = album_year_;
    END TRACK_INSERT;
    line 18 is the select statement.

    and the table tags create:
    Code:
    CREATE TABLE "SYSTEM"."TAGS" 
       (	"ID" NUMBER(11,0) NOT NULL ENABLE, 
    	"NAME" VARCHAR2(50 CHAR) NOT NULL ENABLE, 
    	"PLAY_COUNT" NUMBER(11,0) DEFAULT 0 NOT NULL ENABLE, 
    	 PRIMARY KEY ("ID")
      USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
      TABLESPACE "SYSTEM"  ENABLE, 
    	 UNIQUE ("NAME")
      USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
      TABLESPACE "SYSTEM"  ENABLE
       ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
      TABLESPACE "SYSTEM" ;

  5. #5
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Maybe oracle is trying to cast the name column to a number, so you might try to explictely cast the number to a varchar:

    Code:
    select id into tag_album_year_id_ from tags where name = to_char(album_year_)
    The question is, why do you compare a number parameter against a varchar column?

  6. #6
    Join Date
    Mar 2010
    Posts
    14
    damn, i knew it would be a stupid mistake

    the reason is that the TAGS.NAME column isn't always a NUMBER, but it could be. Like now it represents a year

    Thanks again shammat!

    Great community you got here

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •