RE: Query with return value

[timothy . helck]

Eric,
 
PL/SQL (Procedural Language SQL) is an Oracle product. It allows you to make
a script that combines a lot of rudimentary procedural language stuff with
sql statements. So you can create variables, arrays, do looping, handle
exceptions etc. The script is saved in a file and can be run from the
SQL*PLUS command line with an '@' (eg "@script.sql"). Or you can put one
inside a DBI cursor and pass it a few parameters and have it execute several
inserts, updates etc. to different tables without a lot of hassle.
 
There's probably all sorts of free information and tutorials about PL/SQL on
the internet. I myself like books better, because I can read them in the
bathtub. I have "Sams Teach Yourself PL/SQL in 21 Days" and I think it's
pretty good. Someone else in our office has the O'Reilly book, and those are
usually very good. You don't need to become an expert at PL/SQL, but if you
learn the basics you'll be very glad you did.
 
Sorry if this has gotten a little off topic for this board, but Perl DBI and
PL/SQL are really good together and more people should know about it!!!
 
Tim

-----Original Message-----
From: Eric, Audet [mailto:[EMAIL PROTECTED]]
Sent: Monday, July 30, 2001 1:31 PM
To: '[EMAIL PROTECTED]'; Eric, Audet; [EMAIL PROTECTED]
Subject: RE: Query with return value


Oh ... no, I am not familiar with this style ... Where can I find some
documents with examples on this?
 
Eric

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Monday, July 30, 2001 1:28 PM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: Query with return value



Eric, 

Do you realize that you can put all sorts of pl/sql stuff into a DBI cursor?
You could do both of your inserts within one cursor, greatly simplifying
things.

Try something like this: 

        my $insertCursor = q { 
        DECLARE custId number(8); 
        BEGIN 
                SELECT cust_id_seq.nextval INTO custId from DUAL; 
                INSERT INTO some_table(cust_id) VALUES(custId); 
                INSERT INTO some_other_table(cust_id) VALUES(custId); 
        END; 
        }; 
If you're not already familiar with pl/sql you should be. Used in this way,
it complements Perl beautifully. 

Tim 

BTW My definition of a stupid question is "a question that if you're
embarassed to ask it, you stay stupid." 


-----Original Message----- 
From: Eric, Audet [ mailto:[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> ] 
Sent: Monday, July 30, 2001 12:36 PM 
To: [EMAIL PROTECTED] 
Subject: Query with return value 


Now ... this could be a stupid question but, eh .. I will try anyway 

I am using the DBI module: 

I have a table (custid, email, name ...) 
Custid is a sequence (customer_custid_seq) 
the custid is DEFAULT nextval('customer_custid_seq') 

Well ... when I insert values into this table, I need to get the custid back

so I can insert into another table that use custid as a foreign key. 

I tried to do a SELECT, but it did not work ... my guess is that I need to 
commit the changes first. I don't want to do that ... because I may have to 
rollback. 

So how can I get that custid 

Here's what I was tryint to do ... but not working 
     my($query) = "INSERT INTO customer 
(password,fname,lname,addr1,addr2,addr3,city,prov,pcode,country,tel,fax,emai

l,language,emai$ 
     $sth4 = $dbh->prepare($query); 
     $sth4->execute() or &ReturnError(9) and exit; 
     $sth4->finish() or &ReturnError(10) and exit; 
     my($query) = "SELECT custid FROM customer WHERE email = 
'$GETFORM{'email'}'"; 
     my($sth) = $dbh->prepare($query); 
     $sth->execute() or &ReturnError(11) and exit; 
     $sth->finish() or &ReturnError(12) and exit; 
     ($custid)=$sth->fetchrow_array; 

Eric