Creating Stroed Proc error 3

[crazyboybert]

Hi

I am trying to create a stored proc using sql*plus with the follwing code

CREATE OR REPLACE PROCEDURE GetTableRowCount(sTable in varchar2(8)) 
IS 
BEGIN

select ROW_COUNT from DLETBLID where TABLE_NAME = sTable;
     
END GetTableRowCount;

I get the error:

Warning: Procedure created with compilation errors.


Any help greatly appreciated

Rob


------------------------------------

Go placidly amidst the noise and haste,
find what peace there may be in silence.
-Anon

------------------------------------

 

[BJCooperIT]

It is only a guess since you did not post the error you received but try removing the length:

CREATE OR REPLACE PROCEDURE 
    GetTableRowCount(sTable in varchar2)

If running the script in SQL*Plus, type:

show error

Good Luck!

[sup]Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw[/sup]
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000
[sup]When posting code, please use TGML to help readability. Thanks![sup]

 

[ociruj]

hi,

"select ROW_COUNT from DLETBLID where TABLE_NAME = sTable;"

should be:

select ROW_COUNT into <VARIABLE> from DLETBLID where TABLE_NAME = sTable;

regards

 

[JediDan]

Honestly, your procedure doesn't make a whole lot of sense...

1. Parameter variables cannot have a length. e.g. your input parameter should read &quot;sTable in varchar2&quot;.
2. You don't store the &quot;ROW_COUNT&quot; value in a variable, and you have no output facility so you'll never see it.

If you want to find out how many rows are in a table, you really need only this:

SELECT COUNT(*)
FROM <table_name>

I don't think putting it into a PL/SQL procedure is going to make it faster. However, if you DID want to use a stored procedure, you would have to use dynamic SQL, which would look something like this:

CREATE OR REPLACE
FUNCTION table_rowcount
(
table_name in VARCHAR2
)
RETURN INTEGER
is

rowcount INTEGER;

BEGIN
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || table_name
INTO rowcount;

RETURN rowcount;
END;
/

Good luck.

 

[crazyboybert]

Thankyou all very much for your help. Using the advice provided I have managed to get a stored proc up and running and successfully returned some data.

JediDan - I'm afraid it doesnt make much sense as I come from a MSSQL background so only have TSQL experiencen and am feeling my way with Oracle. Have been scrabbling somewhat to reconclie the syntatical differences and also the language feature differences. Am now having 'some' success though. I appreciate your advice regarding count(*) but you misinterpret my intentions. I am attempting to spoof a numeric identity column for each of my tables - a feature which is seemingly unavailable in Oracle (or certainly with an auto increment field anyway) and am using another table to manage table row ids....Is there are more suitable way to recreate this functionality in Oracle. Am i right in thinking there is no facility to create a table with

CREATE TABLE(id identity(1,1))

where column 1 is an auto incrementing column starting at 1 and incrementing by 1 with the field name 'id'....?


Thank you all again for your help.


Rob


------------------------------------

Go placidly amidst the noise and haste,
find what peace there may be in silence.
-Anon

------------------------------------

 

[jee]

You don't need to use dynamic SQL for a SELECT COUNT (*)... type statement.

You only need to use dynamic SQL in PL/SQL for

1) DDL statements

2) DML statements that are supported by the database, but are not yet supported by PL/SQL - for Oracle 8i, most of the analytical functions fall into this category, the CASE syntax for SELECTS as well, and a few others.

3) DML statements where you want to vary the syntax of the statement depending on input parameters.

You can create numeric unique ids for table rows using the Sequence Generator feature. Read up on this in the Oracle Application Developers Guide - Fundamentals. A link follows:

http://download-west.oracle.com/docs/cd/A87860_01/doc/appdev.817/a76939/toc.htm

See chapter 2, around page 2-29.

 

[JediDan]

Just about anything can be done in Oracle, but it's not always immediately apparent:

You'll want to create a table:

CREATE TABLE table_name (col_name col_type(col_length))

i.e.:

CREATE TABLE myTable (id number(5,0))

The (5,0) says it a number with 5 places, none of which are to the right of the decimal point.

That just gets you a table with a column called &quot;id&quot; with 5 places. Then, you'll want to create a sequence, which is the facility to generate the numbers automatically. Sequences in Oracle aren't explicity linked to the table, you create them and the INSERT the NEXTVAL from them whenever adding rows to your table. Sequence creation syntax in your case might look something like:

CREATE SEQUENCE id_seq
 INCREMENT BY 1
 START WITH 1
 MINVALUE 1
 MAXVALUE 999999999999999999999999999

Use the value when adding rows to the table, such as:

INSERT INTO myTable
VALUES (id_seq.NEXTVAL)

Hope this helps.

 

[crazyboybert]

Thanks again for your help.

Jee - excellent Oracle reference - Thank You.

JediDan - Exactly[\b] what i needed - Thank You.

Rob

------------------------------------

Go placidly amidst the noise and haste,
find what peace there may be in silence.
-Anon

------------------------------------