Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2006
    Posts
    12

    Question Unanswered: OUTPUT arguments

    I would like to know why when I have an output argument of type CHAR (gvenpedl.udmalt%TYPE), the type of the returned value is VARCHAR??

    Example:

    CREATE OR REPLACE PROCEDURE test(
    o_udmdes OUT gvenpedl.udmalt%TYPE
    )
    BEGIN
    o_udmdes: = ' UN';
    RAISE_APPLICATION_ERROR (-20000, ' length: ' || LENGTH(o_udmdes));
    END;
    .

    run java.sql.SQLException: ORA-20000: length: 32512

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    For testing purposes (Oracle 10g), your procedure is modified a little bit (it is easier to test it without another PL/SQL block):
    Code:
    SQL> create table gvenpedl (udmalt char(20));
    
    Table created.
    
    SQL> create or replace procedure test
      2  is
      3    o_udmdes gvenpedl.udmalt%type;
      4  begin
      5    o_udmdes := ' UN';
      6    dbms_output.put_line(length(o_udmdes));
      7  end;
      8  /
    
    Procedure created.
    
    SQL> set serveroutput on
    SQL> execute test;
    20
    
    PL/SQL procedure successfully completed.
    
    SQL>
    I got CHAR's length (20) as the output value, not "32512" (or some similar large value).

    Now, you said that output argument is of CHAR datatype (gvenpedl.udmalt%type), but - how do we know it is true? Why didn't you post table description? Did you perhaps view wrong column (or table?) Or did I miss something?

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    It seems that LENGTH actually returns the length of the target variable for the OUT parameter:
    Code:
    SQL> create table t1 (c char(5));
    
    Table created.
    
    SQL> CREATE OR REPLACE PROCEDURE p1(
      2  o_udmdes OUT t1.c%TYPE
      3  )
      4  IS
      5  BEGIN
      6  o_udmdes := ' UN';
      7  RAISE_APPLICATION_ERROR (-20000, ' length: ' || LENGTH(o_udmdes));
      8  END;
      9  /
    
    Procedure created.
    
    SQL> var x varchar2(255)
    SQL> exec p1(:x)
    BEGIN p1(:x); END;
    
    *
    ERROR at line 1:
    ORA-20000:  length: 255
    ORA-06512: at "DESDIR.P1", line 7
    ORA-06512: at line 1
    
    SQL> var x varchar2(1234)
    SQL> exec p1(:x)
    BEGIN p1(:x); END;
    
    *
    ERROR at line 1:
    ORA-20000:  length: 1234
    ORA-06512: at "DESDIR.P1", line 7
    ORA-06512: at line 1

  4. #4
    Join Date
    Mar 2006
    Posts
    12

    OUTPUT arguments

    OK, from SQLPLUS is thus, the problem is that I'm attempting to execute it from java,
    using the following:

    String sql = "call test (?)";
    CallableStatement cstmt = conn.prepareCall(sql);
    cstmt.registerOutParameter(1, Types.CHAR);
    cstmt.execute();

    Same if i use special JDBC routine:
    Special Oracle version of registerOutParameter for registering CHAR, VARCHAR, LONG, RAW and LONG RAW columns.


    http://www.princeton.edu/~storacle/j...Statement.html

    ((oracle.jdbc.driver.OracleCallableStatement)cstmt ).registerOutParameter(1, Types.CHAR, 6);



    But the result is always the same, the variable into the function, oracle creates it of type CHAR of 32512,
    what I am making bad?



    Quote Originally Posted by andrewst
    It seems that LENGTH actually returns the length of the target variable for the OUT parameter:
    Code:
    SQL> create table t1 (c char(5));
    
    Table created.
    
    SQL> CREATE OR REPLACE PROCEDURE p1(
      2  o_udmdes OUT t1.c%TYPE
      3  )
      4  IS
      5  BEGIN
      6  o_udmdes := ' UN';
      7  RAISE_APPLICATION_ERROR (-20000, ' length: ' || LENGTH(o_udmdes));
      8  END;
      9  /
    
    Procedure created.
    
    SQL> var x varchar2(255)
    SQL> exec p1(:x)
    BEGIN p1(:x); END;
    
    *
    ERROR at line 1:
    ORA-20000:  length: 255
    ORA-06512: at "DESDIR.P1", line 7
    ORA-06512: at line 1
    
    SQL> var x varchar2(1234)
    SQL> exec p1(:x)
    BEGIN p1(:x); END;
    
    *
    ERROR at line 1:
    ORA-20000:  length: 1234
    ORA-06512: at "DESDIR.P1", line 7
    ORA-06512: at line 1

  5. #5
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    I don't know why you get such a result, but why not using Types.VARCHAR ?

    That would solve your problem IMO : there is no converting issue between CHAR and VARCHAR2 or vice-versa.

    Code:
    rbaraer@Ora10g> create table t1 (c char(5));
    
    Table créée.
    
    rbaraer@Ora10g> CREATE OR REPLACE PROCEDURE p1(
       o_udmdes OUT Varchar2
    )
    IS
    BEGIN
       o_udmdes := ' UN';
       RAISE_APPLICATION_ERROR (-20000, ' length: ' || LENGTH(o_udmdes));
    END;
    /  2    3    4    5    6    7    8    9
    
    Procédure créée.
    
    rbaraer@Ora10g> var x varchar2(1234)
    rbaraer@Ora10g> exec p1(:x)
    BEGIN p1(:x); END;
    
    *
    ERREUR à la ligne 1 :
    ORA-20000:  length: 3
    ORA-06512: à "RBARAER.P1", ligne 7
    ORA-06512: à ligne 1
    
    
    rbaraer@Ora10g>
    Besides, I found something which is slightly off topic but which might interest you a few weeks ago : see here.

    Using the OUT Mode

    ...

    Before exiting a subprogram, assign values to all OUT formal parameters. Otherwise, the corresponding actual parameters will be null. If you exit successfully, PL/SQL assigns values to the actual parameters. If you exit with an unhandled exception, PL/SQL does not assign values to the actual parameters.

    Using the IN OUT Mode

    ...

    If you exit a subprogram successfully, PL/SQL assigns values to the actual parameters. If you exit with an unhandled exception, PL/SQL does not assign values to the actual parameters.
    Now I am aware of that : if an exception is raised to the caller then it is considered "unhandled" on the PL/SQL side, so that from the caller's point of view every OUT parameter is NULL and every IN OUT parameter will have their IN value.

    Here there is not this problem because you are testing the length inside the procedure, but don't try to test it in your java code after your -20000 exception .

    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

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