Procedure with a Select query

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;

ASKER

it says,, invalid character at
open myCur for select_query;

It is likely the ending ';' in the string.

ASKER

is it not required to have a ; at the line of each code?

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

ASKER

did not help

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?

ASKER

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

>>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?

ASKER

i want the result set to be display from the select query that was built

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

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) || ''''

>>> 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);

>>> 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"

ASKER

set serveroutput on - only displays DBMS_OUTPUT.put_line

what about the select output?

>>> what about the select output?

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

>>what about the select output?

I posted that already in http:#a39768744

ASKER

the number of columns are random , so setting the output to a variable is not teh option , we need

>>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?

>>>> 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

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.

ASKER

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

execute compare_parameters('abc');;

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

>>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.

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.

ASKER

ok, got the point.

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

>>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.

>>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

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial