Link to home
Start Free TrialLog in
Avatar of Jerry N
Jerry NFlag for United States of America

asked on

How to create a View from a PL/SQL procedure (Oracle 9i)

We are using oracle 9i. Although I've programmed for 20+ years in VB, VBA, C, etc. I am new to Oracle.
I wish to run a report off of a view.  I would rather not create a table to generate the report off of, since we will be using whatever method we come up with quite a bit in the future.  Creation of many tables may get out of hand in a hurry. In other languages, I would create something on the fly and then clean up after myself - very little overhead.   I'm trying to do the same with PL/SQL.

I'm using Access as the report/user interface and have had no problems as long as the views are static.  I am able to pass and retrieve paramters between Oracle and Access.
The view I need to use uses two tables. It is static except for one area where I need to filter one of the tables by a passed company name (i.e. "WHERE ..... AND COMPANY='COMPANY1' AND....)

I have tried using EXECUTE IMMEDIATE and it errors, but I can't tell what the error is. I looked at the resultant str (see code) and it looks fine.  
Is there a way to create a view on the fly?

Or, what other methodology might be suggested.



str := 'CREATE OR REPLACE VIEW MY_ANALYSIS(source_id, COUNT_TRANSACTIONS, LN, PRODUCT_NO, SUM_COST, SUM_RETAIL, PERC_DISC)';
  str := str || ' AS SELECT   a.source_id, COUNT (a.source_id) AS COUNT_TRANSACTIONS, A.LN, a.PRODUCT_NO, SUM(A.COST) AS SUM_COST,';
  str := str || ' SUM(A.PRICE) AS SUM_RETAIL, to_char((B.PERC_DISC *100),' || CHR(39)||'999.999'|| CHR(39)||') AS PERC_DISC FROM MYFILE1 a,'; 
  str := str || ' MYFILE2 b WHERE a.MY_FLAG =' ||CHR(39) ||'Y' || CHR(39);
  str := str || ' A.PRODUCT_NO = B.PRODUCT_NO AND B.COMPANY =' ||CHR(39) ;
  str := str ||'COMPANY1'||CHR(39);
  str := str ||' GROUP BY a.source_id, A.LN, A.PRODUCT_NO,B.PERC_DISC';
  str := str ||' ORDER BY A.LN DESC, A.PRODUCT_NO ASC;'    ;
   EXECUTE IMMEDIATE str;

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of SmilingPixie
SmilingPixie

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sujith
What you need is a REF CURSOR. You can use a function that returns a ref cursor to get the results.
Ref cursors are like result sets in VB. You can dynamically open a ref cursor for a query and use it in the reports.

See the sample code.
create or replace function test_func
return sys_refcursor
as
l_cur sys_refcursor;
begin
open l_cur FOR 
'SELECT a.source_id, COUNT (a.source_id) AS COUNT_TRANSACTIONS, A.LN, a.PRODUCT_NO, SUM(A.COST) AS SUM_COST,  SUM(A.PRICE) AS SUM_RETAIL, to_char((B.PERC_DISC *100), ''999.999'') AS PERC_DISC FROM MYFILE1 a, MYFILE2 b WHERE a.MY_FLAG = ''Y'' and A.PRODUCT_NO = B.PRODUCT_NO AND B.COMPANY = ''COMPANY1'' GROUP BY a.source_id, A.LN, A.PRODUCT_NO,B.PERC_DISC ORDER BY A.LN DESC, A.PRODUCT_NO ASC ' ;
 
return l_cur;
end;
/

Open in new window

Add an exception handler section to your code to see what error you are getting as shown below :

declare
..
v_err number;
v_msg varchar2(4000);
....
begin
... -- all your code goes here

exception when others then
v_err := SQLCODE;
v_msg := SQLERRM;
 dbms_output.put_line('my error code:'||v_err);
 dbms_output.put_line('my error mesg:'||v_msg);

end;  -- end pl/sql block
/
Avatar of Jerry N

ASKER

nav:
That error code did help.
However, the error is an invalid character. Could it be because of CHR(39) (')?
if so, how do I assign something that is typically in a single quote ?
I typically take a statement like this:
MyVar = "A"
and say:
str="MyVar = 'A' "
and life is good.
So I tried to use double quotes inside the single quotes. That gives me a SQL statement not terminated error. Yet when I check the str, it is terminated with a ;

What's the correct way to handle this?
Also, how do I print out str if it is > 255 characters - I tried dbms_output.put_line(str);  but it says it's too long.

sujh:
If I am able to bring the cursor back to the Access VBA calling procedure, now what?  I need an ODBC link to the query or a table to get it into an Access report. That's how I'm used to doing it. Is there another way?
Is your query going to change every time during the execution of the code?
Why not create a view and directly query from it? Why do you need to use a dynamic code to create the view everytime?

Avatar of Jerry N

ASKER

The reason I need to create a dynamic view is that one of the paramaters of the view will change
COMPANY = 'companya'

Avatar of Jerry N

ASKER

The solution was that I was using a ";" to terminate the str in the above code.
This was giving an error of invalid character.
When it was removed, the creation of the view worked as planned.
Apparently there is an implicit commit
Avatar of SmilingPixie
SmilingPixie

I did point out in my solution to him that he had extra semi-colons in his code and that it might be part of the reason he was getting errors.  (It's in the very last line of my comments).  However, if he wants to close it with the points refunded - that's fine, I'm not going to make any kind of major fuss on it.
GNOVAK, is your issue resolved or do you still need some help.
just to mention in dynamic sql statements..

it should be as shown below

v_var := 'SMITH';
v_stmt := 'select * from emp where empname = '''  || v_var || ''' and deptno = ' || 10 ;
....

I mean we have to use 2 single quotes ( ' ) consecutively to say that we need a single quote. Also dbms_output.put_line will give errors if the length is more. Since this is just to debug, we can either use substr(var,1,250) inside the dbms_output.put_line(..) to get only first 250 chars and then next few characters by using another dbms_output.put_line(..) in a similar fashion or we can just insert that variable into some log/debug table if you do not want to use dbms_output.put_line(..)

Thanks
>> The reason I need to create a dynamic view is that one of the paramaters of the view will change
COMPANY = 'companya'
So if there are 1000 companies, will you create 1000 views for each?
Create a single view with company as one of the columns and use a where clause to filter the results for your needs.

select * from <your view having all companies>
where company = 'companya'
Avatar of Jerry N

ASKER

Smiling Pixie -
you are right!!!
I thought that was my comment and didn't even see it.
Sorry about that.
POints are yours.
Avatar of Jerry N

ASKER

Thanks for pointing this out.
It was that. Next time I will read through responses more carefully.
No offense meant - I really appreciate the help as I am learning.