I am stuck not able to iterate through values output from select statement.

My code :

CREATE OR replace PROCEDURE getdetails ( v_tb in VARCHAR2 )
AS
BEGIN
  FOR i IN
  (
         SELECT table_name
         FROM   all_tables
         WHERE  table_name LIKE ''''
                       || v_tb
                       || '%'
                       || '''' )
  LOOP
       DBMS_OUTPUT.PUT_LINE(i);

       /* 
           My logic will be coming that I cannot share ...
       */

  END LOOP;

END;

Calling

BEGIN
    Getdetails('DEMO');
END; 

Issue : The select statement is not getting initialized with v_tb

SELECT table_name
             FROM   all_tables
             WHERE  table_name LIKE ''''
                           || v_tb
                           || '%'
                           || ''''

Any solution is much appreciated !!!

CodePudding user response：

When I compile your code I get:

14/8      PL/SQL: Statement ignored
14/8      PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
Errors: check compiler log

The reason is that "i" cannot be used as argument in dbms_output because it isn't a scalar value, it is a record variable. The solution is to reference the actual column that is selected in the statement

       DBMS_OUTPUT.PUT_LINE(i.table_name);

The reason that the statement never returns any rows is that the quotes are double escaped. Escape a quote with a 2nd quote. 4 quotes to escape a single quote is too much in a plain pl/sql statement.

So, putting it all together gives:

CREATE OR replace PROCEDURE getdetails ( v_tb in VARCHAR2 )
AS
BEGIN
  FOR i IN
  (
         SELECT table_name
         FROM   all_tables
         WHERE  table_name LIKE ''
                       || v_tb
                       || '%'
                       || '' )
  LOOP
       DBMS_OUTPUT.PUT_LINE(i.table_name);

       /* 
           My logic will be coming that I cannot share ...
       */

  END LOOP;

END;
/

Procedure GETDETAILS compiled

set serveroutput on size 999999
clear screen
BEGIN
    Getdetails('BOO');
END; 
/

BOOK
BOOK_TITLE


PL/SQL procedure successfully completed.
/

CodePudding user response：

If you need quotes in your string, you should write the SQL query as follows:

SELECT table_name
         FROM   all_tables
         WHERE  table_name LIKE ''''
                       || v_tb
                       || '%'
                       || '''some text'''

some text will be concatenated as 'some text'