tablename as input-parameter for procedure 1

[hudo]

I'd like to have the tablename as a parameter for a procedure:

CREATE OR REPLACE
PROCEDURE myproc( p_table VARCHAR2 )

AS

CURSOR c_forcolumn (p_table VARCHAR2)
IS
SELECT alpha.ROWID ,alpha.scope_column
FROM p_table alpha
ORDER BY scope_column;


BEGIN

NULL;

FOR rec IN myproc(the_scope_table)
LOOP
IF (rec.scope_column LIKE 'ABC___' ) THEN
---do something
UPDATE p_table
SET scope_column = 'XYZ'
WHERE rec.ROWID = p_table.ROWID;

NULL;
END IF;

END LOOP;

END myproc;

Is there a way to implement this ?

 

[lewisp]

Use a ref cursor.

 

[hudo]

Can you post me please an example ?

 

[lewisp]

How about:

DECLARE
  TYPE test_cursor_type IS REF CURSOR;
  test_cur test_cursor_type;
--
  l_rowid VARCHAR2(20);
  l_scope VARCHAR2(100);
BEGIN
  OPEN test_cur FOR 'SELECT alpha.ROWID,' ||
                           'alpha.scope_column ' ||
                    'FROM  ' || p_table  || ' alpha ||
                   ' ORDER BY scope_column';

--
  LOOP
    FETCH test_cur INTO l_rowid,
                        l_scope;
--
    EXIT WHEN test_cur%NOTFOUND;
--
    EXECUTE IMMEDIATE 'UPDATE ' || p_table ||
                     ' SET    scope_column = ''XYZ'' ' ||
                      'WHERE  ROWID = ''' || l_rowid;

  END LOOP;
--
  CLOSE test_cur;
END;

 

[hudo]

@Many thanks to lewisp. Here is my code:

CREATE OR REPLACE
PROCEDURE DYN_KENR_SWITCH ( in_table VARCHAR2)

AS

l_number	NUMBER(8);
l_counter	NUMBER(12); 

l_rowid			VARCHAR2(100);
l_rownum		NUMBER;
l_kenr	        VARCHAR2(14); 



TYPE alpha_type	IS REF CURSOR;

alpha_rec	alpha_type;

BEGIN 

l_counter := 0;

OPEN alpha_rec FOR
'SELECT  ROWID, ROWNUM ,KENR FROM '||in_table;

