CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle10goracle-sqldeveloper
Lok
Lok

Reputation: 131

How to save result from sql query to a variable and later use it in the script?

I have the following script:

    cl scr;
    variable l number;
    begin
      select max(length(name)) into :l from goodcustomer;
    end;
    /
    --print :l;
    alter table goodcustomer modify name varchar2(:l);

I am trying to modify the length of name attribute to the maximum length of name (which is 17) that currently exists in the table. The code above is giving me the following error in sql developer:

    Error report -
    SQL Error: ORA-00910: specified length too long for its datatype
    00910. 00000 -  "specified length too long for its datatype"
    *Cause:    for datatypes CHAR and RAW, the length specified was > 2000;
               otherwise, the length specified was > 4000.
    *Action:   use a shorter length or switch to a datatype permitting a
               longer length such as a VARCHAR2, LONG CHAR, or LONG RAW

What am I doing wrong here? Isn't l a number that can be used for giving size of varchar2? Any other way to achieve the same will also be appreciated?

Upvotes: 0

Views: 314

Answers (1)

stefan
stefan

Reputation: 2252

Maybe you can use dynamic SQL. Example (Oracle 18c):

Table

SQL> create table goodcustomer ( name varchar2( 4000 ) ) ;

Table created.

SQL> 
SQL> describe goodcustomer 
Name   Null?   Type             
NAME           VARCHAR2(4000)

INSERT some names

SQL> begin
  2    insert into goodcustomer values( 'shortestname' ) ;
  3    insert into goodcustomer values( 'l o n g e s tname' ) ;
  4  end ;
  5  /

PL/SQL procedure successfully completed.

SQL> select max( length( name ) ) from goodcustomer ;
  MAX(LENGTH(NAME)) 
                 17

ALTER TABLE ... MODIFY

SQL> declare
  2    l number := 0 ;
  3    sqlstr varchar2( 4000 ) := '' ;
  4  begin
  5    select max( length( name ) ) into l from goodcustomer ;
  6    sqlstr := 'alter table goodcustomer modify name varchar2( ' 
  7           || to_char( l ) 
  8           || ')' ;
  9    execute immediate sqlstr ;
 10  end ;
 11  /

PL/SQL procedure successfully completed.

-- The NAME column now: VARCHAR2( 17 )
SQL> describe goodcustomer
Name   Null?   Type           
NAME           VARCHAR2(17)

Additional reading, which may clarify things for you (substitution versus bind variables) see here.

Upvotes: 1

Related Questions