Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Syntax problem Oracle
Special Forums UNIX and Linux Applications Syntax problem Oracle Post 303000819 by gandolf989 on Thursday 20th of July 2017 09:58:13 AM
Old 07-20-2017
Here is the updated code with the implicit cursor. You can take out the filter "WHERE rownum < 8". Let me know what your grade is. I should get some credit.

Code:
sys@test> CREATE OR REPLACE PROCEDURE TAB_ROW_COUNT
  2  AS
  3   tab_var  VARCHAR2(4000);
  4   ct_var   NUMBER;
  5   CURSOR c1 IS
  6      SELECT table_name
  7        FROM user_tables
  8       WHERE rownum < 8;
  9  BEGIN
 10     FOR tab_var IN c1
 11     LOOP
 12        EXECUTE IMMEDIATE 'SELECT COUNT(*) cnt FROM '||tab_var.table_name INTO ct_var;
 13        DBMS_OUTPUT.PUT_LINE('There are ' || TO_CHAR(ct_var, '999,999') || ' rows in ' || tab_var.table_name || ' table');
 14     END LOOP;
 15  END;
 16  /

Procedure created.

Elapsed: 00:00:00.02
sys@test>
sys@test> EXEC TAB_ROW_COUNT;
There are    4,119 rows in TAB$ table
There are       10 rows in CLU$ table
There are        0 rows in FET$ table
There are        0 rows in UET$ table
There are   12,042 rows in SEG$ table
There are       29 rows in UNDO$ table
There are       13 rows in TS$ table

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

syntax problem

dear friends, I have a large size file containg two fields data like this *** **** 122 222 ***** ***** ***** ***** 232 233 i have file like this. i want to remove blank lines from file . i think awk is servive this problem i wrote a awk command but the error is... (3 Replies)
Discussion started by: rajan_ka1
3 Replies

2. Shell Programming and Scripting

syntax problem

Dear friends, I am writing shell script in csh . i want to make arthimatic operation in csh. i wrote sysntax like this. set val = 230 set tmp = `0.1 * $val + 300` echo $tmp but it is not working . anyone please give me syntax. (3 Replies)
Discussion started by: rajan_ka1
3 Replies

3. UNIX for Dummies Questions & Answers

Oracle like syntax required

m kinda new to unix. i have been trying to write a script where i am trying to switch between users. but the problem is that the syntax like USERNAME/PASSWORD (like oracle SCOT/TIGER) is not working. if i write su USERNAME then the script goes to the command prompt and asks for user to enter... (0 Replies)
Discussion started by: ShellBoy
0 Replies

4. Shell Programming and Scripting

syntax problem grepping?

I am calculating a time and appending a space in front of it to get only certain records in a file because the times are represented in HH:II:SS format and I don't want to see anything other than the actual hour and minute combination (hence appending the space to the front of the time). My... (9 Replies)
Discussion started by: dsimpg1
9 Replies

5. Shell Programming and Scripting

Help for Sed Syntax problem

I have one File named "txt_file" # cat txt_file <DBType>RT</DBType> <AppType>RT</AppType> -------------------------------------------------- I want replace "<AppType>RT</AppType>" to <AppType>XY</AppType> in txt_file and output redirect to Newfile ... (2 Replies)
Discussion started by: SanjayLinux
2 Replies

6. Shell Programming and Scripting

CShell Syntax Problem

Hi guys, Basically I'm trying to write a CShell script that calls an awk script on a given directory (given in command-line). I keep getting a syntax error with my code though: #!/bin/csh set dir = $ARGV foreach file ( $dir/* ) set output = 'awk -f /Desktop/aal $file' echo... (3 Replies)
Discussion started by: ROFL
3 Replies

7. Shell Programming and Scripting

Syntax Problem with awk

Hello, I have perl script,which take some part of data in the file. the below command works fine in normal cmd prompt. `awk '/CDI/ && // && !/Result for/ {print $3 $5 > "final.txt"}' datalist.txt`; `nawk -F"" '{print $2}' finalcdi.txt`; But not working. Please use code tags, thanks. (5 Replies)
Discussion started by: rasingraj
5 Replies

8. Shell Programming and Scripting

Problem with if-else syntax

