×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Want to create Store proc on my sql query buy i get error wrong number or types of arguments

Want to create Store proc on my sql query buy i get error wrong number or types of arguments

Want to create Store proc on my sql query buy i get error wrong number or types of arguments

(OP)
Hello I have a created a store proc on a simple store proc, but i get error , please help

CREATE OR REPLACE PROCEDURE p_procedure2 IS
BEGIN

Select

PROVIDER.PROV_ID_NBR,
IDS.PROVIDER_TYPE_SPECIALTY.PROV_TYP_SPCL_USG_CD_SK,
PROVIDER.PROV_FULL_NM,
IDSXREF.PROV_TYP_SPCL_XREF.PROV_TYP_CD
FROM
(Select * from
IDS.PROVIDER @ods_co.BSCA.EDS.COM
WHERE IDS.PROVIDER.SS_CD = 'CAPS'
AND (
IDS.PROVIDER.ODS_INSRT_DT <= trunc(sysdate,'MM')-1
OR
IDS.PROVIDER.ODS_UPDT_DT <= trunc(sysdate,'MM')-1
)) PROVIDER
INNER JOIN IDS.PROVIDER_TAX_IDENTIFIER @ods_co.BSCA.EDS.COM ON (PROVIDER.PROV_SK=IDS.PROVIDER_TAX_IDENTIFIER.PROV_SK)
and IDS.PROVIDER_TAX_IDENTIFIER.SS_CD = 'CAPS'

INNER JOIN (Select MAX(TAX_ID_UPDT_DT)LTIME,PROV_SK from
IDS.PROVIDER_TAX_IDENTIFIER @ods_co.BSCA.EDS.COM
WHERE IDS.PROVIDER_TAX_IDENTIFIER.SS_CD = 'CAPS'
GROUP BY PROV_SK
) L on IDS.PROVIDER_TAX_IDENTIFIER.PROV_SK = L.PROV_SK and IDS.PROVIDER_TAX_IDENTIFIER.TAX_ID_UPDT_DT = L.LTIME

LEFT OUTER JOIN IDS.PROVIDER_EMAIL @ods_co.BSCA.EDS.COM ON (PROVIDER.PROV_SK = IDS.PROVIDER_EMAIL.PROV_SK)
and IDS.PROVIDER_EMAIL.SS_CD = 'CAPS'
INNER JOIN IDS.PROVIDER_TYPE_SPECIALTY @ods_co.BSCA.EDS.COM on (PROVIDER.PROV_SK=IDS.PROVIDER_TYPE_SPECIALTY.PROV_SK)
and IDS.PROVIDER_TYPE_SPECIALTY.SS_CD = 'CAPS'
AND IDS.PROVIDER_TYPE_SPECIALTY.PROV_TYP_SPCL_USG_CD_SK = ( select cd_map_sk from com.cd_map@ods_co.BSCA.EDS.COM where SDN = 'PROVIDER:PROVIDER SPECIALITY USAGE'
AND SRC_CD='0' AND PROV_TYP_SPCL_END_DT IS NULL )
LEFT OUTER JOIN IDSXREF.PROV_TYP_SPCL_XREF @ods_co.BSCA.EDS.COM ON (IDS.PROVIDER_TYPE_SPECIALTY.PROV_TYP_SPCL_SK=IDSXREF.PROV_TYP_SPCL_XREF.PROV_TYP_SPCL_SK)
and IDSXREF.PROV_TYP_SPCL_XREF.SS_CD = 'CAPS'

WHERE

TAX_ID_UPDT_DT IS NOT NULL


;

END p_procedure2;

RE: Want to create Store proc on my sql query buy i get error wrong number or types of arguments

What are you trying to do with this stored procedure?
It just runs a select statement.

What is the error you are receiving?

Regards

T

RE: Want to create Store proc on my sql query buy i get error wrong number or types of arguments

(OP)
Hello thanks for quick reply. I am new to store procedures since my select statement was running slow, i thought I will create Store procedure on my select so that it might help fasten the query. I get below error.

ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'P_PROCEDURE2'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

RE: Want to create Store proc on my sql query buy i get error wrong number or types of arguments

It would help if you would post at least the first part of your procedure along with how you are trying to call it. Your problem is in the arguments you are passing into the procedure, and the error messages don't really help beyond that.

RE: Want to create Store proc on my sql query buy i get error wrong number or types of arguments

Quote (Carp)

Your problem is in the arguments you are passing into the procedure...

...and by procedure definition that Toblerone provided, if he is passing in even one argument, then it will cause the error message...The procedure has no arguments. I'll illustrate:

CODE

create or replace procedure x is
begin
    dbms_output.put_line('This is "Procedure x".');
end;
/

Procedure created.

SQL> exec x

This is "Procedure x".

