[Tutor] How can I execute a PL/SQL Procedure directly through

[Kent Johnson]

Your original query asked how to execute an existing stored procedure. 
This is the SQL code to *create* a stored procedure. Are you trying to 
create it or execute it?

Kent

Amit Saxena wrote:
> this is the file content of "metadata.sql"
> 
> set serveroutput on
> 
> CREATE OR REPLACE PROCEDURE Create_Process_Team (org_id IN VARCHAR2, 
> org_desc IN VARCHAR2, org_team IN VARCHAR2, sessioner_id IN VARCHAR2) IS
> 
> ptt_id KCM_PROCESS_TEAM_TEMPLATE.ID%TYPE;
> pt_id KCM_PROCESS_TEAM.ID%TYPE;
> 
> BEGIN
>    
>     select HIBERNATE_SEQUENCE.NEXTVAL into ptt_id from dual;
>     select HIBERNATE_SEQUENCE.NEXTVAL into pt_id from dual;
>    
>     --METADATA
>     --Entry in Process Team Template Table
>     insert into 
> KCM_PROCESS_TEAM_TEMPLATE(ID,VERSION,DESCRIPTION,ORG_FK,NAME) 
> values(ptt_id,0,org_desc,org_id,org_team);
> 
>     --Entry in Process Team Template Type Table
>     insert into KCM_PROCESS_TEAM_TEMPLATE_TYPE(PROCESS_TEAM_TEMPLATE_FK, 
> PROCESS_TYPE_FK) values(ptt_id, 3);
> 
>     --Entry in Process Team Role Table
>     --Sessioner
>     INSERT INTO KCM_PROCESS_TEAM_ROLE(ID, VERSION, ROLE_FK, ORG_TYPE_FK, 
> IS_HIERARCHICAL, PROCESS_TEAM_TEMPLATE_FK, IS_OPTIONAL) 
> values(HIBERNATE_SEQUENCE.NEXTVAL,0,29,2,1,ptt_id,0);
> 
>    
>     --CREATING WORKFLOW TEAM
>     --Entry in Process Team Table
>     INSERT INTO KCM_PROCESS_TEAM (ID, VERSION, DESCRIPTION, NAME, 
> CUSTOMER_RELATIONSHIP_FK, ISTEMPLATE,ORG_FK) VALUES(pt_id, 0, 
> org_desc,org_team, NULL, 1, org_id);
> 
>     --Entry in Team User Table
>     --Insert Sessioner
>     insert into 
> KCM_TEAM_USER(ID,VERSION,USER_FK,ROLE_FK,PROCESS_TEAM_FK) 
> values(HIBERNATE_SEQUENCE.NEXTVAL,0,sessioner_id,'29',pt_id);   
>    
>     --Entry in Team Type Table
>     insert into KCM_PROCESS_TEAM_TYPE values(pt_id,4);
> 
> END;
> /
> 
> On 7/30/07, *Amit Saxena* <amitsaxena69 at gmail.com 
> <mailto:amitsaxena69 at gmail.com>> wrote:
> 
>     It is a complete PL/SQL Procedure which is written in this
>     "metadata.sql" file
> 
>     On 7/30/07, *Greg Lindstrom* < gslindstrom at gmail.com
>     <mailto:gslindstrom at gmail.com>> wrote:
> 
> 
> 
>         On 7/30/07, *Amit Saxena* <amitsaxena69 at gmail.com
>         <mailto:amitsaxena69 at gmail.com>> wrote:
> 
> 
>             I m still not able to run a Procedure
> 
>             from cx_Oracle import makedsn
>             import adodb
> 
>             db = adodb.NewADOConnection('oci8')
>             connection_string = makedsn(" 10.200.91.27
>             <http://10.200.91.27>", 1521, "scorpio")   #<== your values
>             here, of course
>             db.Connect(connection_string, "kcmdev", "devkcm")  #<== and
>             here, too
> 
>             #query = "SELECT * from USER_ROLE where ROLE_FK = 29"
>             results = db.Execute("metadata.sql")
>             print results
> 
> 
>             is giving an error saying
> 
>             Traceback (most recent call last):
>               File "D:/Python/sql.py", line 9, in <module>
>                 results = db.Execute("metadata.sql")
>               File "C:\Python25\Lib\site-packages\adodb\adodb.py", line
>             274, in Execute
>                 c = self._query(sql,params)
>               File "C:\Python25\Lib\site-packages\adodb\adodb.py", line
>             265, in _query
>                 raise sys.exc_info()[0] ,str(err)+': '+sql
>             DatabaseError: ORA-00900: invalid SQL statement
>             : metadata.sql
> 
> 
>         What is in metadata.sql?  If it is a PL/SQL query, then you
>         should "SELECT metadata()".  If is a file containing sql (is the
>         pl/sql you wish to execute in this file?) then I'm not sure how
>         to proceed.
> 
>         Is the PL/SQL you wish to run saved in Oracle?  Can you show us
>         what's in the metadata.sql file?
> 
>         --greg
> 
> 
> 
> 
>     -- 
>     Thanks and Regards,
>     Amit Saxena
>     Senior QA Engineer
>     Ketera
>     Direct: +91 4199 5028
>     Mobile: +91 99001 18641
>     asaxena at ketera.com <mailto:asaxena at ketera.com>
> 
>     » Visit us at http://www.ketera.com
>     » Watch the demo at http://www.ketera.com/resources/demos.html 
> 
> 
> 
> 
> -- 
> Thanks and Regards,
> Amit Saxena
> Senior QA Engineer
> Ketera
> Direct: +91 4199 5028
> Mobile: +91 99001 18641
> asaxena at ketera.com <mailto:asaxena at ketera.com>
> 
> » Visit us at http://www.ketera.com
> » Watch the demo at http://www.ketera.com/resources/demos.html 
> <http://www.ketera.com/resources/demos.html>
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> Tutor maillist  -  Tutor at python.org
> http://mail.python.org/mailman/listinfo/tutor