Problems with running PL/SQL procedures

[automatix]

Hi there!

I'm trying to execute an Oraqcle PL/SQL procedures, but it does not work.
I think the code looks fine and maybe the oracle procedure itself causes the error (I hope this is right, so it is the admins fault ;-)).
Can anyone ratify that my code is okay or correct it?

Here's the code:

$query = "begin insert_person(0, 'Sebastian', 'Korten', 'Hiwi', 1, '',0,0); end;";

$sth = OCIParse ($connection, $query);

OCIExecute ( $sth );

and here the error message:

Warning: OCIStmtExecute: ORA-06550: line 1, column 7: PLS-00221: 'INSERT_PERSON' is not a procedure or is undefined ORA-06550: line 1, column 7: PL/SQL:
Statement ignored in /import/php/knobloch/o.face/gui/test_plsql.php on line 31

so long,

Michael

[Anon]

your code looks ok, the problem seems to be with the call to insert_person(). Is this a function? is it a function for the same user you are logging in with?

[brenbart]

I have a PHP app that accesses SQL 2000 database via ODBC function calls. It works on my win95 machine, however I have a new 2000 pro machine that has the latest version of php on it (I've tried every other version down to the php4.0.4 which is on my 95 machine.)

When I try to run the app on the 2000 box I get this error:

Warning: SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'., SQL state 28000 in SQLConnect in c:\inetpub\wwwroot\AS400MainQuery.php on line 32

I have the DSN set up the same way as my 95 machine and have tested it with Access and it seems to function correctly. When I test the same script on the 2000 box with it being pointed at an MSSQL 7.0 server it works fine.

If I switch to the mssql functions the script doesn't work on either win2kpro or win95 against the MSSQL2k server but works fine against MSSQL7.0.

It seems that the problem is with the combo of my either the win2kpro or win2kserver as the other combos win95/win2kserver/mssql2k server and win2kpro/winnt4.0/mssql7.0 seem to work fine.

Suggestions?

[brenbart]

Oh how sad! I mispelled my own name....

[brenbart]

I might also add that I didn't mean to place this as a reply to someone else's problem. So sad being new....

[automatix]

It is a function. And it is for the same user. My admin has given me his code, but I do not understand the PL/SQL Language. Do you? Thanks for you reply!

Michael

-- $Id: insert_person.sql,v 1.6 2001/11/15 17:13:09 michels Exp michels $

-- Uebergabe
-- p_person_seq: person.kennid, Typ: varchar2,
-- Bedeutung: Nummer der Person, kann uebergeben werden, wenn nicht wird selbst erzeugt
-- p_vorname: person.vorname, Typ: varchar2
-- p_name: person.name, Typ: varchar2
-- p_titel: person.titel, Typ: varchar2
-- p_anrede_id: Typ: number, Bedeutung: zeigt auf Tabelle anrede,
-- kann uebergeben werden, entweder p_anrede_id oder p_anrede_text
-- p_anrede_text: Typ varchar2, Bedeutung: wie anrede_id,
-- kann uebergeben werden, entweder p_anrede_id oder p_anrede_text
-- p_ansprechpartner_id: number, Bedeutung: ID des Ansprechpartners, Mitarbeiter von Cognid

create or replace function insert_person (
p_person_seq IN person.Kenn_ID%TYPE,
p_vorname IN person.Vorname%TYPE,
p_name IN person.Name%TYPE,
p_titel IN person.titel%TYPE,
p_anrede_id IN Anrede.id%TYPE,
p_anrede_text IN anrede.text%TYPE,
p_ansprechpartner_id IN number,
p_Schlagwort_ID IN person.Schlagwort_ID%TYPE) RETURN NUMBER

AS
v_person_seq NUMBER;
v_anrede_id NUMBER;
BEGIN

    IF nvl(p_anrede_id,0) = 0 THEN -- wenn id 0 oder null dann suchen in Tabelle
            BEGIN
                    select id into v_anrede_id from anrede where text = p_anrede_text;
            EXCEPTION WHEN NO_DATA_FOUND THEN -- wenn anrede_id nicht gefunden wird => default 1
                    v_anrede_id := 1;
            END;
    END IF;

    IF nvl(p_person_seq,0) = 0 THEN -- kenn_id nicht mitgegeben, also per sequencer holen
            select Anrede_SEQ.nextval into v_person_seq from dual;
    ELSE
            v_person_seq := p_person_seq; -- Parameter uebernehmen
    END IF;

    IF nvl(p_ansprechpartner_id,0) != 0  THEN -- kontakt eintragen
    -- kontakt_art_id = 1 bedeutet Ansprechpartner
            insert into kontakt (id, von_person_id, datum, zu_person_id,
                                 kontakt_art_id)
            values (kontakt_seq.nextval, p_ansprechpartner_id,
                    sysdate, v_person_seq, 1);      

      END IF;

    insert into person (kenn_id, name, vorname, titel, anrede_id)
    values
    (v_person_seq, p_name, p_vorname, p_titel, v_anrede_id);

    RETURN 0;

END insert_person;

/
show errors;

[automatix]

I've solved the problem. Here is the working code. I have not considered the return value.

$query = "insert_person(0, 'Michael', 'Knobloch', 'Hiwi', 1, '',0,0)";
echo "$query<br>";
//Erster Versuch
// by [e-mail]
//$sth = OCIParse ($connection, $query);
//echo"$query";
// This calls stored procedure sp_newaddress, with :address_id being an
// in/out variable and :error_code being an out variable.
// Then you do the binding:
//OCIExecute ( $sth );

//Zweiter Versuch
// call stored procedure
$stmt = OCIParse($connection, "begin :result := $query; end;");

// OCIBindByName binds return value from insert_person with the variable
OCIBindByName($stmt, "result", &$result);

//Execute statement and commit
OCIExecute($stmt);

//Write return value to $result
OCIResult($stmt,$result);

echo $result;