Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2010
    Posts
    64

    Unanswered: Insert failing / arguments failing in procedure

    Hi All,

    please find attached the code :-

    ------------------------------
    drop table temp ;

    create table temp
    (
    UNAM varchar2(20)
    , STA varchar2(20)
    , DES varchar2(20)
    , RU varchar2(20)
    , RD number
    , RT varchar2(20)
    , PCD varchar2(20)
    , PCTM varchar2(20)
    , pol number
    , Innum number
    );


    CREATE OR REPLACE Procedure myproc (P NUMBER, A number, R varchar2(30))
    AS
    BEGIN

    insert into temp
    (STA,STADES,REQUSR,REQDTE,REQTME,PRCDTE,PRCTME,pol ref,assyst)
    values
    (
    500
    , 'Submitted'
    , nvl(R,substr(user,1,11))
    , to_char(sysdate,'J') - 2415021
    , to_char(sysdate,'HH24:MI')
    , ''
    , ''
    , P
    , A
    );

    EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line('exception');

    END myproc ;
    /

    show errors;
    ---------------

    Please advice me with the error.

    Thanks !!
    Subhotech
    Last edited by subhotech; 02-04-11 at 07:29.

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by subhotech View Post
    Please advice me with the error.
    Hi,
    what about be guided by the showed error messages?
    1/49 PLS-00103: Encountered the symbol "("
    VARCHAR2 length is not supported when declaring procedure parameter. The correct syntax is available in SQL Reference book, available e.g. online on http://tahiti.oracle.com/ Please, consult it.
    6/52 PLS-00103: Encountered the symbol "REF"
    What about verbatim typing column names of the TEMP table in the INSERT statement? Oracle is not flexible enough to guess it from your inventional ones (except the first one, they do not match the table column names at all; additionally, space in column name "pol ref" is invalid).

    Lastly, what is the purpose of that EXCEPTION hiding block? Just pretending that the procedure succeeded even when any failure happened, especially when the client did not set SERVEROUTPUT ON?

Posting Permissions

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