how to transfer variables that have spaces from batch to oracle sqlplus

Question

I've a little problem when i pass my parameter to my script pl/sql. I would pass a string with space. Because my pl/sql script treat the information in function of their position. So i've the same information a the same position, and the spaces provides the exact position of the information. So i can't change that.

But, when i pass my string, my pl/sql script "take" only the string until the first space. After is ommit.

For example, i pass 'Test1 Name Adresse Validation', but i've only 'Test1' in my database... It considere only 'Test1'.

How can i solved it ?

More information :

Sqlplus line command :

sqlplus -L %user%/%pwd%@%db% @C:\Hardis\NDL\SQL\MHUHMS.sql !l!

And to recup the value of my parameter in my pl/sql script :

l:='&1';

In my pl/sql script l is define like this : l NCHAR(2000);

Thank's

Ditto · Accepted Answer · 2015-11-02 14:09:09Z

2

typically parameters are space seperated, so if you are passing "a b c d" .. you can receive that in SQL using &1, &2, &3 and &4. Now, if you really only have 1 parameter "abcd" .. that sometimes comes in as "ab cd" .. and you want that in 1 variable .. I believe you need to double quote it.

  SQL> @test asdf
  old   4:   l := '&1';
  new   4:   l := 'asdf';
  Recieved: asdf

  PL/SQL procedure successfully completed.

  SQL> @test as df
  old   4:   l := '&1';
  new   4:   l := 'as';
  Recieved: as

  PL/SQL procedure successfully completed.

  SQL> @test "as df"
  old   4:   l := '&1';
  new   4:   l := 'as df';
  Recieved: as df

  PL/SQL procedure successfully completed.

  SQL>

Test script "test.sql" used was:

  set serverout on

  declare
    l  varchar2(1000);
  begin
    l := '&1';

    dbms_output.put_line ( 'Recieved: ' || l );
  end;
  /

answered Nov 2, 2015 at 14:09

Ditto

3,3793 gold badges16 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

S. Guillaume Over a year ago

thank's for reply. The problem now it's the lenght of the string... There are more than 240 carateres... and sqlplus can't accept it... Do you have a tip ?puu.sh/l6HB4/592ddae194.png, puu.sh/l6HC3/72a5757a34.png (Here the string is too long, the max size is 239caracteres)

Alex Poole Over a year ago

@S.Guillaume - that's a completely separate question; which you've already asked. If the elements are always in the same position maybe you can pass them unquoted and use multiple positional substitution variables instead?

S. Guillaume Over a year ago

@AlexPoole it's not really a separate question because it's related. One problem engage the second one. So it's a vicious circle...

Ditto Over a year ago

@S. Guillaume: Don't pass it as a parameter .. go another route: community.oracle.com/thread/2295410

S. Guillaume Over a year ago

@Ditto Thank's, my english is little basic, i don't really understand how to pass file as parameter... Can you explain more ? Thank's in advance

|

Collectives™ on Stack Overflow

how to transfer variables that have spaces from batch to oracle sqlplus

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related