Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2007
    Posts
    4

    Unanswered: Please help me.....PLSQL procedure

    SQL> SELECT * FROM ERP_PROFILE;

    PROFILEID PROFILECODE PROFILENAME USERCODE
    ---------- ----------- ------------ -------------
    1001 201 admin 11
    1002 202 manager 12
    1003 203 assistant 13
    1004 204 clerk


    SQL> DESC ERP_PROFILE;

    Name Null? Type
    --------------------- -------- -------------
    PROFILEID NUMBER
    PROFILECODE NOT NULL NUMBER
    PROFILENAME VARCHAR2(50)
    USERCODE NUMBER


    Hi,
    i want to create the package specification
    and package body for the select statement using
    procedure.how to create please help me. i am
    novice to oracle database 10g.I want to these
    statement....
    SELECT * FROM ERP_PROFILE;
    SELECT PROFILEID,PROFILECODE,PROFILENAME FROM ERP_PROFILE;

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    OK, so what did you already do? Did you write any code? If so, what is wrong with it? Even if it is not successfully created, type SHOW ERRORS at the SQL*Plus prompt and you'll see the errors. Please, post them here so that we could help you.

    Here is the PL/SQL User's Guide and Reference; chapter 9 describes use of packages. Depending on how wide your knowledge is, you might need to read more (or less) of the book.

  3. #3
    Join Date
    Feb 2007
    Posts
    4
    Hi,
    Already i have created INSERT,DELETE,UPDATE command,
    But i couldn't work in SELECT statement....
    Plz how to create the package specification
    and package body for the select statement using PLSQL
    procedure.
    Plz help me....

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'm not in the mood to write the whole package for you. Once again, please, post your code here so that we could see what is wrong and help you.

  5. #5
    Join Date
    Feb 2007
    Posts
    5
    This is package might satisfy ur requirement have look at it

    CREATE OR REPLACE PACKAGE PKG_ERP_PROFILE IS

    -- Public type declarations
    type pkg_ref_cursor is REF CURSOR;

    PROCEDURE STP_GET_ERP_PROFILE
    (
    p_out_list OUT pkg_ref_cursor
    );
    end PKG_ERP_PROFILE;

    CREATE OR REPLACE PACKAGE BODY PKG_ERP_PROFILE IS

    PROCEDURE STP_GET_ERP_PROFILE
    (
    p_out_list OUT pkg_ref_cursor
    )
    IS
    OPEN p_out_list FOR
    SELECT
    PROFILEID,
    PROFILECODE,
    PROFILENAME
    FROM ERP_PROFILE;
    EXCEPTION
    WHEN OTHERS THEN
    RAISE;
    END STP_GET_ERP_PROFILE;

    END PKG_ERP_PROFILE;



    Procedure "STP_GET_ERP_PROFILE" input as ref cursor which can be a result set if u are using front end application like JAVA to invoke this proc...

  6. #6
    Join Date
    Feb 2007
    Posts
    4
    Hi,
    Thanks for ur help.....

  7. #7
    Join Date
    Feb 2007
    Posts
    4
    Hi,
    I have run the PACKAGE BODY
    I got one error..


    ERROR
    -----------------------------------------------------------------
    PLS-00103: Encountered the symbol "FOR" when expecting one of the
    following:
    := . ( @ % ; not null range default character

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    For advice, you can have it good, fast or cheap. Pick any two.
    With free advice, you get what you paid for it.
    You'd be better off actually learning PL/SQL, instead of depending upon others to do you home work assignments for you.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Feb 2007
    Posts
    5
    I agree with anacedent... as the error you got is very basic ..
    u can find BEGIN keyword is missing


    PROCEDURE STP_GET_ERP_PROFILE
    (
    p_out_list OUT pkg_ref_cursor
    )
    IS
    BEGIN
    ....
    ...
    ..
    .

Posting Permissions

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