Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2012
    Posts
    2

    Unanswered: Is it possible to use a variable in the FROM clause of a cursor?

    I have a stored procedure where i have the schema and table hardcoded as follows:

    CURSOR MY_TABLE IS
    SELECT *
    FROM AABASE.DOC;

    I am trying to make the procedure more dynamic by prompting the user to enter the schema and table names:

    DECLARE
    v_schema varchar2(40) := UPPER('&SCHEMA');
    v_table_name varchar2(40):= UPPER('&TABLE_NAME');
    .
    .
    -- I know the following does not work work. Is it even possible to have
    -- variable(s) in the FROM clause of a cursor declaration?
    CURSOR MY_TABLE IS
    SELECT *
    FROM <v_schema_name>.<v_table_name>;

    BEGIN
    .
    .
    END;


    Thank you in advance!!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I am trying to make the procedure more dynamic by prompting the user to enter the schema and table names:

    Given that PL/SQL runs deep inside the database engine & has NO direct contact with the outside world, this plan won't get off the ground.

    SQL must be known & defined at compile time.

    GOOGLE "Dynamic SQL" or "EXECUTE IMMEDIATE"
    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.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    CURSOR MY_TABLE IS
    SELECT *
    FROM <v_schema_name>.<v_table_name>;
    It would be possible, if you filled the variables with appropriate ways.(not by prompting)

    But, more difficulty would be how to know the number of columns and names of each columns and using them in your PL/SQL procedure.
    Last edited by tonkuma; 07-16-12 at 18:44.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >It would be possible, if you filled the variables with appropriate ways.(not by prompting)
    Please post reproducible test case that shows above is true.
    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.

  5. #5
    Join Date
    Jul 2012
    Posts
    2
    i want to do the validations on data before entering it into interface table
    like while transfering data from temp table to interface table only if no of records for the same batchname shud not b more than 15 in interface table

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >like while transfering data from temp table to interface table
    more often than not, TEMP table is not required within Oracle application
    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.

  7. #7
    Join Date
    Jul 2012
    Posts
    2
    anacedent, thanks for your advice.

    I went with dynamic SQL and excerpts of the code look as follows:
    --------------------------------------
    DECLARE

    TYPE cur_typ is REF CURSOR;
    MY_TABLE cur_typ;

    v_schema varchar2(40) := UPPER('&TABLE_OWNER');
    v_table_name varchar2(40) := UPPER('&TABLE_NAME');
    v_rows number := 10;

    MY_TABLE_REC AABASE.DOC%ROWTYPE;

    BEGIN
    .
    .
    QUERY_STRING := 'SELECT * FROM ' || v_schema ||'.'|| v_table_name || ' WHERE rownum < :n';
    DBMS_OUTPUT.PUT_LINE (QUERY_STRING);

    OPEN MY_TABLE FOR QUERY_STRING USING v_rows;

    LOOP
    FETCH MY_TABLE INTO MY_TABLE_REC;
    .
    .
    END LOOP;

    ----------------------------------------------------------

    If I could get around the hurdle of having to hardcode the following line, i'd be set:

    MY_TABLE_REC AABASE.DOC%ROWTYPE;

    Any ideas?

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I never said it would be easy.
    read Tonkuma's response from yesterday in this thread.
    While many things are possible, not everything that is possible should actually be done.
    It is possible to implement what you think should be done.
    It is also possible to stick yourself in the eye with a sharp pencil.
    I would advise against doing either action.
    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
    Mar 2007
    Posts
    623
    Quote Originally Posted by Loukis View Post
    If I could get around the hurdle of having to hardcode the following line, i'd be set:

    MY_TABLE_REC AABASE.DOC%ROWTYPE;

    Any ideas?
    Even if you succeeded in creating variable with "dynamic" data type, your next challenge would be dynamically processing it.
    I am not good in imagining which process would be "dynamic" enough. The only one is just simple output its content, which may be achieved by DBMS_SQL.

    It may be implemented similarly to PRINT_TABLE procedure by Tom Kyte. You may check its implementation in this link: http://asktom.oracle.com/pls/apex/f?...:1035431863958

Posting Permissions

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