passing VARCHAR2 as parameter in stored procedure.

Posted on 2007-08-03
Last Modified: 2013-12-19
If I have a VARCHAR2 parameter that I need to use in a query in the stored procedure, how do I do it?
For e.g.
I have a query like:
pStrValue is of type VARCHAR2.
It works fine with SQLServer 2005 but not on Oracle 9i. It compiles, but no rows are returned.
SELECT * from test1
WHERE col1 = pStrValue;
I have tried with SELECT * from test1
WHERE col1 = ''' || pStrValue || ''' ;  -- this does not work too.
When I put in the actual value, it works fine.For e.g.
SELECT * from test1
WHERE col1 = 'user1';
Question by:Enid_JP
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    SELECT * from test1
    WHERE col1 = pStrValue;

    that should work.

    now, what is the data type of col1? if it is CHAR instead of VARCHAR2, that might explain...
    LVL 14

    Expert Comment

    Maybe there is problem with case-sensitivity. Make sure that passed value is also user1 (not User1, USER1) - if not, You have to use upper/lower on both sides of comparision.
    LVL 27

    Expert Comment

    Print and see what value is being passed to pStrValue

    SELECT * from test1
    WHERE col1 = trim(' ' from pStrValue);
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    This is related to:

    Please post a stand alone code example of what you are trying to do.  Like I mentioned in the other question, I'm not clear on exactly what you are after.

    Is this what you are trying?
    drop table tab1;
    create table tab1 ( col1 number, col2 varchar2(10) );

    insert into tab1 values(1,'Hello');
    insert into tab1 values(2,'World');

    create or replace procedure junk ( inText in varchar2 )
          myResult number;
          select col1 into myResult from tab1 where col2 = inText;
          dbms_output.put_line('Got: ' || to_char(myResult));

    exec junk('Hello');
    exec junk('World');

    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    PAQ ? ...
    LVL 1

    Accepted Solution

    PAQed with no points refunded (of 50)

    EE Admin

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
    Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
    Via a live example, show how to take different types of Oracle backups using RMAN.

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now