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

[toblerone65]

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 = 'PROVIDERROVIDER 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;

 

[Thargy]

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

 

[toblerone65]

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

 

[carp]

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.

 

[SantaMufasa]

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:

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.

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

 

[carp]

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

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.

 

[toblerone65]

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

 

[SantaMufasa]

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:

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.

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

 

[toblerone65]

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

 

[SantaMufasa]

Toblerone,

How does your session differ from my successful run of your 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.

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

 

[toblerone65]

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?

 

[SantaMufasa]

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.

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