Dynamic changing of dblink in Oracle sql

Based on the information provided the answer is:  Programmatically.

For a more detailed answer, we'll need more detailed information.  For example:  What are you wanting to accomplish by changing the link names?  Is this a script to connect to multiple databases, is it some other program, what???

I am trying to retrieve long operations in that dB instance

Can you let me know how to do it programmatically?

Thank you.

>>I am trying to retrieve long operations in that dB instance

Sorry but I do not know what this means.

By "long operations" do you mean the records that are displayed in GV$SESSION_LONGOPS?

Yes Mark, Correct

Still not enough information:
How will the link change dynamically?
Where does the list of links you want to use come from?
How are you executing the SQL?
What tool or tools are you using?

Hi slightwv,

I am working in Oracle And the list of hosts and server names comes from a drop-down list . When a user selects database name from a drop down list , the associated host names come up in the second drop-down list. And these values are passed to an sQL statement based on the where conditions and the dblink is a combination of database name and host name , along with ‘@‘ concatenated after gv$longops sys table. and when the user clicks on the button submit will bring up the long operations.

Hope I have answered your questions clearly, let me know if you want any more clarity. Really appreciate your time and support.

Thanks,
Rads

>>I am working in Oracle

To use one of their common statements:  Oracle is the largest software company in the world.

Oracle what?

Is this some app you've developed or purchased or ???

App is developed using Oracle apex

Sorry, I don't know APEX.

Seems like you can use dynamic SQL within a report. Adding a remote database connection should be pretty similar.

See if this gets you started down the right PATH:
https://www.oracle.com/technetwork/developer-tools/apex/dynamic-report-092026.html

Apex is a free built in tool of oracle which comes free when you get Oracle. All Apex code is only sql and plsql .

Will check the one you have sent .

Rads

I know what APEX is.  I don't know how to program with it.

This is not what I want , sorry. A simple Sql with dbname and hostname concatenated at the the end of a SQL statement

Select * from gv$sessionlongops||’@‘||replace(:p1_dbname,’-‘,null)||:p1_host
Where rownum<10;

It throws error missing right parenthesis.
But here the concatenation is not working , sorry if I have confused you.

Thank you,
rads

Or dblink name expected error

You cannot execute a native sql statement that way.  You need to build the string and execute the built string.

Oh ok! So can you send me some sample code for that.

Thank you,
R

No because I don't have APEX or know how to program in it.

I posted the link I did because it builds a query as a string using selected values from a drop down.  It looked exactly what you were trying to do.  Instead of dynamically selecting a category like in that example, you dynamically change the select to add the dblink information.  It should be the same.

I did the same but it throws error as “In a procedure RETURN statement cannot contain an expression

Several of us here can do PL/SQL but how that PL/SQL interacts with APEX is what I don't understand.

You'll need to wait until an APEX Expert comes to this question.  I don't think there are many Experts here that have personally used APEX so you might just get more links from searches.

There seem to be many examples of dynamic SQL using APEX.  I would start there.

You can call an PL/SQL block from APEX, so I suggest you wrap your concatenated SQL statement with an "EXECUTE IMMEDIATE" or make use of DBMS_SQL. This way, it should work ;-)

Hi Alex,

I wrote the code (please see attached) on the click of a button and as well tried with pl/sql block in apex, but it is not working or not showing any errors too.

Thank you.

The attachment is missing.

the dblink can not be a parameter
it has to be part of the sql string

I agree, you'll probably need to concatenate the variable into the string

Something like this:
q:=q||' from gv@session_longops@ ' || your_variables;

Typo, it should read: q:=q||' from gv$session_longops@ ' || your_variables;

Alex,

Please see attached ss. But data is not showing up on the screen , only the select statement.

Thanks,
R

htp.prn doesn't execute the SQL! it just renders the string parameter as HTML to screen ;-)
you need to use either "execute immediate" or "dbms_sql" in order to EXECUTE that statement

>>But data is not showing up on the screen , only the select statement.

Remember I don't know how to program with APEX.

You will need to use that select in the same way you execute a query and display the results.

If you execute 'select sysdate from dual' and display the results, same here but with the SQL as a string.

The link I posted uses a report.  Are you using reports similar to the link I posted?  If not, what are you doing to execute the query?

Alex,

Can you give me some examples (dbms_sql and execute immediate) , on how to implement it in this scenario.

Thanks much,
R

Alex,

getting the error below, can you let me know what is wrong in this code -

Technical Info (only visible for developers)
is_internal_error: true
apex_error_code: APEX.REGION.UNHANDLED_ERROR
ora_sqlcode: -923
ora_sqlerrm: ORA-00923: FROM keyword not found where expected
component.type: APEX_APPLICATION_PAGES
component.id: 801000000025
component.name: LRO
error_backtrace:
ORA-06512: at line 21
ORA-06512: at "SYS.DBMS_SQL", line 1707
ORA-06512: at "APEX_180100.WWV_FLOW_DYNAMIC_EXEC", line 2360
ORA-06512: at "APEX_180100.WWV_FLOW_DISP_PAGE_PLUGS", line 999


declare
  q varchar2(4000);
  x varchar2(4000);  
  sql_stmt  VARCHAR2(2000);
begin
  q:='   select lo.inst_id, ';
  q:=q||'       lo.sid,  ';
  q:=q||'       lo.username, ';
  q:=q||'       lo.opname, ';
  q:=q||'       lo.elapsed_seconds ,';
  q:=q||'       lo.time_remaining, ';
  q:=q||'       lo.sofar,';
  q:=q||'       lo.totalwork,';
  q:=q||'       trunc((lo.sofar/lo.totalwork)*100) pct,';
  q:=q||'       sql_plan_operation';
  q:=q||'from  gv$session_longops@'||REPLACE(:P25_DATABASE,'-',null)||'_'||:P25_HOST ;
  sql_stmt:=q;
 --htp.p(x);
  EXECUTE IMMEDIATE sql_stmt;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
     Raise_application_error (-20001,'No Records for this instance');
END;

Thanks,
R

You need to change a line:

  q:=q||' from  gv$session_longops@'||REPLACE(:P25_DATABASE,'-',null)||'_'||:P25_HOST || ' lo';

Select allOpen in new window

missing space and missing alias...

Afterall, you need to create a region (classic report) with its source as "PL/SQL Function Body returning SQL Query":

The 2 page items in the query should contain a value, otherwise you'll receive an error...

declare
  q varchar2(4000);
begin
  q:='   select lo.inst_id, ';
  q:=q||'       lo.sid,  ';
  q:=q||'       lo.username, ';
  q:=q||'       lo.opname, ';
  q:=q||'       lo.elapsed_seconds ,';
  q:=q||'       lo.time_remaining, ';
  q:=q||'       lo.sofar,';
  q:=q||'       lo.totalwork,';
  q:=q||'       trunc((lo.sofar/lo.totalwork)*100) pct,';
  q:=q||'       sql_plan_operation';
  q:=q||' from  gv$session_longops@'||REPLACE(:P25_DATABASE,'-',null)||'_'||:P25_HOST || ' lo';
  return(q);
END;

Select allOpen in new window

Alex,

As you mentioned earlier that I should use DBMS_SQL or execute immediate, then there is no need for that then?

This is throwing error after I made changes as you said. Can I give a return statement here ?

Thanks,
R

It throws error as @ should be given in double quotes and which I tried but still throws error as invalid character

>>Can I give a return statement here ?

That is what is shown in the link I provided.

Hi Slightwv,

I implemented it but on second thought was asking about it.

Thanks,
R

I'm not sure what you mean by "implemented".  Is it working or are you still getting the error?

If you are getting an error, at what step is it being generated?  If at execution time, put back the htp.prn statement and see if you can execute the generated SQL from the SQL worksheet, SQL Developer or sqlplus?

If the error is generated when you are trying to build the string, post the code but I'm not sure I can help since I cannot test with your parameters.

Alex / slightwv,

It was generating the SQL, till 1 day ago which showed up the entire SQL and since I added the code under the pl/sql function body returning sql query, it is throwing errors as below -

ORA-20999: Parsing returned query results in "ORA-20999: Failed to parse SQL query! <p>ORA-06550: line 4, column 6: ORA-00911: invalid character</p>".

Post the code you have and if you can, put back the htp.prn and post the generated SQL.

declare
  q varchar2(4000);
begin
  q:='   select lo.inst_id, ';
  q:=q||'       lo.sid,  ';
  q:=q||'       lo.username, ';
  q:=q||'       lo.opname, ';
  q:=q||'       lo.elapsed_seconds ,';
  q:=q||'       lo.time_remaining, ';
  q:=q||'       lo.sofar,';
  q:=q||'       lo.totalwork,';
  q:=q||'       trunc((lo.sofar/lo.totalwork)*100) pct,';
  q:=q||'       sql_plan_operation';
  q:=q||'       from  gv$session_longops@'||REPLACE(:P25_DATABASE,'-',null)||'_'||:P25_HOST || '   lo';
htp.prn(q)
  return(q);
END;

select lo.inst_id, lo.sid, lo.username, lo.opname, lo.elapsed_seconds , lo.time_remaining, lo.sofar, lo.totalwork,
trunc((lo.sofar/lo.totalwork)*100) pct, sql_plan_operation from gv$session_longops@test1_test2 lo;

declare
  q varchar2(4000);
begin
  q:='   select lo.inst_id, ';
  q:=q||'       lo.sid,  ';
  q:=q||'       lo.username, ';
  q:=q||'       lo.opname, ';
  q:=q||'       lo.elapsed_seconds ,';
  q:=q||'       lo.time_remaining, ';
  q:=q||'       lo.sofar,';
  q:=q||'       lo.totalwork,';
  q:=q||'       trunc((lo.sofar/lo.totalwork)*100) pct,';
  q:=q||'       sql_plan_operation';
  q:=q||'       from  gv$session_longops@'||REPLACE(:P25_DATABASE,'-',null)||'_'||:P25_HOST || '   lo';
htp.prn(q);
  return(q);
END;

Query looks good.  Seems to be something in APEX.  Leaves me out.

Alex,

Now when I changed the code directly to pl/sql dynamic content in place of classic report - it throws error as below -
In a procedure, RETURN statement cannot contain an expression ORA-06550: line 18, column 3: PL/SQL: Statement ignored

Now it is not even generating the sql

Thanks,
R

You haven't posted that error message before.

It seems you can tell APEX what type of code it is executing.

https://stackoverflow.com/questions/18369804/unable-to-return-query-output-in-apex-pl-sql-expression

I keep going back to the link I originally posted.  In there is mentions "Defining a report region based on a PL/SQL function returning a SQL query"

Slightwv,

Let me rephrase as I am trying to make it work using multiple approaches but it throws errors in both ways -

1.  Approach 1-
Create a report region with region source as PL/SQL function Body returning SQL Query - this is the code below

declare
  q varchar2(4000);
 begin
  q:='     select lo.inst_id, ';
  q:=q||'       lo.sid,  ';
  q:=q||'       lo.username, ';
  q:=q||'       lo.opname, ';
  q:=q||'       lo.elapsed_seconds ,';
  q:=q||'       lo.time_remaining, ';
  q:=q||'       lo.sofar,';
  q:=q||'       lo.totalwork,';
  q:=q||'       trunc((lo.sofar/lo.totalwork)*100) pct,';
  q:=q||'       sql_plan_operation';
  q:=q||'  from  gv$session_longops@'||REPLACE(:P25_DATABASE,'-',null)||'_'||:P25_HOST || '  lo';
 htp.prn(q);
 return q;                
  --DBMS_OUTPUT.PUT_LINE (q);
 END;  -------------  This throws error as ORA-20999: Parsing returned query results in "ORA-20999: Failed to parse SQL query! <p>ORA-06550: line 4, column 6: ORA-00911: invalid character</p>".

 2. Approach 2 -
Create a PL/SQL dynamic content region - this is the code below -

declare
  q varchar2(4000);
 begin
  q:='     select lo.inst_id, ';
  q:=q||'       lo.sid,  ';
  q:=q||'       lo.username, ';
  q:=q||'       lo.opname, ';
  q:=q||'       lo.elapsed_seconds ,';
  q:=q||'       lo.time_remaining, ';
  q:=q||'       lo.sofar,';
  q:=q||'       lo.totalwork,';
  q:=q||'       trunc((lo.sofar/lo.totalwork)*100) pct,';
  q:=q||'       sql_plan_operation';
  q:=q||'  from  gv$session_longops@'||REPLACE(:P25_DATABASE,'-',null)||'_'||:P25_HOST || '  lo';
 htp.prn(q);
 return q;                
  --DBMS_OUTPUT.PUT_LINE (q);
 END;

Approach 2, throws error as  ora_sqlerrm: ORA-06550: line 16, column 2: PLS-00372: In a procedure, RETURN statement cannot contain an expression ORA-06550: line 16, column 2: PL/SQL: Statement ignored

As this error came up, I commented out the the "Return statement " then it does not throw error and shows the SQL statement generated.

But it does not execute the SQL Statement . Hope this helps.

Thanks,
R

So for the second approach - I tried to add the execute immediate statement , it throws error as "Connection description to remote database not found", inspite of giving default values as Alex mentioned

Again, not knowing APEX, I wouldn't expect a straight "execute immediate" to work for a select.  You don't do that in normal PL/SQL.

I would expect the ORA-06550 for approach 2.

For approach1 and the "ORA-00911: invalid character", can you run the query without trying to append the dblink?  Sure, it will run against the local database but it should provide more data to analyze.

Slightwv,

I removed the string from the '@' and added the clause "where rownum < 10" and it execute the statement and sowed the first 10 records.

so basically the the dblinks have issues , then in that case it is still generating the SQL statement, right ?

R

Looks like it is related specifically to dblinks in the select.  I'll see if I can get a little help.

If it is the '@', I suppose you can try a trick depending on when it is failing on parsing.

...
 q:=q||'  from  gv$session_longops'||chr(64) || REPLACE(:P25_DATABASE,'-',null)||'_'||:P25_HOST || '  lo';
...

Throwing same error "Invalid Character"

Something to consider: create a local synonym for each remote table/db you want to connect to, then use the local synonym in Apex instead of the actual "@dblink" syntax.

Mark
 There are a lot of hosts with a lot of instances , it is very difficult to create so many synonyms.

Hi sdstuber

Looks interesting will surely try this and update you. I need to send 2 parameters databasename and hostname.

Thank you,
R

sdstuber

Is the  p_link_name the dblink that should be passed, correct?

Again, as long as both page item values are NOT NULL, the following code WILL work! I tested it on our DEV machine within a test APEX application!

PL/SQL function body returning a SQL...

declare
  q varchar2(4000);
begin
  q:='   select lo.inst_id, ';
  q:=q||'       lo.sid,  ';
  q:=q||'       lo.username, ';
  q:=q||'       lo.opname, ';
  q:=q||'       lo.elapsed_seconds ,';
  q:=q||'       lo.time_remaining, ';
  q:=q||'       lo.sofar,';
  q:=q||'       lo.totalwork,';
  q:=q||'       trunc((lo.sofar/lo.totalwork)*100) pct,';
  q:=q||'       sql_plan_operation';
  q:=q||' from  gv$session_longops@'||REPLACE(:P25_DATABASE,'-',null)||'_'||:P25_HOST || ' lo';
  return(q);
END;

Select allOpen in new window

If you need just one "master" monitor page to get the longops from all the other databases, I'd suggest something else:
Create a master-detail page, whereas the master part would be based on "select db_link from dba_db_links".
The detail part would be a classic report based on the snippet from above and you just change the lower part to use the "db_link" from the master SQL...

Alex

Will try that as well. I passed default values but still the errors were coming up.

Thanks,
R

You still need string concatenation for the variable.

From:
 FROM gv$session_longops@v_dblink';          

to:
 FROM gv$session_longops@' || v_dblink;

ok sdstuber , will try that and let you know.

Thank you,
R

Alex,

I tried creating a master-detail form but when creating the master for dba_db_links does not populate, so I tried creating a DB view, and it throws error as "Insufficient Privileges"

Thanks,
R

sdstuber,

I have created the function successfully. But when I try to run the SQL statement in SQL Developer, it does not return any data -


Thanks,
R

sdstuber

When I have implemented the SQL in APEX - throws this error

ORA-20999: Failed to parse SQL query! <p>ORA-06550: line 12, column 57: ORA-00904: "READ_REMOTE_LONGOPS": invalid identifier</p>

SELECT inst_id,
       sid,
       username,
       opname,
       elapsed_seconds,
       time_remaining,
       sofar,
       totalwork,
       pct,
       sql_plan_operation
  FROM TABLE(read_remote_longops(:P25_DATABASE,:P25_HOSTNAME));

The parsing schema of your application is not be the owner of the function.

Either create the function under the parsing schema, or prefix the function call with the schema owner and grant the parsing schema execute privilege on the function

>>> . But when I try to run the SQL statement in SQL Developer, it does not return any data


You need to modify the security check to do something that makes sense.

It does not throw any errors now and I have created the function with the schema owner and the grants have been given for the parsing schema to execute the function but does not return any data either in SQL Developer or APEX.

You're going to have to do more on your end to diagnose and work through the problem...

Did you change the security check appropriately?

Are you passing in valid parameters?  If so, how have you confirmed this?

Are you getting a log message for invalid link name?

Are there rows to be retrieved from the remote system?

not a very good approach ...

the number of db links you can define in a database is limited by a parameter
so, if the database link o the remote database is not defined, you'll get an error or have to augment that parameter

when i want info about a few 100 databases, i centralize the info, with scripts which periodically run on each host

my scripts produce a delete and multiple insert statements

declare 
  this_hostname varchar2(30) := 'server name';
  delete from local_longops where host_name = this_hostname;
end;
insert into local_longops (host_name, instance_name, sid, username, opname, ...) values ('server name', 'instance_name', 'sid', 'username', ...);
insert into local_longops (host_name, instance_name, sid, username, opname, ...) values ('server name', 'instance_nameB', 'sidB', 'username', ...);
commit;

Select allOpen in new window

but you need admin access to every host and some rather nifty programming skills

the end result is everything in your local database

if long ops is your way to look for bad querries ... you might need a different approach

not a very good approach ...

That's rather a matter of taste / point of view in this case.

the number of db links you can define in a database is limited by a parameter

Says who?! Please give reference and /or source for this allegation!

What's limited is the number of open database links in a session, which can lead to problems, like ORA-02020.
But even here there are some workaround, e.g. shown here http://dbtricks.com/?p=198

i was referring to open_links
yup, you're right, max open db links in 1 session

my bad, need to get a coffee before commenting

Hi Everyone ,

Sdstuber,

You're going to have to do more on your end to diagnose and work through the problem... ---- Yeah will do

Did you change the security check appropriately?  -- Not sure on this as what exactly you are trying to say

Are you passing in valid parameters?  If so, how have you confirmed this? --- will test today with more parameters

Are you getting a log message for invalid link name? --- Yes , it is showing the log message as invalid link name

Are there rows to be retrieved from the remote system? --- Yes, as I checked in directly with a direct dblink and it retrieves records, but not working with the function - SQL

For all of the above I need to test more and see where exactly its failing. Will update asap.

Thank you all for the support and time you are giving , really appreciate and I am overwhelmed with this response.

R

Hi Geert

I will not be able to get the admin access to all the hosts they have here , and it is time consuming to get it approved.

That's a good idea though.

Thank you.
R

sdstuber,

This is how I am testing - in SQL Developer-

1. Checking if the records exist by directly passing the dblink , and if records exist then
2. Executing the SQL statement using the function , passing the same parameters , used in the direct db link

And I changed the function code to add some dbms_output lines to see where it is failing, but it does not show anything  in the dbms_output

Is there any other way I can check on this .

Thanks,
R

Did you change this?

 IF UPPER(v_dblink) IN ('MY_LINK_NAME1', 'MY_LINK_NAME2', 'MY_LINK_NAME3')

Select allOpen in new window

instead of 'MY_LINK_NAME1'  put in one of your valid database link names that points to a remote db that has data to be returned.
Then pass in parameters that construct that db link name

The point of that IF is to check if you have provided valid parameters.  If you are not checking your parameters against real values, then real values always fail thus causing the query to return nothing.


Create a dummy db link, something that would be ok to post online and you'll drop later on.
Then modify the function to validate with that db link,  then post your modified code as well as the query and parameters you are using to test.

sdstuber,

Will change and try as mentioned.

Thanks,
R

sdstuber,


its getting the data in sql developer , but not in oracle apex.still checking.

Thanks,
R

In APEX it might be because the item values haven't been submitted.

Check your logs for invalid input parameters as an easy way to check if they are being passed to the function correctly.

ok will do.

sdstuber,

---In APEX it might be because the item values haven't been submitted.

I have created a page with list of values for database  name and host name that the user selects and these values are sent as parameters to the sql which is calling the function and the user clicks on a button to display in a report format.
 
Hope I have answered your question.

Thanks,
R

>>> I have created a page with list of values for database  name and host name that the user selects and these values are sent as parameters to the sql which is calling the function


I figured that was your intent, but your previous comments indicate the function is working, except when you call it from APEX.
Assuming you're testing with the same parameter values, the only reason that would happen is if the parameters you "think" you are passing to the function aren't really making it to the function.

That's why you should check the logs from the function.  Assuming you modified the function as the comments indicated then you should have logs of the values you passed in and can determine what is being passed (possibly nothing.)

Depending on how your APEX report is populated it's possible the item values aren't submitted until AFTER the report has already rendered - which would produce the symptoms you're seeing.

These page items (LOV in your case) have to be submitted (thus have to be in the page buffer) to make it to the function! APEX has a nice debug feature; please enable this for this specific page and you'll see that these page items do NOT have a value submitted (I guess)

What APEX version do you use?
If it's a quite "generic" page, you might export it and I could import for testing and fixing ;-)

With either a Classic Report or an Interactive Report, in your Source section, just under the SQL Query you should see a field

"Page Items to Submit"

Be sure to include both of your list-of-value Items in that field.  Then they will be submitted prior to the report's generation.
If it's null or otherwise missing your 2 input items, then the report function will not be passed the values you expect and thus likely return nothing in the query when executed.

Good Morning ,

Hi sdstuber,

I did select  the "Page Items to Submit" but it is the same.

Will try each of the options you  have mentioned earlier ,and give you update. Sorry for the later reply as I have been given to work on another task. , the delay in the testing and replying to your messages. Extremely sorry !!

Thanks,
R

Hi Alex,

What APEX version do you use? ---- 18.1
If it's a quite "generic" page, you might export it and I could import for testing and fixing ;-) - Sorry Alex, I wish I could , but it is restricted.

Thanks,
R

Alex,

These page items (LOV in your case) have to be submitted (thus have to be in the page buffer) to make it to the function! APEX has a nice debug feature; please enable this for this specific page and you'll see that these page items do NOT have a value submitted (I guess)

I don't see any errors in the debug - see the attached screenshot . Not sure if you can see but still attaching the screenshot.

thanks,
R
Capture.PNG

and the user clicks on a button to display in a report format.

Are you transferring to another page?! If so, you'd have to pass the LOV values as parameters...

I don't see any errors in the debug - see the attached screenshot . Not sure if you can see but still attaching the screenshot.

Sure, there shouldn't be any errors, but I'd like to see, which values get assigned to those 2 page items ;-)
Could you please provide the full image or the portion where you can see this?

sdstuber,

I figured that was your intent, but your previous comments indicate the function is working, except when you call it from APEX.
Assuming you're testing with the same parameter values, the only reason that would happen is if the parameters you "think" you are passing to the function aren't really making it to the function.

That's why you should check the logs from the function.  Assuming you modified the function as the comments indicated then you should have logs of the values you passed in and can determine what is being passed (possibly nothing.)

Depending on how your APEX report is populated it's possible the item values aren't submitted until AFTER the report has already rendered - which would produce the symptoms you're seeing.

Yes, I did modify the function as you suggested and is passed correctly on the sql developer side and data is coming as expected as I even checked the count using the direct dblink and as well with the function generated data. And they both match.

Thanks,R

Alex,

Are you transferring to another page?! If so, you'd have to pass the LOV values as parameters... -- No I am not , just running the sql based on the parameters selected on the same page.

r

Sure, there shouldn't be any errors, but I'd like to see, which values get assigned to those 2 page items ;-)
Could you please provide the full image or the portion where you can see this?

It is showing the values under debug-session-Page Items - the values selected from the LOVs for Database Name and Host

R

I've never read an APEX debug log but I do see where the select appears to return 15 rows.  Is that how many rows you expect to see on the report?

Slightwv,

That is the default setting for any report we create in APEX.

R

I was suggesting the query actually ran and returned rows.  Not that 15 was a default value.

>>>> Yes, I did modify the function as you suggested and is passed correctly on the sql developer side and data is coming as expected as I even checked the count using the direct dblink and as well with the function generated data. And they both match.


I was talking about APEX usage, you've already established the function works correctly.
So, if you're having problems with the same function,  with the same inputs, connecting to the same db links to the same remote db - then the problem is one of those "same" things is not really true.

Either it's not the same function  (possibly because function created in multiple schemas and/or databases?)
Or
it's not the same inputs (this includes failure of sending the inputs to the function)
Or
the inputs are generating the same db link names but different links (possibly private links or other public links of the same name?)
Or
the links are pointing to different databases (this should only happen if there are multiple links of the same name but different targets)

So, when I suggested you "check the logs"  I meant, AFTER you tried it in APEX, check the logs from the function to see what APEX sent your function.

You haven't posted your modified code, or application page, or the output logs from the function so we can't see what you're doing to make the determination for you.

Along with the "dummy link" I suggested above,  create a new dummy page with LOVs for the dummy link and a report using the function on the two dummy LOVs

Shouldn't take more than a few minutes to create.

This allows you to post all of the code being used so we can see what you're doing and where it's going wrong.

Please don't post the code as screen images,  send the code as text.

sdstuber,

For now they have given me the privilege to use 2 dblinks only  to test , which I will send the details soon .

R

sdstuber,

How do I see the log on APEX side ,as I was thinking wrong about the Debug option.

R

Assuming you are using an instrumentation library like logger, log4plsql, ilo, etc - then your logs will be written to the corresponding table(s) of your intrumentation set.

You would see them by querying the table.  I don't know what you're using, so I can't tell you what table that would be.

If you are not using such a system, then I suggest you start.
At bare minimum, create a table with a timestamp and varchar2 column.  Then write to the table inserting systimestamp and your logging message.  There are, of course, more sophisticated logging/debugging mechanisms but that will at least get you started.

Also, you can write debug messages directly into the apex logs with the apex_debug package.

If you use that, then you can query the APEX_DEBUG_MESSAGES  view.
That's probably a good place to start.

ok sdstuber , will start it off with that.

But I also tried executing the function SQL in the SQL Commands window of Oracle APEX and it returns the data there but not on the page.

R

>>>> But I also tried executing the function SQL in the SQL Commands window of Oracle APEX and it returns the data there but not on the page.


Yes, that's because the function works.

The problem isn't APEX.  You can test the function in sqlplus, sqldeveloper, toad, apex, sqlcl, it doesn't matter.

Your page report is failing because of the way you're using the function within the report of your page.


I'm fairly confident the problem is your page isn't really passing the input values from your LOVs to the query.

Once you modify the function to log its inputs (for testing purposes you might want to log them at the beginning of the function, regardless of success or failure) and then when you look at those logs you'll probably see the same thing I'm guessing at.

Hopefully this exercise hammers in the importance of good instrumentation, logging and debugging.
If you build that in as a habit, problems like this should take a only few seconds to identify rather than days of back and forth.

sure will do. Working on that. Thanks much !

R

sdstuber,

code for error log -


changed the names for the objects, all these objects are successfully compiled.

Will call this package, procedure in the exception part

Let me know your comments /suggestions please

R

rr_lngops  - this function won't compile as you posted it, it's missing a semi-colon after the v_results declaration.


>>> IF UPPER(v_dblink) IN ('dummy_link99', 'dummy_link98', 'dummy_link97','dummy_link96','dummy_link95')

Think about what this condition is checking-
If you force the v_dblink value to upper case, it will never match any of those values because they are all lower case.

Either use LOWER, or put the link names in upper case,  or remove UPPER completely and make sure the inputs are typed exactly as you have entered them in your in-list.

sdstuber ,

Will change it to lower and try to see if it works.

And as well implement the debug  part.

Thanks,
R

sdstuber,

I do see the values , when debug is on and shows the values against each of the values selected from the drop-down lists.

R

Are the values you are seeing the exact same values you tested the function with in sql developer?

As an additional check, add an exit message just before the RETURN clause,  then run the page again with debug and check the debug messages.

    apex_debug.info('rr_lngops exiting with ' || to_char(v_results.count,'fm99999') || ' rows returned');
    RETURN v_results;
END;

Select allOpen in new window

sdstuber,

Yes, the values are the same , tested in sql developer .

Will do add and test that as well.

Thanks much ,
R

sdstuber ,

this is the debug -accept refresh , I have masked the actual values passed

