Stored Procedures once more time

[ggray]

Hi guys, i have a question!

i have to run a stored procedure from pyodbc, but the stored procedure
is not yet in the database. What i want to do is to create the store
procedure in the database from my python script and then execute it !

============== Here is the store procedure, nothing fancy
==============
CREATE PROCEDURE MYPROCNAME
DECLARE vInHandle utl_file.file_type;
vNewLine VARCHAR2(250);
BEGIN
vInHandle := utl_file.fopen('PATH','FILE', 'R');
LOOP BEGIN
utl_file.get_line(vInHandle,vNewLine);
dbms_output.put_line(vNewLine);
EXCEPTION WHEN OTHERS THEN EXIT;
END;
END LOOP;
utl_file.fclose(vInHandle);
END fopen;
END MYPROCNAME;
============== Here is the store procedure, nothing fancy
==============

exec MYPROCNAME;

[mkleehammer]

You would use something like this:

cnxn = pyodbc.connect(...)
cursor = cnxn.cursor()

cursor.execute(

"""
CREATE PROCEDURE MYPROCNAME
DECLARE vInHandle utl_file.file_type;
vNewLine VARCHAR2(250);
BEGIN
vInHandle := utl_file.fopen('PATH','FILE', 'R');
LOOP BEGIN
utl_file.get_line(vInHandle,vNewLine);
dbms_output.put_line(vNewLine);
EXCEPTION WHEN OTHERS THEN EXIT;
END;
END LOOP;
utl_file.fclose(vInHandle);
END fopen;
END MYPROCNAME;

""")
cnxn.commit()

cursor.execute("exec MYPROCNAME")

I didn't actually test this, but it should be something like that.

You can *try* putting it all into one string and executing it, but
there are a lot of databases that won't allow that.