New to Oracle with a simple select statement RSS

• • Reply
  

  indianapolym...

  Member

  11 Points

  28 Posts

  New to Oracle with a simple select statement

  Aug 07, 2012 12:14 PM|LINK

  I am updating an Oracle stored procedure and I want to test parts of this large proc before making the changes.

  I can do the following and get results:

  SELECT * FROM table_name WHERE column_name_in_table = '1234'

  But when this goes to production, the hard coded number will be a variable but when I do the following:

  p_userid char(8 BYTE) := '1234';
  SELECT * FROM table_name WHERE column_name_in_table = trim(p_userid)

  I get an error: ORA-00904: "P_USERID": invalid identifier
  00904. 00000 -  "%s: invalid identifier"

  What is the easiest way to see a select statement results in Oracle?

  • Edited by indianapolymath on Aug 07, 2012 12:15 PM
  • Edited by indianapolymath on Aug 07, 2012 12:15 PM
  • Edited by indianapolymath on Aug 07, 2012 12:15 PM
  • Edited by indianapolymath on Aug 07, 2012 05:21 PM
• • Reply
  

  shibuv

  Member

  189 Points

  34 Posts

  Re: New to Oracle with a simple select statement

  Aug 07, 2012 01:35 PM|LINK

  when defining p_userid try using number or varchar instead of char(8 BYTE)

• • Reply
  

  indianapolym...

  Member

  11 Points

  28 Posts

  Re: New to Oracle with a simple select statement

  Aug 07, 2012 05:19 PM|LINK

  Help a guy out here :-)

  So can I take it from the silence that there is not a good way to unit test code before updating a whole procedure or package?  Say it isn't so!

  variables oracle

• • Reply
  

  indianapolym...

  Member

  11 Points

  28 Posts

  Re: New to Oracle with a simple select statement

  Aug 07, 2012 05:28 PM|LINK

  Figured it out...needed the keyword "variable" in front

  • Marked as answer by indianapolymath on Aug 07, 2012 05:28 PM