PL/SQL procedure successfully completed.

SQL> exec x ('1')

BEGIN x ('1'); END;

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

So, in this case, the error comes with a) defining no argument for the procedure, then b) providing an extraneous argument in the invocation.

santaMufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel."

RE: Want to create Store proc on my sql query buy i get error wrong number or types of arguments

Sorry - I did not see the source code in the initial post for some reason.
There are a couple of other problems you are going to run into.

First, with PL/SQL you need to SELECT ... INTO variables of some sort. For instance, if you were just selecting one value, you would need something like

CODE

CREATE OR REPLACE PROCEDURE example_proc AS
   l_provider_id NUMBER;
BEGIN
   SELECT provider_id INTO l_provider
     FROM provider_table
    WHERE provider_name = 'ACME';
END; 
But even here, this procedure is not doing anything. That is why you will probably need to have arguments to pass criteria into your procedure and pass the desired data out of your procedure (or function). Without arguments to pass values in/out of your procedure and not storing data in a table anywhere within the code, it really is not doing anything.

Secondly, if your query is running too slowly, putting it into a procedure is not going to make it run faster. You really need to figure out whether or not you can tune your query to run faster (which might include taking steps such as adding indexes or collecting fresh statistics). All the procedure is going to do is run the same query and impose additional overhead due to context switching.

RE: Want to create Store proc on my sql query buy i get error wrong number or types of arguments

(OP)
Thank you all, I will try the above solution, I am new to store procs in Oracle. I will rephrase my store proc in above format

RE: Want to create Store proc on my sql query buy i get error wrong number or types of arguments

Toblerone,

If you are building a sample procedure from the examples that we posted, above, then we should offer an example that addresses the use of an argument:

CODE

create or replace procedure prn2screen (x varchar2) is
begin
    dbms_output.put_line(x);
end;
/

Procedure created.

begin
    for y in 1..10 loop
        prn2screen(y);
    end loop;
end;
/
1
2
3
4
5
6
7
8
9
10

PL/SQL procedure successfully completed.


 santaMufasa
(aka Dave of Sandy, Utah, USA)
"People may forget what you say, but they will never forget how you made them feel."

RE: Want to create Store proc on my sql query buy i get error wrong number or types of arguments

(OP)
I have created below but i get same error when i execute the procedure. BUt when i remove the table to DUAL it runs fine, when ever i use real table i get error.

CREATE OR REPLACE PROCEDURE ritheshest
IS
Emp_number integer;
BEGIN
SELECT count(distinct BCBS_ASSOC_PLN_CD)
INTO Emp_number
FROM BCBS_ASSOC_PLN_DIM;
insert into ODS
( numbercount) values
(Emp_number);
commit;
END;




ERROR MSG:
BEGIN ritheshest; END;
Error at line 1
ORA-06550: line 1, column 7:
PLS-00905: object RMAKKE01.RITHESHEST is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

RE: Want to create Store proc on my sql query buy i get error wrong number or types of arguments

Toblerone,

How does your session differ from my successful run of your code?:

CODE

SQL> show user
USER is "RMAKKE01"

SQL> select * from BCBS_ASSOC_PLN_DIM;

BCBS_ASSOC_PLN_CD
-----------------
               10
               20
               30
               40
               50
               60
               70

7 rows selected.

CREATE OR REPLACE PROCEDURE ritheshest
 IS
 Emp_number integer;
 BEGIN
 SELECT count(distinct BCBS_ASSOC_PLN_CD)
 INTO Emp_number 
FROM BCBS_ASSOC_PLN_DIM
; 
insert into ODS
 ( numbercount) values
 (Emp_number);
 commit;
 END;
/

Procedure created.

SQL> exec ritheshest

PL/SQL procedure successfully completed.

SQL> select * from ods;

NUMBERCOUNT
-----------
          7 

Your code seems fine to me.

santaMufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel."

RE: Want to create Store proc on my sql query buy i get error wrong number or types of arguments

(OP)
Problem was with access , direct access to the tables...seems for store proc i need direct access to the tables. Now it is working fine. Besides that I am creating this store proc because my query was running very slow I was using DB links to. I was told instead if i use store proc or materialized view its better. Any suggestions?

RE: Want to create Store proc on my sql query buy i get error wrong number or types of arguments

Quote:

I was told instead if i use store proc or materialized view its better.

Storing the results of a long-running query as a materialized view is typically much faster than running the long-running query. But if you must refresh the materialized view on a very regular basis, then you may lose the performance benefits of the materialized view.

The only (typical) performance advantage of stored procedures is that Oracle parses the code once and stores the executable image in the database. With all other things being equal, if a stored procedure contains SQL code only, there is no performance benefit of a stored procedure over the straight SQL.

santaMufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel."

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close