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 303000823 by bdby on Thursday 20th of July 2017 11:21:25 AM
Old 07-20-2017
Hi Cero,

Thanks for your help on this. You were in the ball park and I used your execute immediate statement. Here is the final solution that I was looking for.

Code:
CREATE OR REPLACE PROCEDURE TAB_ROW_COUNT 
AS
 ct_var  number;
 tab_rec  varchar2(4000);
 cursor c1 is 
 select table_name from user_tables ;
 c1_rec  c1%ROWTYPE;
 BEGIN
 OPEN c1;
 loop
  fetch c1 into c1_rec;
  exit when c1%NOTFOUND;
   execute immediate 'select count(*) from '||c1_rec.table_name into ct_var;
   
  dbms_output.put_line('There are '||TO_CHAR(ct_var,'999,999')||' rows in '||c1_rec.table_name||' table.'); 
  end loop;
 close c1;
 END;
/

output:
Code:
SQL> execute tab_row_count;
There are        4 rows in REGIONS table.
There are       25 rows in COUNTRIES table.
There are       23 rows in LOCATIONS table.
There are       27 rows in DEPARTMENTS table.
There are       19 rows in JOBS table.
There are      107 rows in EMPLOYEES table.
There are       10 rows in JOB_HISTORY table.
  
PL/SQL procedure successfully completed.

This output would have been the same return as the select statement:

select table_name, num_rows from user_tables;

Yes I am a OCP for the last 15 years and don't get to do any of this and am learning this on my own...again.

It was fun.
Last edited by Scott; 07-20-2017 at 12:32 PM.. Reason: Please use code tags for output, too. Thanks.
 

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 DEBIAN

sql::reservedwords::oracle

SQL::ReservedWords::Oracle(3pm) 			User Contributed Perl Documentation			   SQL::ReservedWords::Oracle(3pm)

NAME

       SQL::ReservedWords::Oracle - Reserved SQL words by Oracle

SYNOPSIS

	  if ( SQL::ReservedWords::Oracle->is_reserved( $word ) ) {
	      print "$word is a reserved Oracle word!";
	  }

DESCRIPTION

       Determine if words are reserved by Oracle Database.

METHODS

       is_reserved( $word )
	   Returns a boolean indicating if $word is reserved by either Oracle7, Oracle8i, Oracle9i or Oracle10g.

       is_reserved_by_oracle7( $word )
	   Returns a boolean indicating if $word is reserved by Oracle7.

       is_reserved_by_oracle8( $word )
	   Returns a boolean indicating if $word is reserved by Oracle8i.

       is_reserved_by_oracle9( $word )
	   Returns a boolean indicating if $word is reserved by Oracle9i.

       is_reserved_by_oracle10( $word )
	   Returns a boolean indicating if $word is reserved by Oracle10g.

       reserved_by( $word )
	   Returns a list with Oracle versions that reserves $word.

       words
	   Returns a list with all reserved words.

EXPORTS

       Nothing by default. Following subroutines can be exported:

       is_reserved
       is_reserved_by_oracle7
       is_reserved_by_oracle8
       is_reserved_by_oracle9
       is_reserved_by_oracle10
       reserved_by
       words

SEE ALSO

       SQL::ReservedWords

       <http://www.oracle.com/technology/documentation/>

AUTHOR

       Christian Hansen "chansen@cpan.org"

COPYRIGHT

       This program is free software, you can redistribute it and/or modify it under the same terms as Perl itself.

perl v5.8.8							    2008-03-28					   SQL::ReservedWords::Oracle(3pm)
All times are GMT -4. The time now is 07:31 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy