Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

executing procedure in toad

858631Aug 20 2012 — edited Aug 20 2012
Hi Guru's

I am new to plsql please guide me

I am using toad 9.7.2

I have created a sample procedure - Goal when i pass the table name the procedure should give the count
of that table .
below is the procedure code
CREATE OR REPLACE PROCEDURE test_count(p_table_name IN VARCHAR2,
                                       p_cnt        OUT NUMBER,
                                       p_err_msg    OUT VARCHAR2) IS

    v_sql VARCHAR2(1000);
BEGIN
    v_sql := 'select count(1) from ' || p_table_name;
    EXECUTE IMMEDIATE v_sql
        INTO p_cnt;
        
EXCEPTION
    WHEN OTHERS THEN
        p_err_msg := 'Exception : ' || SQLERRM;
END;
My doubts
--------------
1)when i execute the procudure F5 is say procedure created .
2) when i try to see the output in toad I am stuck
i try to execute my procedure like this method to see theoutput even i gave dbms_output.put_line in my code

exec test_count; (F5) to execute
exec test_count ( 'employees'); -- trying to pass the table name employees which has 20 record but gets error

ERROR

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

I tried this too but same error

Begin
test_count;
END;


how to execute the procedure in TOAD ? and how to see the source code in toad? Please suggest

I tried this but can see only no rows return.... but the procedure is created
SELECT   text  FROM user_source where name = 'test_count'
or 
SELECT text FROM all_source WHERE name= 'test_count'
Thanks
Suresh
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 17 2012
Added on Aug 20 2012
6 comments
7,019 views