CODEWITHSUNDEEP
oracle-databasevariablesproceduredblink
Marcus
Marcus

Reputation: 3869

Oracle DB link into variable

I have below procedure in which I want to make my select query dynamic depending on the DB link. So I put the DB link into variable IN_DB_CONNECTION_NAME and provide in my select query, but I am getting error as:

PL/SQL: ORA-00942: table or view does not exist.

I am using correct sql query to get the DB link from db_connection table. Here is my DB_CONNECTION table:

enter image description here

PROCEDURE "EXT_10004_SELF_SIGWF_CVB"(IN_KPI_DEF_ID IN NUMBER DEFAULT 0) AS

IN_EVENT_ID NUMBER;
IN_DB_CONNECTION_NAME VARCHAR2(100);

BEGIN

Select EVENT_ID INTO IN_EVENT_ID FROM RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION where KPI_DEF_ID = IN_KPI_DEF_ID;
Select DB.DB_LINK INTO IN_DB_CONNECTION_NAME FROM KPI_DEFINITION KD 
join DB_CONNECTION DB ON KD.DB_CONNECTION_ID = DB.DB_CONNECTION_ID
AND KD.KPI_DEF_ID = IN_KPI_DEF_ID;

Insert into TEMP_WF_WORKFLOW_EXTRACTION(ID,NAME,SUBSCRIPTION_ID)
Select DISTINCT(WF.ID),WF.NAME,WF.SUBSCRIPTION_ID
from WF_WORKFLOW@IN_DB_CONNECTION_NAME WF where
WF.STATUS_ID = 0 and WF.NAME IN ('CVB pack subscribe')

END EXT_10004_SELF_SIGWF_CVB;

Upvotes: 1

Views: 1858

Answers (1)

Frank Ockenfuss
Frank Ockenfuss

Reputation: 2043

something like

PROCEDURE "EXT_10004_SELF_SIGWF_CVB"(IN_KPI_DEF_ID IN NUMBER DEFAULT 0) AS

IN_EVENT_ID NUMBER;
IN_DB_CONNECTION_NAME VARCHAR2(100);
l_sql varchar2(500);
BEGIN

Select EVENT_ID INTO IN_EVENT_ID FROM    RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION where KPI_DEF_ID = IN_KPI_DEF_ID;
Select DB.DB_LINK INTO IN_DB_CONNECTION_NAME FROM KPI_DEFINITION KD 
join DB_CONNECTION DB ON KD.DB_CONNECTION_ID = DB.DB_CONNECTION_ID
AND KD.KPI_DEF_ID = IN_KPI_DEF_ID;

l_sql:= 'insert into...from WF_WORKFLOW@'||IN_DB_CONNECTION_NAME||' WF...';
/*    Insert into TEMP_WF_WORKFLOW_EXTRACTION(ID,NAME,SUBSCRIPTION_ID)
Select DISTINCT(WF.ID),WF.NAME,WF.SUBSCRIPTION_ID
from WF_WORKFLOW@IN_DB_CONNECTION_NAME WF where
WF.STATUS_ID = 0 and WF.NAME IN ('CVB pack subscribe') */


execute immediate l_sql;

END EXT_10004_SELF_SIGWF_CVB;

Upvotes: 1

Related Questions