- Do you execute "set serveroutput on" before your select?
If not, the dbms_output.put_line commands are buffered but not
displayed.
- match_col is a function not a procedure then you get your
error with the exec command. You have to use:
var phone varchar2(100)
exec :phone := match_col(...);
--
Have a nice day
Michel
<sayoni_at_my-deja.com> a écrit dans le message : 8a6oso$dau$1_at_nnrp1.deja.com...
> Newbie facing Strange problem..Running Oracle 8i
>
> I have compiled a function with no errors but when I try running it I
> get strange errors...
>
> If I run it from PL/SQL using
> SQL> select match_Col('Suburb','Christie','St','2065','Wollstonecraft')
> from dual;
>
> MATCH_COL('SUBURB','CHRISTIE','ST','2065','WOLLSTONECRAFT')
> ----------------------------------------------------------------
>
> but no debug stmts that are in the function (using dbms_output.put_line)
>
> I immediately run the function using exec
> SQL> exec match_Col('Suburb','Christie','St','2065','Wollstonecraft');
> W%ollstonecraft
> SELECT phone FROM IPND WHERE streettype1 = :pSttype AND postcode =
> :pPost AND
> streetname1 = :pStreet AND locality like :rTemp_name
> BEGIN match_Col('Suburb','Christie','St','2065','Wollstonecraft'); END;
>
> *
> ERROR at line 1:
> ORA-06550: line 1, column 7:
> PLS-00221: 'MATCH_COL' is not a procedure or is undefined
> ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored
>
> Some of the debug stmts are spit out but then an error
>
> I run it again and the debug stmts are once again not visible.
>
> My function looks like this...
>
> CREATE OR REPLACE FUNCTION match_Col (
> pColname varchar2,
> pStreet varchar2,
> pSttype varchar2,
> pPost varchar2,
> pSuburb varchar2 ) RETURN VARCHAR2 IS
> rLength_name number;
> qry_str varchar2(200);
> rTemp_name varchar2(50);
> i number := 1;
> rPhone varchar2(20) := 'DUMMY';
> BEGIN
> dbms_output.enable(20000);
> IF pColname = 'Streetname' THEN
> rLength_name := length(pStreet);
> ELSIF pColname = 'Suburb' THEN
> rLength_name := length(pSuburb);
> END IF;
> WHILE i <= rLength_name LOOP -- Loop through the Colname
> replacing one letter at a time with wildcard '%'
> IF pColname = 'Streetname' THEN
> rTemp_name := substr( pStreet,1,i) || '%' ||
> substr( pStreet,i+1);
> ELSIF pColname = 'Suburb' THEN
> rTemp_name := substr( pSuburb,1,i) || '%' ||
> substr( pSuburb,i+1);
> END IF;
> dbms_output.put_line(rTemp_name);
> IF (pColname = 'Suburb') THEN
> qry_str := 'SELECT phone FROM IPND WHERE
> streettype1 = :pSttype AND postcode = :pPost AND ' ||
> 'streetname1 = :pStreet AND locality
> like :rTemp_name';
> dbms_output.put_line(qry_str);
> EXECUTE IMMEDIATE qry_str INTO rPhone USING
> pSttype, pPost, pStreet, rTemp_name;
> dbms_output.put_line('executed ..');
> ELSIF pColname = 'Streetname' THEN
> qry_str := 'SELECT phone FROM IPND WHERE
> streettype1 = :pSttype AND postcode = :pPost AND ' ||
> 'locality = :pSuburb AND streetname1
> like :rTemp_name';
> dbms_output.put_line(qry_str);
> EXECUTE IMMEDIATE qry_str INTO rPhone USING
> pSttype, pPost, pSuburb, rTemp_name;
> END IF;
>
> IF SQL%NOTFOUND = TRUE THEN
> dbms_output.put_line('not found..');
>
> IF pColname = 'Streetname' THEN
> rTemp_name := substr( pStreet,1,i) ||
> '%' || substr( pStreet,i+2);
> ELSIF pColname = 'Suburb' THEN
> rTemp_name := substr( pSuburb,1,i) ||
> '%' || substr( pSuburb,i+2);
> END IF;
>
> IF pColname = 'Suburb' THEN
> qry_str := 'SELECT phone FROM IPND
> WHERE streettype1 = :pSttype AND postcode = :pPost AND ' ||
> 'streetname1 = :pStreet AND
> locality like :rTemp_name';
> EXECUTE IMMEDIATE qry_str INTO rPhone
> USING pSttype, pPost, pStreet, rTemp_name;
> ELSIF pColname = 'Streetname' THEN
> qry_str := 'SELECT phone FROM IPND
> WHERE streettype1 = :pSttype AND postcode = :pPost AND ' ||
> 'locality = :pSuburb AND
> streetname1 like :rTemp_name';
> EXECUTE IMMEDIATE qry_str INTO rPhone
> USING pSttype, pPost, pSuburb, rTemp_name;
> END IF;
>
> DBMS_OUTPUT.PUT_LINE('Loop number = ' || i );
> dbms_output.put_line(qry_str);
>
> IF SQL%NOTFOUND = TRUE THEN
> dbms_output.put_line('incrementing i....');
> i := i + 1;
> ELSE
> RETURN rPhone;
> END IF;
> ELSE
> RETURN rPhone;
> END IF;
> END LOOP;
> END match_Col;
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Mar 09 2000 - 00:00:00 CST
