Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2003
    Posts
    4

    Unanswered: Rowid in WHERE clause

    Hi all,

    I am trying to use rowid in the where clause of a select statement like follows.

    declare
    message varchar2(20);
    row_id varchar2(20);
    begin
    row_id := 'AAADL1AABAAACTEAAE';
    select rowid into row_id from jat.sms_dispatcher where rowid = row_id;
    message := rowidtochar(row_id);
    dbms_output.put_line(message);
    end;

    I am getting the error

    ORA-01410: invalid ROWID
    ORA-06512: at line 6.

    Pls. help me in overcoming this problem. All suggestions are most welcome.

    Txa and Regds,
    Santha Ram.

  2. #2
    Join Date
    Sep 2003
    Location
    Brussel
    Posts
    52
    Isn't it stupid to select the rowid into a variable you use to perform your search??? If you search for the rowid of a row you search by his rowid, than you allready know the rowid of that row.
    A good programmer is a LAZY programmer!

  3. #3
    Join Date
    Oct 2003
    Posts
    2

    Lightbulb

    Check the row_id datatype declaration.

    I think the where clause condition datatype mismatch is causing the problem !!

    UELK2

  4. #4
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    UELK2 is most probably right. ROWID is not of type VARCHAR.

    You should use the conversion function built in Oracle


    <<<From the Oracle documentation>>>

    Examples
    The following example converts a character rowid representation to a rowid. (The function will return a different rowid on different databases).

    SELECT last_name FROM employees
    WHERE ROWID = CHARTOROWID('AAAFd1AAFAAAABSAA/');

    LAST_NAME
    -------------------------
    Greene

    >>>End of the documentation<<<

  5. #5
    Join Date
    Oct 2003
    Posts
    4
    Hi,

    It's just an example and only a speck of big problem. If I overcome the error in this simple example, probably I could in the original problem. Go through the entire problem.

    declare
    sms jat.sms_dispatcher%rowtype;
    /*insert variable */

    msg_id varchar2(40);
    exp_date number(38);
    dest_addr varchar2(100);
    l_counter number(38);
    send_date number(38);
    fs_path varchar2(100);
    mesg varchar2(100);
    l_status number(38);
    origin_addr varchar2(100);
    charged_party number(38);
    cm_time number(38);
    code_text varchar2(2500);
    row_id varchar2(40);

    /*enqueue varables*/
    eo dbms_aq.enqueue_options_t;
    mp dbms_aq.message_properties_t ;
    m_id RAW(16);
    msg aq.message_typ;

    /*exceptions*/
    no_messages exception;
    pragma exception_init (no_messages, -25228);

    begin
    dbms_output.put_line(to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MIS'));
    exp_date := 1064015375;
    dest_addr := '03138302110';
    l_counter := 5;
    send_date := 1063972175;
    fs_path := 'abcd';
    mesg := 'X-Mms-Mesage_type';
    l_status := 0;
    origin_addr := 'anonymous';
    charged_party := 1;
    cm_time := 1;

    for i in 1 .. 10
    loop
    msg_id := to_char(i);

    execute immediate 'insert into jat.sms_dispatcher values ('''|| msg_id ||''',' || exp_date || ','''|| dest_addr ||''',' || l_counter || ',' || send_date || ','''||fs_path||''','''||mesg||''',' || l_status || ','''|| origin_addr||''',' || charged_party || ',' || cm_time || ')
    returning rowid into :1' using out row_id ;

    /*dbms_output.put_line(row_id);*/
    /*enqueue*/

    mp.priority:=1;
    msg := aq.message_typ('Row ID-->', row_id);

    DBMS_AQ.ENQUEUE
    (
    queue_name =>'aq.MultiConsumerMsg81_queue',
    enqueue_options =>eo,
    message_properties => mp,
    payload => msg,
    msgid =>m_id
    );
    /*dbms_output.put_line(msg.text);*/
    commit;

    end loop;
    commit;
    dbms_output.put_line(to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MIS'));

    end;
    /

    :::::::::::::::::::::::::::::::::::::::::::::;

    declare
    sms jat.sms_dispatcher%rowtype;
    /*insert variable */
    msg_id varchar2(40);
    exp_date number(38);
    dest_addr varchar2(100);
    l_counter number(38);
    send_date number(38);
    fs_path varchar2(100);
    mesg varchar2(100);
    l_status number(38);
    origin_addr varchar2(100);
    charged_party number(38);
    cm_time number(38);
    code_text varchar2(2500);
    row_id varchar2(40);

    /*dequeue*/
    do dbms_aq.dequeue_options_t ;
    mp dbms_aq.message_properties_t ;
    mhandle RAW(16);
    message aq.message_typ;

    /*for file operations*/
    fp UTL_FILE.FILE_TYPE;

    /*exceptions*/
    no_messages exception;
    pragma exception_init (no_messages, -25228);

    begin
    /*dequeue*/
    dbms_output.put_line(to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MIS'));
    do.navigation:=dbms_aq.first_message;
    do.consumer_name:='sub';
    do.wait:=dbms_aq.no_wait;
    do.dequeue_mode := DBMS_AQ.LOCKED;
    fp := UTL_FILE.FOPEN (
    'c:\',
    'smsfile.txt',
    'w');
    for i in 1..10
    loop
    do.dequeue_mode := dbms_aq.REMOVE;

    dbms_aq.dequeue(
    queue_name=>'aq.MultiConsumerMsg81_queue',
    dequeue_options=>do,
    message_properties=>mp,
    payload=>message,
    msgid=>mhandle);
    /*commit;*/

    /*selecting from sms dispatcher*/
    /*dbms_output.put_line (message.text);*/

    select messageid, expirydate, destaddr, counter, sendafterdate, fspath, message, status, originaddr, chargedparty, cmtime into sms from jat.sms_dispatcher where rowid = message.text;
    /* THIS IS WHERE I AM GETTING THE PROBLEM, AND I TRIED IT FIRST ON A SIMPLE EXAMPLE*/

    /*dbms_output.put_line (sms.messageid);*/
    UTL_FILE.PUT_LINE (
    fp,
    'sms.messageid');
    UTL_FILE.FFLUSH(fp);

    /*updating status*/
    execute immediate 'update jat.sms_dispatcher set status = 1 where rowid = :1' using chartorowid(message.text);
    commit;
    do.dequeue_mode := DBMS_AQ.LOCKED;
    do.navigation := dbms_aq.NEXT_MESSAGE;
    end loop;
    /*DBMS_OUTPUT.PUT_LINE ('Records dequeued');*/

    EXCEPTION
    WHEN no_messages
    THEN
    dbms_output.put_line('All the msgs are dequeued');
    commit;
    UTL_FILE.FCLOSE (
    fp);
    dbms_output.put_line(to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MIS'));
    end;
    /

    I can't help if you still say it's no less than stupidity.
    All the suggestions are welcome.

    Txs and Regds,
    Santha Ram.

  6. #6
    Join Date
    Oct 2003
    Posts
    4
    Hi,

    Thank you for your valuable suggestions.

    I tried the conversion functions ( rowidtochar() and chartorowid() ) also. When I could not able to get the result even after trying with conversion functions I thought of posting it on dbforums. I also tried declaring row_id as type rowid (I am not sure whether it's correct or not, but no error was thrown for this declaration), but this also did not help much.

    Txs and regards
    Santha Ram.

  7. #7
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Ok, let's get back to square one.

    I've tested your code here (using another table, but that doesn't really matter)

    <<<>>>
    declare
    message varchar2(20);
    row_id varchar2(20);
    begin
    row_id := 'AAADL1AABAAACTEAAE';
    select rowid into row_id from jat.sms_dispatcher where rowid = row_id;
    message := rowidtochar(row_id);
    dbms_output.put_line(message);
    end;
    <<<>>>

    The code works perfectly fine, implicit conversion (from char to rowid and backwards) seems to be working quite well.

    However, I could reproduce the error you are running into be entering an invalid row_id. In the above mentioned code, I replaced

    row_id := 'AAADL1AABAAACTEAAE';

    with

    row_id := 'CEDRIC'; (which is obviously not a RowId)

    and now I get the exact same error message you have. This means that the character string you are entering DOES NOT qualify as a ROWID (which is, by the way, totally consistent with the Oracle documentation about error ORA-01410).

    Good luck.

  8. #8
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    What's the type of message_typ.text ? Should be ROWID imho.

    If I where you I would use the ROWID datatype everywhere, without converting between VARCHAR2 and ROWID.

    'invalid rowid' means that the rowid has been corrupted somewhere, perhaps during a conversion.

    --

    I would never store the rowid in a queue, anyway. Say someone reorganizes the table sms_dispatcher - all the rowids contained in the queue become rubbish. I would store, instead, the PK of the table (perhaps generating it using a sequence).

    ---

    execute immediate 'insert into jat.sms_dispatcher values ('''|| msg_id ||''',' || exp_date || ','''|| dest_addr ||''',' || l_counter || ',' || send_date || ','''||fs_path||''','''||mesg||''',' || l_status || ','''|| origin_addr||''',' || charged_party || ',' || cm_time || ')
    returning rowid into :1' using out row_id ;

    is better rewritten as static sql with bind variables:

    insert into jat.sms_dispatcher (a, b ... z)
    values a_val, b_val, ... z_val
    returning rowid into row_id;

    in order to avoid polluting the library cache, and wasting time hard parsing - it is also easier to write !

    HTH
    Al

  9. #9
    Join Date
    Oct 2003
    Posts
    4
    Hi all,

    Thank you for the suggestions. I could able to solve the problem. The problem was not with the conversion of rowid (as I thought) but with the dequeuing.

    Txs and Rgds,
    santharam.

Posting Permissions

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