Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2009
    Posts
    7

    Unanswered: Need some help to make a pl/sql procedure

    Hi,

    I'm currently trying to make a procedure.

    This is my code so far (actually I'm not sure that I have quite understand how the code works, so be nice )

    create or replace PROCEDURE etabler_ravare
    IS
    CURSOR c_ravare IS
    SELECT vare_nr, vare_navn
    from arnea06.rv;
    begin
    OPEN c_ravare;
    LOOP
    insert into ravare (ravare_id, beskrivelse);
    EXIT WHEN c_ravare%NOT_FOUND;
    END LOOP;
    CLOSE c_ravare;
    end;

    Ok, what I try to do is to insert all data from table arnea06.rv into my table ravare. This must be a procedure.
    I'm only getting error message: Error(3,1): PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following: ( ; is with authid as cluster compress order using compiled wrapped external deterministic parallel_enable pipelined result_cache The symbol "is" was substituted for "BEGIN" to continue.

    And:
    Error(7,1): PLS-00103: Encountered the symbol "END" when expecting one of the following: . , @ ; return returning <an identifier> <a double-quoted delimited-identifier> group having intersect minus partition start subpartition union where connect sample

    Could any one please help me out here? How should the code look like?

  2. #2
    Join Date
    Jan 2009
    Posts
    17
    try this

    create or replace PROCEDURE etabler_ravare
    IS
    CURSOR c_ravare IS
    SELECT vare_nr,vare_navn
    from arnea06.rv;
    rec_ravare c_ravare%rowtype;
    begin
    OPEN c_ravare;
    LOOP
    fetch c_ravare into rec_ravare;
    EXIT WHEN c_ravare%NOTFOUND;
    insert into ravare (ravare_id, beskrivelse)values(rec_ravare.vare_nr,rec_ravare.v are_navn);
    END LOOP;
    CLOSE c_ravare;
    end;

  3. #3
    Join Date
    Feb 2009
    Posts
    7
    Thanks

    However, now this error message appear:
    Error(13,1): PL/SQL: SQL Statement ignored
    Error(13,83): PL/SQL: ORA-00917: comma missing (I can't see where that comma should be)

  4. #4
    Join Date
    Jan 2009
    Posts
    17
    just a small change in insert statement. Removed the space at
    rec_ravare.v are_navn

    use this now

    create or replace PROCEDURE etabler_ravare
    IS
    CURSOR c_ravare IS
    SELECT vare_nr,vare_navn
    from arnea06.rv;
    rec_ravare c_ravare%rowtype;
    begin
    OPEN c_ravare;
    LOOP
    fetch c_ravare into rec_ravare;
    EXIT WHEN c_ravare%NOTFOUND;
    insert into ravare (ravare_id, beskrivelse)
    values(rec_ravare.vare_nr,rec_ravare.vare_navn);
    END LOOP;
    CLOSE c_ravare;
    end;

  5. #5
    Join Date
    Feb 2009
    Posts
    7
    No big thing then

    I'm not sure that you want the next error messages I get.
    But I'll post them anyway:

    Error(4,8): PLS-00341: declaration of cursor 'C_RAVARE' is incomplete or malformed
    Error(5,1): PL/SQL: SQL Statement ignored
    Error(6,14): PL/SQL: ORA-00942: table or view does not exist
    Error(7,12): PL/SQL: Item ignored
    Error(11,1): PL/SQL: SQL Statement ignored
    Error(11,21): PLS-00320: the declaration of the type of this expression is incomplete or malformed
    Error(13,1): PL/SQL: SQL Statement ignored
    Error(13,70): PLS-00320: the declaration of the type of this expression is incomplete or malformed
    Error(13,81): PL/SQL: ORA-00984: column not alowed here

  6. #6
    Join Date
    Jan 2009
    Posts
    17
    it seems the table you have specified there does no exist.
    check the table names once.

    i checked the procedure on emp table
    its working for me.

    SQL> create or replace PROCEDURE etabler_ravare
    2 IS
    3 CURSOR c_ravare IS
    4 SELECT ename,sal
    5 from emp;
    6 rec_ravare c_ravare%rowtype;
    7 begin
    8 OPEN c_ravare;
    9 LOOP
    10 fetch c_ravare into rec_ravare;
    11 EXIT WHEN c_ravare%NOTFOUND;
    12 insert into empl (ename,sal)values(rec_ravare.ename,rec_ravare.sal) ;
    13 END LOOP;
    14 CLOSE c_ravare;
    15 end;
    16 /

    Procedure created.

  7. #7
    Join Date
    Feb 2009
    Posts
    7
    Hmmm, strange..

    I tried this:
    select *
    from arnea06.rv;

    And got this:
    VARE_NR VARE_NAVN
    ------------------ ----------
    26004 MAIS GRPP
    25501 SOYA

    ***

    35710 Soycomil FC
    31055 BIOMOS

    185 rows selected

    So the table does exist. Any idea what the error may be?

  8. #8
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by Kjetil-A
    So the table does exist. Any idea what the error may be?
    You do not have necessary privileges on that table.
    When used in PL/SQL, you have to have SELECT privilege granted directly; it is not enough to have this privilege granted via role.

    Issue "set role none" before running SQL statement you posted and see its result.

    Have a look at http://asktom.oracle.com/pls/asktom/...:1065832643319 for more detailed explanation.

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Once you get the priviledges completed, try the following

    Code:
    create or replace PROCEDURE etabler_ravare
    IS 
    begin
    insert into ravare (ravare_id, beskrivelse)
    SELECT vare_nr,vare_navn
     from arnea06.rv;
     end;
    which will also copy everything into the ravare table.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  10. #10
    Join Date
    Feb 2009
    Posts
    7
    you have to have SELECT privilege granted directly;
    How can I do that? Is it something I must do in the procedure or in the table I want to copy all data from?

  11. #11
    Join Date
    Feb 2009
    Posts
    7
    I tried to set: set role none;
    But now this message apperas:

    Error message:
    ORA-01031: insufficient privileges
    01031. 00000 - "insufficient privileges"
    *Cause: An attempt was made to change the current username or password
    without the appropriate privilege. This error also occurs if
    attempting to install a database without the necessary operating
    system privileges.
    When Trusted Oracle is configure in DBMS MAC, this error may occur
    if the user was granted the necessary privilege at a higher label
    than the current login.
    *Action: Ask the database administrator to perform the operation or grant
    the required privileges.
    For Trusted Oracle users getting this error although granted the
    the appropriate privilege at a higher label, ask the database
    administrator to regrant the privilege at the appropriate label.

    I realle don't understand what I have to do to make this prodecure work

  12. #12
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by Kjetil-A
    I tried to set: set role none;
    But now this message apperas:

    Error message:
    ORA-01031: insufficient privileges
    I have no idea when did this error come out as you refuse to post the copy/paste output of session running; I do not intend anything to guess.
    Quote Originally Posted by Kjetil-A
    I realle don't understand what I have to do to make this prodecure work
    Did you read the document I linked? It is stated there quite clearly - you have to grant the SELECT ON rv table directly:
    Login as user arnea06.
    Issue GRANT SELECT ON rv TO <the user which owns the etabler_ravare procedure>

    Or maybe better, access only tables in the schema where stored procedure is defined. If you want to access table in another schema, create stored procedure in that schema.

  13. #13
    Join Date
    Feb 2009
    Posts
    7
    I have no idea when did this error come out as you refuse to post the copy/paste output of session running; I do not intend anything to guess.
    I just had to restart the connection the server and then the error message dissapperad again.

    Did you read the document I linked? It is stated there quite clearly - you have to grant the SELECT ON rv table directly:
    Login as user arnea06.
    Issue GRANT SELECT ON rv TO <the user which owns the etabler_ravare procedure>
    Yes, but maybe not good enought

    Or maybe better, access only tables in the schema where stored procedure is defined. If you want to access table in another schema, create stored procedure in that schema.
    Thanks It is now working

Posting Permissions

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