Dynamic SQL

From: <sayoni_at_my-deja.com>
Date: 2000/03/08
Message-ID: <8a6oso$dau$1@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 Wed Mar 08 2000 - 00:00:00 CST