associative array type for blob column in the table

Question

i am using the code in given link
http://www.oracle.com/technology/oramag/oracle/07-jan/o17odp.html

i chnages that code like this

CREATE TABLE JOBS
(
JOB_ID VARCHAR2(10 BYTE),
JOB_TITLE VARCHAR2(35 BYTE),
MIN_SALARY NUMBER(6),
MAX_SALARY NUMBER(6),
JOBPIC BLOB
)

CREATE OR REPLACE PACKAGE associative_array
AS
-- define an associative array type for each column in the jobs table
TYPE t_job_id IS TABLE OF jobs.job_id%TYPE
INDEX BY PLS_INTEGER;

TYPE t_job_title IS TABLE OF jobs.job_title%TYPE
INDEX BY PLS_INTEGER;

TYPE t_min_salary IS TABLE OF jobs.min_salary%TYPE
INDEX BY PLS_INTEGER;

TYPE t_max_salary IS TABLE OF jobs.max_salary%TYPE
INDEX BY PLS_INTEGER;

TYPE t_jobpic IS TABLE OF jobs.jobpic%TYPE
INDEX BY PLS_INTEGER;

-- define the procedure that will perform the array insert
PROCEDURE array_insert (
p_job_id IN t_job_id,
p_job_title IN t_job_title,
p_min_salary IN t_min_salary,
p_max_salary IN t_max_salary,
p_jobpic IN t_jobpic
);
END associative_array;
/

CREATE OR REPLACE package body SHC_OLD.associative_array as
-- implement the procedure that will perform the array insert

procedure array_insert (p_job_id in t_job_id,
p_job_title in t_job_title,
p_min_salary in t_min_salary,
p_max_salary in t_max_salary,
P_JOBPIC IN T_JOBPIC
) is
begin
forall i in p_job_id.first..p_job_id.last
insert into jobs (job_id,
job_title,
min_salary,
max_salary,
JOBPIC
)
values (p_job_id(i),
p_job_title(i),
p_min_salary(i),
p_max_salary(i),
P_JOBPIC(i)

);
end array_insert;
end associative_array;
/

this procedure is called from .net. from .net sending blob is posiible or not.if yes how

Back to Thread List

Threads: [ Previous | Next ]

Legend
	Guru : 2500 - 1000000 pts
	Expert : 1000 - 2499 pts
	Pro : 500 - 999 pts
	Journeyman : 200 - 499 pts
	Newbie : 0 - 199 pts
	Oracle ACE Director
	Oracle ACE Member
	Oracle Employee ACE
	Helpful Answer (5 pts)
	Correct Answer (10 pts)

Roei Bar · Answer

if you have SQL 2008 you can also use a new feature FileStream here is a sample code // (1) read in the file to be saved as a blob in the database FileStream input = new FileStream(filename FileMode.Open FileAccess.Read); byte[] buffer = new byte[(int)input.Length]; input.Read(buffer 0 buffer.Length); // (2) insert empty blob in the database cn = GetConnection(); tx = cn.BeginTransaction(); cmd = new SqlCommand(dbo.stp_AddBLOB cn tx); cmd.CommandType = System.Data.CommandType.StoredProcedure; SqlDataReader r = cmd.ExecuteReader( System.Data.CommandBehavior.SingleRow); r.Read(); string id = r[0].ToString(); string path = r[1].ToString(); r.Close(); // (3) get the transaction context cmd2 = new SqlCommand(SELECT GET_FILESTREAM_TRANSACTION_CONTEXT() cn tx); Object obj = cmd2.ExecuteScalar(); byte[] txCtx = (byte[])obj; // (4) open the filestream to the blob SafeFileHandle handle = OpenSqlFilestream( path DESIRED_ACCESS_WRITE SQL_FILESTREAM_OPEN_NO_FLAGS txCtx (UInt32)txCtx.Length 0); // (5) open a Filestream to write the blob FileStream output = new FileStream( handle FileAccess.Write buffer.Length false); output.Write( buffer 0 buffer.Length); output.Close();

Roei Bar · Answer

by saying document what do you mean since in bases Image DataType is Array of type Byte A variable-length stream of binary data ranging from 0 to 2 31 -1 (or 2147483647) bytes. meaning its binary data it could be anything not only image

mahammad raffee · Answer

i want to insert a document in jobpic column.how to do it

Roei Bar · Answer

you have a datatype called SqlDbType.Image here is the code for this : // Construct INSERT Command SqlCommand StoredProcedureCommand = new SqlCommand(array_insert conn); addEmp.Parameters.Add(@p_job_id SqlDbType.VarChar 2).Value = pjobid; addEmp.Parameters.Add(@p_job_title SqlDbType.NVarChar 35).Value = ptitle; addEmp.Parameters.Add(@p_min_salary SqlDbType.Number).Value = pminSalery; addEmp.Parameters.Add(@p_max_salary SqlDbType.Number).Value = pmaxSalery; addEmp.Parameters.Add(@ReportsTo SqlDbType.Int).Value = preportsTo; addEmp.Parameters.Add(@P_JOBPIC SqlDbType.Image photo.Length).Value = photo; _conn.Open(); addEmp.ExecuteNonQuery(); _conn.Close();