Home » SQL & PL/SQL » SQL & PL/SQL » passing variable to a select query
passing variable to a select query [message #290265] Fri, 28 December 2007 04:21 Go to next message
piscean_n
Messages: 36
Registered: December 2007
Member
how to pass a variable into select query like:

select * from table where table_name = some_variable..

what is the proper syntax to pass variable.
Re: passing variable to a select query [message #290270 is a reply to message #290265] Fri, 28 December 2007 04:27 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
In what context do you use this query?
Is it in a program? If yes, what language?
We need more info in order to help you.
Re: passing variable to a select query [message #290279 is a reply to message #290265] Fri, 28 December 2007 04:45 Go to previous messageGo to next message
piscean_n
Messages: 36
Registered: December 2007
Member
create function(tablename in varchar2)
as

select * from tables where table_name = tablename(input parameter)

m not gettin proper synatx to pass this function input parameter.
Re: passing variable to a select query [message #290282 is a reply to message #290279] Fri, 28 December 2007 04:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you don't post the full and actual code how can you expect we can help you?
Post the function creation statement with line numbers and error message.
You should know this now.

Regards
Michel
Re: passing variable to a select query [message #290284 is a reply to message #290265] Fri, 28 December 2007 04:52 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Try to check out ,

1. Syntax of creating function .
2. use of select statement inside the Procedures/Functions
3. Use of Native sql comands (once above two points are explored)
4. use of refcursors (if needed)

Thumbs Up
Rajuvan.
Re: passing variable to a select query [message #290286 is a reply to message #290282] Fri, 28 December 2007 04:54 Go to previous messageGo to next message
piscean_n
Messages: 36
Registered: December 2007
Member
CREATE OR REPLACE FUNCTION writeMetaDetail(tablename IN VARCHAR2,count_rows IN NUMBER) RETURN NUMBER
AS

  BEGIN

FOR meta IN(SELECT  COLUMN_NAME FROM all_tab_columns  WHERE table_name = :tablename
                                ORDER BY column_id ) 
										
LOOP

UTL_FILE.PUT(OutMetaFile, meta.COLUMN_NAME ||'|');


END LOOP;


PLS-00049: bad bind variable 'TABLENAME'
Re: passing variable to a select query [message #290289 is a reply to message #290286] Fri, 28 December 2007 05:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Where are the line numbers?
Use SQL*Plus to make your test and copy and paste your session.

Now, why do you use ":tablename" and "tablename" parameter of your function?

In addition, this not the only error in your function so I think this is not the actual code.
Once again copy and paste your session, don't fake something.

Regards
Michel
Re: passing variable to a select query [message #290293 is a reply to message #290265] Fri, 28 December 2007 05:22 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member



Quote:

3. Use of Native sql comands (once above two points are explored)
4. use of refcursors (if needed)


In your case use of

OPEN <refcursor> FOR 'querystring'


will be better option. Just search .

Thumbs Up
Rajuvan.
Re: passing dynamic tablename to a select query [message #290348 is a reply to message #290293] Fri, 28 December 2007 07:33 Go to previous messageGo to next message
piscean_n
Messages: 36
Registered: December 2007
Member
CREATE OR REPLACE FUNCTION writeDataToFile(p_tablename IN VARCHAR2) 
RETURN UTL_FILE.FILE_TYPE
AS
v_OutFile  UTL_FILE.FILE_TYPE;
v_FileName  VARCHAR2(400);
v_startDate DATE;
v_count_rows       PLS_INTEGER := 0;
v_seperator CHAR(1) := NULL;
sql_stmt  VARCHAR2(500);


BEGIN

v_FileName := ('00000001_' || TO_CHAR(SYSDATE,'yyyymmddhhmiss') || '_' || p_tablename || '.txt');
v_OutFile := UTL_FILE.FOPEN('ORALOAD', v_FileName, 'w');
v_startDate := SYSDATE;

FOR meta IN(SELECT  column_name FROM all_tab_columns  WHERE table_name = p_tablename
                              ORDER BY column_id ) 
										
LOOP

FOR rec IN (SELECT * FROM p_tablename WHERE Request_id IN(66,145,147))

LOOP
 UTL_FILE.PUT(v_OutFile,is_blank(rec.meta.column_name) || '|' );

end loop;
end loop;


here i want to pass that variable p_tablename in select * from (p_tablenmae)

error :
table or wiew does not exist.

[Updated on: Fri, 28 December 2007 09:05] by Moderator

Report message to a moderator

Re: passing dynamic tablename to a select query [message #290359 is a reply to message #290348] Fri, 28 December 2007 09:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still don't see any line numbers.
Still don't see a copy and paste of execution.
You don't want to follow what I say, I don't want to help you.

Regards
Michel
Re: passing dynamic tablename to a select query [message #290390 is a reply to message #290348] Fri, 28 December 2007 12:14 Go to previous message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
This is an invalid function. Syntax is incorrect. It will not compile. Why don't you do as Michel asks and just post your damn code instead of going back and forth 10 times putting up pieces of it.

Wouldn't it be easier to just cut and paste your session than having to type incorrect and invalid code every time?
Previous Topic: Problem with selecting distinct values
Next Topic: Timestamp date conversion
Goto Forum:
  


Current Time: Wed Dec 07 04:51:15 CST 2016

Total time taken to generate the page: 0.10360 seconds