Solved

Procedure with  a Select query

Posted on 2014-01-09
30
385 Views
Last Modified: 2014-02-24
Experts
i seek your advice in the below procedure.
the select query is built dynamically based on the input.
i'm having trouble in executing the select query that is built. please assist

thanks

  CREATE OR REPLACE PROCEDURE "COMPARE_PARAMETERS"(in_client_sid IN char)

 IS

  select_field  varchar2(4000);
  from_clause   varchar2(4000);
  where_clause  varchar2(4000);
  where_clause2 varchar2(4000);
  where_clause3 varchar2(4000);
  where_clause4 varchar2(4000);
  order_by      varchar2(4000);
  select_query  varchar2(4000);
  i             number;
  sid           databases.sid%Type;


  type t_databases_instance is table of databases.instance%type;
  type t_databases_sid is table of databases.sid%type;
  l_databases_instance t_databases_instance;
  l_databases_sid      t_databases_sid;
BEGIN
  DBMS_OUTPUT.put_line(in_client_sid);
  select instance, sid bulk collect
    into l_databases_instance, l_databases_sid
    from databases
   where client in (select instance
                      from clients
                     where upper(code) = upper(in_client_sid))
     and dataguard = 'N';
  select_field  := 'SELECT ';
  from_clause   := ' FROM ';
  where_clause  := ' WHERE ';
  where_clause2 := ' ';
  where_clause3 := ' ';
  where_clause4 := ' ';
  order_by      := ' ORDER BY dp1.parameter ;';
  if (l_databases_instance.count <= 1) then
    DBMS_OUTPUT.put_line('Atleast need 2 databases to compare parameters');
  else
    for i in l_databases_instance.first .. l_databases_instance.last loop
      DBMS_OUTPUT.put_line(l_databases_instance(i) || ', ' ||
                           l_databases_sid(i));
      select_field := select_field || ' d' || i || '.sid, dp' || i ||
                      '.parameter ';
      from_clause  := from_clause || 'databases d' || i ||
                      ',DB_PARAMETERS dp' || i;
    
      where_clause := where_clause || ' d' || i || '.instance=' ||
                      l_databases_instance(i) || ' AND d' || i ||
                      '.instance =dp' || i || '.db_instance';
      DBMS_OUTPUT.put_line(l_databases_instance.first);
    
      if (i != l_databases_instance.first) then
        where_clause2 := where_clause2 || 'dp' ||
                         l_databases_instance.first || '.parameter = ' || 'dp' || i ||
                         '.parameter ';
        where_clause3 := where_clause3 || 'dp' ||
                         l_databases_instance.first || '.pvalue != ' || 'dp' || i ||
                         '.pvalue ';
        where_clause4 := where_clause4 || 'and dp' ||
                         l_databases_instance.first ||
                         '.PARAMETER not in (''instance_name'',''log_archive_dest_1'',''spfile'',''utl_file_dir'',''db_name'',''db_domain'',''db_unique_name'') and dp' ||
                         l_databases_instance.first ||
                         '.parameter not like (''%dest'') and dp' ||
                         l_databases_instance.first ||
                         '.parameter not like (''dg_%'')';
      
      end if;
      if (i != l_databases_instance.count) then
        select_field  := select_field || ',';
        from_clause   := from_clause || ',';
        where_clause  := where_clause || ' and ';
        where_clause2 := where_clause2 || ' and ';
        where_clause3 := where_clause3 || ' and ';
      end if;
    
    end loop;
    select_query := select_field || from_clause || where_clause ||
                    where_clause2 || where_clause3 || where_clause4 ||
                    order_by;
    DBMS_OUTPUT.put_line(select_query);

  
  end if;
END;

Open in new window

0
Comment
Question by:eagle_ea
  • 11
  • 10
  • 9
30 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Assuming the SQL build is valid, what do you want to do with the results?

If you want a CURSOR try the following:

in the declare section:
mycur sys_refcursor;

then in the code:
open myCur for select_query;
0
 

Author Comment

by:eagle_ea
Comment Utility
it says,, invalid character at
open myCur for select_query;
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
It is likely the ending ';' in the string.
0
 

Author Comment

by:eagle_ea
Comment Utility
is it not required to have a ; at the line of each code?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
It depends on where the code is.

Try a simple example:
declare
	mycur sys_refcursor;
	mysql varchar2(100);
begin
	mysql := 'select sysdate from dual';
	open mycur for mysql;
	close mycur;