...Parse JSON...
......Validate item page affinity
......Read Page Item values and validate protected items
........Name=P25_DBLINK (id=xxx_xxx), value=xxx_xxx, checksum=
.........Name=P25_APPLICATION (id=xxx_xxx), value=xxx_xxx, checksum=
.........Name=P25_ENV (id=xxx_xxx), value=xxx_xxx, checksum=
.........Name=P25_HOST (id=xxx_xxx), value=xxx_xxx, checksum=
.........Name=P25_DATABASE (id=xxx_xxx), value=xxx_xxx, checksum=
.........Name=P25_PORT (id=xxx_xxx), value=xxx_xxx, checksum=
......Check existance of all Protected Page Items=
...Check authorization security schemes
Session State: Save form items and p_arg_values
Session State: P25_DBLINK=>xxx_xxx
... do not save: same value / password / no session
Session State: P25_SEARCH=>
... do not save: same value / password / no session
Session State: P25_APPLICATION=>xxx_xxx
... do not save: same value / password / no session
Session State: P25_ENV=>xxx_xxx
... do not save: same value / password / no session
Session State: P25_HOST=>xxx_xxx
... do not save: same value / password / no session
Session State: P25_DATABASE=>xxx_xxx
... do not save: same value / password / no session
Session State: P25_PORT=>xxx_xxx
... do not save: same value / password / no session
Processes - point: ON_SUBMIT_BEFORE_COMPUTATION
...Process "Search_Load" - Type: NATIVE_PLSQL...Proc
SQL Statement prepared: line count=1, first index=1, last index=1, characters=1054, found binds=6
Branches - point: BEFORE_COMPUTATION
Process point: AFTER_SUBMIT
Tabs: Perform Branching for Tab Requests
Branches - point: BEFORE_VALIDATION
Validations:
Perform basic and predefined validations:
Perform custom validations:
Branches - point: BEFORE_PROCESSING
Processes - point: AFTER_SUBMIT
Branches - point: AFTER_PROCESSING
...Branch "ClearAll" - Type: REDIRECT_URL      
......Skip because "When Button Pressed" doesn't match with REQUEST......
...No branch specified, redirect to current page
......Branch to URL (Redirect) f?p=xxx_xxx#SUCCESS_MSG#
Stop APEX Engine detected
Final commit

and when i clicked on the identifier and viewed ---

rr_lngops exiting with 0 rows returned

Thanks,
R

where are the rr_lngops debug lines?

Not just the last line, but all of them?

sdstuber,

That is only the part that comes up, even after adding these lines -
apex_debug.info('rr_lngops exiting with ' || to_char(v_results.count,'fm99999') || ' rows returned');

Not sure what I am doing wrong.

R

did you add the

apex_debug.ENTER(
        'RR_LNGOPS',
        'p_dbname',
        p_dbname,
        'p_hostname',
        p_hostname
    );

line as shown above in  https:#a42777397

Yes , I did

see the function code -
CREATE OR REPLACE FUNCTION rr_lngops(p_dbname IN VARCHAR2, p_hostname IN VARCHAR2)
    RETURN lng_ops_tab
IS
    v_results                 lng_ops_tab;
    v_dblink                  VARCHAR2(2000);
    c_sql_template   CONSTANT VARCHAR2(4000) := '
          SELECT long_ops_obj(
                         inst_id,
                         sid,
                         username,
                         opname,
                         elapsed_seconds,
                         time_remaining,
                         sofar,
                         totalwork,
                         TRUNC((sofar / totalwork) * 100),
                         sql_plan_operation
                 ) obj
            FROM gv$session_longops@~insert_db_link_here~';
BEGIN
    apex_debug.ENTER(
        'RR_LNGOPS',
        'p_dbname',
        p_dbname,
        'p_hostname',
        p_hostname
    );
    ---  Validate the p_link_name value before continuing!!!!
    v_dblink := REPLACE(p_dbname, '-', NULL) || '_' || REPLACE(p_hostname, '-', NULL); --- added replace function to the hostname
    IF LOWER(v_dblink) IN ('dummy_link99',
                           'dummy_link98',
                           'dummy_link97',
                           'dummy_link96',
                           'dummy_link95')
    THEN
        EXECUTE IMMEDIATE REPLACE(c_sql_template, '~insert_db_link_here~', v_dblink)
            BULK COLLECT INTO v_results;
    ELSE
        DBMS_OUTPUT.put_line('Invalid Link Name used: ' || v_dblink);
        v_results := lng_ops_tab();
    END IF;

    RETURN v_results;
END;

It should be obvious that you're not using the code you just posted.


The log message you showed above is for an exit message which isn't there.  So whatever code you put that into isn't the same code you're executing within your page - or at least not the code you just posted.

Do you have a test schema and a deployment schema?  so maybe you're editing in one place but running something else?


Make sure BOTH messages are in the function then run your page again with debug turned on.
Since the "enter" message runs first,  you can't get to the "exit" message without having already logged the first message.
So, keep fixing the code the page runs and retesting until you get BOTH messages in your apex logs.

Once you do, THEN post the log messages.

Ok will post .

Thank you
R

sdstuber,

this is the function code , copying as it is , except changed the actual values for the parameters -



Thanks,
R

I don't know why you're hiding your parameters, it just makes this process harder because you have to go through obfuscation steps.

If you create dummy links to test with then you should be able to expose "dbname = DUMMY" and hostname = "LINK99"

But regardless, you're not picking up the DEBUG info from the function enter.

So 3 options -

1 - either you're not running the code you posted
or
2 - you're not running with your debug level high enough.
or
3 - you're removing the useful log message from the debug output

