Creating a Stored Procedure 2

[runmd]

I would like to fire off an oracle stored procedure in a ASP.NET page that I will pass a query parameter (string variable) to assign to a where clause. I am concatenating the string to the rest of the SQL statement and would like to execute it. Do I use the EXEC() command? I would also like to query other tables based on an id from the first query. So, far this will not compile.

CREATE OR REPLACE PROCEDURE GetTribeInfo (qryParam STRING) AS

strSQL VARCHAR2(100);

BEGIN

/* Get Tribes by Selected Counties */
strSQL := 'SELECT TRIBAL_ID, TRIBAL_NAME, COUNTY_NAME, STATE_NAME
  FROM TRIBE_COUNTY_VW
  WHERE (COUNTY_ID =' || qryParam || 'ORDER BY COUNTY_NAME';

EXEC(strSQL)

  /* Get Tribes by Selected Counties */
  --SELECT TRIBAL_ID, TRIBAL_NAME, COUNTY_NAME, STATE_NAME
  --FROM TRIBE_COUNTY_VW
  --WHERE (COUNTY_ID = || qryParam
  --ORDER BY COUNTY_NAME
  
  /* Get Tribe Information */
  /*--SELECT
  --FROM  VwAuthors
  --WHERE    exists (Select * from Titles where au_id = VwAuthors.au_id)
  --ORDER BY title_id*/

END;

I would like to take the Tribal ID from the first query to run in a second query for retrieving a second dataset of tribal information. I have not written the 2nd query yet since I just wanted to fix the first compile error so that I can at least retrieve the first dataset. Is this possible to do in an oracle stored procedure?

 

[SantaMufasa]

RunMD,

Actually, Oracle's procedure language, PL/SQL, offers some syntactical simplifications as alternatives to the type of code you are trying to execute. (BTW, Since I'm not sure yet what you are trying to do in your second query, my code suggestion, below, has a commented line where you could place your second query.):

CREATE OR REPLACE PROCEDURE GetTribeInfo (qryParam STRING) AS
begin
    for x in (SELECT TRIBAL_ID
                    ,TRIBAL_NAME
                    ,COUNTY_ID
                    ,COUNTY_NAME
                    ,STATE_NAME
                FROM TRIBE_COUNTY_VW
               WHERE COUNTY_ID = qryParam
               ORDER BY COUNTY_NAME) loop
        dbms_output.put_line
            (x.tribal_id||', '||x.tribal_name||', '||
             x.county_name||' ('||x.county_id||'), '||x.state_name);
        -- Inner loop for Second Query can go here
    end loop;
end;
/

Procedure created.

...And here are a couple of invocations of GetTribeInfo with some data that I made up for the table, "TRIBE_COUNTY_VW":

set serveroutput on format wrap
REM Above "set" is SQL*Plus code to turn on output to your screen from
REM    the DBMS_OUTPUT.PUT_LINE print procedure in GetTribeInfo
begin
    dbms_output.put_line('===================================');
    GetTribeInfo (18);
    dbms_output.put_line('===================================');
    GetTribeInfo (10);
    dbms_output.put_line('===================================');
end;
/

===================================
17, Apache, Graham (18), Arizona
39, Yuma, Graham (18), Arizona
25, Pima, Graham (18), Arizona
===================================
17, Apache, Coconino (10), Arizona
19, Hopi, Coconino (10), Arizona
37, Yavapai, Coconino (10), Arizona
22, Navajo, Coconino (10), Arizona
===================================

Let us know if this is getting close to what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Thargy]

runmd,

Santa's advice is spot-on (as usual). Howeve, I believe that there is a bigger issue here, namely SQL injection.

If you are passing a string to a stored procedure is a great way to make it vulnerable to a SQL injection attack. Anyone intercepting the string could add toxic content to it, and thereby do some serious damage to your database.

Regards

T

 

[runmd]

SantaMufasa,

The passing of one COUNTY_ID value is not my problem, it's the passing of multiple COUNTY_ID's in a string where I need multiple records for multiple counties. The qryParam would be equal to this:

91 OR COUNTY_ID = 92 OR COUNTY_ID = 93 OR COUNTY_ID = 94)