end;
/

Open in new window

0
 

Author Comment

by:eagle_ea
Comment Utility
did not help
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Then you must have a syntax issue in the SQL contained in the string.

Then you capture the output from the dbms_ouptut, did you run the generated select in something like sqlplus?
0
 

Author Comment

by:eagle_ea
Comment Utility
ok...there was an extra ; within the select_query that has been removed. error does not appear.

my question is, where does the results appear when i execute the proc in sql window?

i simply says,
PL/SQL procedure successfully completed
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>where does the results appear when i execute the proc in sql window?


That is the very first question I asked you:  What do you want to do with the results?
0
 

Author Comment

by:eagle_ea
Comment Utility
i want the result set to be display from the select query that was built
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Just displayed to the screen or displayed in some app?

I'm not sure why you create a procedure to generate SQL to just use DBMS_OUTPUT to dump the data to the screen.

But anyway you need a loop and fetch the values from the cursor.

Here's a simple example:
declare
	mycur sys_refcursor;
	mysql varchar2(100);
	myval1 varchar2(100);
	myval2 number;
begin
	mysql := 'select ''Hello'', 123 from dual';
	open mycur for mysql;
	fetch mycur into myval1, myval2;
	loop
		exit when mycur%NOTFOUND;
		dbms_output.put_line('I got: ' || myval1 || ',' || myval2);
		fetch mycur into myval1, myval2;
	end loop;
	close mycur;
end;
/

Open in new window

0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
Yourif your database instances are strings (CHAR/VARCHAR2) the resulting sql will be invalid, because  of the way you are constructing your query

These lines...

 || '.instance='
 || l_databases_instance(i)

will result in something like this...


WHERE d1.instance = MY_TEST_INSTANCE1
     AND d1.instance = dp1.db_instance
     AND d2.instance = MY_TEST_INSTANCE2


But you should have something like this...


WHERE d1.instance = 'MY_TEST_INSTANCE1'
     AND d1.instance = dp1.db_instance
     AND d2.instance = 'MY_TEST_INSTANCE2'

so, change your sql construction

 || '.instance='
 || '''' || l_databases_instance(i) || ''''
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
>>> i want the result set to be display from the select query that was built

then you need to actually execute the query and display the results.
currently you don't do anything except display the query


I suggest building your select to return a concatenated string.

that way you can loop through your query output and simply do

dbms_output.put_line(x.result_text);
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
>>> my question is, where does the results appear when i execute the proc in sql window?


what tool are you using?  

toad - click on dbms_output tab, click the red icon, to turn it green so it will capture the output.

sql*plus  - type "set serveroutput on"
0
 

Author Comment

by:eagle_ea
Comment Utility
set serveroutput on - only displays  DBMS_OUTPUT.put_line

what about the select output?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
>>> what about the select output?


You'd have to actually execute the select statement - as slightwv noted above -you never do that
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>what about the select output?

I posted that already in http:#a39768744
0
 

Author Comment

by:eagle_ea
Comment Utility
the number of columns are random , so setting the output to a variable is not teh option , we need
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>the number of columns are random

Then you need some other method.

You need to 'store' the results of the query somewhere before they can be displayed.  When you execute a query in something like sqlplus, it sort of does all this for you.  Since you are doing it manually, you need to account for the results manually.

Maybe generate XML from the query and return a single CLOB.
Possibly change things to generate a CSV one row at a time?
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
>>>> the number of columns are random ,

and that's why I suggested you create your select statement so that it returns a single value

so, instead of

SELECT d1.sid,
         dp1.parameter,
         d2.sid,
         dp2.parameter....

construct it like this...

SELECT d1.sid || '   '
         dp1.parameter || '   '
         d2.sid || '   '
         dp2.parameter .....


then execute your query, loop through and print each row which will have exactly one value

You'll probably want to use rpad/lpad to get your columns to line up nicely
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
I missed the previous suggestion of concatenation or I would not have suggested the CSV.

The problem here is if your concatenated string can exceed 4000 characters in total.  If so, you will need to adjust it a little.
0
 

Author Comment

by:eagle_ea
Comment Utility
i'm using pl/sql developer. just executing this procedure will not display results of my query?

execute compare_parameters('abc');;
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
of course not.  why would it?

your procedure doesn't have any code in for executing the statement, it just generates one.

