We have views in Oracle that return no records unless their Organization ID is known.
They were created in Discoverer and we are trying to import their results into our data warehouse. Using SQL Developer, we can set the ORG ID session variable using the following:
EXEC mo_global.set_policy_context('S','1');
This works. Once this is executed, the query returns values. What we want to do is create a function, stored procedure, whatever whereby we can set the ORG ID on behalf of our Oracle Data Integrator (basically proxy in) so that it can retrieve the data.
The following creates a simple table, adds some records and builds a pipeline function and supporting types that returns the data. Everything works nicely.
However, when we enable the EXEC command by removing the two dashes in the TEST_ACTION_PIPED, it does not like it. It won't compile.
--EXEC mo_global.set_policy_context(S,'1'); --Where 1 = Organization ID
Sample
I had to do each step independently to get everything to build and compile. Also, since this table is not being "secured" by Oracle Developer, it technically does not need to have a session set. The example is designed to build a working pipeline function, then show that when we try adding the EXEC mo_global command, it won't compile.
Is there a way to se that session variable in a procedure the way we can in SQL Developer?
--Step 1: Create a Table
CREATE TABLE TEST_ACTION (ACTION VARCHAR2(30 BYTE));
--Step 2. Add Data
INSERT INTO TEST_ACTION (ACTION) VALUES ('Action 1');
INSERT INTO TEST_ACTION (ACTION) VALUES ('Action 2');
COMMIT;
--Step 3. Create a Type (Based Upon Columns, Types and Sizes) to hold data.
CREATE OR REPLACE TYPE TEST_ACTION_TYPE AS OBJECT ( ACTION VARCHAR2(30 BYTE));
--Step 4. Create a Table of TEST_ACTION_TYPE to hold rows.
CREATE OR REPLACE TYPE TEST_ACTION_TABLE AS TABLE OF TEST_ACTION_TYPE;
--Step 5. Create a Pipelined Function to retrieve a TEST_ACTION_TABLE
CREATE OR REPLACE FUNCTION TEST_ACTION_PIPED RETURN TEST_ACTION_TABLE PIPELINED
AS
BEGIN
--EXEC mo_global.set_policy_context(S,'1'); --Where 1 = Organization ID
FOR v_Rec IN (SELECT DISTINCT ACTION FROM TEST_ACTION ORDER BY ACTION) LOOP
PIPE ROW (TEST_ACTION_TYPE(v_Rec.ACTION));
END LOOP;
RETURN;
END;
--Step 6. Retrieve the Table
SELECT ACTION FROM TABLE(TEST_ACTION_PIPED());