So, I've strung together this in the vb code that I want to send and use in the where clause. Is this possible to execute as part of the query and how would I do that? I just get compile errors. I want to retrieve tribes for each county, then in the inner query, pull out the tribal information for each tribe which I could use the TRIBAL_ID as the common identifier. So for each TRIBAL_ID from the outer loop, pull the TRIBAL_ID in the inner loop. I'll test what you have shown me, which I appreciate greatly. What I have brought up here is something that I will need to figure out or come up with another way to send multiple counties from an ASP.NET VB application to an oracle stored procedure. This data will be used to show in an expandable/heirarchical gridview control where the top row would show the tribal name, county and state, the expandable row below the tribal name would show the tribal contact information. If you need to know more about what I am trying to do, let me know. I understand I could post some of my question to the ASP.NET forum but I want to get the Oracle stuff taken care of first.

Thanks,
runmd

 

[karluk]

There are undoubtedly many ways to pass a list of values to a stored procedure that are far superior to your proposal of passing almost the entire where clause of your query. However, as a (hopefully) instructional exercise in some of the issues involved in coding an Oracle stored procedure, let's take a look at how we might make this strategy work.

Your immediate problem is the line "EXEC(strSQL)". You are attempting to execute the query contained in strSQL, but unfortunately this is not a valid statement in Oracle's pl/sql. You will find that, if you replace this one line with "EXECUTE IMMEDIATE strSQL", your procedure will compile without error.

Naturally things aren't quite so simple. Just getting rid of the compile errors doesn't address all of the issues that will happen when you try executing your procedure. You have a minor problem in that you have declared strSQL to be only 100 characters long, which isn't long enough to contain the select statement you are building. There are also two more fundamental problems: you haven't provided any local variables to hold the results of your select, and you are expecting multiple rows to be returned from the select, which will require some sort of looping mechanism to process all the rows.

One way to achieve this is to use a so-called "cursor variable". Doing so will allow you to open the contents of strSQL as a cursor and fetch the rows one by one, applying any processing that is required between fetches. Modifying your procedure to use a cursor varible would look something like

CREATE OR REPLACE PROCEDURE GetTribeInfo (qryParam STRING) AS
strSQL VARCHAR2(1000);
cv_tribe sys_refcursor;  /* cursor variable declaration */
v_tribal_id tribe_county_vw.tribal_id%type;
v_tribal_name tribe_county_vw.tribal_name%type;
v_county_name tribe_county_vw.county_name%type;
v_state_name tribe_county_vw.state_name%type;

BEGIN
/* Get Tribes by Selected Counties */
strSQL := 'SELECT TRIBAL_ID, TRIBAL_NAME, COUNTY_NAME, STATE_NAME
  FROM TRIBE_COUNTY_VW
  WHERE (COUNTY_ID =' || qryParam || 'ORDER BY COUNTY_NAME';
open cv_tribe for strSQL;
loop
  fetch cv_tribe into v_tribal_id, v_tribal_name, v_county_name, v_state_name;
  exit when cv_tribe%notfound;
  dbms_output.put_line('TRIBAL_ID='||v_tribal_id||'  TRIBAL_NAME='||v_tribal_name);
  /* Insert logic needed by your inner loop here. */
end loop;
close cv_tribe;
END;

When I execute this procedure with your suggested value of qryParam, I get the following results on some sample data I've created.

SQL> execute GetTribeInfo('91 OR COUNTY_ID = 92 OR COUNTY_ID = 93 OR COUNTY_ID = 94)');
TRIBAL_ID=35  TRIBAL_NAME=ARAPAHO
TRIBAL_ID=1  TRIBAL_NAME=WINNEBAGO
TRIBAL_ID=117  TRIBAL_NAME=OSAGE
TRIBAL_ID=68  TRIBAL_NAME=CHEYENNE

PL/SQL procedure successfully completed.

 

[runmd]

Thanks Karluk!

So, now I am tying this into a oracle package for use with an ASP.NET vb application. I get an error when filling a dataset since the table or query is not returning any rows. I'm going to post it on the ASP.NET forum.