Assign value to Variable and call in multiple commands

Question

I need to assign a value to variable from select query output and call the variable into sql commands

For eg: I get PDB_NAME from v$pdbs and assign value to v_pdb I want to use v_pdb in multiple sql commands to run against PDBs

I tried to assign value from SELECT query to v_pdb and call the v_pdb in 'alter session set container=v_pdb';, it looks like working, but i get ORA-00922: missing or invalid option error

set serveroutput on;
declare
v_sql varchar2(80);
v_pdb varchar2(30);
BEGIN
  FOR pdb IN (select name from v$pdbs where con_id=3 and OPEN_MODE='READ WRITE') 
  LOOP
    v_sql := 'alter session set container='||pdb.name;
    DBMS_OUTPUT.PUT_LINE('Executing: ' || v_sql);
    --execute immediate 'alter session set container='||pdb.name||';';
    execute immediate v_sql;
    --v_sql := 'show con_name';
    --execute immediate 'show con_name';
    --execute immediate v_sql;
    v_sql := 'create tablespace APPDATA datafile '+DATA' size 1G autoextend on next 100M maxsize 5G ENCRYPTION USING 'AES256' DEFAULT STORAGE (ENCRYPT)';
    DBMS_OUTPUT.PUT_LINE('Executing: ' || v_sql);
    execute immediate v_sql;
    v_sql := 'drop user bigschema cascade';
    DBMS_OUTPUT.PUT_LINE('Executing: ' || v_sql);
    --execute immediate 'drop user bigschema cascade'; 
    execute immediate v_sql;
    v_sql := 'create user bigschema identified by B67_kuca_ecdf default tablespace APPDATA temporary tablespace TEMP profile DEFAULT account unlock';
    DBMS_OUTPUT.PUT_LINE('Executing: ' || v_sql);
    execute immediate v_sql;
    v_sql := 'alter user bigschema quota unlimited on APPDATA';
    DBMS_OUTPUT.PUT_LINE('Executing: ' || v_sql);
    execute immediate v_sql;
    v_sql := 'grant dba to bigschema';
    DBMS_OUTPUT.PUT_LINE('Executing: ' || v_sql);
    execute immediate v_sql;
    v_sql := 'conn bigschema/"B67_kuca_ecdf"@'||pdb.name;
    DBMS_OUTPUT.PUT_LINE('Executing: ' || v_sql);
    execute immediate v_sql;
    v_sql := 'drop table MV2OCI';
    DBMS_OUTPUT.PUT_LINE('Executing: ' || v_sql);
    execute immediate v_sql;
    v_sql := 'create table MV2OCI tablespace APPDATA as select * from dba_objects';
    DBMS_OUTPUT.PUT_LINE('Executing: ' || v_sql);
    execute immediate v_sql;
    v_sql := 'alter table MV2OCI nologging';
    DBMS_OUTPUT.PUT_LINE('Executing: ' || v_sql);
    execute immediate v_sql;
    v_sql := 'show user';
    DBMS_OUTPUT.PUT_LINE('Executing: ' || v_sql);
    execute immediate v_sql;
    v_sql := 'insert into MV2OCI select * from dba_objects';
    DBMS_OUTPUT.PUT_LINE('Executing: ' || v_sql);
    execute immediate v_sql;
    v_sql := 'insert into MV2OCI select * from MV2OCI';
    DBMS_OUTPUT.PUT_LINE('Executing: ' || v_sql);
    execute immediate v_sql;
 END LOOP;
END;
/

I simply want to get the value for variable v_pdb from "select name from v$pdbs where con_id=3 and OPEN_MODE='READ WRITE'"

And call the v_pdb as follows:

alter session set container=v_pdb; run other sql commands ... ......

Try v_pdb := pdb.name; as the first line of your loops body. — sticky bit
– sticky bit, Commented May 21, 2019 at 22:02

alexgibbs · Accepted Answer · 2019-05-21 22:18:05Z

1

I believe the problem is in the trailing semi-colon in your dynamic SQL. Dynamic SQL does not include a trailing semi-colon -- since the dynamic SQL is a single statement, no statement-separator is required.

After dropping the trailing semi-colon (and the "show" command (a client command)) this works ok. But I don't know of a good way to get DBMS_OUTPUT going unless you are already in a given PDB. That has been dropped in this example.

declare
    v_sql varchar2(80);
BEGIN
    FOR pdb IN (select name from v$pdbs where con_id=3 and OPEN_MODE='READ WRITE')
        LOOP
            v_sql := 'alter session set container='||pdb.name;
            execute immediate V_SQL;
            DBMS_OUTPUT.ENABLE;
            v_sql := 'CREATE TABLE TEST_TABLE(LOREM_IPSUM NUMBER)';
            execute immediate V_SQL;
        END LOOP;
END;
/

Result:

PL/SQL procedure successfully completed.

Navigating over to the PDB, TEST_TABLE now exists there.

edited May 21, 2019 at 22:18

answered May 21, 2019 at 22:08

alexgibbs

2,4802 gold badges18 silver badges19 bronze badges

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

2 Comments

user918753 Over a year ago

Thanks, but it doesn't look like working with show con_name command I need to run multiple SQL statements after i set "alter session set container=pdb;" I get error when replace select * from dual to show con_name v_sql := 'show con_name'; /SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 Executing: alter session set container=PDB1D002 Executing: show con_name declare * ERROR at line 1: ORA-00900: invalid SQL statement ORA-06512: at line 11 ORA-06512: at line 11

alexgibbs Over a year ago

Thanks user918753. "SHOW" is a sqlplus/sqlcl command -- sqlplus/sqlcl see "show" and actually run other commands to gather other information to display in the terminal. SQLPlus commands can't be run in dynamic SQL, only valid SQL or PL/SQL can be executed in an execute immediate block. You could instead run a valid SQL statement to get the connection name and do whatever is needed with it.

Shaun Peterson · Accepted Answer · 2019-05-21 22:13:17Z

1

I do not think that it actually has anything to do with your pdb variable...

When you use execute immediate you can not have a ; in the string

So for each of your execute immediate statments remove the ; eg

execute immediate 'alter session set container='||pdb.name||';';

becomes

execute immediate 'alter session set container='||pdb.name;

answered May 21, 2019 at 22:13

Shaun Peterson

1,7901 gold badge16 silver badges21 bronze badges

Comments

Jon Heller · Accepted Answer · 2019-05-22 02:22:13Z

1

There are several ways to improve the code and the coding process:

Exclude statement terminators from dynamic SQL: As others have mentioned, remove the ; from the end of SQL statements used in dynamic SQL.
Escape strings: Strings in strings need to be escaped. The string 'DATA' should be ''DATA''.
Pay attention to the full error message: Always display the entire error message, including the line number and column number. That information points exactly to the problem.
Use the smallest possible example: A smaller example would have less errors, making it easier to find the real problem. And in the process of simplifying the example you will likely find the answer yourself.

answered May 22, 2019 at 2:22

Jon Heller

37.1k6 gold badges84 silver badges150 bronze badges

Collectives™ on Stack Overflow

Assign value to Variable and call in multiple commands

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related