Home » SQL & PL/SQL » Client Tools » Passing parameter to a SQLPLUS script
Passing parameter to a SQLPLUS script [message #636201] Sun, 19 April 2015 13:10 Go to next message
Asfakul
Messages: 43
Registered: July 2014
Member
Hi all, My requirement is this.

I have two table. Product and Entity. I will call a sqlplus script like <@myscript product > and pass the table name. it will print the content of the table. Simple. I have made one Function which gives a Ref cursor as output.
Code as below.

CREATE OR REPLACE FUNCTION get_detail(p_table IN VARCHAR2,p_out out sys_refcursor)
RETURN boolean
AS
l_table VARCHAR2(20);
BEGIN
    l_table:=p_table;
IF l_table ='product' THEN
    OPEN p_out  FOR 
    SELECT * FROM product;
elsif l_table ='entity' THEN
    OPEN p_out  FOR 
    SELECT * FROM entity;
END IF;
RETURN TRUE;
exception 
WHEN others THEN
    RETURN FALSE;
    raise_application_error(-20001,substr(sqlerrm,1,100));
END;


SQLPLUS script :-



set serveroutput on
set echo off
set feedback off
variable var1 varchar2(20);

DECLARE
o_cursor sys_refcursor;
L_productrow  product%ROWTYPE;
L_entityrow   entity%ROWTYPE;

begin
   :var1:=&1;
   if :var1 = 'product' then
      if get_detail('product',o_cursor) then
       loop
		   fetch o_cursor into L_productrow;
			
		   exit when o_cursor%notfound;
		   dbms_output.put_line(L_productrow.product_id||','||L_productrow.CreateDate);
    
       end loop;
      end if;
   elsif :var1='entity' then
    if get_detail('entity',o_cursor) then
       loop
		   fetch o_cursor into L_entityrow;
			
		   exit when o_cursor%notfound;
		   dbms_output.put_line(L_entityrow.entity_id||','||L_entityrow.CreateDate);
    
       end loop;
   end if;
  end if;
exception 
when others then 
   dbms_output.put_line(dbms_utility.format_error_backtrace);
   dbms_output.put_line(sqlerrm);
   
end;
/



I am getting the error :

ORA-06550: line 7, column 12:
PLS-00357: Table,View Or Sequence reference 'PRODUCT' not allowed in this
context
ORA-06550: line 7, column 4:
PL/SQL: Statement ignored



I am not able to understand what I am doing wrong.
Re: Passing parameter to a SQLPLUS script [message #636205 is a reply to message #636201] Mon, 20 April 2015 00:13 Go to previous messageGo to next message
Littlefoot
Messages: 21367
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Your code, modified to select from Scott's EMP and DEPT tables:
 37  /
Enter value for 1: emp
old   6:    :var1 := &1;
new   6:    :var1 := emp;
   :var1 := emp;
            *
ERROR at line 6:
ORA-06550: line 6, column 13:
PLS-00357: Table,View Or Sequence reference 'EMP' not allowed in this context
ORA-06550: line 6, column 4:
PL/SQL: Statement ignored


SQL> /
Enter value for 1: 'emp'
old   6:    :var1 := &1;
new   6:    :var1 := 'emp';
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
SQL>
Re: Passing parameter to a SQLPLUS script [message #636206 is a reply to message #636201] Mon, 20 April 2015 00:17 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

when others then 
   dbms_output.put_line(dbms_utility.format_error_backtrace);
   dbms_output.put_line(sqlerrm);


What does this give more than without it?
Remove it.
Read WHEN OTHERS.

Re: Passing parameter to a SQLPLUS script [message #636207 is a reply to message #636201] Mon, 20 April 2015 01:14 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3123
Registered: May 2013
Location: World Wide on the Web
Senior Member
Or, do it in a proper way to log the errors in error logging table. See this demo by Michel.
Re: Passing parameter to a SQLPLUS script [message #636212 is a reply to message #636206] Mon, 20 April 2015 02:00 Go to previous messageGo to next message
Asfakul
Messages: 43
Registered: July 2014
Member
Hi Michel, Thanks for pointing it out. I was trying to see at which line exactly I am getting the error.
Re: Passing parameter to a SQLPLUS script [message #636214 is a reply to message #636212] Mon, 20 April 2015 02:03 Go to previous message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I was trying to see at which line exactly I am getting the error.


Which is what you have by default.

Previous Topic: script cutting off
Next Topic: Importing data in csv file . . .
Goto Forum:
  


Current Time: Fri Feb 23 19:22:34 CST 2018

Total time taken to generate the page: 0.02894 seconds