Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2006
    Posts
    1

    Unanswered: Oracle Procedure Execution Problem

    Hi,

    Can any body tell me why the follwoing error message is coming when I am trying to execute the follwoing procedure :

    Create Or Replace Procedure p_create_nsdl_details Is
    err_msg Varchar2(500);
    Cursor cur_nsdl is
    Select nsdl_clientid,
    nsdl_frame_slno
    from nsdl_details;
    Begin
    For c_rec In cur_nsdl Loop
    Begin
    execute immediate 'Insert /*+APPEND */ Into nsdl_details_new Select nsdl_dpid,nsdl_clientid,to_lob(nsdl_image) "nsdl_image",nsdl_mess_code,nsdl_mess_desc,nsdl_cr eateby,nsdl_createdate,nsdl_details,nsdl_frame_sln o,nsdl_frm_status from nsdl_details where nsdl_clientid ='''||c_rec.nsdl_clientid ||''' and nsdl_frame_slno = ||'c_rec.nsdl_frame_slno'';
    commit;
    Exception
    When Others then
    err_msg := substr(sqlerrm, 1, 500);
    dbms_output.put_line('Error' || err_msg);
    Rollback;
    End;
    End Loop;
    End;


    Error Message :

    ErrorORA-00936: missing expression

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It is because you are trying to do it fancy instead of making it work first.

    INSERT statement you use in EXECUTE IMMEDIATE isn't correctly written. I'd suggest you to declare a CHARACTER variable and store the whole INSERT statement into it, display it using DBMS_OUTPUT.PUT_LINE to check whether it looks OK or not and then, if everything is fine, execute the statement using EXECUTE IMMEDIATE.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    You can also remove the whole execute immediate line for testing purposes. I suspect (but haven't counted them) that you have some an error with the "opening" and closing of single quotes.

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Get rid of the cursor, the execute immediate and replace the whole thing by one simple insert statement which selects from the nsdl_details table.

    Alan

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    do what Alan said. Also you don't need a column-alias for to_lob(nsdl_image)
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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