Re: I just want to run a stored procedure...

On Jun 29, 3:13 am, codefragm...@xxxxxxxxxxxxxx wrote:
On 28 Jun, 17:35, "fitzjarr...@xxxxxxx" <fitzjarr...@xxxxxxx> wrote:





On Jun 28, 11:24 am, codefragm...@xxxxxxxxxxxxxx wrote:

Hi
I have a stored procedure that I want to run.The shell of it is
included at the bottom of this
post. I try running the below in sqlplusw and get the following
result.
Can you just run stored procedures in oracle in the same manner you
can in query analyser
in MS Sql?

thanks

> exec CODE0 (1,1)
BEGIN CODE0; END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'CODE0' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

CREATE OR REPLACE PACKAGE "CODE0" AS
TYPE CUR IS REF CURSOR;
PROCEDURE CODE0 (pCUR1 OUT CUR,
pARG1 IN NUMBER,
pARG2 IN NUMBER);
END CODE0;
GO

CREATE OR REPLACE PACKAGE BODY "CODE0" AS
PROCEDURE CODE0 (pCUR1 OUT CUR,
pARG1 IN NUMBER,
pARG2 IN NUMBER) IS
BEGIN
DECLARE
pLocal1 NUMBER(2);
BEGIN
pLocal2 := ISVISIBILITYENABLED();

OPEN pCUR1 FOR

<--------sql here------->

END;
END CODE0;
END CODE0;
GO

You can't run a package, only the procedures and functions within it:

exec code0.code0(1,1)

And you can't run a procedure or function declared in a package
without using the package name. Unfortunately you've named your
package the same as your procedure; not the best practice to maintain.

David Fitzjarrell- Hide quoted text -

- Show quoted text -

Thanks for the help, nearly there. The problem seems to be the cursor.
I'm at the "hello world" level with oracle (better in MS SQL) so
I guess I just need to declare one? Not sure of the syntax though
Any help appreciated, once I'm past this point then (in my head at
least) I can get on and actually get into Oracle

> exec code1.code1(1,1)
BEGIN code1.code1(1,1); END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to
'ABSENCECODE1'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored- Hide quoted text -

- Show quoted text -

From SQL*Plus:

SQL> variable bob refcursor
SQL> exec code1.code1(bob,1,1)

Should then work.


David Fitzjarrell

.

Relevant Pages

  • Re: I just want to run a stored procedure...
    ... I have a stored procedure that I want to run.The shell of it is ... PROCEDURE CODE0 (pCUR1 OUT CUR, ... I renamed absencecode1 to code1 in this example. ...
    (comp.databases.oracle.server)
  • Re: I just want to run a stored procedure...
    ... I have a stored procedure that I want to run.The shell of it is ... PROCEDURE CODE0 (pCUR1 OUT CUR, ... I'm at the "hello world" level with oracle (better in MS SQL) so ...
    (comp.databases.oracle.server)
  • Re: I just want to run a stored procedure...
    ... I have a stored procedure that I want to run.The shell of it is ... PROCEDURE CODE0 (pCUR1 OUT CUR, ... The problem seems to be the cursor. ...
    (comp.databases.oracle.server)
  • Re: Views vs Stored Procedures, whats the difference?
    ... I hope you are not suggesting you embed SQL queries into the application? ... A stored procedure logic will be exactly as fast as the algorithm you ... I understant that SQL Server supports hints. ... implementations (nestedloop, merge, hash, ..) on decent sized tables, then ...
    (comp.databases.ms-sqlserver)
  • Re: Stored Procedures - Patterns and Practices
    ... >published the reasoning behind its opinions. ... I disagree that the debate in SQL Server related discussion forums ... If the natural key is long or spans too many ... I want to call a stored procedure that adds a customer ...
    (microsoft.public.sqlserver.programming)