Oracle DB link into variable

Question

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:

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;

Use DBMS_OUTPUT to print the dbname and verify the SQL. Either you do not have the privilege, or the dblink doesn't exist or the table itself doesn't exist. — Lalit Kumar B
– Lalit Kumar B, Commented Oct 1, 2015 at 9:44
Please check my edited question where i have provided db_connection table and as i said i already test my select query and it works fine. — Marcus
– Marcus, Commented Oct 1, 2015 at 9:47
you should better hide your username and password columns :-) To solve this, build the sql l_sql:='insert...' and execute it dynamicaly with execute immediatly l_sql — Frank Ockenfuss
– Frank Ockenfuss, Commented Oct 1, 2015 at 9:54
@Frank can you please explain in detail little bit. I dont understood. Can you write the query please? There is no ip address or SID provided with the table so no danger with the username and pwd :) — Marcus
– Marcus, Commented Oct 1, 2015 at 9:56

Frank Ockenfuss · Accepted Answer · 2015-10-01 10:00:00Z

1

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;

answered Oct 1, 2015 at 10:00

Frank Ockenfuss

2,04312 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Marcus Over a year ago

is there any other way to get the solution ? i really not aware of execute immediate statement.

Frank Ockenfuss Over a year ago

the execution of a dynamic sql is required, if a column name, table name or database link is determined at runtime

Collectives™ on Stack Overflow

Oracle DB link into variable

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related