HR@ORCL>TRUNCATE TABLE TEMP_TABLE_LONG2
HR@ORCL>EXECUTE DYNAMICQUERYLONG3(p_table=>'ORDERS')
HR@ORCL>SELECT * FROM TEMP_TABLE_LONG2
Works okay.
HR@ORCL>TRUNCATE TABLE TEMP_TABLE_LONG2
HR@ORCL>EXECUTE DYNAMICQUERYLONG2(p_dblink=>'OE_REMOTE',p_table=>'ORDERS')
HR@ORCL>SELECT * FROM TEMP_TABLE_LONG2
Errors out with the below error message. Could you please try to help me fix the error below?
BEGIN DYNAMICQUERYLONG2(p_dblink=>'OE_REMOTE',p_table=>'ORDERS'); END;
Error report -
ORA-03138: Connection terminated due to security policy violation
ORA-03101: invalid input data for
ORA-02063: preceding line from OE_REMOTE
ORA-06512: at "SYS.DBMS_SQL", line 2066
ORA-06512: at "HR.DYNAMICQUERYLONG2", line 77
ORA-06512: at line 1
03138. 00000 - "Connection terminated due to security policy violation"
*Cause: Connection was terminated due to a security policy violation.
*Action: Contact the Database Administrator
HR@ORCL>create or replace PROCEDURE DynamicQuerylong3 (
p_table IN VARCHAR2 DEFAULT NULL
) AS
v_CursorID INTEGER;
v_SelectStmt VARCHAR2(500);
V_TABLE_NAME TEMP_TABLE_LONG2.TABLE_name%TYPE;
V_CONSTRAINT_NAME TEMP_TABLE_LONG2.CONSTRAINT_name%TYPE;
V_CONSTRAINT_TYPE TEMP_TABLE_LONG2.CONSTRAINT_TYPE%TYPE;
V_SEARCH_CONDITION TEMP_TABLE_LONG2.search_condition%TYPE;--CLOB
v_long_one_piece_length VARCHAR2(32700);
v_search_condition_vc VARCHAR2(32700);
v_Dummy INTEGER;
v_long_offset INTEGER;
BEGIN
-- Open the cursor for processing.
v_CursorID := DBMS_SQL.OPEN_CURSOR;
-- Create the query string.
v_SelectStmt := 'SELECT
ucons.table_name,
ucons.constraint_name,
ucons.constraint_type,
ucons.search_condition
FROM
user_constraints ucons
WHERE
ucons.table_name = :b1 AND ucons.constraint_type = ''C'' ';
-- Parse the query.
DBMS_SQL.PARSE(v_CursorID, v_SelectStmt, DBMS_SQL.V7);
-- Bind the input variables.
dbms_sql.bind_variable(v_cursorid,':b1',p_table);
-- Define the output variables.
DBMS_SQL.DEFINE_COLUMN(c => v_CursorID,position => 1,column => V_TABLE_NAME, column_size => 20);
DBMS_SQL.DEFINE_COLUMN(c => v_CursorID,position => 2,column => V_CONSTRAINT_NAME, column_size => 20);
DBMS_SQL.DEFINE_COLUMN(c => v_CursorID,position => 3,column => V_CONSTRAINT_TYPE, column_size => 30);
DBMS_SQL.DEFINE_COLUMN_LONG(c => v_CursorID,position => 4);
-- Execute the statement. We don't care about the return
-- value, but we do need to declare a variable for it.
v_Dummy := DBMS_SQL.EXECUTE(v_CursorID);
-- This is the fetch loop.
LOOP
-- Fetch the rows into the buffer, and also check for the exit
-- condition from the loop.
IF DBMS_SQL.FETCH_ROWS(v_CursorID) = 0 THEN
EXIT;
END IF;
-- Retrieve the rows from the buffer into PL/SQL variables.
DBMS_SQL.COLUMN_VALUE(C=>v_CursorID,POSITION=> 1,VALUE=>V_TABLE_NAME);
DBMS_SQL.COLUMN_VALUE(C=>v_CursorID,POSITION=> 2,VALUE=>V_CONSTRAINT_NAME);
DBMS_SQL.COLUMN_VALUE(C=>v_CursorID,POSITION=> 3,VALUE=>V_CONSTRAINT_TYPE);
v_long_offset := 0;
v_search_condition := '';
v_search_condition_vc := '';
LOOP
--v_search_condition_vc := '';
dbms_sql.column_value_long(c => v_cursorid,position => 4,length => 32700,offset => v_long_offset,value => v_search_condition_vc, value_length=> v_long_one_piece_length);
EXIT WHEN v_long_one_piece_length = 0;
v_search_condition := v_search_condition||v_search_condition_vc;
v_long_offset := v_long_offset + v_long_one_piece_length;
END LOOP;
-- Insert the fetched data into temp_table.
INSERT INTO TEMP_TABLE_LONG2 (TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION)
VALUES (V_TABLE_NAME , V_CONSTRAINT_NAME ,V_CONSTRAINT_TYPE,V_SEARCH_CONDITION );
END LOOP;
-- Close the cursor.
DBMS_SQL.CLOSE_CURSOR(v_CursorID);
-- Commit our work.
COMMIT;
--EXCEPTION
--WHEN OTHERS THEN
-- Close the cursor, then raise the error again.
--DBMS_SQL.CLOSE_CURSOR(v_CursorID);
--RAISE;
END DynamicQuerylong3;
and code for DynamicQuerylong2
HR@ORCL>create or replace PROCEDURE DynamicQuerylong2 (
/* Uses DBMS_SQL to query the students table, and puts the
results in temp_table. The first names, last names, and
majors are inserted for up to two majors inputted. */
p_dblink IN VARCHAR2 DEFAULT NULL,
p_table IN VARCHAR2 DEFAULT NULL
) AS
v_CursorID INTEGER;
v_SelectStmt VARCHAR2(500);
V_TABLE_NAME TEMP_TABLE_LONG2.TABLE_name%TYPE;
V_CONSTRAINT_NAME TEMP_TABLE_LONG2.CONSTRAINT_name%TYPE;
V_CONSTRAINT_TYPE TEMP_TABLE_LONG2.CONSTRAINT_TYPE%TYPE;
V_SEARCH_CONDITION TEMP_TABLE_LONG2.search_condition%TYPE;--CLOB
v_long_one_piece_length VARCHAR2(32700);
v_search_condition_vc VARCHAR2(32700);
v_Dummy INTEGER;
v_long_offset INTEGER;
BEGIN
-- Open the cursor for processing.
v_CursorID := DBMS_SQL.OPEN_CURSOR;
-- Create the query string.
v_SelectStmt := 'SELECT
ucons.table_name,
ucons.constraint_name,
ucons.constraint_type,
ucons.search_condition
FROM
user_constraints'
|| '@'
|| p_dblink
|| ' ucons
WHERE
ucons.table_name = :b1 AND ucons.constraint_type = ''C'' ';
-- Parse the query.
DBMS_SQL.PARSE(v_CursorID, v_SelectStmt, DBMS_SQL.V7);
-- Bind the input variables.
dbms_sql.bind_variable(v_cursorid,':b1',p_table);
-- Define the output variables.
DBMS_SQL.DEFINE_COLUMN(c => v_CursorID,position => 1,column => V_TABLE_NAME, column_size => 20);
DBMS_SQL.DEFINE_COLUMN(c => v_CursorID,position => 2,column => V_CONSTRAINT_NAME, column_size => 20);
DBMS_SQL.DEFINE_COLUMN(c => v_CursorID,position => 3,column => V_CONSTRAINT_TYPE, column_size => 30);
DBMS_SQL.DEFINE_COLUMN_LONG(c => v_CursorID,position => 4);
-- Execute the statement. We don't care about the return
-- value, but we do need to declare a variable for it.
v_Dummy := DBMS_SQL.EXECUTE(v_CursorID);
-- This is the fetch loop.
LOOP
-- Fetch the rows into the buffer, and also check for the exit
-- condition from the loop.
IF DBMS_SQL.FETCH_ROWS(v_CursorID) = 0 THEN
EXIT;
END IF;
-- Retrieve the rows from the buffer into PL/SQL variables.
DBMS_SQL.COLUMN_VALUE(C=>v_CursorID,POSITION=> 1,VALUE=>V_TABLE_NAME);
DBMS_SQL.COLUMN_VALUE(C=>v_CursorID,POSITION=> 2,VALUE=>V_CONSTRAINT_NAME);
DBMS_SQL.COLUMN_VALUE(C=>v_CursorID,POSITION=> 3,VALUE=>V_CONSTRAINT_TYPE);
v_long_offset := 0;
v_search_condition := '';
v_search_condition_vc := '';
LOOP
v_search_condition_vc := '';
dbms_sql.column_value_long(c => v_cursorid,position => 4,length => 32700,offset => v_long_offset,value => v_search_condition_vc, value_length=> v_long_one_piece_length);
EXIT WHEN v_long_one_piece_length = 0;
v_search_condition := v_search_condition||v_search_condition_vc;
v_long_offset := v_long_offset + v_long_one_piece_length;
END LOOP;
-- Insert the fetched data into temp_table.
INSERT INTO TEMP_TABLE_LONG2 (TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION)
VALUES (V_TABLE_NAME , V_CONSTRAINT_NAME ,V_CONSTRAINT_TYPE,V_SEARCH_CONDITION );
END LOOP;
-- Close the cursor.
DBMS_SQL.CLOSE_CURSOR(v_CursorID);
-- Commit our work.
COMMIT;
--EXCEPTION
--WHEN OTHERS THEN
-- Close the cursor, then raise the error again.
--DBMS_SQL.CLOSE_CURSOR(v_CursorID);
--RAISE;
END DynamicQuerylong2;
Code for temp_table_long2
CREATE TABLE "HR"."TEMP_TABLE_LONG2"
( "TABLE_NAME" VARCHAR2(128 BYTE),
"CONSTRAINT_NAME" VARCHAR2(128 BYTE),
"CONSTRAINT_TYPE" VARCHAR2(1 BYTE),
"SEARCH_CONDITION" CLOB
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "EXAMPLE" ;
Code for database link
CREATE DATABASE LINK "OE_REMOTE"
CONNECT TO "OE" IDENTIFIED BY VALUES ':1'
USING 'ORCL';
Code for OE.ORDERS table
CREATE TABLE "OE"."ORDERS"
( "ORDER_ID" NUMBER(12,0),
"ORDER_DATE" TIMESTAMP (6) WITH LOCAL TIME ZONE CONSTRAINT "ORDER_DATE_NN" NOT NULL ENABLE,
"ORDER_MODE" VARCHAR2(8 BYTE),
"CUSTOMER_ID" NUMBER(6,0) CONSTRAINT "ORDER_CUSTOMER_ID_NN" NOT NULL ENABLE,
"ORDER_STATUS" NUMBER(2,0),
"ORDER_TOTAL" NUMBER(8,2),
"SALES_REP_ID" NUMBER(6,0),
"PROMOTION_ID" NUMBER(6,0),
CONSTRAINT "ORDER_MODE_LOV" CHECK (order_mode in ('direct','online')) ENABLE,
CONSTRAINT "ORDER_TOTAL_MIN" CHECK (order_total >= 0) ENABLE,
CONSTRAINT "ORDER_PK" PRIMARY KEY ("ORDER_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "EXAMPLE" ENABLE,
CONSTRAINT "ORDERS_SALES_REP_FK" FOREIGN KEY ("SALES_REP_ID")
REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ON DELETE SET NULL ENABLE,
CONSTRAINT "ORDERS_CUSTOMER_ID_FK" FOREIGN KEY ("CUSTOMER_ID")
REFERENCES "OE"."CUSTOMERS" ("CUSTOMER_ID") ON DELETE SET NULL ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "EXAMPLE" ;
COMMENT ON COLUMN "OE"."ORDERS"."ORDER_ID" IS 'PRIMARY KEY column.';
COMMENT ON COLUMN "OE"."ORDERS"."ORDER_DATE" IS 'TIMESTAMP WITH LOCAL TIME ZONE column, NOT NULL constraint.';
COMMENT ON COLUMN "OE"."ORDERS"."ORDER_MODE" IS 'CHECK constraint.';
COMMENT ON COLUMN "OE"."ORDERS"."ORDER_STATUS" IS '0: Not fully entered, 1: Entered, 2: Canceled - bad credit, -
3: Canceled - by customer, 4: Shipped - whole order, -
5: Shipped - replacement items, 6: Shipped - backlog on items, -
7: Shipped - special delivery, 8: Shipped - billed, 9: Shipped - payment plan,-
10: Shipped - paid';
COMMENT ON COLUMN "OE"."ORDERS"."ORDER_TOTAL" IS 'CHECK constraint.';
COMMENT ON COLUMN "OE"."ORDERS"."SALES_REP_ID" IS 'References hr.employees.employee_id.';
COMMENT ON COLUMN "OE"."ORDERS"."PROMOTION_ID" IS 'Sales promotion ID. Used in SH schema';
COMMENT ON TABLE "OE"."ORDERS" IS 'Contains orders entered by a salesperson as well as over the Web.';
CREATE INDEX "OE"."ORD_CUSTOMER_IX" ON "OE"."ORDERS" ("CUSTOMER_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "EXAMPLE" ;
CREATE INDEX "OE"."ORD_ORDER_DATE_IX" ON "OE"."ORDERS" ("ORDER_DATE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "EXAMPLE" ;
CREATE INDEX "OE"."ORD_SALES_REP_IX" ON "OE"."ORDERS" ("SALES_REP_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "EXAMPLE" ;
Privileges as of now.
grant execute on dbms_sql to hr;
grant connect to hr
grant CREATE SESSION to hr;
grant UNLIMITED TABLESPACE to hr;
grant CREATE TABLE to hr;
grant CREATE CLUSTER to hr;
grant CREATE VIEW to hr;
grant CREATE SEQUENCE to hr;
grant CREATE PROCEDURE to hr;
grant CREATE TRIGGER to hr;
grant CREATE TYPE to hr;
grant CREATE OPERATOR to hr;
grant CREATE INDEXTYPE to hr;
GRANT CREATE DATABASE LINK TO HR;
GRANT CREATE PUBLIC DATABASE LINK TO hr;
GRANT CREATE SESSION TO HR;
grant execute on dbms_sql to oe;
grant create session to oe;
grant execute on dbms_sql to oe;
grant connect to oe
grant CREATE SESSION to oe;
grant UNLIMITED TABLESPACE to oe;
grant CREATE TABLE to oe;
grant CREATE CLUSTER to oe;
grant CREATE VIEW to oe;
grant CREATE SEQUENCE to oe;
grant CREATE PROCEDURE to oe;
grant CREATE TRIGGER to oe;
grant CREATE TYPE to oe;
grant CREATE OPERATOR to oe;
grant CREATE INDEXTYPE to oe;
GRANT CREATE DATABASE LINK TO oe;
GRANT CREATE PUBLIC DATABASE LINK TO oe;
GRANT CREATE SESSION TO oe;
This is done after installing Oracle db-sample-schemas from github.
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
on Windows 10 Home.