GridView DeleteCommand Erroring on StoredProcedure?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David R. Longnecker - 23 Jan 2006 17:08 GMT

I'm attempting to call a stored procedure to delete a grouping of records
based on the GridView's DeleteCommand; however, I constantly receive an
error message from Oracle that, after searching through Google and Meta,
does not provide much insight:

ORA-06550: line 1, column 7:
PLS-00801: internal error [22503]
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

The Code:
DeleteCommand="KPR_DELETE(:report_id)" DeleteCommandType="StoredProcedure"

<DeleteParameters>
<asp:FormParameter FormField="report_id" Name="report_id" Type="string"
/>
</DeleteParameters>

The Stored Procedure:

1 CREATE OR REPLACE PROCEDURE KPR_Delete (ReportID IN VARCHAR)
2 IS
3 BEGIN
4 DELETE FROM REPORTS_DETAILS where report_id = TO_NUMBER(ReportID);
5 DELETE FROM REPORTS where report_id = TO_NUMBER(ReportID);
6 COMMIT;
7 END;

I can run the stored procedure just fine from SQLPlus using : "exec
KPR_Delete (123);"; however, the error continues to pop-up when running it
through the web page. Is there a particular way to pass these variables
from the GridView that I'm missing?

Thanks in advance!

-David

Signature

David R. Longnecker
CCNA, MCSA, Network+, A+
Management Information Services
Wichita Public Schools, USD 259

Reply to this Message

Kevin Yu [MSFT] - 24 Jan 2006 02:12 GMT

Hi David,

The ORA-06550 is a compilation error. But the stored procedure seems to be
fine. Could you try to add a colon before report_id in the parameter name
like the following?

<DeleteParameters>
<asp:FormParameter FormField="report_id" Name=":report_id"
Type="string"
/>

Kevin Yu

Signature

=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Reply to this Message

David R. Longnecker - 24 Jan 2006 16:01 GMT

With the added colon on the parameter, I receive the following error.

Updated code:

<DeleteParameters>
<asp:FormParameter FormField="report_id" Name=":report_id" Type="string" />
</DeleteParameters>

Error:
--
ORA-06550: line 1, column 42:
PLS-00103: Encountered the symbol ":" when expecting one of the following:

( - + case mod new not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall merge time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
The symbol ":" was ignored.

Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.

Exception Details: System.Data.OracleClient.OracleException: ORA-06550: line
1, column 42:
PLS-00103: Encountered the symbol ":" when expecting one of the following:

( - + case mod new not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall merge time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
The symbol ":" was ignored.
--

Thanks!

-David

> Hi David,
>
[quoted text clipped - 11 lines]
> "This posting is provided "AS IS" with no warranties, and confers no
> rights."

Reply to this Message

Kevin Yu [MSFT] - 25 Jan 2006 06:43 GMT

Hi David,

Have you tried using some trace tool of Oracle to see what SQL statement is
being called on the server?

Kevin Yu

Signature

=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Reply to this Message

David R. Longnecker - 25 Jan 2006 15:29 GMT

To both of the trace questions, I'll see what I can do. Personally, I just
have SQL Plus; however, I'm sure we have someone with the Enterprise Suite
around here somewhere.

I'll post more when I have it.

-David

> Hi David,
>
[quoted text clipped - 6 lines]
> "This posting is provided "AS IS" with no warranties, and confers no
> rights."

Reply to this Message

Kevin Yu [MSFT] - 26 Jan 2006 02:43 GMT

Thank you David, please post back when you have the trace done.

Kevin Yu

Signature

=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Reply to this Message

David R. Longnecker - 26 Jan 2006 16:01 GMT

Apparently we do not have any tools in our array that can provide the same
output as the SQL Profiler that I'm used to with SQL Server. Is there any
other way to gather this information for Oracle? (though perhaps a question
best targeted for an Oracle group (^_~))

Thanks!

-David

> Thank you David, please post back when you have the trace done.
>
> Kevin Yu
> =======
> "This posting is provided "AS IS" with no warranties, and confers no
> rights."

Reply to this Message

Kevin Yu [MSFT] - 27 Jan 2006 04:38 GMT

Hi David,

^_^ I'm not quite familiar with the tools to do trace for Oracle. But I
remember that there is one with the older versions. And I believe there
should be one in the current version. Maybe it's an optional component in
the installation disc and you can try to install it again.

Kevin Yu

Signature

=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Reply to this Message

GridView DeleteCommand Erroring on StoredProcedure?

Free Magazines