I'm hoping the problem is #2, so, making that assumption, you have 2 options.

Debug with "LEVEL5" or higher instead of the default "YES"  (this is the easier approach)
or
Change the APEX_DEBUG.ENTER procedure to an APEX_DEBUG.INFO procedure  (this requires you to construct an informative debug message)

Pick one of the above, implement, and try again.

sdstuber,

I am changing the dummy links here to post the code, but passing the actual values only. if I pass dummy links in the actual function, it would throw error as remote db description not found.

Will send the actual debug code to your email , if you can share it.

Thanks,
R

my email is in my profile, but I have no NDA with you or your employer.

But the point of my comment was that keeping stuff hidden isn't necessary in order to conduct valid tests.

A db link name is just a name, it doesn't "mean" anything.

So,  create a dblink named "dummy_link99" that points to one of your real databases.

Then you pass in "dummy" for your database name and "link99" for your host name,  It will validate just fine using the code you have posted above.

By doing this you can always post your inputs, screen shots, code, logs, etc without needing to worry about hiding any private information.

The "dummy" link and values will all be just as valid as whatever your real db and host names are.

Once you have worked out your page and function call issues with the dummy, you can continue testing the same way simply changing the dummy link to point to each real database you want to use.

Every time you will be able to post everything you do (except the link creation of course) without the need for obfuscation steps.


But - all of that is moot, because it shouldn't be necessary anyway.  If you can set yourself up a test case that captures all of the pertinent information, it should be easy to see where the problem lies.  My guess is still that your page is rendering the report without having submitted the input parameters to the function.

will delete once you look at it.

You chose the harder path, but you didn't follow the note about it.

"this requires you to construct an informative debug message)"

You can't just switch "info" for "enter" and hope it will work.
 In order for an info debug message to be helpful, you need to include helpful information in it.
Swapping procedure names doesn't do that for you.

Put it back to enter and set your debug level to "LEVEL5"
If you don't know how to do that,  look at your URL where it has "YES" in the debug portion of the url parameters.
Change "YES" to "LEVEL5" or something higher like "LEVEL6", "LEVEL7", "LEVEL8", or "LEVEL9" - but LEVEL5 should be sufficient.

You might want to talk to your employer about finding an APEX developer and a PL/SQL developer to assist  with this project.
A good APEX developer should already be a good PL/SQL developer, but it doesn't always happen.

Please don't push private info and then hope to delete it.
Your information is public now.  Nobody here has an NDA with you or your employer.
Google, Bing, Yahoo, etc could have already indexed your secrets.

sdstuber,

As I mentioned earlier I am not so good at pl/sql, and I know about APEX but not depth of it. As I have been developing basic apps.

Thank you for your suggestions. Will try to check on changing the Level and try to see the debug code.

Thanks,
R

Even without the info I requested, your logs still show the problem is probably what I said, you aren't passing the parameters to the function when you render the report.

0.05188      0.00003      .........Name=P25_HOST (id=106004430106699788), value=*******, checksum=      4      0%
0.05191      0.00003      .........Name=P25_DATABASE (id=106004852937699787), value=*******, checksum=      4      0%

Notice how these values aren't part of the logs when you run the query?

sdstuber,

you are 100% correct, the value for hostname is not getting submitted, it shows as blank.

I tried with level5 and level 6.

Will check on that.

R

0.07415      0.00008      
read_remote_longops p_dbname=>*****,p_hostname=>
5      
0%
0.07423      0.00059      
read_remote_longops exiting with 0 rows returned

in the field labeled

"Page Items to Submit"

Do you have BOTH page items listed there or only the database LOV?

They should both be there, separated by a comma, with or without white space

P25_DATABASE, P25_HOST

Select allOpen in new window

If you want, you can clear out the field and then select them from the LOV popup button and it will format them for you

yes they both are there.

Can you export your page and post it, or does it contain private/proprietary information?

If the former, please do so.
If the latter, please do not.

If the page is private, then copy that page and remove everything except the report and the 2 lovs.
modify the LOV queries in the copy to be simple hard coded lists of your test values (use real values for your test)

Test that page.  If it doesn't work, post it here ( ***** out  the private values) - there shouldn't be anything proprietary left in the stripped down page, only the relevant objects which are generic or the query I posted above.
If the page does work, then go through the other items, dynamic actions, computations, and processes defined for your main page and compare to the test page.

sdstuber,

I have created an text item, which would populate the db name and host name based on the selection from the user in apex. And I have changed the function code to accept one value dblink as earlier you have mentioned. And now FINALLY it is working in SQL and as well as APEX.

Thank you so so so much for all the time and effort you took to resolve this. really appreciate and thank to each one of you.

Thanks to sdstuber, slightwv, Alex and Geert for your support.

R

Thank you all  so much ... sdstuber, Alex, slightwv, Geert and Mark for your time and support.

Hi sdstuber,

As I have hard coded and tested only with those values in the db function, and as there are many database links , is there anyway I can re-write the hardcoded db links , with the subquery?

Please advise, as when i changed from hardcoded to subquery it throws error as sub query not allowed.

Thanks,
R