Reputation: 11
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
Reputation: 36922
There are several ways to improve the code and the coding process:
;
from the end of SQL statements used in dynamic SQL.'DATA'
should be ''DATA''
.Upvotes: 1
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
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