Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2011

    Unanswered: stored procedure out char string

    I create table named customer.

    create table customer(name char(10),id INTEGER);

    and inserted values by

    insert into customer values('JOHN',1);

    after that created stored procedure by

    create or replace procedure custpos( idvalue INT INTEGER,custname OUT CHAR)
    select name into custname from customer where id=idvalue;

    and called that stored procedure from Pro*C/C++ sql code by

    char* CUST_NAME;
    int id=1;
    EXEC SQL CALL custpos(:id,:CUST_NAME);

    and getting an error ORA-01405: fetched column value is NULL

    I think issue is with
    create or replace procedure custpos( idvalue INT INTEGER,custname OUT CHAR)

    what should be the correct datatype for output custname parameter?

  2. #2
    Join Date
    Jan 2004
    Croatia, Europe
    Provided Answers: 4
    What you posted is probably not true. What is (in CREATE PROCEDURE) "idvalue INT integer"? An error! Perhaps you should copy/paste the whole SQL*Plus session so that we'd see what you did and how.

    Because, properly written, your code works fine (although, I'd suggest you to use VARCHAR2 instead of CHAR datatype):
    SQL> create table customer(name char(10),id INTEGER);
    Table created.
    SQL> insert into customer values('JOHN',1);
    1 row created.
    SQL> select * from customer;
    NAME               ID
    ---------- ----------
    JOHN                1
    SQL> create or replace procedure custpost (idvalue in integer, custname out char)
      2  is
      3  begin
      4    select name into custname
      5      from customer
      6      where id = idvalue;
      7  end;
      8  /
    Procedure created.
    SQL> declare
      2    l_name char(10);
      3  begin
      4    custpost (1, l_name);
      5    dbms_output.put_line('Returned: ' || l_name);
      6  end;
      7  /
    Returned: JOHN
    PL/SQL procedure successfully completed.
    Therefore, a procedure with data you inserted seems to be working fine. I don't use Pro*C/C++ so I can't speak for it; someone else might know it, though.

    Finally, here's what Oracle suggests:
    Quote Originally Posted by Oracle
    ORA-01405: fetched column value is NULL

    Cause: The INTO clause of a FETCH operation contained a NULL value, and no indicator was used. The column buffer in the program remained unchanged, and the cursor return code was +2. This is an error unless you are running Oracle with DBMS=6, emulating version 6, in which case it is only a warning.

    Action: You may do any of the following:

    Use the NVL function to convert the retrieved NULL to another value, such as zero or blank. This is the simplest solution.

    Use an indicator to record the presence of the NULL. You probably should use this option when you want a specific action to be taken when a NULL arises.

    Revise the cursor definition so that no columns possibly containing NULL values are retrieved.

  3. #3
    Join Date
    Dec 2011
    Thanks LittleFoot for quick reply
    last portion in which you were printing out the output helped me in finding out the solution

    i declared

    char* CUST_NAME;

    in Pro*C/C++

    but i should declare it as char CUST_NAME[10];

Posting Permissions

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