Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2003
    Posts
    16

    Unanswered: Declared size of varchar in PL

    Is there some way of getting the declared size of a varchar parameter to a PL function? Something like:

    FUNCTION stuff(a_string VARCHAR) RETURN NUMBER IS
    RETURN declared_size(a_string);
    END stuff;

    ... so if I passed this function a string declared as "varchar(1000) "it would return 1000.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    PHP Code:
    CREATE OR REPLACE FUNCTION declared_size (a_string IN CHAR)
       RETURN 
    NUMBER
    IS
    BEGIN
       
    RETURN TO_NUMBER (SUBSTR (a_string,
                                 
    INSTR (a_string'(') + 1,
                                 
    LENGTH (a_string) - INSTR (a_string'(') - 1
                                
    )
                        );
    END
    However, I don't understand why would you use function 'stuff' and then call 'declared_size' to return just the same thing as the 'declared_size' itself?

  3. #3
    Join Date
    Jan 2003
    Posts
    16
    Sorry, the example was a bit confusing. I do not want to pass the string constant 'varchar(1000)', but rather a pl variable declared this way. For example:

    str varchar(300);
    begin
    ...
    n:=declared_length(str)


    ... should give "n" a value of 300.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I'm convinced the answer is "no", you can't do it. But it's hard to prove a negative! I see nothing in the PL/SQL manual that suggests this can be done.

  5. #5
    Join Date
    Jan 2003
    Posts
    16
    That was my assumption, too.

    But I couldn't just give up without asking the gurus first ;-)

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Try posting the question on Ask Tom for a more definitive response.

  7. #7
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    This certainly isn't the most elegant solution, but does the job.

    PHP Code:
    CREATE OR REPLACE FUNCTION STR_SIZEinSource IN OUT VARCHAR2 ) RETURN BINARY_INTEGER IS
       SaveString VARCHAR2
    32767 );
       
    Result     BINARY_INTEGER;
    BEGIN
       SaveString 
    := inSource;
       
    inSource   := '1';
       
    BEGIN
          
    FOR i IN 1..32766 LOOP
             inSource 
    := inSource||'1';
          
    END LOOP;
       
    EXCEPTION WHEN OTHERS THEN
          NULL
    ;
       
    END;

       
    Result   := LENGTHinSource );
       
    inSource := SaveString;
       
       RETURN 
    Result;
    END
    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Very clever!

  9. #9
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Tony,

    I guess it could also work for numbers, by repeatedly multiplying the number by 10 you would eventually get the size, and adding a division of 10 the precision.

    I would have thought though that this sort of thing would be available in pre-supplied packages?

    Cheers
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

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