Results 1 to 3 of 3

Thread: Dynamic Sql

  1. #1
    Join Date
    Jun 2003
    Location
    US
    Posts
    10

    Exclamation Unanswered: Dynamic Sql

    I work on oracle 8.1.7.The Procedure below fails at the Fetch. It says
    PL/SQL procedure successfully completed, but i don't get any output result. Fetch does not return any rowsThe query does execute in SQL PLUS.
    Can anyone tell me what's wrong? Thanks in advance.

    set serveroutput on
    CREATE OR REPLACE PROCEDURE SP_TEST_DYNAMIC_SQL(p_WhereString VARCHAR2)
    AS
    v_SqlString VARCHAR2(2000);
    v_CursorID INTEGER;
    v_Dummy INTEGER;
    val1 test.id%TYPE;
    val2 test.name%TYPE;
    BEGIN
    dbms_output.enable(1000000);
    v_CursorID := DBMS_SQL.OPEN_CURSOR;
    v_SqlString := 'SELECT id,name,create_date FROM test where ';
    v_SqlString := v_SqlString || p_WhereString;

    DBMS_SQL.PARSE(v_CursorID,v_SqlString,DBMS_SQL.V7) ;
    DBMS_SQL.DEFINE_COLUMN(v_CursorID,1,val1,2);
    DBMS_SQL.DEFINE_COLUMN(v_CursorID,2,val2,30);
    v_Dummy := DBMS_SQL.EXECUTE(v_CursorID);

    LOOP

    IF DBMS_SQL.FETCH_ROWS(v_CursorID) = 0 THEN
    EXIT;
    END IF;
    DBMS_SQL.COLUMN_VALUE(v_CursorID,1,val1);
    DBMS_OUTPUT.PUT_LINE(val1);
    DBMS_SQL.COLUMN_VALUE(v_CursorID,2,val2);
    DBMS_OUTPUT.PUT_LINE(val2);

    END LOOP;

    DBMS_SQL.CLOSE_CURSOR(v_CursorID);


    EXCEPTION

    WHEN OTHERS THEN
    DBMS_SQL.CLOSE_CURSOR(v_CursorID);

    END SP_TEST_DYNAMIC_SQL;
    /

  2. #2
    Join Date
    Sep 2002
    Location
    Austria
    Posts
    37

    solution provided ...

    -- create "your tname-table .."
    drop table test;
    create table test (id integer,
    name varchar2(10) default 'Pre',
    create_date date default sysdate);

    insert into test (id) values (1);
    insert into test (id) values (12);
    insert into test (id) values (21);
    insert into test (id) values (221);


    set serveroutput on size 20000

    CREATE OR REPLACE PROCEDURE SP_TEST_DYNAMIC_SQL(p_WhereString VARCHAR2)
    AS
    v_SqlString VARCHAR2(2000);
    v_CursorID INTEGER;
    v_Dummy INTEGER;
    vID1 test.id%TYPE;
    vName test.name%TYPE;
    vDate test.create_date%type;

    BEGIN
    dbms_output.enable(1000000);
    v_CursorID := DBMS_SQL.OPEN_CURSOR;
    v_SqlString := 'SELECT id,name,create_date FROM test where ';
    v_SqlString := v_SqlString || p_WhereString;

    dbms_output.put_line('About to execute: ' || v_SQLString);

    DBMS_SQL.PARSE(v_CursorID,v_SqlString,DBMS_SQL.nat ive); -- native instead of v7
    -- here is the ERROR
    DBMS_SQL.DEFINE_COLUMN(v_CursorID,1,vID1); -- no length to be specified
    DBMS_SQL.DEFINE_COLUMN(v_CursorID,2,vName,30);
    DBMS_SQL.DEFINE_COLUMN(v_CursorID,3,vDate);

    v_Dummy := DBMS_SQL.EXECUTE(v_CursorID);
    dbms_output.put_line('returncode after excute : ' || v_dummy);

    while DBMS_SQL.FETCH_ROWS(v_CursorID) > 0 loop
    DBMS_SQL.COLUMN_VALUE(v_CursorID,1,vID1);
    DBMS_SQL.COLUMN_VALUE(v_CursorID,2,vName);
    DBMS_OUTPUT.PUT_LINE(vID1 || ' ' || vName);
    END LOOP;

    DBMS_SQL.CLOSE_CURSOR(v_CursorID);

    END SP_TEST_DYNAMIC_SQL;
    /
    show errors;

    execute sp_test_dynamic_sql ('id>10');

    SQL>
    SQL> execute sp_test_dynamic_sql ('id>10');
    About to execute: SELECT id,name,create_date FROM test where id>10
    returncode after excute : 0
    12 Pre
    21 Pre
    221 Pre

    PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

  3. #3
    Join Date
    Jun 2003
    Location
    US
    Posts
    10
    It worked , Thanks a bunch for your help.

Posting Permissions

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