Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Posts
    3

    Unanswered: Help in production environment ASAP please

    Hello,

    I'm in a middle of a problem right now on our production environment.
    I need to read and compare the information inside a long datatype column.
    To do this I use the following function to read the long field:

    CREATE OR REPLACE FUNCTION Getlong
    (p_tname IN VARCHAR2,
    p_cname IN VARCHAR2,
    p_rowid IN ROWID)
    RETURN VARCHAR2
    AS
    l_cursor INTEGER DEFAULT dbms_sql.open_cursor;
    l_n NUMBER;
    l_long_val VARCHAR2(32767);
    l_long_len NUMBER;
    l_buflen NUMBER := 4000;
    l_curpos NUMBER := 0;
    l_pos_start NUMBER := 1;
    counter INTEGER := 0;
    l_long_txt VARCHAR2(32767);
    BEGIN
    dbms_sql.Parse(l_cursor,'select '
    ||p_cname
    ||' from '
    ||p_tname
    ||' where rowid = :x',dbms_sql.native);

    dbms_sql.Bind_variable(l_cursor,':x',p_rowid);

    dbms_sql.Define_column_long(l_cursor,1);

    l_n := dbms_sql.Execute(l_cursor);

    IF (dbms_sql.Fetch_rows(l_cursor) > 0) THEN
    WHILE l_pos_start <= 32767 LOOP
    dbms_sql.Column_value_long(l_cursor,l_pos_start,l_ buflen,0,l_long_val,
    l_long_len);

    l_pos_start := l_pos_start + l_buflen;

    l_long_txt := l_long_txt
    ||l_long_val;
    END LOOP;
    END IF;

    dbms_sql.Close_cursor(l_cursor);

    RETURN l_long_txt;
    END getlong;


    If I have the while loop function comment, the function works well and return the first 4000 charaters of the long field.
    If I try to use the while to get more characters I get the ORA-01007: variable not in select list
    What I'm doing wrong?

    Thank you very much in advance
    Rui
    Last edited by hortar; 02-27-09 at 13:41.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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