CODEWITHSUNDEEP
oracle-databaseplsqlsqlplus
user918753
user918753

Reputation: 11

Assign value to Variable and call in multiple commands

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 ... ......

Upvotes: 0

Views: 700

Answers (3)

Jon Heller
Jon Heller

Reputation: 36922

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

  1. Exclude statement terminators from dynamic SQL: As others have mentioned, remove the ; from the end of SQL statements used in dynamic SQL.
  2. Escape strings: Strings in strings need to be escaped. The string 'DATA' should be ''DATA''.
  3. 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.
  4. 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.

Upvotes: 1

alexgibbs
alexgibbs

Reputation: 2480

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.

Upvotes: 1

Shaun Peterson
Shaun Peterson

Reputation: 1790

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;

Upvotes: 1

Related Questions