1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Procedure with one parameter - dynamic sql and cursor

Discussion in 'SQL PL/SQL' started by viallos, Mar 16, 2012.

  1. viallos

    viallos Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi Folks

    I have created procedure with one parameter that should display all column_names with their data_types from ALL_TAB_COLUMNS for specified table name which is my parameter, for example:

    Code (Text):
    select column_name, data_type from ALL_TAB_COLUMNS
    where table_name = 'TRANS'
    list what I need.

    My procedure code compiled fine as below.

    Code (Text):
    create or replace procedure kursor_dynam_2 (tabela varchar2) is
    TYPE rcur IS REF CURSOR;
    cur1 rcur;
    Type rec_t is Record (kolumna varchar2(20), typ varchar2(20));
    zap1 varchar2(2000);
    wart1 rec_t;
    BEGIN
    zap1:= 'select column_name, data_type from ALL_TAB_COLUMNS where table_name = ' || tabela;
    OPEN cur1 FOR zap1;
    FETCH cur1 INTO wart1;
    WHILE cur1%FOUND
    LOOP
    Dbms_output.Put_line(wart1.kolumna || wart1.typ );
    FETCH cur1 INTO wart1;
    END LOOP;
    CLOSE cur1;
    END;
    However when I try to run my procedure using below code

    Code (Text):
    SET SERVEROUTPUT ON;
    DECLARE
    tabela varchar2(20);
    Begin
    tabela:='TRANS';
    kursor_dynam_2(tabela);
    End;
    I keep on getting

    Any ideas why?

    I tried couple of different things like changing procedure code in line 8 to

    Code (Text):
    zap1:= 'select column_name, data_type from ALL_TAB_COLUMNS where table_name = ' || '' || tabela|| '';
    or
    Code (Text):
    zap1:= 'select column_name, data_type from ALL_TAB_COLUMNS where table_name = ' || '"' || tabela|| '"';
    but still getting the same.

    Pls help how to pass table_name as a parameter into this procedure.

    Thanks
     
  2. tj.abrahamsen

    tj.abrahamsen Active Member

    Messages:
    10
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    Utah
    Hey there. I don't have a database to work with where I am at, but by looking at your code...try this:

    zap1:= 'select column_name, data_type from ALL_TAB_COLUMNS where table_name = ''' || tabela || '''';

    That is three single quotes on a row before tabela, and then four single quotes on a row after tabela. When you want a single quote inside a string it has to be two of them..representing one in the output.

    Hope this was of any help. :)

    ~ TJ
     
  3. viallos

    viallos Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Thanks for reply.
    I got it work: it is four quotes before and after tabela.

    zap1:= 'select column_name, data_type from ALL_TAB_COLUMNS where table_name = ' || '''' || tabela || '''';
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The orignally posted solution:

    zap1:= 'select column_name, data_type from ALL_TAB_COLUMNS where table_name = ''' || tabela || '''';

    works without further modification and is a 'cleaner' statement.