Re: Move result of select count(*) to a variable of DCl (437 Views)
Reply
Occasional Contributor
cachaza
Posts: 5
Registered: ‎10-18-2012
Message 1 of 3 (476 Views)

Move result of select count(*) to a variable of DCl

Hi. Everyone.

i have this Dcl

 

$ set ver
$ Ws := "Write Sys$output"
$ Com_Totreg == 5
$ sql
  DECLARE :Sql_Totreg INTEGER;
  atta 'f bdcesantia';
  set tran read o;
  select Count(*) into :Sql_Totreg from ctabcuo;
  PRINT :Sql_Totreg ;
  EXIT;
$
$ Ws "Registros Leidos(Com):''Com_Totreg'"
$ Ws "Registros Leidos(Sql):''Sql_Totreg'"
$ EXIT

....the question is : how can "rescue" the result of select count(*) to a variable of Dcl.......i need somethin like this :

"Com_Totreg==Sql_Totreg"

 

Thanks very much.

Honored Contributor
Hein van den Heuvel
Posts: 6,588
Registered: ‎05-19-2003
Message 2 of 3 (468 Views)

Re: Move result of select count(*) to a variable of DCl

I don't think there is a provisioned method.

 

Typically you would redirect the SQL output into a file or pipe, and parse the output.

 

You may want to check out  the RDB facilty to call external function such as LIB$SET_SYMBOL or LIB$SET_LOGICAL

See : http://www.oracle.com/technetwork/products/rdb/external-routines-090694.html

 

hth,

Hein

 

 

 

 

Esteemed Contributor
H.Becker
Posts: 370
Registered: ‎04-09-2009
Message 3 of 3 (437 Views)

Re: Move result of select count(*) to a variable of DCl

The best you can do is to define a job logical name (assumed you have SQL$DATABASE pointing to your database), like:

 

$ pipe ( write sys$output "set display no row counter" ; write sys$output "select '$ def/job Sql_Totreg ',Count(*) from ctabcuo;" )  | mc sql$ | @sys$pipe

$ Sql_Totreg = f$log("Sql_Totreg")

 

Sure enough, you can use a DCL script instead of the two write statements in parentheses.

The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation.