Home » SQL & PL/SQL » SQL & PL/SQL » Bind variable not declared??? (oracle 10g release 2)
Bind variable not declared??? [message #315853] Wed, 23 April 2008 00:26 Go to next message
horax
Messages: 34
Registered: March 2008
Member
I'm in a class for plsql, and they've given me the code for a package, and I'm supposed to write an anonymous block to execute the procedure within the package and give a certain output.

Easy, right?

Well, for some reason, it's telling me that one of my bind variables is not declared even though I think I've declared it.

Here's the code:

CREATE OR REPLACE PACKAGE BODY order_info_pkg IS
 FUNCTION ship_name_pf  
   (p_basket IN NUMBER)
   RETURN VARCHAR2
  IS
   lv_name_txt VARCHAR2(25);
 BEGIN
  SELECT shipfirstname||' '||shiplastname
   INTO lv_name_txt
   FROM bb_basket
   WHERE idBasket = p_basket;
  RETURN lv_name_txt;
 EXCEPTION
   WHEN NO_DATA_FOUND THEN
     DBMS_OUTPUT.PUT_LINE('Invalid basket id');
 END ship_name_pf;
 PROCEDURE basket_info_pp
  (p_basket IN NUMBER,
   p_shop OUT NUMBER,
   p_date OUT DATE,
   p_name OUT VARCHAR2)
  IS
 BEGIN
   SELECT idshopper, dtordered
    INTO p_shop, p_date
    FROM bb_basket
    WHERE idbasket = p_basket;
   p_name:=ship_name_pf(p_basket);
 EXCEPTION
   WHEN NO_DATA_FOUND THEN
     DBMS_OUTPUT.PUT_LINE('Invalid basket id');
 END basket_info_pp;
END;
/

variable g_shop number
variable g_date date
variable g_name varchar2

DECLARE
lv_ordername VARCHAR2(30);
BEGIN
SELECT dtordered
 INTO :g_date
  FROM bb_basket
   WHERE idbasket=p_basket;
SELECT order_info_pkg.basket_info_pp(12, :g_shop, :g_date, :g_name)
 INTO lv_ordername 
  FROM bb_basket
   WHERE idbasket=p_basket;
DBMS_OUTPUT.PUT_LINE(lv_ordername, :g_shop, :g_date, :g_name);
end;
/


I know I would not normally do two select statemetnsf rom the same table, but I'm doing this so I can experiemet with the code a bit.

Any help woudl be greatly appreciated...why isn't my bind variable :g_date declared?

[Updated on: Wed, 23 April 2008 00:31]

Report message to a moderator

Re: Bind variable not declared??? [message #315855 is a reply to message #315853] Wed, 23 April 2008 00:36 Go to previous message
horax
Messages: 34
Registered: March 2008
Member
Never mind...I made the wrong object (the procedure, nto the function) private... ugh...
Previous Topic: Month Extracting
Next Topic: Why "connection as SYS should be as SYSDBA or SYSOPER"?
Goto Forum:
  


Current Time: Thu Dec 08 14:10:07 CST 2016

Total time taken to generate the page: 0.24491 seconds