| How to use IN parameters with in the begin statement.. [message #38691] |
Mon, 06 May 2002 13:45  |
Suchita
Messages: 13
Registered: May 2002
|
Junior Member |
|
|
Hi,
Here is my procedure.
CREATE OR REPLACE PROCEDURE comp1(
schema_name VARCHAR2,
tolerance NUMBER,
no_of_rows OUT NUMBER) AS
NO_OF_ROWS_SELECTED NUMBER(10);
BEGIN
SELECT a.invoice_id,
a.amount,b.amount
FROM schema_name.invoice@x1 a,
schema_name.invoice@x2 b
WHERE
a.invoice_id(+) = b.invoice_id
AND ABS(a.amount-b.amount) /
DECODE( SQRT(POWER(a.amount,2)+POWER (b.amount,2)),0,1,SQRT(POWER(a.amount,2)+POWER(b.amount,2)) )
> tolerance;
NO_OF_ROWS_SELECTED := SQL%ROWCOUNT;
comp1.no_of_rows := NO_OF_ROWS_SELECTED;
DBMS_OUTPUT.PUT_LINE ('No_of_rows_selected='||comp1.no_of_rows);
END;
/
I am getting the following error.
12/6 PL/SQL: SQL Statement ignored
14/13 PLS-00201: identifier 'SCHEMA_NAME' must be declared
If anyone knows how to use it please let me know.
Thanks,
Kumar
|
|
|
|
| Re: How to use IN parameters with in the begin statement.. [message #38692 is a reply to message #38691] |
Mon, 06 May 2002 14:08  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
There are many different problems with your code:
1) You can only use dynamic SQL to handle varying table or column names as you are trying to do here with schema_name.
2) You can only SELECT INTO a variable - in other words, you cannot just have a plain SELECT statement in your procedure.
3) A SELECT INTO can only handle one row. So, SQL%ROWCOUNT will not give you what you are looking for (it will always either be 0 or 1, unless you are doing a bulk collect).
4) To determine the number of rows, you will need a query that contains a COUNT(*), or you will need to use a CURSOR FOR loop to iterate through the result set and count the rows.
5) No need to assign the number to a variable and then the OUT parameter - just assign to the OUT parameter directly.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
