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

[Amit Saxena]

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> 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> wrote:
> >
> >
> >
> > On 7/30/07, Amit Saxena <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 ", 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
>
> » 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

» Visit us at http://www.ketera.com
» Watch the demo at http://www.ketera.com/resources/demos.html
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mail.python.org/pipermail/tutor/attachments/20070730/693bd171/attachment.htm