Plus, as noted above, even if you did,  the generated syntax looks like it has some errors in it
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>executing this procedure will not display results of my query?

Again, no.  Tools like pl/sql developer and sqlplus will execute a select and 'automatically' display the results because they have been programmed to do it.

Since you are manually creating and executing the query yourself, you need to manually display the results yourself.

Now, you can copy/paste the select statement generated by your procedure into pl/sql developer and execute it.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
If you're thinking about sql*server t-sql functions  where you can have


select a,b,c from table

and then the results will display in studio, that is possible, but only in Oracle 12c and only in tools designed to handle that kind of output.  As far as I know,  pl/sql developer has not been updated in such a way yet.

In oracle  11gR2 and lower,  your procedure can't simply "select" - your code must do something with the query results.

In your case though, the point is moot.  Your procedure, in its current form, doesn't ever execute the query you generate.  You simply have a procedure that creates a string.

Oracle doesn't know your string has a sql statement inside it.  It's just text.
0
 

Author Comment

by:eagle_ea
Comment Utility
ok, got the point.

if i dont know how many columns will be built, how can i define the variables prior hand?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>if i dont know how many columns will be built, how can i define the variables prior hand?

You cannot.  You need to use one of the posted alternatives.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
>>if i dont know how many columns will be built, how can i define the variables prior hand?

and that's why I suggested you create your select statement so that it returns a single value

so, instead of

SELECT d1.sid,
         dp1.parameter,
         d2.sid,
         dp2.parameter....

construct it like this...

SELECT d1.sid || '   '
         dp1.parameter || '   '
         d2.sid || '   '
         dp2.parameter .....


then execute your query, loop through and print each row which will have exactly one value

You'll probably want to use rpad/lpad to get your columns to line up nicely
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
Comment Utility
Here try this...

Same idea as your previous question on this topic.
Eliminates the need for dynamic sql.
Demonstrates what we meant by using a fixed result.

It does have the caveat that each line is restricted to 32767 characters.
So, that puts an upper limit on how many instances you can compare at one time.
At 50 characters per instance value, that still lets you go up to several hundred.

CREATE OR REPLACE PROCEDURE "COMPARE_PARAMETERS"(in_client_sid IN CHAR)
IS
BEGIN
    FOR x
        IN (SELECT 'Parameter' parameter,
                   EXTRACT(
                       XMLAGG(XMLELEMENT("x", RPAD(d.instance, 50)) ORDER BY instance),
                       '/x/text()'
                   ).getclobval()
                       sidvalues
              FROM databases d
            UNION ALL
              SELECT parameter,
                     EXTRACT(XMLAGG(XMLELEMENT("x", pvalue) ORDER BY instance), '/x/text()').getclobval()
                         sidvalues
                FROM (SELECT d.instance,
                             p.parameter,
                             RPAD(NVL(p2.pvalue, ' '), 50) pvalue,
                             MAX(NVL(p2.pvalue, '{null}')) OVER (PARTITION BY p.parameter) maxvalue,
                             MIN(NVL(p2.pvalue, '{null}')) OVER (PARTITION BY p.parameter) minvalue
                        FROM databases d
                             CROSS JOIN (SELECT DISTINCT parameter FROM db_parameters) p
                             LEFT JOIN db_parameters p2
                                 ON p2.db_instance = d.instance AND p2.parameter = p.parameter
                       WHERE d.client IN (SELECT c.instance
                                            FROM clients c
                                           WHERE UPPER(c.code) = UPPER(in_client_sid))
                         AND d.dataguard = 'N'
                         AND p.parameter NOT IN ('instance_name',
                                                 'log_archive_dest_1',
                                                 'spfile',
                                                 'utl_file_dir',
                                                 'db_name',
                                                 'db_domain',
                                                 'db_unique_name')
                         AND p.parameter NOT LIKE ('%dest')
                         AND p.parameter NOT LIKE ('dg_%'))
               WHERE maxvalue != minvalue
            GROUP BY parameter
            ORDER BY parameter)
    LOOP
        DBMS_OUTPUT.put_line(RPAD(x.parameter, 50) || ' ' || x.sidvalue);

        IF x.parameter = 'Parameter'
        THEN
            DBMS_OUTPUT.put_line(
                RPAD('-', 50, '-') || ' ' || RPAD('-', DBMS_LOB.getlength(x.sidvalue), '-')
            );
        END IF;
    END LOOP;
END;

Open in new window

0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
why the penalty grade?
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now