CODEWITHSUNDEEP
oracle-databasebatch-fileplsqlsqlplus
S. Guillaume
S. Guillaume

Reputation: 67

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

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

Upvotes: 0

Views: 2483

Answers (1)

Ditto
Ditto

Reputation: 3344

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;
  /

Upvotes: 2

Related Questions