why substituion variables change

[kytemaniac]

Hi

my oracle version is as follow:

Code: Select all

[oracle@sg-cent57 createschema]$ sqlplus -v

SQL*Plus: Release 11.2.0.2.0 Production



os is on

Code: Select all

[oracle@svk-cent57 createschema]$ cat /etc/redhat-release
CentOS release 5.7 (Final)


My main script is as follow

Code: Select all

begin
 dbms_output.put_line('Point 1 what is 1 &1');
 dbms_output.put_line('Point 1 what is 2 &2');
 dbms_output.put_line('Point 1 what is 3 &3');
 dbms_output.put_line('Point 1 what is 4 &4');
 dbms_output.put_line('Point 1 what is 5 &5');
 dbms_output.put_line('Point 1 what is 6 &6');
 dbms_output.put_line('Point 1 what is 7 &7');
 dbms_output.put_line('Point 1 what is 8 &8');
end;
/

prompt ||=======creating user EXCEL_ADMIN start =======||



@sys/create_user.sql EXCEL_ADMIN &4 TSEXCEL_ADM TSXLS_ADM_TEMP


begin
 dbms_output.put_line('Point 2 what is 1 &1');
 dbms_output.put_line('Point 2 what is 2 &2');
 dbms_output.put_line('Point 2 what is 3 &3');
 dbms_output.put_line('Point 2 what is 4 &4');
 dbms_output.put_line('Point 2 what is 5 &5');
 dbms_output.put_line('Point 2 what is 6 &6');
 dbms_output.put_line('Point 2 what is 7 &7');
 dbms_output.put_line('Point 2 what is 8 &8');
end;
/





output is as follow

Code: Select all

Point 1 what is 1 /s01/app/oracle/product/11.2.0/db_1
Point 1 what is 2 ABKDB4
Point 1 what is 3 excel
Point 1 what is 4 excel
Point 1 what is 5 excel
Point 1 what is 6 excel_system
Point 1 what is 7 excel_sys
Point 1 what is 8 excel
||=======creating user EXCEL_ADMIN start =======||

Creating User EXCEL_ADMIN
============================

User EXCEL_ADMIN is already created!
Point 2 what is 1 EXCEL_ADMIN
Point 2 what is 2 excel
Point 2 what is 3 TSEXCEL_ADM
Point 2 what is 4 TSXLS_ADM_TEMP
Point 2 what is 5 excel
Point 2 what is 6 excel_system
Point 2 what is 7 excel_sys
Point 2 what is 8 excel




now if you realize this parameter 1 to 4 change after invoking create_user.sql.

here's my code for creating user

Code: Select all

set serveroutput on
--parameter 1: $USERNAME
--parameter 2: $PASSWORD
--parameter 3: default tablespace
--parameter 4: temporary tablespace

set verify off
set feedback off

prompt
prompt Creating User &1
prompt ============================
prompt

DECLARE
v_username DBA_USERS.USERNAME%TYPE;
v_password DBA_USERS.PASSWORD%TYPE;
v_default_tablespace DBA_USERS.DEFAULT_TABLESPACE%TYPE;
v_temporary_tablespace DBA_USERS.TEMPORARY_TABLESPACE%TYPE;
v_count number;
v_sql VARCHAR2(1000);
ecode NUMBER;
emesg VARCHAR2(200);
BEGIN
   v_username :=upper('&1');
   v_password :='&2';
   v_default_tablespace :=upper('&3');
   v_temporary_tablespace :=upper('&4');
   
   SELECT count(0) into v_count from dba_users where username=v_username;
   IF v_count = 1 THEN
      dbms_output.put_line('User ' || v_username || ' is already created!');
   ELSE
      dbms_output.put_line('User ' || v_username || ' is not created!');
      v_sql :='create user ' || v_username || ' identified by ' || v_password ;
      v_sql := v_sql || ' default tablespace ' || v_default_tablespace;
      v_sql := v_sql || ' temporary tablespace ' || v_temporary_tablespace;
      v_sql := v_sql || ' profile DEFAULT';
      
      dbms_output.put_line('v_sql ' || v_sql);
      BEGIN
         execute immediate v_sql;
         
      EXCEPTION WHEN OTHERS THEN
         ecode := SQLCODE;
        emesg := SQLERRM;
        dbms_output.put_line(TO_CHAR(ecode) || '-' || emesg);
      END;
   END IF;   
END;
/


what's wrong with my code, why does it change the parameter file? I don't understand why?

any assistance would be really appreciated!

[Tim...]

Hi.

Let's make the example more simple.

script1.sql

Code: Select all

set serveroutput on
set verify off
begin
  dbms_output.put_line('Script1');
  dbms_output.put_line('P1=&1');
  dbms_output.put_line('P2=&2');
  dbms_output.put_line('P3=&3');
  dbms_output.put_line('P4=&4');
end;
/

@script2.sql &3 &4

begin
  dbms_output.put_line('Script1');
  dbms_output.put_line('P1=&1');
  dbms_output.put_line('P2=&2');
  dbms_output.put_line('P3=&3');
  dbms_output.put_line('P4=&4');
end;
/

script2.sql

Code: Select all

begin
  dbms_output.put_line('Script2');
  dbms_output.put_line('P1=&1');
  dbms_output.put_line('P2=&2');
end;
/

Output when calling script1.sql with 4 parameters.

Code: Select all

SQL> @script1 ONE TWO THREE FOUR
Script1
P1=ONE
P2=TWO
P3=THREE
P4=FOUR

PL/SQL procedure successfully completed.

Script2
P1=THREE
P2=FOUR

PL/SQL procedure successfully completed.

Script1
P1=THREE
P2=FOUR
P3=THREE
P4=FOUR

PL/SQL procedure successfully completed.

SQL>

So what we can see is when we call the second script, the values for placeholders 1 and 2 are overwritten. This is as expected, because we have passed the values of placeholders 3 and 4 in as parameters to placeholders 1 and 2 in the second script.

The values of placeholders are not script-specific. There is just a single memory location for each that is being overwritten when you call internal scripts.

Cheers

Tim...