Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2011
    Posts
    6

    Question Unanswered: How to pass concation string as parameter to a procedure

    Hi,

    I typed following code :

    Declare

    p_Query varchar2(1000);
    Begin
    p_Query:='select "A"||","||"B" from dual';
    somgmodule.dump_query_in_csv(p_Query, 'DIR', 'Pending_HVSO_details.txt');
    end;

    But its giving following error:

    ORA-00904: "B": invalid identifier
    ORA-06512: at "SYS.DBMS_SYS_SQL", line 826
    ORA-06512: at "SYS.DBMS_SQL", line 39
    ORA-06512: at "SOMGMODULE.DUMP_QUERY_IN_CSV", line 20
    ORA-06512: at line 11

    Pls Help....

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Please show us the source code for somgmodule.dump_query_in_csv and please use [code] tags (Details are here: http://www.dbforums.com/misc.php?do=bbcode)

  3. #3
    Join Date
    Apr 2011
    Posts
    6

    Question

    Following is the code in procedure, trying to upload the data from select query in a text file.... If any other method is available other than spooling than pls specify:

    Code:
    CREATE OR REPLACE procedure DUMP_QUERY_IN_CSV (p_Query in VARCHAR2
    ,p_Dir_Name in VARCHAR2
    ,p_File_Name in VARCHAR2)
    as
    l_output UTL_FILE.FILE_TYPE;
    l_theCursor INTEGER;
    l_columnValue VARCHAR2(2000);
    l_status INTEGER;
    l_colCnt NUMBER;
    l_separator VARCHAR2(10);
    l_cnt NUMBER;
    rec_tab DBMS_SQL.DESC_TAB;
    begin
    -- Open file for rewrite
    l_output := utl_file.fopen('DIR', p_File_Name, 'w');
    
    -- parse given query
    l_theCursor := dbms_sql.open_cursor;
    
    dbms_sql.parse(l_theCursor, p_Query, DBMS_SQL.NATIVE);
    
    l_status := dbms_sql.execute(l_theCursor);
    
    -- Get columns description
    dbms_sql.describe_columns(l_theCursor, l_colCnt, rec_tab);
    
    -- Get columns definition
    for i in 1 .. l_colCnt loop
    dbms_sql.define_column(l_theCursor, i, l_columnValue, 2000);
    end loop;
    
    -- Fetch new row from query
    while (dbms_sql.fetch_rows(l_theCursor) > 0) loop
    -- Write all column's value
    for i in 1 .. l_colCnt loop
    dbms_sql.column_value(l_theCursor, i, l_columnValue);
    utl_file.put(l_output, l_separator || l_columnValue);
    l_separator := ';';
    end loop;
    
    utl_file.new_line(l_output);
    l_separator := null;
    end loop;
    
    dbms_sql.close_cursor(l_theCursor);
    
    -- Close output file
    utl_file.fflush(l_output);
    utl_file.fclose(l_output);
    
    
    end DUMP_QUERY_IN_CSV;
    Last edited by snehalw; 04-27-11 at 06:08.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    please use [code] tags to make the code readable:
    Details are here: http://www.dbforums.com/misc.php?do=bbcode

  5. #5
    Join Date
    Apr 2011
    Posts
    6
    Not understanding how the link would help... Pls explain in detail..

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    It explains how you have to use the [code] tags.

    Just edit your post and put a [code] tag before your code and [/code] after your code

  7. #7
    Join Date
    Apr 2011
    Posts
    6
    edit done pls check

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    The procedure looks fine, I assume you have some mixup with the double and single quotes in your code calling the procedure

Posting Permissions

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