Home » SQL & PL/SQL » SQL & PL/SQL » get_all records + error PLS-00306 (merged)
get_all records + error PLS-00306 (merged) [message #307679] Wed, 19 March 2008 08:10 Go to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
I have attached a sample script of the procedure now we send one i_sales_pnt_id eg:858 and other input.
Now we want to get for all the i_sales_pnt_id condition remains the same
pls advice me
Re: get_all records [message #307686 is a reply to message #307679] Wed, 19 March 2008 08:43 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
1. either this isn't your actual code, or the code fails when you compile (check the where clause at line 45)
2. You have been posting here long enough to know that you haven't supplied enough info. Supply a test case i.e. create table and insert statements to cover your problem.
3. Based on your description, I'm still not sure what you are asking. (point 2 should help out with that)
Re: get_all records [message #307823 is a reply to message #307686] Thu, 20 March 2008 01:44 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
I have attached a actual script of the procedure now we are sending one i_sales_pnt_id eg:858 and other input.
Now we want to get for all the i_sales_pnt_id condition remains the same pls advice me

EXEC sales_pnt('858','05-MAR-2005',1,:RED)

sales_pnt_id	section	                                                volt_tp_id	inputted_date	transaction_date	result_value
858	Ventes totales KWh par tranches :  Résidentiels > 50 KWh et <=200KWh	1	05-03-2005  00:00:00'	06-01-2007 00:00:00	5
858	Ventes totales KWh par tranches :  Résidentiels > 200KWh	1	05-03-2005  00:00:00'	06-01-2007 00:00:00	25
858	Ventes totales KWh par tranches :  Professionnels	1	05-03-2005  00:00:00'	06-01-2007 00:00:00	3
858	Ventes totales KWh par tranches :  Eclairage public	1	05-03-2005  00:00:00'	06-01-2007 00:00:00	0


  • Attachment: PROC.sql
    (Size: 52.23KB, Downloaded 125 times)
Re: get_all records [message #307879 is a reply to message #307823] Thu, 20 March 2008 04:11 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
No test case, no help.
error pls-00306 [message #307911 is a reply to message #307679] Thu, 20 March 2008 05:56 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
why is this wrong number or types of arguments ?

SQL> desc bsq_pkg
PROCEDURE pnt_id
Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 abc_PNT_ID                 VARCHAR2(4)             OUT
 old_DATE                    DATE                    IN
 TP_ID                        NUMBER(2)               IN
 O_DB_CUR                       REF CURSOR              OUT

SQL> EXEC pnt_id(:sp,'25-MAR-2006',1,:RED)
BEGIN pnt_id(:sp,'25-MAR-2006',1,:RED); END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'pnt_id'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Re: get_all records [message #307913 is a reply to message #307879] Thu, 20 March 2008 05:59 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
what test case do u need ?
Re: error pls-00306 [message #307917 is a reply to message #307911] Thu, 20 March 2008 06:07 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
'25-MAR-2006' is not a DATE, but it's a STRING
Re: error pls-00306 [message #307918 is a reply to message #307911] Thu, 20 March 2008 06:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
'25-MAR-2006' is a string not a date.
And we don't know what is :sp and :red.
And you must format your execution and align the columns.

Regards
Michel
Re: get_all records [message #307921 is a reply to message #307913] Thu, 20 March 2008 06:14 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Quote:
Supply a test case i.e. create table and insert statements to cover your problem.

In short, you need to supply the scripts that will allow me to run and test your procedure.

[Updated on: Thu, 20 March 2008 06:14]

Report message to a moderator

Re: get_all records [message #307932 is a reply to message #307921] Thu, 20 March 2008 07:22 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
i have changed the package and proc
package parameter
(
i_sales_pnt_id out sales_pnt.pk_sales_pnt_id%type,
i_tran_date  date,
i_volt_tp_id    cust.volt_tp_id%type,
o_db_cur  OUT BSAReport_pkg.ref_db_cur
);

procedure allsales_pnt
(
i_sales_pnt_id 	out sales_pnt.pk_sales_pnt_id%type,
i_tran_date		 date,
i_volt_tp_id   	cust.volt_tp_id%type,
o_db_cur 		OUT BSAReport_pkg.ref_db_cur
)
as
v_start_date 			 date;
v_end_date 				 date;
v_inputted_date 		 date;
v_prev_month_consumption number;
v_curr_month_consumption number;
v_count 				 number;
CURSOR C1 IS SELECT pk_sales_pnt_id FROM sales_pnt;
begin
open c1;
fetch c1 into i_sales_pnt_id;
rest of the line or code remains the same

package and procedure created and complied no errors

when executed i am getting error

SQL> EXEC BSAREPORT_PKG.ALLSALES_PNT(:sp,'25-MAR-2006',1,:RED)
BEGIN BSAREPORT_PKG.ALLSALES_PNT(:sp,'25-MAR-2006',1,:RED); END;

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


SQL> desc BSAREPORT_PKG
PROCEDURE ALLSALES_PNT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 I_SALES_PNT_ID                 VARCHAR2(4)             OUT
 I_TRAN_DATE                    DATE                    IN
 I_VOLT_TP_ID                   NUMBER(2)               IN
 O_DB_CUR                       REF CURSOR              OUT
Re: get_all records [message #307933 is a reply to message #307932] Thu, 20 March 2008 07:27 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:
Supply a test case i.e. create table and insert statements to cover your problem.
Re: get_all records [message #307934 is a reply to message #307932] Thu, 20 March 2008 07:28 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
'25-MAR-06' is not a date

(Where) did you define sp and red?
Re: error pls-00306 [message #307936 is a reply to message #307911] Thu, 20 March 2008 07:30 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Don't create multiple post for the same issue!
Locked
Re: get_all records [message #307941 is a reply to message #307934] Thu, 20 March 2008 07:46 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
when i give for only one sales pnt with the same date it works

SQL> set serveroutput on
SQL> VARIABLE red REFCURSOR
SQL> VARIABLE sp REFCURSOR
SQL> EXEC ALLSALES_PNT(:sp,'25-MAR-2005',1,:RED)

CREATE TABLE BILLING_STATISTICS
(
  SALES_PNT_ID      VARCHAR2(3 BYTE),
  SECTION           VARCHAR2(200 BYTE),
  VOLT_TP_ID        NUMBER,
  INPUTTED_DATE     DATE,
  TRANSACTION_DATE  DATE,
  RESULT_VALUE      NUMBER
)
Re: get_all records [message #307942 is a reply to message #307941] Thu, 20 March 2008 07:47 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
date format is correct
Re: get_all records [message #307950 is a reply to message #307942] Thu, 20 March 2008 09:07 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Red and sp are wrong

You pass a REFCURSOR, and a sales_pnt.pk_sales_pnt_id%type is expected.

You pass a REFCROSOR, and a BSAReport_pkg.ref_db_cur is expected.

also '25-MAR-2005' still is a string, no matter what the date format is. Never rely on implicit conversion.

[Updated on: Thu, 20 March 2008 09:08]

Report message to a moderator

Re: get_all records [message #307956 is a reply to message #307950] Thu, 20 March 2008 09:37 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
No the DATE format is NOT correct. Your EXEC statement is sending the procedure a STRING, not a DATE. change '25-MAR-2005' to to_date('25-MAR-2005','DD-MON-RRRR') and see what results you get.

Then review the remaining comments and fix the other problems.
Re: get_all records [message #307964 is a reply to message #307956] Thu, 20 March 2008 10:38 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
where i am going wrong i need to insert all the sales point
i have created package and procedure with no error

EXEC ALLSALES_PNT(:sp,to_date('25-MAR-2005','DD-MON-RRR'),1,:RED)


PL/SQL procedure successfully completed.

But i am not getting what i want unable to insert records into table

as for the info on table i want insert around
154*12*24=44352
pls look into procedure attached procedure.sql in the post this code is a sample code

CREATE OR REPLACE package BSAReport_pkg as
TYPE REF_SRC_CUR IS REF CURSOR;

procedure allsales_pnt
(
SPI_ID OUT  BSAReport_pkg.REF_SRC_CUR,
i_tran_date  date,
i_volt_tp_id    cust.volt_tp_id%type,
 o_db_cur  OUT BSAReport_pkg.ref_db_cur
);

CREATE OR REPLACE procedure allsales_pnt
(
spi_id OUT BSAReport_pkg.REF_SRC_CUR,
i_tran_date		 date,
i_volt_tp_id   	cust.volt_tp_id%type,
o_db_cur 		OUT BSAReport_pkg.ref_db_cur
)
as
v_start_date 			 date;
v_end_date 				 date;
v_inputted_date 		 date;
v_prev_month_consumption number;
v_curr_month_consumption number;
v_count 				 number;
begin
open spi_id for SELECT pk_sales_pnt_id FROM sales_pnt;
Re: get_all records [message #307973 is a reply to message #307964] Thu, 20 March 2008 11:21 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
"I'm not getting what I want" isn't an Oracle error either.

Re: get_all records [message #307982 is a reply to message #307973] Thu, 20 March 2008 11:58 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
no errors in the procedure or package

i am not getting the result i want
Re: get_all records + error PLS-00306 (merged) [message #307983 is a reply to message #307679] Thu, 20 March 2008 12:00 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>i am not getting the result i want
Neither are we (TINW)
Re: get_all records [message #307998 is a reply to message #307982] Thu, 20 March 2008 13:57 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
oracle_coorgi wrote on Thu, 20 March 2008 17:58
no errors in the procedure or package

i am not getting the result i want

So, you want errors in the package?
Re: get_all records [message #307999 is a reply to message #307998] Thu, 20 March 2008 14:07 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Since the only information you gave us about what you do want is

Quote:
as for the info on table i want insert around
154*12*24=44352


The solution is :

Quote:

insert into some_table values(44352);



Man, I'm glad this is finally taken care of.
Previous Topic: Open Cursor Problem
Next Topic: Add partition and Move Data
Goto Forum:
  


Current Time: Sun Dec 11 03:57:11 CST 2016

Total time taken to generate the page: 0.14730 seconds