Help with PLSQL insert syntax
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Help with PLSQL insert syntax

  1. #1
    Join Date
    Mar 2002
    Location
    Manchester, England
    Posts
    202
    Guys,
    I'm pretty new to PL/SQL and I'm haveing trouble with a package I'm trying to write. It should just be a simple package that counts the rows in all tables and inserts the results into a stats table. The error I returns is:-
    ERROR at line 20:
    ORA-06550: line 20, column 37:
    PLS-00201: identifier 'TBLNAME' must be declared
    ORA-06550: line 20, column 3:
    PL/SQL: SQL Statement ignored

    I've tested the variables and they all work its just seems to be looping the count(*) query that gives me the problem. Its worth pointing out that if I define the ROW_CNT variable its populate the table with this value - i just cant get it to dynamialy do it :(

    The table DDL......

    CREATE TABLE CAD_STATS_COUNT (
    DBNAME VARCHAR2(10) NOT NULL,
    TABLE_NAME VARCHAR2(30) NOT NULL,
    ROW_COUNT NUMBER NOT NULL,
    TIMESTAMP VARCHAR2(8) NOT NULL);

    The package is......

    /* Start of PL/SQL procedure */
    /* set serveroutput on size 100000 */

    declare
    /* define variables */
    DBNAME varchar(50); /*cad_stats_count.dbname%TYPE;*/
    TBLNAME varchar(100); /*cad_stats_count.table_name%TYPE;*/
    ROW_CNT number; /*(unt.row_count%TYPE;*/
    TODAY varchar(100); /*cad_stats_count.timestamp%TYPE;*/
    /* cursor declaration */
    CURSOR C1 is select table_name from dba_tables;
    begin
    /* set variables */
    Select name into DBNAME from v$database;
    Select to_char((sysdate),'yyyymmdd') into TODAY from sys.dual;
    /* start cursor loop */
    open C1;
    loop
    fetch C1 into TBLNAME;
    exit when C1%NOTFOUND;
    /* count tables */
    dbms_output.put_line /*to test variables*/(DBNAME||' '||TBLNAME||' '||TODAY);
    Select count(*) into ROW_CNT from TBLNAME;
    insert into cad_stats_count values (DBNAME,TBLNAME,ROW_CNT,TODAY);
    end loop;
    end;
    .

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Hi,
    you have to use dynamic SQL here:

    execute immediate 'Select count(*) from '||TBLNAME into ROW_CNT;

    You tried po pass object name through a bind variable and that's not possible.

    Remember also that tables have their owners and you need to use the owner name along the table name if you want to query tables outside your own schema.

    BTW, you didn't write a package but an anonymous PL/SQL block ...
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  3. #3
    Join Date
    Mar 2002
    Location
    Manchester, England
    Posts
    202
    you beauty!!! \o/
    I used the execute immediate and I also took the driving list from user_tables (as I only wanted tables in my schema) and it works a treat. I realise there are probable better ways to do this but I wanted to learn a little PLSQL.....and I did! Thanks again.

    *chrispy adds ales to christmas card list *

  4. #4
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Originally posted by mrchrispy
    you beauty!!! \o/
    *chrispy adds ales to christmas card list *
    Oh ... thank you ...
    Ales
    The whole difference between a little boy and an adult man is the price of toys

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width