Re: dynamic-sql question

From: victor <victor_at_POST.pb>
Date: 5 Nov 1998 11:41:12 GMT
Message-ID: <01be08b1$215d2a60$6b14abcc@victor>

Marc Eilens <eilensm_at_fh-trier.de> wrote in article <3640C61C.6DF41E78_at_fh-trier.de>...
>
> --------------7D092F05C80C6906274DFB24
> Content-Type: text/plain; charset=us-ascii
> Content-Transfer-Encoding: 7bit
>
> > Hi,
>
> I'm sorry, there's sth. missing in my first posting.It should be:
>
>
> I've got a question concerning dynamic sql using the dbms_sql package.
> When may I use sth. as follows
>
> procedure dynamic(table_name IN VARCHAR2)
>

SEE THIS FUNCTION CHECK_PER(DBEG DATE,DEND DATE) RETURN VARCHAR2 IS

CURSOR#       INTEGER;
Kod_per1#            NBU_KOD_FILE.Kod_per%TYPE;

ORA_CHECKPROC_PER# NBU_KOD_PER.CHECKPROC_NAME%TYPE ; RT VARCHAR2(250);
BEGIN  select Kod_per
  into Kod_per1#
 from nbu_kod_file
 where kod_file=kod_file#;

SELECT CHECKPROC_NAME
 INTO ORA_CHECKPROC_PER#
FROM NBU_KOD_PER
WHERE KOD_PER=KOD_PER1#;  IF ORA_CHECKPROC_PER# IS NULL
  THEN RETURN '0#';
 END IF;

         CURSOR#:=DBMS_SQL.OPEN_CURSOR;
         DBMS_SQL.PARSE(CURSOR#,'BEGIN :X :=

'||ORA_CHECKPROC_PER#||'(TO_DATE('''||TO_CHAR(DBEG,'DDMMYYYY')||''',''DDMMYY YY''),TO_DATE('''||TO_CHAR(DEND,'DDMMYYYY')||''',''DDMMYYYY'')); END;',DBMS_SQL.NATIVE);

         DBMS_SQL.BIND_VARIABLE(CURSOR#,':X',LPAD(' ',400,' '));
         RT:=DBMS_SQL.EXECUTE(CURSOR#);
         DBMS_SQL.VARIABLE_VALUE(CURSOR#,':X',RT);
         DBMS_SQL.CLOSE_CURSOR(CURSOR#);
         CURSOR#:=NULL;

 return RT;

EXCEPTION WHEN OTHERS THEN
   if dbms_sql.is_open(cursor#) then
     dbms_sql.close_cursor(cursor#);
   end if;
 RT := ERR.ErrorMessage;
 return '-1#nbu0:Ошибка проверки дат('||rt||')#'; end; Received on Thu Nov 05 1998 - 05:41:12 CST