LOOP
	FETCH alpha_rec INTO l_rowid ,l_rownum ,l_kenr;
	
	EXIT WHEN alpha_rec%NOTFOUND;
	l_counter := l_counter +1;	
	IF ( l_kenr IS NOT NULL ) THEN	
		IF ( l_kenr LIKE '________222004' ) THEN		
			l_number := SUBSTR(l_kenr, 1 ,8);			
		  	EXECUTE IMMEDIATE 'UPDATE '||in_table||' SET KENNUNG = ''222004'||l_number||''' WHERE ROWID = '''||l_rowid||'''';			
		END IF;		
		IF ( l_counter > 1000 ) THEN
		  	l_counter := 0;

			INSERT INTO PROTOKOLL_TEST (tabelle, records, times)
			VALUES( in_table ,l_rownum ,sysdate);
			COMMIT;
		END IF;
	END IF;
	
END LOOP;

NULL;
COMMIT;
END DYN_KENR_SWITCH;

 

[bill4syth]

I'm attempting something similar - need to pass in a parameter(s) to be used in an IN statement in the where clause (comming from .NET). I have tried strings into a normal cursor i.e.:
'''id1'',''id2'',''id3'''
to no availe.
I attemped the solution given by lewisp but recieve error 904,
Here is a stripped down version of my code (it's within a package):

procedureSelect ( in_string IN VARCHAR2)
IS
type cust_cur_type IS REF CURSOR;
cust_cur cust_cur_type;
l_cust_id number;

Begin
Open cust_cur For 'Select cust_id from customer_table where cust_id in
(' || in_string || ')';

Loop
Fetch cust_cur into l_cust_id;
Exit when cust_cur %NOTFOUND;
--code
End LOOP;
End;

I recieve the 904 error at the OPEN cursor line.
I have also tried making the parens as part of the in_string as well, so it would look like:
'(''id1'',''id2'',''id3'')'

I get the feeling it doesn't even like the formation of the ref cursor (as a string itself) - it seems to bomb even before attempting to open it (but obviously it was done in those previous examples) This is 9i btw.

Thanks for any help

 

[sem]

I think there's nothing wrong with the code. The error is in parameter passed. Without quotes id1-id3 are treated as identifiers or, according to the syntax (as almost nothing else may be used within standalone query), as column names. You should quote them properly.

BTW, do you really have cust_id='id1'? If you suppose that your procedure may read the value of variable id1 declared somewhere else you're mistaken

Regards, Dima

http://seminihin.narod.ru

 

[bill4syth]

I have found a solution (may not be the best but it seems to work).

In this particular usage there are actually 3 different parameters that
need to allow for IN statements (not just one in my example above).

I am now forming the Select statement on the client side and passing
that in as a varchar2 parameter.
I then open a ref cursor on that parameter - seems to work well.

Not all that elegant and I don't like handling the SQL on the client but oh well.

 

[bill4syth]

sem,

I'm pretty confident I quoted them correctly, this is how I set up a string in testing which included the parens:
in_string = '(''id1'',''id2'',''id3'')'

surrounding the IDs are two single quotes so that they would be recogized as single quotes.

FYI - this is just a basic example - not the actual code (the param names are too ugly to show actual code )
so no, nothing like "id1" for IDs

Thanks again

 

[sem]

This is quite insecure solution as anybody may use your procedure to execute any SQL command.

Regards, Dima

http://seminihin.narod.ru

 

[bill4syth]

Sem,
In the average package I would agree however in my scenario they would need extensive knowledge of the package to make sure it didn't bomb.
It is only for a ref cursor which controls the flow of the package.

ie.
open cursor_name for input_select_string;
LOOP
Exit when...
Fetch cursor_name into LOOKUPID
do code using lookupid
END LOOP;

Can you put dml into a ref cursor? - obviously I'm not a dba. It is also a fairly closed/secure environment.

 

[sem]

The message was that nothing prevents you from building the query within SP as you initially planned. Just do it correctly.

Regards, Dima

http://seminihin.narod.ru

 

[bill4syth]

Sem,
Don't know if you saw my earlier post but again I'm pretty sure I quoted those strings correctly.
Give an example if you see that I am doing this incorrectly.

 

[sem]

What is cust_id datatype?

Regards, Dima

http://seminihin.narod.ru

 

[bill4syth]

varchar2

 

[sem]

create or replace procedure procedureSelect (in_string IN VARCHAR2)
IS
type cust_cur_type IS REF CURSOR;
cust_cur cust_cur_type;
l_cust_id  number;

Begin
Open cust_cur For 'Select cust_id from customer_table where cust_id in (' || in_string || ')';

Loop
 Fetch cust_cur into l_cust_id;
 Exit when cust_cur %NOTFOUND;
 --code
End LOOP;
End;

and to invoke it:

var par varchar2(100)
exec :par := '''abc'',''bcd'''
exec procedureSelect(:par)

Regards, Dima

http://seminihin.narod.ru

 

[bill4syth]

Exactly what I originaly had.
thanks anyway.

 

[bill4syth]

I should say that it's not exactly what I have in my exampl - mine included the parens - however I also tried your suggestion

 

[sem]

I your code doesn't work then it differs from mine. Here's working example based on scott.emp table.

SQL> create or replace procedure procedureSelect (in_string IN VARCHAR2)
  2  IS
  3  type emp_cur_type IS REF CURSOR;
  4  emp_cur emp_cur_type;
  5  l_empno  number;
  6  Begin
  7  Open emp_cur For 'Select empno from emp where ename in (' || in_string || ')';
  8  Loop
  9   Fetch emp_cur into l_empno;
 10   Exit when emp_cur %NOTFOUND;
 11   --code
 12  End LOOP;
 13  End;
 14  /

Procedure created.

SQL> var par varchar2(100)
SQL> exec :par := '''JONES'',''SCOTT'''

PL/SQL procedure successfully completed.

SQL> exec procedureSelect(:par)

PL/SQL procedure successfully completed.

The error may be somewhere else, e.g. wrong column name (cust_id). In any case if you can build the whole statement outside you should be able to do it inside.

Regards, Dima

http://seminihin.narod.ru