Results 1 to 5 of 5

Thread: DBMS_SQL help

  1. #1
    Join Date
    May 2004
    Posts
    2

    Cool Unanswered: DBMS_SQL help

    Hi,

    I'm very new to writing dynamic SQL and would be grateful if anyone could help me with my problem below.

    I have to write dynamic SQL that will retrieve the number of rows from a given table. I'm unsure of what dbms_sql functions to use. I know i have to use parse, thats quite obvious but can anyone tell me what other ones i should use in order to get my value.

    This the dynamic sql that i have written:
    I have declared v_cursorSB_handler

    v_cursorSB_handler INTEGER := DBMS_SQL.OPEN_CURSOR;


    DBMS_SQL.PARSE(v_cursorBA_handler,
    'SELECT COUNT(ROWNUM) FROM '||v_if_table_BA||
    ' WHERE if_row_batch_num = '|| v_batch_num||
    ' AND if_row_stat = '||v_status||
    ' AND if_name = '||v_if_name,
    DBMS_SQL.NATIVE);

    whats do i write next?

    Thanks!

  2. #2
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    this is how I use dynamic sql

    PHP Code:
    DECLARE
        
    v_sql_query VARCHAR2(4000);
        
    vVar1 VARCHAr2(100);
        
    vVar2 VARCHAr2(100);
        
    TYPE cur_typ IS REF CURSOR;
        
    c1 cur_typ;
    BEGIN
        v_sql_query 
    := 'Select field1, field2 from table';
        
    OPEN c1 FOR v_sql_query;
            
    LOOP
            FETCH c1 INTO vVar1
    vVar2;
                EXIT 
    WHEN c1%NOTFOUND;
                EXIT 
    WHEN c1%NOTFOUND IS NULL;
                
            
    END LOOP;
        
    CLOSE c1;
    END
    There are 10 types of people in the world, those that know Binary and those that don't.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Have you tried looking at the examples in the documentation?

    One thing you are doing wrong is not using bind variables. Instead of concatenating your values into the SQL the smart thing to do is:

    DBMS_SQL.PARSE(v_cursorBA_handler,
    'SELECT COUNT(ROWNUM) FROM '||v_if_table_BA||
    ' WHERE if_row_batch_num = :bv_batch_num'||
    ' AND if_row_stat = :bv_status'||
    ' AND if_name = :bv_if_name',
    DBMS_SQL.NATIVE);

    Then...
    DBMS_SQL.BIND_VARIABLE(v_cursorBA_handler, ':bv_batch_num', v_batch_num);
    ...etc.

  4. #4
    Join Date
    May 2004
    Posts
    2

    Dbms_sql

    Thanks for getting back to me guys, andrew i have done what you suggested, but I am using EXECUTE_AND_FETCH to retrieve the number of rows, is this correct? It should return 3 rows but only returns 1. Am i using the wrong function?

    DBMS_SQL.PARSE (v_cursorSB_handler,
    'SELECT NAME FROM '||v_if_table_SB||
    ' WHERE if_row_batch_num = :BatchNum '||
    ' AND if_row_stat = :status'||
    ' AND if_name = :interface_name', DBMS_SQL.NATIVE);

    DBMS_SQL.BIND_VARIABLE (v_cursorSB_handler,':BatchNum',v_batch_num);
    DBMS_SQL.BIND_VARIABLE (v_cursorSB_handler,':status',v_status);
    DBMS_SQL.BIND_VARIABLE (v_cursorSB_handler,':interface_name',v_if_name);

    v_execute := DBMS_SQL.EXECUTE_AND_FETCH (v_cursorSB_handler);

    All i want is the number of rows retrieved....i have looked on the internet for examples and read lots of documentation but still come to a dead end.

    Kind Regards

    Liddle_Me

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    If you just want the number of rows, use COUNT(*) in the query. EXECUTE_AND_FETCH will only fetch a single row unless you are using arrays. If you use COUNT(*) then the EXECUTE_AND_FETCH will return 1 but the COLUMN_VALUE will return 3.

    Another good place to find examples of DBMS_SQL in use is Ask Tom. Search for DBMS_SQL. Be sure to look at the examples written by Tom Kyte in his answers rather than those in questions!

Posting Permissions

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