I'm calling the following if-else from nawk. But I keep getting an error at the "else". I've tried putting more brackets and ; but still I get complaints about the "else". Any ideas ? Thanks, wbrunc BEGIN { FS = "," ; OFS = "," } { if ( $8 ~ /A/ && $9 == B ) $1="4/29/2013" ; $2="J.Doe"... (2 Replies)
Discussion started by: wbrunc
2 Replies

9. Shell Programming and Scripting

awk syntax problem

Hi, I am using this awk command in my shell script : find . -name "*" -ctime -6 | xargs cat | grep -E -v ^fileName\|^\(\) | awk -v DATE="${CURR_DATE}" -v DATE_LOG=$DATE_SYS 'BEGIN {FS=";";OFS=";";CONVFMT="%.9g";OFMT="%.9g"}... (4 Replies)
Discussion started by: abhi1988sri
4 Replies

10. Shell Programming and Scripting

awk problem with syntax

awk -v sw="lemons|dogs" 'NR>100 && NR<200 BEGIN { c=split(sw,a,""); } { for (w in a) { if ($0 ~ a) d]++; } } END { for (i in a) { o=o (a"="(d]?d]:0)","); } sub(",*$","",o); print o; }' /home/jahitt/data.txt what am i doing wrong with the above code? im pretty sure the issue is in the... (6 Replies)
Discussion started by: SkySmart
6 Replies

LEARN ABOUT PHP

db2_procedure_columns

DB2_PROCEDURE_COLUMNS(3)						 1						  DB2_PROCEDURE_COLUMNS(3)

db2_procedure_columns - Returns a result set listing stored procedure parameters

SYNOPSIS

       resource db2_procedure_columns (resource  $connection, string  $qualifier, string  $schema, string  $procedure, string  $parameter)

DESCRIPTION

	Returns a result set listing the parameters for one or more stored procedures.

PARAMETERS

	      o $connection
		- A valid connection to an IBM DB2, Cloudscape, or Apache Derby database.

	      o $qualifier
		- A qualifier for DB2 databases running on OS/390 or z/OS servers. For other databases, pass NULL or an empty string.

	      o $schema
		- The schema which contains the procedures. This parameter accepts a search pattern containing _ and % as wildcards.

	      o $procedure
		- The name of the procedure. This parameter accepts a search pattern containing _ and % as wildcards.

	      o $parameter
		-  The	name of the parameter. This parameter accepts a search pattern containing _ and % as wildcards. If this parameter is NULL,
		all parameters for the specified stored procedures are returned.

RETURN VALUES

	Returns a statement resource with a result set containing rows describing the parameters for the stored procedures matching the  specified
       parameters. The rows are composed of the following columns:

       +---------------------------+---------------------------------------------------+
       |       Column name	   |						       |
       |			   |						       |
       |			   |			Description		       |
       |			   |						       |
       +---------------------------+---------------------------------------------------+
       |      PROCEDURE_CAT	   |						       |
       |			   |						       |
       |			   | The  catalog  that  contains  the	procedure. The |
       |			   | value is NULL if this table does not  have  cata- |
       |			   | logs.					       |
       |			   |						       |
       |     PROCEDURE_SCHEM	   |						       |
       |			   |						       |
       |			   | Name  of the schema that contains the stored pro- |
       |			   | cedure.					       |
       |			   |						       |
       |      PROCEDURE_NAME	   |						       |
       |			   |						       |
       |			   |		  Name of the procedure.	       |
       |			   |						       |
       |       COLUMN_NAME	   |						       |
       |			   |						       |
       |			   |		  Name of the parameter.	       |
       |			   |						       |
       |       COLUMN_TYPE	   |						       |
       |			   |						       |
       |			   |  An integer value representing the  type  of  the |
       |			   | parameter: 				       |
       |			   |						       |
       |			   |	    box, tab (|); c | c | .  T{ Return value   |
       |			   |						       |
       |			   |		      Parameter type		       |
       |			   |						       |
       +---------------------------+---------------------------------------------------+
       |   1 (SQL_PARAM_INPUT)	   |						       |
       |			   |						       |
       |			   |		   Input (IN) parameter.	       |
       |			   |						       |
       |2 (SQL_PARAM_INPUT_OUTPUT) |						       |
       |			   |						       |
       |			   |	      Input/output (INOUT) parameter.	       |
       |			   |						       |
       |   3 (SQL_PARAM_OUTPUT)    |						       |
       |			   |						       |
       |			   |		  Output (OUT) parameter.	       |
       |			   |						       |
       +---------------------------+---------------------------------------------------+
       T} T{ DATA_TYPE

       T} |T{ The SQL data type for the parameter represented as an integer value.

       T} T{ TYPE_NAME

       T} |T{ A string representing the data type for the parameter.

       T} T{ COLUMN_SIZE

       T} |T{ An integer value representing the size of the parameter.

       T} T{ BUFFER_LENGTH

       T} |T{
	Maximum number of bytes necessary to store data for this parameter.

       T} T{ DECIMAL_DIGITS

       T} |T{
	The scale of the parameter, or NULL where scale is not applicable.

       T} T{ NUM_PREC_RADIX

       T} |T{
	An  integer value of either 10 (representing an exact numeric data type), 2 (representing an approximate numeric data type), or NULL (rep-
       resenting a data type for which radix is not applicable).

       T} T{ NULLABLE

       T} |T{ An integer value representing whether the parameter is nullable or not.

       T} T{ REMARKS

       T} |T{ Description of the parameter.

       T} T{ COLUMN_DEF

       T} |T{ Default value for the parameter.

       T} T{ SQL_DATA_TYPE

       T} |T{ An integer value representing the size of the parameter.

       T} T{ SQL_DATETIME_SUB

       T} |T{
	Returns an integer value representing a datetime subtype code, or NULL for SQL data types to which this does not apply.

       T} T{ CHAR_OCTET_LENGTH

       T} |T{
	Maximum length in octets for a character data type parameter, which matches COLUMN_SIZE for single-byte character set data,  or  NULL  for
       non-character data types.

       T} T{ ORDINAL_POSITION

       T} |T{ The 1-indexed position of the parameter in the CALL statement.

       T} T{ IS_NULLABLE

       T} |T{
	A  string  value  where 'YES' means that the parameter accepts or returns NULL values and 'NO' means that the parameter does not accept or
       return NULL values.

       T}

SEE ALSO

       db2_column_privileges(3), db2_columns(3), db2_foreign_keys(3), db2_primary_keys(3), db2_procedures(3), db2_special_columns(3),  db2_statis-
       tics(3), db2_table_privileges(3), db2_tables(3).

PHP Documentation Group 												  DB2_PROCEDURE_COLUMNS(3)
All times are GMT -4. The time now is 12:38 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy