Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    2

    Unanswered: Dynamic SQL : Dynamically Declare Variables

    Hi

    I have a problem with Dynamic SQL that I have been trying to solve for the past 2 weeks but to no avail. Please help!

    I am writing a stored procedure that will take in the table name as an input parameter and then to retrieve a particular record based on some criteria, then insert into another table which resides in a different database. Please note that I am dealing with many different databases on different locations so the normal "INSERT INTO TABLEA SELECT * FROM TABLEB" cannot work as it would complain that table or view cannot be found.

    As the table name is dynamic, the method that I am trying is to use the 'Execute Immediate'. But as I need to cater for different tables, I need to know the columns that I am retrieving. Therefore, I have try to dynamically declare the variables but it does not seem to work at all. Below is a sample of what I am trying to achieve:

    Procedure DynamicSelect (input_TableName Varchar2)

    v_Stmt Varchar2;

    BEGIN

    v_Stmt := "DECLARE v_row " || input_TableName || "%RowType;" ;

    v_Stmt := v_Stmt || "BEGIN " ;

    v_Stmt := v_Stmt || "SELECT * Into v_row From " || input_TableName || ";";

    v_Stmt := v_Stmt || "INSERT Into TableB (v_row.column1, v_row.column2.......v_row.columnN) ;";

    v_Stmt := v_Stmt || "END; " ;

    Execute Immedaite v_Stmt;

    END DynamicSelect ;

    Can Dynamic SQL achieve something like this?

    Please help! Thanks for your advice in advance

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Dynamic SQL : Dynamically Declare Variables

    Yes, it should work if you get the syntax right. Your INSERT statement is missing the VALUES keyword, and you cannot use double quotes in PL/SQL, among other errors.

    But in any case, why not make it simpler like this? :-

    Procedure DynamicSelect (input_TableName Varchar2)

    v_Stmt Varchar2(2000);

    BEGIN

    v_Stmt := 'INSERT Into TableB SELECT * FROM ' || input_TableName;

    Execute Immediate v_Stmt;

    END DynamicSelect ;

  3. #3
    Join Date
    Jan 2004
    Posts
    2
    Hi Andrew

    Thanks for your help! But it cannot work as I have try it out. My problem is I am dealing with a few different databases in remote location via DBLinks and I am also not accessing the actual tables but via views.

    Thus, the solution that you have suggested cannot work.

    Therefore, I am trying to find out can I dynamically declare variables using Execute Immediate? I am trying to achieve the following:

    v_Stmt := "DECLARE v_row " || input_TableName || "%RowType;" ;

    v_Stmt := v_Stmt || "BEGIN " ;

    v_Stmt := v_Stmt || "SELECT * Into v_row From " || input_TableName || ";";

    v_Stmt := v_Stmt || "INSERT Into TableB (v_row.column1, v_row.column2.......v_row.columnN) ;";

    v_Stmt := v_Stmt || "END; " ;

    Execute Immedaite v_Stmt;

    But it always gives me problems such as line length overflow? Please help!

    Regards

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    What makes you think that because you are accessing views not tables and using dblinks, the statement must be broken into 2? That conclusion simply does not follow.

    If you are still declaring v_stmt as VARCHAR2 without specifying a length like VARCHAR2(2000) then the default length of 1 will not be long enough to hold your PL/SQL block.

Posting Permissions

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