Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Angry Unanswered: stored procedure

    stored procedure


    I wrote some basick procedure to extract data, update data, delete data and de these things based on a ID_field. They all have a PLS-00103 error. Does anyone know what that is?

    Example:
    create or replace procedure gdawkins.GetPBDOPRSs
    (PB_ID IN int,
    OP_OC_ID IN int )
    AS
    BEGIN
    SELECT OP_OC_ID, PB_ID FROM
    PBD_OP_OC
    END;

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    you have a syntax error
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Wink store procedure

    Thanks, any ideas how to clean them up.

  4. #4
    Join Date
    Jul 2004
    Posts
    61
    hi,

    in oder to get data, you should use function return a cursor
    , you can't use proc beause proc doesn't allow return data.
    Example:
    create or replace package cursor_types
    as
    type ref_cursor is ref cursor;
    end;
    /
    create or replace function f_test_cursorin(piNum in int )
    return cursor_types.ref_cursor
    as
    sString varchar2(100);
    answer_cursor cursor_types.ref_cursor;
    begin
    sString := 'Hello with cursor!';
    open answer_cursor for select piNum , sString from dual;
    return answer_cursor;
    end;

    Good success
    Thi Nguyen

  5. #5
    Join Date
    Apr 2004
    Posts
    246
    stop posting false info.
    procedures allow return data. define a param as "in out" and it returns.
    the problem with the initial proc was the lack of an "into" clause on the select
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by garrydawkins
    stored procedure
    Example:
    create or replace procedure gdawkins.GetPBDOPRSs
    (PB_ID IN int,
    OP_OC_ID IN int )
    AS
    BEGIN
    SELECT OP_OC_ID, PB_ID FROM
    PBD_OP_OC
    END;
    Well, you need this changed a little.
    This is for an insert:

    PHP Code:

    create 
    or replace procedure gdawkins.InsertPBDOPRSs
    (  PB_ID      IN int,
       
    OP_OC_ID IN int 
    )
    AS
    BEGIN
        
    insert into PBD_OP_OC 
    (
      
    column1,
      
    column2 )
    values (
      
    OP_OC_ID,
      
    PB_ID );

    END;

    This is for a select statement (this is very rough and only an example):
    PHP Code:

    create 
    or replace procedure gdawkins.InsertPBDOPRSs
    (  PB_ID      IN int,
       
    OP_OC_ID IN int 
    )
    AS

      
    v1 varchar2(100);
      
    v2 varchar2(100);

    BEGIN
        
    select column1
    column2 
    into v1
    v2
    from PBD_OP_OC 
    where column1 
    =  OP_OC_ID 
    and column2 PB_ID;

    dbms_output.put_line ('DATA! = '|| v1 ||'   '|| v2);

    END;

    Last edited by The_Duck; 07-22-04 at 15:20.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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